Would like to get a report of machines with agents that have stopped making contact
Unable to get enough information from kace. An advanced search giving me different information that a report using same input.
Last sync before today
Device connected is false
Last sync > (date 3 months past)
====What I would like to get====
----
sort by machine name
has asset
does not have device
asset is not surplus
----
Since an asset and a device are created when provisioned I know they BOTH exist and kace agent is working but at some point some agents cease functioning and we have an asset (permanent record) but no device listed. we have a surplus check box in asset (along with date for permanent asset tracking)
I've spent many hours working through advanced searches variations but there doesn't seem to be options available to run this comparison... Can anyone lend a hand with how to accomplish this?
Would be much appreciated!
Answers (1)
It sounds like you are starting with the machine table, which won't work because there isn't a machine. You need to start with the asset table.
The quickest way to identify computer assets that don't have a matching machine is this query:
SELECT * FROM ORG1.ASSET WHERE
ASSET_TYPE_ID = 5 and
MAPPED_ID is null;
To identify assets not marked as surplus you need to join to the ASSET_DATA_5 table and then add the criteria:
SELECT ASSET.NAME, MAPPED_ID, FIELD_number
FROM ORG1.ASSET
LEFT JOIN ASSET_DATA_5 on ASSET_DATA_5.ID = ASSET.ASSET_DATA_ID
WHERE
-- add criteria for is surplus here
-- e.g. ASSET_DATA_5.FIELD_number = "Yes"
ASSET_TYPE_ID = 5 and
MAPPED_ID is null
The custom fields in ASSET_DATA_5 will have column names like FIELD_10001 and the number will be specific to your SMA. You can get a list of the custom field definitions for the computer asset type with this query:
SELECT * FROM ORG1.ASSET_FIELD_DEFINITION WHERE ASSET_TYPE_ID = 5;
Hope that helps.
