Retrieving host inventory information directly from FOG Database

[ToxicFlyman]

I’'m trying to write a script that will update my computer inventory based on fogs database information. The problem is that the hostname/mac address and inventory information are stored in separate tables. I can’t seem to find a way to tie them together because all of the ids in them are different in each table. I have tried sifting through the php code that fog uses to find out how it identifies and joins that information together(like for making tasks or displaying all host info on the webpage). Does anyone know how I could query the database and get the info for both tables to match together to I can group hosts with their inventory information?

[Wayne Workman]

@ToxicFlyman

It’s a relational database model. their are IDs as PKs and FKs but are not enforced by the DB, but are guaranteed by the FOG front end I believe. Simple inner joins or outer joins will give you what you want. What version of FOG are you using?

[Tom Elliott]

The <tablerelationHostID> to hosts table hostID fields are the primary linkers if that helps out at all.

[ToxicFlyman]

This post is deleted!

[ToxicFlyman]

@Wayne-Workman We are currently running fog version 1.2.0

[Wayne Workman]

@ToxicFlyman

Do you have tables called “hosts” and “hostMac” ? what are the exact table names in FOG 1.2.0 ? (sorry don’t have 1.2.0 anywhere to test on).

What are the exact field names in those two tables? Are these the only tables you want info from or are there others?

[ToxicFlyman]

@Wayne-Workman The hostMAC table fields are hmID, hmHostID, hm MAC, hmDesc. The hosts table fields are hostID, hostName, hostDesc, hostIP, hostImage, hostBuilding, hostCreateDate, hostLastDeploy, hostCreateBy, hostMAC, hostUseAD, hostADDomain, hostADOU, hostADUser, hostADPass, hostProductKey, hostPrinterLevel, hostKernalArgs, hostKernel, hostDevice

[ToxicFlyman]

@Tom-Elliott We are running fog 1.2.0. Does that version have that field connecting them?

[Tom Elliott]

what do you mean?

[Tom Elliott]

Any table that has a relation to the host table will be setup that way.

[ToxicFlyman]

@Tom-Elliott On my hosts table I don’t see a <tablerelationHostID>(or any other table). I am looking the at DB through phpMyAdmin if that helps at all. I can see a hostID field that is labeled as a primary key but I can’t see it being linked anywhere. How can I see the relations betwee them?

[Junkhacker]

have you considered just customizing the inventory report built in to fog?

[Tom Elliott]

I don’t know what you mean.

Look

<tablerelation>HostID is not a literal thing. The <tablerelation> will be different between tables. Inventory table, in particular, I believe is just iHostID. On the Hosts table it’s hostID. Does that help.

[Wayne Workman]

@Tom-Elliott In addition to that, it also helps your understanding a whole lot if you look at the data in those fields, too.

[ToxicFlyman]

@Tom-Elliott Alright. That makes sense. I understand that those fields are relating them together. I’m still not seeing how they’re tied together in my console though. When I look in the hosts table and check hostID: 80(for example). IhostID in inventory doesn’t match up with(or just isn’t there) the hostID(or mac address tied to the hostID). Are those fields normally suppose to be the same if they are related? When I’ve made a few relational databases in access and the fields had the same values between tables to keep things in order. Hopefully all of that made sense.

[Tom Elliott]

The way a relational database is supposed to work is to LIMIT the amount of replicatoin.

So rather than multiple tables linking with duplicated data, they link through an ID of the originating table.

For example.

You’re ID Number in the world is: 14000 (lets just say).

Everybody who wanted to find who you are would not be given a duplicate of yourself. They’d be simply given an ID to relate to your information.

Does that make sense?

In either case, fi the iHostID field did not have a relevant matching dataset, menaing an iHostID record of 80 did not exist for the relevant host, I’d say it’s pretty safe to guess that an inventory has not been recieved or has bene removed previously.

[ToxicFlyman]

@Tom-Elliott Okay. I think I’ve figured out the problem. Some hosts had entries in the inventory but some did not. Some inventory entry’s had a corresponding host entry and some did not. That’s what was so confusing. Apparently when we first created our fog server we had some problems and had to export our DB and import it into a new fog install. The problem was when we started again we registered a lot of pcs again and fog left some of the unused inventory and host entries in the database. I’m not sure if that makes sense of if you’ve seen that before but we know what to do now. The hostID and IhostID fields were linked together in the end(except for the unused entries). Thank you for all the help.

[Wayne Workman]

@ToxicFlyman So you have it figured out?

[Tom Elliott]

For future reference, as well, whenever the system does an upload or download task, it performs an inventory tasking.

Just trying to help. So while the import may have “forgotten” about other hosts in the past, performing a simple tasking would correct this for you automagically.

[ToxicFlyman]

@Wayne-Workman I did end up figuring it out. It turns out that our data had left over data from a previous export/import. So that’s why not all of our entries in the both tables had corresponding entry’s while some did.