/bundles/itninjaweb/img/Breadcrumb_cap_w.png
Good afternoon. Within our asset system, for computers, we've created a status field that identifies whether the computer is 'Active', in 'Inventory', or 'Retired', etc. I'm trying to work up the SQL to create a report that shows machines which are flagged as 'Active' in our Asset system, but which are not appearing in the KBOX inventory. I have simple working SQL for one report that shows some summary information for all the systems current in KBOX inventory:

SELECT NAME, USER, LAST_SYNC, CS_MANUFACTURER, CS_MODEL, BIOS_SERIAL_NUMBER
FROM MACHINE

And I have SQL for Asset that shows all systems in the Asset database which are marked as 'Active':

SELECT ASSET.NAME, ASSET_DATA_5.FIELD_32 as Manufacturer, ASSET_DATA_5.FIELD_33 as Model,
ASSET_DATA_5.FIELD_37 as State, ASSET_DATA_5.FIELD_19 as BIOS
FROM ASSET_DATA_5
JOIN ASSET ON ASSET_DATA_5.ID = ASSET.ASSET_DATA_ID
WHERE ASSET_DATA_5.FIELD_37 LIKE 'Active'

Rather than dumping and merging these into Excel each time I'm trying to figure out if I can create a single report that shows any system (perhaps by BIOS serial or hostname) which is flagged as an active Asset but which isn't appearing in Inventory. I'm not a heavy SQL guy so I'm not sure if this is a simple thing or doable at all?

Thanks.
0 Comments   [ - ] Hide Comments

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.
Answer this question or Comment on this question for clarity

Answers

1
timantheos,

It's actually pretty simple. The Asset table has a column called MAPPED_ID, which is the machine id of it's matching inventory item. If it has no matching inventory item, MAPPED_ID will be zero. You can also use this mapping to determine active machines that haven't checked in for a certain threshold. To use this in your query above, try adding:

AND ASSET.MAPPED_ID = 0

Chris...
Answered 12/29/2010 by: chrisgrim
Senior Purple Belt

Please log in to comment