In the final days I have with my current employer, I've been doing some client cleanup. As you may know, Active Directory System Discovery can make a mess out of your SCCM environment if AD isn't kept clean. We have a lot of records in our DB that just don't have the SCCM Client for a variety of reasons (not enough disk space, WMI is broken, etc).
The good thing about AD System Discovery though is that for each record it finds in AD, it'll look to DNS to see if there's a corresponding DNS record. If there is, it'll create a DDR for that machine. So if you have a lot of junk in AD, and DNS scavenging is set to a reasonable amount of time, you should be seeing machines in your SCCM hierarchy that are actually on your corporate network.
So what I set out to do was look for all the machines that have reported back an AD System Discovery record within the last day (technically, the query below is referencing 23 hours) that doesn't have the latest version of the SCCM client (or the client version is null). Here's the 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 from SMS_R_System where ((DATEDIFF(hh, SMS_R_SYSTEM.AgentTime, getdate()) < 23) and AgentName = "SMS_AD_SYSTEM_DISCOVERY_AGENT") and (SMS_R_System.ClientVersion < "4.00.6487.2000" or SMS_R_System.ClientVersion is null)
This gives me all the machines that I need to look into to fix. These are machines I wasn't able to hit using Client Push installations, or at least I never got a successful client installation for them.
11 Responses
The query have a Syntax error or is using features of the language not supported in design view.
Check the quotation marks in the query. I copied and pasted this query the other day and noticed that the quotation marks were formatted wrong. If you copy from the browser to notepad, and then from notepad to the console it should work.
Thanks for your reply. I had already tried but still get the same error "This Have a query syntax error."
The query is very interesting and I hope it works. Now try to understand where the 'error.
Thanks.
Did you delete the quotes and re-add them? I just noticed that if I took the query right off this page and pasted it into notepad that the quotes aren't correct. If I delete them and add them, they look right.
Ok, forget it. It works great. You were right. Thank you and good job.
No problem. Glad it's working for ya :)
Thanks again, now you have a new fan.
When bringing up new systems; what is the fastest way to get them from showing AD to displaying in SCCM All Systems collection. AD Discovery works fine but it takes cook time... even triggering immediate discovery, updating the collection and enabling dynamic add new resources... Love some tips that will get a new system from AD into SCCM quickly. Thanks!
Hi Larry,
Are you using R3 (sounds like it since you mentioned dynamically adding new resources)? If so, with AD System Discovery, do you have delta discovery set? By default that will discover new machines in AD every 5 minutes. From that point, dynamic discovery should kick in if you have it set. So it should take ~10 minutes for the machine once in AD to fall into the All Systems collection (5 min for delta discovery and then 5 for Dynamic updating of the collection, though I haven't tested what the time interval is for dynamic, but I'm giving a rough guess as it would take about 5 min to show up after delta discovery runs).
If I want to add this string to that statement, how to I do that?
SMS_G_System_NETWORK_ADAPTER_CONFIGURATION.DefaultIPGateway != "IP-NUMBER"
I want to do what Eric J wants to do and add the IP Address information - can someone please tell us how to do this?