Hi,

I am having a weird problem. My original SQL statement used to return me workstation clients which are scanned in the last 90 days. For the past few weeks, it only returned 0.

I tested a new SQL following (www) myitforum.com/forums/SCCM-Report-Systems-Reporting-In-Within-Last-30-Days-m237006 (aspx) and it succeeded, but when I add more criteria into the new SQL, it failed with error "The query statement that you entered is not valid. Enter a valid query statement".

Can anyone please assist? Thank you.

Thank you.

Original SQL

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_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_R_System.Name in (select Name from SMS_R_System where ((DATEDIFF(day, SMS_R_SYSTEM.AgentTime, getdate()) <=90) and AgentName = "SMS_AD_SYSTEM_DISCOVERY_AGENT")) and SMS_R_System.Name in (select Name from SMS_R_System where ((DATEDIFF(day, SMS_R_SYSTEM.AgentTime, getdate()) <=90) and AgentName = "Heartbeat Discovery")) and (SMS_R_System.OperatingSystemNameandVersion like "%6.1%") and SMS_G_System_COMPUTER_SYSTEM.SystemType = "x64-based PC" and SMS_G_System_OPERATING_SYSTEM.Caption like "Microsoft Windows 7%"



Tested SQL with success (last 90 days)

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_G_System_WORKSTATION_STATUS on SMS_G_System_WORKSTATION_STATUS.ResourceId = SMS_R_System.ResourceId
inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId
where SMS_G_System_WORKSTATION_STATUS.LastHardwareScan >= Dateadd (day, -90, Getdate())

SQL with further criteria failed

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_G_System_WORKSTATION_STATUS on SMS_G_System_WORKSTATION_STATUS.ResourceId = SMS_R_System.ResourceId
inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId
where SMS_G_System_WORKSTATION_STATUS.LastHardwareScan >= Dateadd (day, -90, Getdate())

and (SMS_R_System.OperatingSystemNameandVersion like "%6.1%")

and SMS_G_System_COMPUTER_SYSTEM.SystemType = "x64-based PC"

and SMS_G_System_OPERATING_SYSTEM.Caption like "Microsoft Windows 7%"

                              

Let's start with that this not a SQL query but a WQL query. That being said, you can't just throw in new statements in the query without telling it where the information comes from. For example, SMS_G_System_COMPUTER_SYSTEM is not even joined to the query, so you can't use it (yet) as a criteria.

This is WQL

Try this

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_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_SYSTEM on SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_WORKSTATION_STATUS on SMS_G_System_WORKSTATION_STATUS.ResourceId = SMS_R_System.ResourceId where SMS_G_System_OPERATING_SYSTEM.Version like "6.1" and SMS_G_System_SYSTEM.SystemType = "X64-based PC" and SMS_G_System_OPERATING_SYSTEM.Caption = "Microsoft Windows 7 Enterprise" and SMS_G_System_WORKSTATION_STATUS.LastHardwareScan >= Dateadd (day, -90, Getdate()) 

Regards,

Vinod

Hi vinod.

Thanks for the wql statement. Somehow when I copy paste your statement into my device collection, no results come out (members = 0). The only difference I set to your statement is "Microsoft Windows 7%" instead of "Microsoft Windows 7 Enterprise".

In the meantime, I use a workaround which is to 1 collection (say A) for 90 days, then 1 collection (B) with limiting collection from A for Windows 7, and then 1 collection (C) with limiting collection from B for 64bit machine.

I will continue testing with wql and let you guys know of any development.

Thanks

Thanks for the wql statement. Somehow when I copy paste your statement into my device collection, no results come out (members = 0). The only difference I set to your statement is "Microsoft Windows 7%" instead of "Microsoft Windows 7 Enterprise".

Change the "=" to a "Like" and the query will start working.