4,608 questions
Datepart is NOT valid for the Where clause. use getdate and datdiff instead.
https://learn.microsoft.com/en-us/mem/configmgr/develop/core/understand/extended-wmi-query-language
Creation date based device collections
Ranjithkumar Duraisamy
231
Reputation points
Hi Team,
I have been trying to create a dynamic collection based on device creation date. But the below query attracts some devices which has 2019 dated as well. Any thing wrong here or any opportunity to improve 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
WHERE DATEPART(DD, SMS_R_System.CreationDate) <= 3
AND SMS_R_System.operatingSystem LIKE "%Windows Server%"
Microsoft Security | Intune | Configuration Manager | Other
6 answers
Sort by: Most helpful
-
Garth Jones 1,666 Reputation points MVP2022-08-18T12:38:19.643+00:00 -
CherryZhang-MSFT 6,496 Reputation points2022-08-19T06:52:50.547+00:00 Hi @Alin Martinos ,
Thanks for your detailed case description.As GarthJones-9654 said, I did some test. Below is my test result for your reference.
WQL Query:
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,
SMS_R_System.CreationDate
FROM SMS_R_System
WHERE DATEDiff(DD, SMS_R_System.CreationDate, Getdate()) <= 20
AND SMS_R_System.OperatingSystemNameandVersion LIKE "%Server%"
-
Ranjithkumar Duraisamy 231 Reputation points
2022-08-19T10:17:03.13+00:00 Thank you so much for both of your input. When try to see the same via Query Design, syntax error pops up. Isn't weird?
-
Garth 5,801 Reputation points2022-08-19T10:57:17.007+00:00 It should be fine. Not all queries work in design viewer. As long as you see request in your collection you are fine.
-
Ranjithkumar Duraisamy 231 Reputation points
2022-08-19T11:15:05.173+00:00 Thank you for clarifying the same.