locked
SQL query to get the without SCCM agent for a Server RRS feed

  • Question

  • Hello every one,

    Can I get the SQL query for a server without a SCCM agent . 

    Friday, July 4, 2014 6:00 AM

Answers

  • Here you go!!

    Add the version for windows 2012 server if you have them as well in your environment

       v_R_System.Netbios_Name0
      ,v_R_System.Full_Domain_Name0
      ,v_R_System.User_Name0
      ,case
    When v_R_System.Operating_System_Name_and0 LIKE N'%Server 6.1'THEN 'Windows Server 2008 R2'
    When v_R_System.Operating_System_Name_and0 LIKE N'%Server 6.0'THEN 'Windows Server 2008'
    When v_R_System.Operating_System_Name_and0 LIKE N'%Server 5.2'THEN 'Windows Server 2003 R2'
     ELSE 'Unknown Version' END AS 'OS version'
    FROM
      v_R_System
    WHERE
      v_R_System.Operating_System_Name_and0 LIKE N'%server%' and v_R_System.Client0 is null



    Kindly mark as answer/Vote as helpful if a reply from anybody helped you in this forum. Delphin



    Monday, July 7, 2014 9:53 AM

All replies

  • You can see the ones without client straight from the console, just add "Client" -column to your Devices view. Just make sure your discovery methods are configured properly so that all the machines in your environment get discovered.
    For the query, you can easily copy the All Client Systems built in query and modify it to suit your needs.
    • Edited by Narcoticoo Friday, July 4, 2014 6:16 AM
    Friday, July 4, 2014 6:15 AM
  • You can use the following query for Collection or regular query (limit it to All System collection):

    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.OperatingSystemNameandVersion like "%Server%" and SMS_R_System.Client is null


    Please take a moment to Vote as Helpful and/or Mark as Answer where applicable. Thanks.

    Friday, July 4, 2014 3:26 PM
  • Idan thank for response

    you shared the collection query . Can I get the SQL query for this 

    Monday, July 7, 2014 9:11 AM
  • select
    vrs.Netbios_Name0, *
    from
    v_R_System vrs
    where
    (
    (vrs.Client0 = 0 or Client0 is null)
    and vrs.Operating_System_Name_and0 like '%server%'
    )

    Torsten Meringer | http://www.mssccmfaq.de

    Monday, July 7, 2014 9:37 AM
  • Here you go!!

    Add the version for windows 2012 server if you have them as well in your environment

       v_R_System.Netbios_Name0
      ,v_R_System.Full_Domain_Name0
      ,v_R_System.User_Name0
      ,case
    When v_R_System.Operating_System_Name_and0 LIKE N'%Server 6.1'THEN 'Windows Server 2008 R2'
    When v_R_System.Operating_System_Name_and0 LIKE N'%Server 6.0'THEN 'Windows Server 2008'
    When v_R_System.Operating_System_Name_and0 LIKE N'%Server 5.2'THEN 'Windows Server 2003 R2'
     ELSE 'Unknown Version' END AS 'OS version'
    FROM
      v_R_System
    WHERE
      v_R_System.Operating_System_Name_and0 LIKE N'%server%' and v_R_System.Client0 is null



    Kindly mark as answer/Vote as helpful if a reply from anybody helped you in this forum. Delphin



    Monday, July 7, 2014 9:53 AM