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

solved 1
venkat p 2020-04-27T15:18:58+05:30 6 Answers 146 views Beginner
1

Answers ( 6 )

  2. Guru Vaidya
    1
    2020-04-28T18:15:58+05:30
    Reply

    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’

  4. venkat p
    0
    2020-04-27T17:21:38+05:30
    Reply

    Servers discovered and Sccm agent installed on those servers. But not added into collections for server OS patching.

    • harjit
      1
      2020-04-27T17:29:38+05:30
      Reply

      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.

      Best answer
  6. venkat p
    0
    2020-04-27T16:40:26+05:30
    Reply

    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

    • Anoop C Nair
      1
      2020-04-27T17:12:44+05:30
      Reply

      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

  8. Anoop C Nair
    0
    2020-04-27T16:10:29+05:30
    Reply

    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?

Leave an answer

Sorry, you do not have permission to answer to this question .