ITCM query to find computers which have not executed a collect task for a specified time

Document ID : KB000030382
Last Modified Date : 14/02/2018
Show Technical Document Details

1- In DSM GUI, create a new Query (target = Computers)

New Query

 

2- In Query Designer, Click on Advanced Argument

2-Advanced.jpg

 

 

3- Put these values :

Pseudo text : Software Inventory Configuration > 10 days

Table :  -Any-

Additional WHERE clause :

select s.object_uuid from STATMOD s, ncmodcfg m where m.moname='Software Inventory Configuration' and s.moid=m.moid and s.mdomid=m.domainid and m.domainid=(select domain_id from ca_n_tier where domain_uuid=(select set_val_uuid from ca_settings where set_id=1)) and stdate<datediff(ss,convert(datetime,'19700101'), getutcdate())-60*60*24*10

 

Advanced fields

 

 

Remarks :

  • "Pseudo text" field is only a display field so put what you want in it
  • The SQL Query should be put in only one line. Do not put a carriage return in "Additional WHERE clause" field.
  • Replace "Software Inventory Configuration" in SQL Query by name of appropriate collect task name.
  • 60*60*24*10 is the number of seconds in 10 days (60 seconds * 60 minutes * 24 hours * 10 days). This could be changed with desired time.
  • If collect task comes from the Enterprise server, use this query instead :

select s.object_uuid from STATMOD s, ncmodcfg m where m.moname='Software Inventory Configuration' and s.moid=m.moid and s.mdomid=m.domainid and m.domainid=(select domain_id from ca_n_tier where domain_type=1) and stdate<datediff(ss,convert(datetime,'19700101'), getutcdate())-60*60*24*10

 

4- Click on OK

This query returns the computers which have a last execution date for collect task "Software Inventory Configuration" older than 10 Days.

4-Final.jpg

 

A limitation of this is :

If a machine has never executed the collect task, it will not be returned by the query as it has no last execution date.