SoloDig

There might be the scenario where you will be looking for computers which doesnt have particular application installed on.Create a new collection and copy the below  simple WQL query which list you all computers there by you can advertise the application package onto it.

select SMS_G_System_SYSTEM.Name from  SMS_R_System inner join SMS_G_System_SYSTEM on SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SYSTEM.Name not in (select SMS_G_System_SYSTEM.Name from  SMS_R_System inner join SMS_G_System_SYSTEM on SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like “Adobe acrobat reader“)

change the application(adobe actobat reader) the one you are looking at.

If you are looking for a collection to query all the machines only from a particular OU not the SUB OU .Let say ,I have Top OU called Workstations\comp(120 systems) in eskon.net domain and in turn it has 3 sub OU’s OU1(10 systems),OU2(20 systems),OU3(49 systems).

Here is the simple query to get machines only from TOP OU called workstations.

SELECT sms_r_system.resourceid,
       sms_r_system.resourcetype,
       sms_r_system.name,
       sms_r_system.smsuniqueidentifier,
       sms_r_system.resourcedomainorworkgroup,
       sms_r_system.client
FROM   sms_r_system
WHERE  sms_r_system.systemouname = “eskon.net/workstations/comp” /*find all machines in that OU (but also finds machines in subOUs */
       AND sms_r_system.resourceID NOT IN (SELECT resourceID FROM sms_r_system where systemouname LIKE “eskon.net/workstations/comp/%”) /*remove records for machines in subOUs*/

The above collection should display only machines that are present in comp OU not from the SUB OU’s (like OU1,OU2,OU3).

some times its hard to know the systems which are having issues in reporting their inventory and even some systems doesnt reporte anything to the site server though the client is installled on it.

Here is the collection which gives you all computers which doesnt report inventory for x days(taken 15days).

select SMS_R_System.ResourceID,SMS_R_System.ResourceType,SMS_R_System.Name,SMS_R_System.SMSUniqueIdentifier,SMS_R_System.ResourceDomainORWorkgroup,SMS_R_System.Client from SMS_R_System where ResourceId in (select SMS_R_System.ResourceID from SMS_R_System inner join SMS_G_System_WORKSTATION_STATUS on SMS_G_System_WORKSTATION_STATUS.ResourceID = SMS_R_System.ResourceId where DATEDIFF(dd,SMS_G_System_WORKSTATION_STATUS.LastHardwareScan,GetDate()) > 15) or ResourceId not in (select ResourceID from SMS_G_System_WORKSTATION_STATUS)

Good luck in troubleshooting why it doesnt report

Hints:

solution that i tried got worked.

1.check if you are able to connect to admin$ and see the log file i.e ccmexec.log( service is running or not)

2.check it the machine has problem with WMI repository, this is the most possible issue to resolve. you can the simple batch script to reolve WMI that i posted on my web(http://solodig.com/scripts/how-get-the-rid-of-systems-which-has-wmi-issue/) This can be done Via psexec on list of machines.the script will compile the MOF file with site server and it resolve most of the systems that failed in sending hinv .Use the collection commander tool (http://sourceforge.net/projects/smscollctr/) to intiate the hinv on list of machine single time.it should gives you the result that has been run or not ?