SCCM 2012 SQL Query

Bruce Meyer 20 Reputation points
2023-05-25T12:14:30.8233333+00:00

Need help with an sql query that can pull below.

NameClient TypeClientPrimary UserCurrent UserSiteCodeActivityActiveClient VersionAD SiteDomainHW ScanOperating SystemOS BuildLast Activity

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,772 questions
Microsoft Configuration Manager
0 comments No comments
{count} votes

Accepted answer
  1. AllenLiu-MSFT 39,131 Reputation points Microsoft Vendor
    2023-05-26T02:57:41.11+00:00

    Hi, @Bruce Meyer

    Thank you for posting in Microsoft Q&A forum.

    
    SELECT distinct
      v_R_System.Name0 AS 'Name',
      CASE 
        WHEN v_R_System.Client_Type0 = '0' THEN 'Legacy'
        WHEN v_R_System.Client_Type0 = '1' THEN 'Advanced'
        ELSE 'Unknown'
      END AS 'Client Type',
      v_UserMachineRelationship.UniqueUserName AS 'Client Primary User',
      v_R_System.User_Name0 AS 'Current User',
      v_FullCollectionMembership.SiteCode AS 'SiteCode',
      v_R_System.Active0 AS 'Activity',
      v_R_System.Client_Version0 AS 'Active Client Version',
      v_R_System.AD_Site_Name0 AS 'AD Site',
      v_R_System.Full_Domain_Name0 AS 'Domain',
      v_GS_WORKSTATION_STATUS.LastHWScan AS 'HW Scan',
      v_GS_OPERATING_SYSTEM.Caption0 AS 'Operating System',
      v_GS_OPERATING_SYSTEM.BuildNumber0 AS 'OS Build',
      v_GS_WORKSTATION_STATUS.LastHWScan AS 'Last Activity'
    FROM v_R_System
    LEFT JOIN v_UserMachineRelationship on v_UserMachineRelationship.MachineResourceID = v_R_System.ResourceID
    LEFT JOIN v_R_User ON v_R_User.Unique_User_Name0 = v_UserMachineRelationship.UniqueUserName
    LEFT JOIN v_GS_WORKSTATION_STATUS ON v_GS_WORKSTATION_STATUS.ResourceID = v_R_System.ResourceID
    LEFT JOIN v_GS_OPERATING_SYSTEM ON v_GS_OPERATING_SYSTEM.ResourceID = v_R_System.ResourceID
    LEFT JOIN v_FullCollectionMembership ON v_FullCollectionMembership.ResourceID = v_R_System.ResourceID
    WHERE v_R_System.Active0 = '1' 
    ORDER BY v_R_System.Name0
    
    

    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 "Add 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.


1 additional answer

Sort by: Most helpful
  1. AllenLiu-MSFT 39,131 Reputation points Microsoft Vendor
    2023-05-26T07:14:22.0633333+00:00

    Hi, @Bruce Meyer

    Add the IP address:

    SELECT distinct
      v_R_System.Name0 AS 'Name',
      CASE 
        WHEN v_R_System.Client_Type0 = '0' THEN 'Legacy'
        WHEN v_R_System.Client_Type0 = '1' THEN 'Advanced'
        ELSE 'Unknown'
      END AS 'Client Type',
      v_UserMachineRelationship.UniqueUserName AS 'Client Primary User',
      v_R_System.User_Name0 AS 'Current User',
      v_FullCollectionMembership.SiteCode AS 'SiteCode',
      v_R_System.Active0 AS 'Activity',
      v_R_System.Client_Version0 AS 'Active Client Version',
      v_R_System.AD_Site_Name0 AS 'AD Site',
      v_R_System.Full_Domain_Name0 AS 'Domain',
      v_GS_WORKSTATION_STATUS.LastHWScan AS 'HW Scan',
      v_GS_OPERATING_SYSTEM.Caption0 AS 'Operating System',
      v_GS_OPERATING_SYSTEM.BuildNumber0 AS 'OS Build',
      v_GS_WORKSTATION_STATUS.LastHWScan AS 'Last Activity',
      LEFT(IPAddress0, LEN('%.%.%.%.%.%.%') - patIndex(',', IPAddress0)) AS 'IP Address'
    FROM v_R_System
    LEFT JOIN v_UserMachineRelationship on v_UserMachineRelationship.MachineResourceID = v_R_System.ResourceID
    LEFT JOIN v_R_User ON v_R_User.Unique_User_Name0 = v_UserMachineRelationship.UniqueUserName
    LEFT JOIN v_GS_WORKSTATION_STATUS ON v_GS_WORKSTATION_STATUS.ResourceID = v_R_System.ResourceID
    LEFT JOIN v_GS_OPERATING_SYSTEM ON v_GS_OPERATING_SYSTEM.ResourceID = v_R_System.ResourceID
    LEFT JOIN v_FullCollectionMembership ON v_FullCollectionMembership.ResourceID = v_R_System.ResourceID
    LEFT JOIN v_GS_NETWORK_ADAPTER_CONFIGUR on v_GS_NETWORK_ADAPTER_CONFIGUR.ResourceID = v_R_System.ResourceID
    WHERE v_R_System.Active0 = '1' and IPAddress0 IS NOT NULL
    ORDER BY v_R_System.Name0