Query to detect newly imaged MECM Systems?

Question

Hi All – We need to send software to all PCs that were imaged by MECM after 03/18/22. Any system before this date doesn’t need it. This will also be an ongoing, long-term rollout of all new PCs, not just the previous 3 months.

To ID these systems, one query I thought of was to set it by the Operating System install date attribute. However, we can’t use that as we regularly upgrade our Win 10/11 system builds and so if a PC was imaged in February, then we upgrade it to 22H2 (for example) when released, it will then get the software it already has.

I then created a collection based on the system creation date:

select * from SMS_R_System where SMS_R_System.CreationDate >= "3/19/2022 3:45:10 AM"

This seems to yield better results. However, some systems are showing up even though they were imaged in October of 2021. I was thinking it’s because the server team upgraded MECM and the local MECM client on PCs was updated, which may have changed the ‘Creation Date?’ Not sure.
Can you think of a better query for this purpose?

Answer 1

I saw your similar post on reddit, and if the whenCreated is working for you, sure, use that. In some situations that may or may not work--it depends upon how your image devices. It "could happen" where in AD, the computer object was created 2 years ago; and for whatever reason, the box is reimaged "today". The whenCreated would be old, and the win32_operatingsystem.installdate will be new... but you can't 100% rely on the OS.installdate either, because that is updated when (for example) a box is upgraded from Windows 10 1909 to Windows 20 to 20H2. The box was "born a while ago"... so it also wouldn't "quite fit" your perfect scenario.

I suspect though... that based on your comments in reddit, everyone understands there may not be a perfect solution. So if the occasional device gets <whatever> reinstalled; you can also just shrug and say "oops, sorry".

Answer 2

Hi, @net1994

Thank you for posting in Microsoft Q&A forum.

The CreationDate is based on the agent installation date. So it's not accurate.
We can use InstallDate in class SMS_G_System_OPERATING_SYSTEM to get the install time of the OS, so the query can be:

select *  from SMS_R_System inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceId = SMS_R_System.ResourceId   
WHERE SMS_G_System_OPERATING_SYSTEM.InstallDate> "3/19/2022 3:45:10 AM"  

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

just a suggestion... it would require you to do a Baseline/CI deployment, then custom inventory extension; but might yield more reliable results:

https://tcsmug.org/blogs/sherry-kissinger/551-windows-10-inplace-update-history-inventory

Answer 4

Thanks Sherry,

After a bit of thought what I'll do is just make a collection with all devices with the problematic software. Then one without it. Then create a new collection to exclude those that already have it. Then I'll know which ones are without the problem software and send out the new client to them.

The whenCreated attribute I did add into our Sys Discovery method. It looks great and was the answer to my prayers. Well, then the console would crash everytime I tried to export the results. Oh man. So while the whenCreated and OS install date or when the device first had the SCCM client installed (entered the SQL db for the first time) are good data points, it's too much scotch tape to get them into something coherent in excel. And probably more of a nightmare trying to create collections on either of those 3 options. We use UI++ when imaging to automatically join to domain and then put them in the appropriate site workstation OU. Great. But a technician told me sometimes they will take the same device and re-image it and give it the same name. So palm to the face on that one.

At the end of the day, I wish I went with the simplicity of going the exclude collection route. I tried to get 'too fancy' and wasted too much time in the process. So the exclude collection we go.....

Answer 5

Hi, @net1994

Thanks very much for your feedback. We're glad that you finally have a solution to address this need. Here's a short summary for the problem, we believe this will help other users to search for useful information more quickly.

Problem/Symptom:
We need to send software to all PCs that were imaged by MECM after 03/18/22. Any system before this date doesn’t need it.
If we use Operating System install date attribute to filter the PCs, the install date will change after the OS of PC upgrade to a new version. So it's not accurate.
If we use SMS_R_System.CreationDate to filter the PCs, the CreationDate is based on the agent installation date, if the client reinstall or upgrade, the CreationDate will change. So it's not accurate too.

So there doesn't seem to be a perfect solution.

Solution/Workaround:
Create a collection A with all devices with the problematic software. And create a collection B without the software. Then deploy the software to collection B.

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.