/build/static/layout/Breadcrumb_cap_w.png

Systems Management Question


Would like to get a report of machines with agents that have stopped making contact

12/04/2019 185 views

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!

0 Comments   [ + ] Show comments

Comments


All 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. 


Answered 12/06/2019 by: chucksteel
Red Belt