First of all, am I right in thinking that SMS_MonthlyUsageSummary gets the last usage from the last software scan, and may have been used since then, but won't show it?

If so, I am trying to make a collection with systems that haven't used Microsoft Visio 2010 in the last 30 days, and have also checked in in the last 30 days. This is what I have so far:

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 

INNER JOIN SMS_MonthlyUsageSummary on SMS_R_SYSTEM.ResourceID = SMS_MonthlyUsageSummary.ResourceID    
INNER JOIN SMS_MeteredFiles ON SMS_MonthlyUsageSummary.FileID = SMS_MeteredFile.MeteredFileID
INNER JOIN SMS_G_SYSTEM_LastSoftwareScan on
SMS_R_SYSTEM.ResourceID=SMS_G_SYSTEM_LastSoftwareScan.ResourceID

WHERE DateDiff(day, SMS_MonthlyUsageSummary.LastUsage, GetDate()) > 30  
AND SMS_MeteredFiles.RuleID = 16777317
AND WHERE DateDiff(day, SMS_G_SYSTEM_LastSoftwareScan.LastScanDate, GetDate()) <30

I apologise for butchering the language in advance. As I said, I am a rookie.

Can you try this.

To use the query it is necessary that software metering for the executable is in place!

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.ResourceId in 
            (select distinct SMS_R_System.ResourceId
            from SMS_R_System
            inner join SMS_G_System_SoftwareUsageData
            on SMS_G_System_SoftwareUsageData.ResourceID = SMS_R_System.ResourceId
            where SMS_G_System_SoftwareUsageData.FileName = "visio.exe"
            and SMS_G_System_SoftwareUsageData.FileVersion
            like "14.0%"
            and SMS_G_System_SoftwareUsageData.StartTimeLocal > DATEADD(day,-30,GetDate()))

• Edited by Frederick Dicaire 19 hours 47 minutes ago

First of all, am I right in thinking that SMS_MonthlyUsageSummary gets the last usage from the last software scan, and may have been used since then, but won't show it?

FYI, no it this information does NOT come from Software Scan cycle, it comes Software Metering data.

Frederick query use AI data which comes from Hardware Inventory.

IMO, Software Inventory is all but useless.

Can you try this.

To use the query it is necessary that software metering for the executable is in place!

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.ResourceId in 
            (select distinct SMS_R_System.ResourceId
            from SMS_R_System
            inner join SMS_G_System_SoftwareUsageData
            on SMS_G_System_SoftwareUsageData.ResourceID = SMS_R_System.ResourceId
            where SMS_G_System_SoftwareUsageData.FileName = "visio.exe"
            and SMS_G_System_SoftwareUsageData.FileVersion
            like "14.0%"
            and SMS_G_System_SoftwareUsageData.StartTimeLocal > DATEADD(day,-30,GetDate()))

• Edited by Frederick Dicaire Monday, August 24, 2015 12:04 PM
• Proposed as answer by Joyce LMicrosoft contingent staff, Moderator 43 minutes ago

The way I got my last query was by sticking together a bunch of queries that I found and hoping it worked. The one you wrote could be interpreted by the wizard, which makes it much easier for me to fiddle and tweak it.

Thanks :)

I had a look and found the dates don't match up. I had clients with a last software scan done in 2013/14, and was wondering why my query wasn't working.

Knowing it matches with a hardware scan helps immensely. Thanks.