As a Microsoft Endpoint Configuration Manager (MEMCM / MECM / SCCM / ConfigMgr) Adminstrators. Knowing WQL is important! But How do you use Datediff & GetDate in WQL? This blog will talk about for you.
Normally I wouldn’t post a reply to blog post but in this case I had too. Using Datediff & GetDate in WQL within MECM can be hard. Creating collections use dates is difficult.
Datediff & GetDate in WQL
So I was reading this blog Creating a Collection Query based upon Hardware Inventory, and this blog too and I notice that Chris posted a blog about editing your ConfigMgr / SMS database.
- You SHOULD never edit your database! This is just asking for trouble.
- Editing your database directly is NOT SUPPORTED by Microsoft; it is one thing to use the SDK to make changes to ConfigMgr / SMS but…
- The problem he is trying to solved was fixed in SMS 2003 SP1 or SP2.
Now the problem as I see it, is he want to create a collection of all WS that have provided hardware inventory in the last 5 days. Well back in SMS 2003 SP1 (maybe SP2) Getdate(), DateDiff() and DateAdd() were added to WQL functionality. https://msdn.microsoft.com/en-us/library/cc146121.aspx. So there is no need to do this hack and it is unnecessary to do this.
In Greg’s blog, He provide the WQL code to:
- All systems that have reported a LastHardwareScan date within the last 30 days:
- All systems that have been discovered since midnight:
I have created this WQL query to more or less matching what Chris is looking for.
select * 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()) < 5
Looking at the query above you can see that it has both datediff and getdate WQL functions! From this you should be able to create your own WQL queries.