Sccm SQL/wql query for last patched date and reboot time

Question

Hi guys, trying to run a SQL query using quick fix engineering to get last 1 month patched details for a specific collection, but I am not able to sort last 1 month, it’s showing all data…can anyone help with the query..

solved 1
Dinesh Cs 2020-06-14T17:37:49+05:30 12 Answers 2800 views Beginner
2

Answers ( 12 )

  2. Dinesh Cs
    0
    2020-11-24T05:27:29+05:30
    Reply

    Hi Karthi, I am trying to add the KB title to this query, but fails..can u help to add KB title, description?

  4. Anoop C Nair
    0
    2020-06-20T10:58:03+05:30
    Reply

    Report if there any error here

    • Dinesh Cs
      0
      2020-06-22T04:23:12+05:30
      Reply

      Query looks good, syntax error is fixed by karthi..But it needs some sorting in Hotfix ID and last Install date….last Install date is showing 2019 but KB ID is 06/2020 and some servers showing old KB ID..I tried to play around,but couldn’t fix…

  6. Dinesh Cs
    0
    2020-06-15T16:05:43+05:30
    Reply

    I am also getting syntax error by running both the reports…

  8. Vinod Pandit
    0
    2020-06-15T09:00:43+05:30
    Reply

    Hello i am getting errors while running 1st query

    • Karthikeyan
      0
      2020-06-15T16:38:49+05:30
      Reply

      what error message ?

      Thanks
      Karthikeyan

      • Karthikeyan
        1
        2020-06-16T03:29:34+05:30
        Reply

        Hi,

        I got it. Few attributes are not added in your system discovery that’s why error.

        Try this query:
        ———————————————————————–
        Select distinct
        –Vrs.ResourceID as ‘ResourceID’,
        Vrs.name0 as ‘MachineName’,
        Vos.Caption0 as ‘Operating System(HW)’,
        case when cdr.IsClient =1 then ‘Yes’ else ‘No’ end as ‘SCCMClient Status’,
        Vcc.ClientStateDescription as ‘SCCMHealth Status’,
        WSLN.Value as ‘Build Version’,
        Vos.CSDVersion0 as ‘Service Pack’,
        Vos.Version0 as ‘OS Version’,
        Vcs.SystemType0 as ‘OS Type’,
        CASE
        WHEN Vse.ChassisTypes0 in (‘1’) THEN ‘Virtual’
        WHEN Vse.ChassisTypes0 in (‘8′,’9′,’10’,’12’,’14’,’11’,’17’,’31’,’32’) THEN ‘Laptop’
        WHEN Vse.ChassisTypes0 in (‘2′,’3′,’4′,’5′,’6′,’7′,’13’,’15’,’16’,’35’) THEN ‘Desktop’
        WHEN Vse.ChassisTypes0 in (’23’) THEN ‘RackMount’
        when Vse.ChassisTypes0 is Null Then ‘NA’
        Else ‘Others’ END as ‘ChassisType’,
        Vcs.Manufacturer0 as ‘Manufacturer’,
        CASE WHEN CAST(Vcs.Manufacturer0 as NVarchar(255)) LIKE ‘%LENOVO%’
        THEN CAST(Vcsp.Version0 as NVarchar(255)) ELSE CAST(Vcs.Model0 as NVarchar(255)) END AS ‘Model’,
        cdr.MACAddress as ‘MACAddress’,
        Seu.SerialNumber0 as ‘SerialNumber’,
        Vse.SMBIOSAssetTag0 as ‘AssetTag’,
        convert(varchar,VPM.TOTALPHYSICALMEMORY0 / (1024)/1000 ) as ‘Total_RAM_Size_GB’,
        (select sum(Size0)/1024 from v_GS_LOGICAL_DISK where ResourceID = vrs.ResourceID) as ‘Total_HDD_Size_GB’,
        (select min(Freespace0)/1024 from v_GS_LOGICAL_DISK where ResourceID = vrs.ResourceID) as ‘Total_Free_Size_GB’,
        case when cdr.clientstate=0 then ‘No Pending Reboot’ else ‘Pending Reboot’ end as ‘Reboot Status’,
        case when cdr.cnisonline=1 then ‘Online’ else ‘Offline’ end as ‘Current Online Status’,
        Cdr.ClientVersion as ‘SCCM Version’,
        Cdr.cnlastonlinetime as ‘Last Online Status’,
        Cdr.lastpolicyrequest as ‘Last Policy Request’,
        cdr.LastDDR as ‘Last Heartbeat Scan’,
        Cdr.LastHardwareScan as ‘Last HW Scan’,
        cdr.LastSoftwareScan as ‘Last SW Scan’,
        Vus.LastScanTime as ‘Last SUP Scan’,
        Vus.lasterrorcode as ‘LastSUPScanStatus’,
        Vus.lastscanpackagelocation as ‘SUPLocationPath’,
        cdr.LastMPServerName as ‘LastMPServerName’,
        cdr.LastStatusMessage as ‘Last Status Msg’,
        (select MAX(AgentTime) from v_AgentDiscoveries where AgentName = ‘SMS_AD_SYSTEM_DISCOVERY_AGENT’ and ResourceID = Vrs.ResourceID) as ‘Last AD SysDisc Scan’,
        cdr.ADLastLogonTime as ‘Last AD Logon Stamp’,
        DateDiff(D, Convert(VarChar, Cdr.cnlastonlinetime,100), GetDate()) as ‘Last Online Status(Days)’,
        DateDiff(D, Convert(VarChar, Cdr.lastpolicyrequest,100), GetDate()) as ‘Last Policy Request(Days)’,
        DateDiff(D, Convert(VarChar, Cdr.LastDDR,100), GetDate()) as ‘Last Heartbeat Scan(Days)’,
        DateDiff(D, Convert(VarChar, Cdr.LastHardwareScan,100), GetDate()) as ‘Last HW Scan(Days)’,
        DateDiff(D, Convert(VarChar, Cdr.LastSoftwareScan,100), GetDate()) as ‘Last SW Scan(Days)’,
        DateDiff(D, Convert(VarChar, Vus.LastScanTime,100), GetDate()) as ‘Last SUP Scan(Days)’,
        DateDiff(D, Convert(VarChar, (select MAX(AgentTime) from v_AgentDiscoveries where AgentName = ‘SMS_AD_SYSTEM_DISCOVERY_AGENT’ and ResourceID = Vrs.ResourceID),100), GetDate()) as ‘Last AD SysDisc Scan (Days)’,
        DateDiff(D, Convert(VarChar, cdr.ADLastLogonTime,100), GetDate()) as ‘Last ADLogon Stamp (Days)’,
        FORMAT(Vos.InstallDate0, ‘MM/dd/yyyy HH:mm:ss’) as ‘OS Install Date’,
        DateDiff(D, Vos.InstallDate0, GetDate()) ‘OS Install Date (Days)’,
        FORMAT(Vos.LastBootUpTime0, ‘MM/dd/yyyy HH:mm:ss’) as ‘LastBootUpDate’,
        DateDiff(D, Convert(VarChar, Vos.LastBootUpTime0,100), GetDate()) as ‘Last BootUp Date (Days)’,
        Tpm.PhysicalPresenceVersionInfo0 as ‘TPMVersion’,
        Vpb.Name0 as ‘BiosName’,
        Vpb.BIOSVersion0 as ‘BiosVersion’,
        Vp.Name0 as ‘ProcessorName’,
        Vp.NumberOfCores0 as ‘NumberOfCores’,
        Vp.NumberOfLogicalProcessors0 as ‘NumberOfLogicalProcessors’,
        Vra.SMS_Assigned_Sites0 as ‘AssignedSite’,
        cdr.ADSiteName as ‘ADSiteName’,
        Vrs.Full_Domain_Name0 as ‘FullDomainName’,
        (Vrs.Name0 + ‘.’ + Vrs.Full_Domain_Name0) as ‘FQDN’,
        Vrs.USER_DOMAIN0 as ‘UserDomain’,
        Vrs.USER_NAME0 as ‘LastLogonID’,
        Vcud.TOPCONSOLEUSER00 as ‘TopConsoleUserID’,
        CASE
        WHEN Vcud.TOPCONSOLEUSER00 IS NOT NULL THEN SUBSTRING(Vcud.TOPCONSOLEUSER00,CHARINDEX(‘’,Vcud.TOPCONSOLEUSER00)+1,CHARINDEX(‘’,Vcud.TOPCONSOLEUSER00)+10)
        ELSE Vrs.USER_NAME0
        END as ‘LogonID’,
        Vru.Mail0 as ‘EMailID’
        FROM v_R_System as Vrs
        LEFT JOIN V_GS_PC_BIOS as Vpb ON Vpb.ResourceID = Vrs.ResourceID
        LEFT JOIN V_GS_SYSTEM_ENCLOSURE_UNIQUE as Seu ON Seu.ResourceID = Vrs.ResourceID
        LEFT JOIN V_GS_X86_PC_MEMORY as Vpm ON VPM.ResourceID = Vrs.ResourceID
        LEFT JOIN V_GS_COMPUTER_SYSTEM as Vcs ON VCS.ResourceID = Vrs.ResourceID
        LEFT JOIN V_GS_TPM as Tpm ON tpm.ResourceID = Vrs.ResourceID
        LEFT JOIN SYSTEM_CONSOLE_USAGE_DATA as Vcud ON Vcud.MachineID = Vrs.ResourceID
        LEFT JOIN V_GS_COMPUTER_SYSTEM_PRODUCT Vcsp ON Vcsp.ResourceID = Vrs.ResourceID
        LEFT JOIN V_GS_SYSTEM_ENCLOSURE as Vse ON Vse.ResourceID = Vrs.ResourceID
        LEFT JOIN V_GS_OPERATING_SYSTEM as Vos ON Vos.ResourceID = Vrs.ResourceID
        LEFT JOIN v_UpdateScanStatus as Vus ON Vus.ResourceID = Vrs.ResourceID
        LEFT JOIN V_GS_LOGICAL_DISK as Vgl ON Vgl.ResourceID = Vrs.ResourceID
        LEFT JOIN V_GS_PROCESSOR as Vp ON Vp.ResourceID = Vrs.ResourceID
        LEFT JOIN v_RA_System_SMSAssignedSites as Vra ON Vra.ResourceID = VRS.ResourceID
        LEFT JOIN v_R_User as Vru ON Vru.User_Name0 = Vrs.User_Name0
        LEFT JOIN fn_GetWindowsServicingStates() as WSS on WSS.Build = Vos.Version0 and WSS.Branch = Vrs.OSBranch01
        LEFT JOIN fn_GetWindowsServicingLocalizedNames() as WSLN On WSS.Name = WSLN.Name
        LEFT JOIN System_MAC_Addres_ARR as Mac ON Mac.ItemKey = Vrs.ResourceID
        LEFT JOIN V_GS_INSTALLED_SOFTWARE as App ON App.ResourceID = Vrs.ResourceID
        LEFT JOIN V_CombinedDeviceResources as Cdr ON Cdr.MachineID = Vrs.ResourceID
        LEFT JOIN v_CH_ClientSummary as Vcc ON Vcc.ResourceID = Vrs.ResourceID
        LEFT JOIN v_GS_VIDEO_CONTROLLER as Vvc ON Vvc.ResourceID = Vrs.ResourceId
        INNER JOIN V_FullCollectionMembership_Valid as Col ON Col.ResourceID = Vrs.ResourceID
        WHERE Col.CollectionID = ‘SMS00001’
        and Vrs.Active0 = 1 and Vrs.Obsolete0 = 0 and Vrs.Decommissioned0 = 0
        and (Vos.operatingSystem0 like ‘%Windows Server%’)
        order by vrs.name0
        ————————————————————————-
        Thanks
        Karthikeyan

  10. Karthikeyan
    3
    2020-06-14T19:14:24+05:30
    Reply

    Hi,

    KB detailed query:
    ————————————
    Select distinct
    Vrs.ResourceID as ‘ResourceID’,
    Vrs.name0 as ‘MachineName’,
    (select max(Convert(VarChar, InstalledOn0,100)) from v_GS_QUICK_FIX_ENGINEERING where ResourceID = Vrs.ResourceID ) as ‘Recent_Install_Date’,
    (select max(HotFixID0) from v_GS_QUICK_FIX_ENGINEERING where ResourceID = Vrs.ResourceID ) as ‘Recent_HotfixID’,
    (select max(InstalledBy0) from v_GS_QUICK_FIX_ENGINEERING where ResourceID = Vrs.ResourceID ) as ‘Recent_Installed_By’,
    Vos.Caption0 as ‘Operating System(HW)’,
    case when cdr.IsClient =1 then ‘Yes’ else ‘No’ end as ‘SCCMClient Status’,
    Vcc.ClientStateDescription as ‘SCCMHealth Status’,
    WSLN.Value as ‘Build Version’,
    Vos.CSDVersion0 as ‘Service Pack’,
    Vos.Version0 as ‘OS Version’,
    Vcs.SystemType0 as ‘OS Type’,
    CASE
    WHEN Vse.ChassisTypes0 in (‘1’) THEN ‘Virtual’
    WHEN Vse.ChassisTypes0 in (‘8′,’9′,’10’,’12’,’14’,’11’,’17’,’31’,’32’) THEN ‘Laptop’
    WHEN Vse.ChassisTypes0 in (‘2′,’3′,’4′,’5′,’6′,’7′,’13’,’15’,’16’,’35’) THEN ‘Desktop’
    WHEN Vse.ChassisTypes0 in (’23’) THEN ‘RackMount’
    when Vse.ChassisTypes0 is Null Then ‘NA’
    Else ‘Others’ END as ‘ChassisType’,
    Vcs.Manufacturer0 as ‘Manufacturer’,
    CASE WHEN CAST(Vcs.Manufacturer0 as NVarchar(255)) LIKE ‘%LENOVO%’
    THEN CAST(Vcsp.Version0 as NVarchar(255)) ELSE CAST(Vcs.Model0 as NVarchar(255)) END AS ‘Model’,
    cdr.MACAddress as ‘MACAddress’,
    Seu.SerialNumber0 as ‘SerialNumber’,
    Vse.SMBIOSAssetTag0 as ‘AssetTag’,
    convert(varchar,VPM.TOTALPHYSICALMEMORY0 / (1024)/1000 ) as ‘Total_RAM_Size_GB’,
    (select sum(Size0)/1024 from v_GS_LOGICAL_DISK where ResourceID = vrs.ResourceID) as ‘Total_HDD_Size_GB’,
    (select min(Freespace0)/1024 from v_GS_LOGICAL_DISK where ResourceID = vrs.ResourceID) as ‘Total_Free_Size_GB’,
    case when cdr.clientstate=0 then ‘No Pending Reboot’ else ‘Pending Reboot’ end as ‘Reboot Status’,
    case when cdr.cnisonline=1 then ‘Online’ else ‘Offline’ end as ‘Current Online Status’,
    Cdr.ClientVersion as ‘SCCM Version’,
    Cdr.cnlastonlinetime as ‘Last Online Status’,
    Cdr.lastpolicyrequest as ‘Last Policy Request’,
    cdr.LastDDR as ‘Last Heartbeat Scan’,
    Cdr.LastHardwareScan as ‘Last HW Scan’,
    cdr.LastSoftwareScan as ‘Last SW Scan’,
    Vus.LastScanTime as ‘Last SUP Scan’,
    Vus.lasterrorcode as ‘LastSUPScanStatus’,
    Vus.lastscanpackagelocation as ‘SUPLocationPath’,
    cdr.LastMPServerName as ‘LastMPServerName’,
    cdr.LastStatusMessage as ‘Last Status Msg’,
    (select MAX(AgentTime) from v_AgentDiscoveries where AgentName = ‘SMS_AD_SYSTEM_DISCOVERY_AGENT’ and ResourceID = Vrs.ResourceID) as ‘Last AD SysDisc Scan’,
    cdr.ADLastLogonTime as ‘Last AD Logon Stamp’,
    DateDiff(D, Convert(VarChar, Cdr.cnlastonlinetime,100), GetDate()) as ‘Last Online Status(Days)’,
    DateDiff(D, Convert(VarChar, Cdr.lastpolicyrequest,100), GetDate()) as ‘Last Policy Request(Days)’,
    DateDiff(D, Convert(VarChar, Cdr.LastDDR,100), GetDate()) as ‘Last Heartbeat Scan(Days)’,
    DateDiff(D, Convert(VarChar, Cdr.LastHardwareScan,100), GetDate()) as ‘Last HW Scan(Days)’,
    DateDiff(D, Convert(VarChar, Cdr.LastSoftwareScan,100), GetDate()) as ‘Last SW Scan(Days)’,
    DateDiff(D, Convert(VarChar, Vus.LastScanTime,100), GetDate()) as ‘Last SUP Scan(Days)’,
    DateDiff(D, Convert(VarChar, (select MAX(AgentTime) from v_AgentDiscoveries where AgentName = ‘SMS_AD_SYSTEM_DISCOVERY_AGENT’ and ResourceID = Vrs.ResourceID),100), GetDate()) as ‘Last AD SysDisc Scan (Days)’,
    DateDiff(D, Convert(VarChar, cdr.ADLastLogonTime,100), GetDate()) as ‘Last ADLogon Stamp (Days)’,
    FORMAT(Vos.InstallDate0, ‘MM/dd/yyyy HH:mm:ss’) as ‘OS Install Date’,
    DateDiff(D, Vos.InstallDate0, GetDate()) ‘OS Install Date (Days)’,
    FORMAT(Vos.LastBootUpTime0, ‘MM/dd/yyyy HH:mm:ss’) as ‘LastBootUpDate’,
    DateDiff(D, Convert(VarChar, Vos.LastBootUpTime0,100), GetDate()) as ‘Last BootUp Date (Days)’,
    Tpm.PhysicalPresenceVersionInfo0 as ‘TPMVersion’,
    Vpb.Name0 as ‘BiosName’,
    Vpb.BIOSVersion0 as ‘BiosVersion’,
    Vp.Name0 as ‘ProcessorName’,
    Vp.NumberOfCores0 as ‘NumberOfCores’,
    Vp.NumberOfLogicalProcessors0 as ‘NumberOfLogicalProcessors’,
    Vra.SMS_Assigned_Sites0 as ‘AssignedSite’,
    cdr.ADSiteName as ‘ADSiteName’,
    Vrs.Full_Domain_Name0 as ‘FullDomainName’,
    (Vrs.Name0 + ‘.’ + Vrs.Full_Domain_Name0) as ‘FQDN’,
    Vrs.USER_DOMAIN0 as ‘UserDomain’,
    Vrs.USER_NAME0 as ‘LastLogonID’,
    Vcud.TOPCONSOLEUSER00 as ‘TopConsoleUserID’,
    CASE
    WHEN Vcud.TOPCONSOLEUSER00 IS NOT NULL THEN SUBSTRING(Vcud.TOPCONSOLEUSER00,CHARINDEX(‘\’,Vcud.TOPCONSOLEUSER00)+1,CHARINDEX(‘\’,Vcud.TOPCONSOLEUSER00)+10)
    ELSE Vrs.USER_NAME0
    END as ‘LogonID’,
    Vru.Mail0 as ‘EMailID’
    FROM v_R_System as Vrs
    LEFT JOIN V_GS_PC_BIOS as Vpb ON Vpb.ResourceID = Vrs.ResourceID
    LEFT JOIN V_GS_SYSTEM_ENCLOSURE_UNIQUE as Seu ON Seu.ResourceID = Vrs.ResourceID
    LEFT JOIN V_GS_X86_PC_MEMORY as Vpm ON VPM.ResourceID = Vrs.ResourceID
    LEFT JOIN V_GS_COMPUTER_SYSTEM as Vcs ON VCS.ResourceID = Vrs.ResourceID
    LEFT JOIN V_GS_TPM as Tpm ON tpm.ResourceID = Vrs.ResourceID
    LEFT JOIN SYSTEM_CONSOLE_USAGE_DATA as Vcud ON Vcud.MachineID = Vrs.ResourceID
    LEFT JOIN V_GS_COMPUTER_SYSTEM_PRODUCT Vcsp ON Vcsp.ResourceID = Vrs.ResourceID
    LEFT JOIN V_GS_SYSTEM_ENCLOSURE as Vse ON Vse.ResourceID = Vrs.ResourceID
    LEFT JOIN V_GS_OPERATING_SYSTEM as Vos ON Vos.ResourceID = Vrs.ResourceID
    LEFT JOIN v_UpdateScanStatus as Vus ON Vus.ResourceID = Vrs.ResourceID
    LEFT JOIN V_GS_LOGICAL_DISK as Vgl ON Vgl.ResourceID = Vrs.ResourceID
    LEFT JOIN V_GS_PROCESSOR as Vp ON Vp.ResourceID = Vrs.ResourceID
    LEFT JOIN v_RA_System_SMSAssignedSites as Vra ON Vra.ResourceID = VRS.ResourceID
    LEFT JOIN v_R_User as Vru ON Vru.User_Name0 = Vrs.User_Name0
    LEFT JOIN fn_GetWindowsServicingStates() as WSS on WSS.Build = Vos.Version0 and WSS.Branch = Vrs.OSBranch01
    LEFT JOIN fn_GetWindowsServicingLocalizedNames() as WSLN On WSS.Name = WSLN.Name
    LEFT JOIN System_MAC_Addres_ARR as Mac ON Mac.ItemKey = Vrs.ResourceID
    LEFT JOIN V_GS_INSTALLED_SOFTWARE as App ON App.ResourceID = Vrs.ResourceID
    LEFT JOIN V_CombinedDeviceResources as Cdr ON Cdr.MachineID = Vrs.ResourceID
    LEFT JOIN v_CH_ClientSummary as Vcc ON Vcc.ResourceID = Vrs.ResourceID
    LEFT JOIN v_GS_VIDEO_CONTROLLER as Vvc ON Vvc.ResourceID = Vrs.ResourceId
    INNER JOIN V_FullCollectionMembership_Valid as Col ON Col.ResourceID = Vrs.ResourceID
    WHERE Col.CollectionID = ‘SMS00001’
    and Vrs.Active0 = 1 and Vrs.Obsolete0 = 0 and Vrs.Decommissioned0 = 0
    and (Vrs.operatingSystem0 like ‘%Windows Server%’ or Vos.Caption0 like ‘%Windows Server%’)
    order by vrs.name0
    ———————————————-
    Thanks
    karthikeyan

  12. Karthikeyan
    1
    2020-06-14T17:42:29+05:30
    Reply

    Hi,

    Use below SQL query for server inventory. for patch detail i will share another sql query.
    ————————————————————————————————————————————-
    Select distinct
    –Vrs.ResourceID as ‘ResourceID’,
    Vrs.name0 as ‘MachineName’,
    Vos.Caption0 as ‘Operating System(HW)’,
    case when cdr.IsClient =1 then ‘Yes’ else ‘No’ end as ‘SCCMClient Status’,
    Vcc.ClientStateDescription as ‘SCCMHealth Status’,
    WSLN.Value as ‘Build Version’,
    Vos.CSDVersion0 as ‘Service Pack’,
    Vos.Version0 as ‘OS Version’,
    Vcs.SystemType0 as ‘OS Type’,
    CASE
    WHEN Vse.ChassisTypes0 in (‘1’) THEN ‘Virtual’
    WHEN Vse.ChassisTypes0 in (‘8′,’9′,’10’,’12’,’14’,’11’,’17’,’31’,’32’) THEN ‘Laptop’
    WHEN Vse.ChassisTypes0 in (‘2′,’3′,’4′,’5′,’6′,’7′,’13’,’15’,’16’,’35’) THEN ‘Desktop’
    WHEN Vse.ChassisTypes0 in (’23’) THEN ‘RackMount’
    when Vse.ChassisTypes0 is Null Then ‘NA’
    Else ‘Others’ END as ‘ChassisType’,
    Vcs.Manufacturer0 as ‘Manufacturer’,
    CASE WHEN CAST(Vcs.Manufacturer0 as NVarchar(255)) LIKE ‘%LENOVO%’
    THEN CAST(Vcsp.Version0 as NVarchar(255)) ELSE CAST(Vcs.Model0 as NVarchar(255)) END AS ‘Model’,
    cdr.MACAddress as ‘MACAddress’,
    Seu.SerialNumber0 as ‘SerialNumber’,
    Vse.SMBIOSAssetTag0 as ‘AssetTag’,
    convert(varchar,VPM.TOTALPHYSICALMEMORY0 / (1024)/1000 ) as ‘Total_RAM_Size_GB’,
    (select sum(Size0)/1024 from v_GS_LOGICAL_DISK where ResourceID = vrs.ResourceID) as ‘Total_HDD_Size_GB’,
    (select min(Freespace0)/1024 from v_GS_LOGICAL_DISK where ResourceID = vrs.ResourceID) as ‘Total_Free_Size_GB’,
    case when cdr.clientstate=0 then ‘No Pending Reboot’ else ‘Pending Reboot’ end as ‘Reboot Status’,
    case when cdr.cnisonline=1 then ‘Online’ else ‘Offline’ end as ‘Current Online Status’,
    Cdr.ClientVersion as ‘SCCM Version’,
    Cdr.cnlastonlinetime as ‘Last Online Status’,
    Cdr.lastpolicyrequest as ‘Last Policy Request’,
    cdr.LastDDR as ‘Last Heartbeat Scan’,
    Cdr.LastHardwareScan as ‘Last HW Scan’,
    cdr.LastSoftwareScan as ‘Last SW Scan’,
    Vus.LastScanTime as ‘Last SUP Scan’,
    Vus.lasterrorcode as ‘LastSUPScanStatus’,
    Vus.lastscanpackagelocation as ‘SUPLocationPath’,
    cdr.LastMPServerName as ‘LastMPServerName’,
    cdr.LastStatusMessage as ‘Last Status Msg’,
    (select MAX(AgentTime) from v_AgentDiscoveries where AgentName = ‘SMS_AD_SYSTEM_DISCOVERY_AGENT’ and ResourceID = Vrs.ResourceID) as ‘Last AD SysDisc Scan’,
    cdr.ADLastLogonTime as ‘Last AD Logon Stamp’,
    DateDiff(D, Convert(VarChar, Cdr.cnlastonlinetime,100), GetDate()) as ‘Last Online Status(Days)’,
    DateDiff(D, Convert(VarChar, Cdr.lastpolicyrequest,100), GetDate()) as ‘Last Policy Request(Days)’,
    DateDiff(D, Convert(VarChar, Cdr.LastDDR,100), GetDate()) as ‘Last Heartbeat Scan(Days)’,
    DateDiff(D, Convert(VarChar, Cdr.LastHardwareScan,100), GetDate()) as ‘Last HW Scan(Days)’,
    DateDiff(D, Convert(VarChar, Cdr.LastSoftwareScan,100), GetDate()) as ‘Last SW Scan(Days)’,
    DateDiff(D, Convert(VarChar, Vus.LastScanTime,100), GetDate()) as ‘Last SUP Scan(Days)’,
    DateDiff(D, Convert(VarChar, (select MAX(AgentTime) from v_AgentDiscoveries where AgentName = ‘SMS_AD_SYSTEM_DISCOVERY_AGENT’ and ResourceID = Vrs.ResourceID),100), GetDate()) as ‘Last AD SysDisc Scan (Days)’,
    DateDiff(D, Convert(VarChar, cdr.ADLastLogonTime,100), GetDate()) as ‘Last ADLogon Stamp (Days)’,
    FORMAT(Vos.InstallDate0, ‘MM/dd/yyyy HH:mm:ss’) as ‘OS Install Date’,
    DateDiff(D, Vos.InstallDate0, GetDate()) ‘OS Install Date (Days)’,
    FORMAT(Vos.LastBootUpTime0, ‘MM/dd/yyyy HH:mm:ss’) as ‘LastBootUpDate’,
    DateDiff(D, Convert(VarChar, Vos.LastBootUpTime0,100), GetDate()) as ‘Last BootUp Date (Days)’,
    Tpm.PhysicalPresenceVersionInfo0 as ‘TPMVersion’,
    Vpb.Name0 as ‘BiosName’,
    Vpb.BIOSVersion0 as ‘BiosVersion’,
    Vp.Name0 as ‘ProcessorName’,
    Vp.NumberOfCores0 as ‘NumberOfCores’,
    Vp.NumberOfLogicalProcessors0 as ‘NumberOfLogicalProcessors’,
    Vra.SMS_Assigned_Sites0 as ‘AssignedSite’,
    cdr.ADSiteName as ‘ADSiteName’,
    Vrs.Full_Domain_Name0 as ‘FullDomainName’,
    (Vrs.Name0 + ‘.’ + Vrs.Full_Domain_Name0) as ‘FQDN’,
    Vrs.USER_DOMAIN0 as ‘UserDomain’,
    Vrs.USER_NAME0 as ‘LastLogonID’,
    Vcud.TOPCONSOLEUSER00 as ‘TopConsoleUserID’,
    CASE
    WHEN Vcud.TOPCONSOLEUSER00 IS NOT NULL THEN SUBSTRING(Vcud.TOPCONSOLEUSER00,CHARINDEX(‘\’,Vcud.TOPCONSOLEUSER00)+1,CHARINDEX(‘\’,Vcud.TOPCONSOLEUSER00)+10)
    ELSE Vrs.USER_NAME0
    END as ‘LogonID’,
    Vru.Mail0 as ‘EMailID’
    FROM v_R_System as Vrs
    LEFT JOIN V_GS_PC_BIOS as Vpb ON Vpb.ResourceID = Vrs.ResourceID
    LEFT JOIN V_GS_SYSTEM_ENCLOSURE_UNIQUE as Seu ON Seu.ResourceID = Vrs.ResourceID
    LEFT JOIN V_GS_X86_PC_MEMORY as Vpm ON VPM.ResourceID = Vrs.ResourceID
    LEFT JOIN V_GS_COMPUTER_SYSTEM as Vcs ON VCS.ResourceID = Vrs.ResourceID
    LEFT JOIN V_GS_TPM as Tpm ON tpm.ResourceID = Vrs.ResourceID
    LEFT JOIN SYSTEM_CONSOLE_USAGE_DATA as Vcud ON Vcud.MachineID = Vrs.ResourceID
    LEFT JOIN V_GS_COMPUTER_SYSTEM_PRODUCT Vcsp ON Vcsp.ResourceID = Vrs.ResourceID
    LEFT JOIN V_GS_SYSTEM_ENCLOSURE as Vse ON Vse.ResourceID = Vrs.ResourceID
    LEFT JOIN V_GS_OPERATING_SYSTEM as Vos ON Vos.ResourceID = Vrs.ResourceID
    LEFT JOIN v_UpdateScanStatus as Vus ON Vus.ResourceID = Vrs.ResourceID
    LEFT JOIN V_GS_LOGICAL_DISK as Vgl ON Vgl.ResourceID = Vrs.ResourceID
    LEFT JOIN V_GS_PROCESSOR as Vp ON Vp.ResourceID = Vrs.ResourceID
    LEFT JOIN v_RA_System_SMSAssignedSites as Vra ON Vra.ResourceID = VRS.ResourceID
    LEFT JOIN v_R_User as Vru ON Vru.User_Name0 = Vrs.User_Name0
    LEFT JOIN fn_GetWindowsServicingStates() as WSS on WSS.Build = Vos.Version0 and WSS.Branch = Vrs.OSBranch01
    LEFT JOIN fn_GetWindowsServicingLocalizedNames() as WSLN On WSS.Name = WSLN.Name
    LEFT JOIN System_MAC_Addres_ARR as Mac ON Mac.ItemKey = Vrs.ResourceID
    LEFT JOIN V_GS_INSTALLED_SOFTWARE as App ON App.ResourceID = Vrs.ResourceID
    LEFT JOIN V_CombinedDeviceResources as Cdr ON Cdr.MachineID = Vrs.ResourceID
    LEFT JOIN v_CH_ClientSummary as Vcc ON Vcc.ResourceID = Vrs.ResourceID
    LEFT JOIN v_GS_VIDEO_CONTROLLER as Vvc ON Vvc.ResourceID = Vrs.ResourceId
    INNER JOIN V_FullCollectionMembership_Valid as Col ON Col.ResourceID = Vrs.ResourceID
    WHERE Col.CollectionID = ‘SMS00001’
    and Vrs.Active0 = 1 and Vrs.Obsolete0 = 0 and Vrs.Decommissioned0 = 0
    and (Vrs.operatingSystem0 like ‘%Windows Server%’ or Vos.Caption0 like ‘%Windows Server%’)
    order by vrs.name0

    Thanks
    karthikeyan

    Best answer

Leave an answer

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