SCCM SQL query to get machines with user id, name, email, last logon

Hello Friends, Good evening and how are you?

Today someone asked me to create one SQL query to get the machine details like hostname, IP Address, RAM, total HDD size, Free Space, Manufacturer, Model, Last Heartbeat, Users name, email ID, Last logged on user name.

Also, We will fetch the domain details and CCM client active status.

Download the below query and enjoy 👍

select distinct Netbios_Name0 as "Machine name",vrs.User_Name0 as "User ID",vru.Full_User_Name0 as "User Full Name",

       AD_Site_Name0 as "AD Site",

case

        when vrs.Active0 = 1 then 'YES'

        when vrs.Active0 = 0 then 'NO'

        else 'NO STATUS'

    end AS "Machine Active",

case

        when vrs.Client0 = 1 then 'YES'

        when vrs.Client0 = 0 then 'NO'

        else 'NO STATUS'

    end AS "Client Active",  Client_Version0 as "Client Version",vrs.Full_Domain_Name0 as "Domain Name",

       vru.User_Principal_Name0 as "Email",Last_Logon_Timestamp0 as "Last Login Time",os.Caption0 as "Operating System",

       os.Version0 as "OS Version",os.CSDVersion0 as "OS Service Pack",vcs.Manufacturer0 as "Manufacture", vcs.Model0 as "HW Model",

       vgp.Name0 as "Processor", vip.IPAddress0 as "IP Address", vip.MACAddress0 as "MAC Address", RAM.Capacity0/1024 as "RAM Gb",

       vcs.SystemType0 as "Machine Type", vld.Size0/1024 as "HD Size GB", vld.FreeSpace0/1024 as "HD Free GB",

       vad.AgentTime as "Last Heartbeat", vws.LastHWScan as "Last HW Scan",vcs.UserName0 as "Last Logged User"

      

 

       from dbo.v_R_System vrs

 join v_R_User vru on vrs.User_Name0=vru.User_Name0

join V_GS_operating_system os on vrs.ResourceID=os.ResourceID

join v_GS_COMPUTER_SYSTEM vcs on vrs.ResourceID=vcs.ResourceID

join v_GS_PROCESSOR vgp on vrs.ResourceID=vgp.ResourceID

join v_GS_NETWORK_ADAPTER_CONFIGUR vip on vrs.ResourceID=vip.ResourceID and vip.IPAddress0 is not null

join v_GS_PHYSICAL_MEMORY ram on vrs.ResourceID=ram.ResourceID

 join v_GS_LOGICAL_DISK vld on vrs.ResourceID=vld.ResourceID and vld.Size0 is not null

join v_AgentDiscoveries vad on vrs.ResourceID=vad.ResourceID and vad.AgentName = 'Heartbeat Discovery'

join v_GS_WORKSTATION_STATUS vws on vrs.ResourceID=vws.ResourceID

order by vrs.Netbios_Name0