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   [ + ] Show 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.

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
Answer this question or Comment on this question for clarity