Server report
Question
Hi all, I tried hard but unable to find an automate query. I need to find out the servers which is not part of any collection. Please help me on this anyone please
Lost your password? Please enter your email address. You will receive a link and will create a new password via email.
Answers ( 6 )
I hope you are looking for machine part of different collections bcoz you cant have any machine not being part of any collection, by default it will be at-least part of “All Systems” and “server or workstation” automatically depending on the server platform and agent is installed.
You may try below example (WIN7-A is the sample machine name) – This will give you the result where the machine is part of various different collections.
select FCM.CollectionId from dbo.v_R_System r join
dbo.v_FullCollectionMembership FCM on R.ResourceID = FCM.ResourceID join
dbo.v_Collection C on C.CollectionID = FCM.CollectionID Where R.Name0
= ‘WIN7-A’
Servers discovered and Sccm agent installed on those servers. But not added into collections for server OS patching.
By default, all systems whether server or workstation will be added automatically in the All Desktops and Server Clients collection, provided they have the SCCM client installed. If you need the servers to show up in a different custom collection, then you can create that collection and use a query to add them in various ways including from specific AD OUs.
Servers are reporting to sccm and status is healthy. But we noticed that lot of servers not added into collections. I need to figure out those servers through sql query or collection query. Anoop sir please help me on this
Which collection? Are client is installed on those servers ?
You didn’t answer my question…
Can you please answer my questions?
Try the following sql query to find out servers with no client ..
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 SMS_R_System.Client is null
Hello – What you meant by any collection? All the SCCM managed servers or discovered servers will be part all Devices collection…
Can you please clarify which collection you are referring to …
any particular custom collection?
Are you looking for collection query or SQL query?