Connect Solarwinds WebHelpDesk to FOG Database for Inventory information
-
I am looking to hook our WebHelpDesk into FOG to pull information from the database to populate Assets in WHD.
We currently do this with WHD and JAMF and I know WHD can reach out to other DBs to do this as well.
What type of DB does FOG use? MySQL, PostgreSQL, something else?
Is there IP restrictions that I need to change for an outside device to access the DB?
Is there a built in DB account? & cab I make a read only account?
Thanks!
-
@astrugatch said in Connect Solarwinds WebHelpDesk to FOG Database for Inventory information:
What type of DB does FOG use? MySQL, PostgreSQL, something else?
MySQL
Is there IP restrictions that I need to change for an outside device to access the DB?
None since a FOG storage node is an external device that reaches back to a FOG Master node for database access.
Is there a built in DB account? & cab I make a read only account?
I would create a custom user ID specific to your application for this remote access, that way any future updates to fog don’t break your db connector. The FOG storage nodes have their own IDs and don’t use the built in FOG ID. You will need to create your db connector account so that it has rights from off the FOG server host server.
-
So I updated MySQL with the command:
GRANT ALL ON fog.* TO ‘fogwhdaccess’@‘IP OF WHD’ IDENTIFIED BY ‘PASSWORD’;
On WHD I installed mysql connector and it definitely sees the DB. I’m now running into an issue that is probably more on Solarwinds’ side which i have an error about timezones
The server time zone value 'EDT' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.The fix that I find online for other things using this connector all seem to point to needing to update the config for the connection to include UTC but I’m doing this through the GUI and don’t know where that config is found (again more of a WHD issue).
-
@astrugatch If its a linux style timezone it would be something like
america/new yorkI’ve also see it writtenEST5EDTor just-0500. Sorry I can’t be much help there but the issue is on the server end and not the database end of the connection. -
Thanks. I’ve put in a ticket with solarwinds and am waiting back from them. I’ll write up a doc on setting this up if there is any interest for other admins (that happen to use both WHD and FOG) I know WHD isn’t too popular so I don’t know how many people would be interested.
-
@astrugatch The fog database is pretty simple, so you shouldn’t have any real issues extracting data from the database. You might consider creating a database view on the fog server side to collect the data in a form you can use it easily on the app server side. The FOG installer doesn’t touch non-fog created views (actually I’m pretty sure FOG doesn’t use custom views at all).
-
Do you happen to know the mapping of the database fields to the table that inventory report spits out?
-
@astrugatch This should get you started.
MariaDB [fog]> describe inventory; +----------------+--------------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------+--------------+------+-----+-------------------+----------------+ | iID | int(11) | NO | PRI | NULL | auto_increment | | iHostID | int(11) | NO | UNI | NULL | | | iPrimaryUser | varchar(50) | NO | | NULL | | | iOtherTag | varchar(50) | NO | | NULL | | | iOtherTag1 | varchar(50) | NO | | NULL | | | iCreateDate | timestamp | NO | | CURRENT_TIMESTAMP | | | iDeleteDate | datetime | NO | | NULL | | | iSysman | varchar(250) | NO | | NULL | | | iSysproduct | varchar(250) | NO | | NULL | | | iSysversion | varchar(250) | NO | | NULL | | | iSysserial | varchar(250) | NO | | NULL | | | iSystype | varchar(250) | NO | | NULL | | | iBiosversion | varchar(250) | NO | | NULL | | | iBiosvendor | varchar(250) | NO | | NULL | | | iBiosdate | varchar(250) | NO | | NULL | | | iMbman | varchar(250) | NO | | NULL | | | iMbproductname | varchar(250) | NO | | NULL | | | iMbversion | varchar(250) | NO | | NULL | | | iMbserial | varchar(250) | NO | | NULL | | | iMbasset | varchar(250) | NO | | NULL | | | iCpuman | varchar(250) | NO | | NULL | | | iCpuversion | varchar(250) | NO | | NULL | | | iCpucurrent | varchar(250) | NO | | NULL | | | iCpumax | varchar(250) | NO | | NULL | | | iMem | varchar(250) | NO | | NULL | | | iHdmodel | varchar(250) | NO | | NULL | | | iHdfirmware | varchar(250) | NO | | NULL | | | iHdserial | varchar(250) | NO | | NULL | | | iCaseman | varchar(250) | NO | | NULL | | | iCasever | varchar(250) | NO | | NULL | | | iCaseserial | varchar(250) | NO | | NULL | | | iCaseasset | varchar(250) | NO | | NULL | | | iSystemUUID | varchar(255) | NO | | NULL | | +----------------+--------------+------+-----+-------------------+----------------+ 33 rows in set (0.00 sec) MariaDB [fog]> describe hosts; +------------------+---------------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------------+---------------+------+-----+---------------------+----------------+ | hostID | int(11) | NO | PRI | NULL | auto_increment | | hostName | varchar(16) | NO | UNI | NULL | | | hostDesc | longtext | NO | | NULL | | | hostIP | varchar(25) | NO | MUL | NULL | | | hostImage | int(11) | NO | | NULL | | | hostBuilding | int(11) | NO | | NULL | | | hostCreateDate | timestamp | NO | | CURRENT_TIMESTAMP | | | hostLastDeploy | datetime | NO | | NULL | | | hostCreateBy | varchar(50) | NO | | NULL | | | hostUseAD | char(1) | NO | MUL | NULL | | | hostADDomain | varchar(250) | NO | | NULL | | | hostADOU | longtext | NO | | NULL | | | hostADUser | varchar(250) | NO | | NULL | | | hostADPass | varchar(250) | NO | | NULL | | | hostADPassLegacy | longtext | NO | | NULL | | | hostProductKey | longtext | YES | | NULL | | | hostPrinterLevel | varchar(2) | NO | | NULL | | | hostKernelArgs | varchar(250) | NO | | NULL | | | hostKernel | varchar(250) | NO | | NULL | | | hostDevice | varchar(250) | NO | | NULL | | | hostInit | longtext | YES | | NULL | | | hostPending | enum('0','1') | NO | | NULL | | | hostPubKey | longtext | NO | | NULL | | | hostSecToken | longtext | NO | | NULL | | | hostSecTime | timestamp | NO | | 0000-00-00 00:00:00 | | | hostPingCode | varchar(20) | YES | | NULL | | | hostExitBios | longtext | YES | | NULL | | | hostExitEfi | longtext | YES | | NULL | | | hostEnforce | enum('0','1') | NO | | 1 | | +------------------+---------------+------+-----+---------------------+----------------+The foreign link would be
hosts.hostID = inventory.iHostID -
That’s perfect. the Inventory table got me basically everything I need. For some reason I can only read one table for the import so I had to choose between the Host and Inventory tables but luckily my hostname and othertag are already the same so I could treat them the same.
-
For anyone interested in doing this you need to make sure you have the older version of the MySQL Java Connector (5.1.47) or you get the timezone error I was seeing. You can also only choose one of the tables/views to map so you will either need all of your data in the host table or the inventory table.
I am currently running WHD 12.5.2 and will be updating to 12.7 soon so I will update this if there are any changes.
-
@astrugatch said in Connect Solarwinds WebHelpDesk to FOG Database for Inventory information:
For some reason I can only read one table for the import
This is why I recommended creating a view so you can create a join in the view and it presents the data as a single pane of glass to the external application.
-
My MySQL knowledge is pretty shallow. For this situation I’ve got enough data, but I’ll read up and maybe change things then.