All SQL Server Installed Version and Computer Details using Hardware Inventory 2

All IE Version using Software Inventory 2

All Active Workstations Client Hardware Inventory Status 2

All Active Servers Client Hardware Inventory Status 4

All Active Workstations Client Software Inventory Status 4

All Active Servers Client Software Inventory Status 5

All PCs with particular software inventory Exe file 6

All IE Version using Software Inventory 6

All Site Servers Issue Hardware Inventory components status 7

All Workstations Assets Inventory details status 7

All Workstations Assets Inventory details status 10

All Client and Inventory Health Report 13

All Total Hardware Inventory within 30 Days machines details 15

All Total Hardware Inventory not within 30 Days machines details 15

All Total Software Inventory within 30 Days machines details 15

All Total Software Inventory not within 30 Days machines details 15

All Software Inventory Report for Specific Computer Based on Installed Software Class 16

All SCCM Servers Inventory status 16

All Workstations Assets Inventory details status 19

 

 

All SQL Server Installed Version and Computer Details using Hardware Inventory

Declare @SoftwareName varchar (255)

Set @SoftwareName = ‘%Microsoft SQL Server%’

Select

distinct vrs.Name0,

vrs.User_Name0,

vga.DisplayName0,

vga.InstallDate0,

vga.Publisher0,

vga.ProdID0

from v_R_System as Vrs

inner join v_GS_ADD_REMOVE_PROGRAMS_64 as Vga on Vrs.ResourceID=Vga.ResourceID

where Vga.DisplayName0 like @SoftwareName

and vga.Publisher0 is not null

All IE Version using Software Inventory

Declare @EXEName varchar(255)

Declare @EXEPath varchar(255)

Set @EXEName = ‘IExplore%’

Set @EXEPath = ‘_:\Program Files\Internet Explorer\’

Select

vrs.Name0,

vrs.User_Name0,

vrs.AD_Site_Name0,

vgs.FileName,

CASE

WHEN vgs.FileVersion LIKE ‘5.%’ THEN ‘Internet Explorer 5’

WHEN vgs.FileVersion LIKE ‘6.%’ THEN ‘Internet Explorer 6’

WHEN vgs.FileVersion LIKE ‘7.%’ THEN ‘Internet Explorer 7’

WHEN vgs.FileVersion LIKE ‘8.%’ THEN ‘Internet Explorer 8’

WHEN vgs.FileVersion LIKE ‘9.%’ THEN ‘Internet Explorer 9’

WHEN vgs.FileVersion LIKE ’10.%’ THEN ‘Internet Explorer 10’

WHEN vgs.FileVersion LIKE ’11.%’ THEN ‘Internet Explorer 11’

ELSE ‘Other Version’ END AS ‘IE Version’,

vgs.FilePath,

vgs.FileVersion

from v_R_System as Vrs

join v_GS_SoftwareFile as Vgs on vrs.ResourceID=vgs.ResourceID

Where vgs.FileName like @EXEName and vgs.FilePath like @EXEPath

–and vrs.Name0 in (‘XXXXX’,’xxxxx1′)

order by vrs.Name0

All Active Workstations Client Hardware Inventory Status

Declare @CollectionID as Varchar(8)

Declare @TotalActive as Numeric(8)

Declare @ActiveHWInv as Numeric(8)

Declare @InActiveHWInv as Numeric(8)

Set @CollectionID = ‘SMS00001’ –Specify the collection ID

select @TotalActive = (

select COUNT(*) as ‘Count’ from v_FullCollectionMembership where CollectionID = @CollectionID

and v_FullCollectionMembership.ResourceID in (

Select Vrs.ResourceID from v_R_System Vrs

inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID

where (Ch.ClientActiveStatus = 1) and Vrs.Operating_System_Name_and0 like ‘%Workstation%’) )

select @ActiveHWInv = (

select COUNT(*) as ‘Count’ from v_FullCollectionMembership where CollectionID = @CollectionID

and v_FullCollectionMembership.ResourceID in (

Select Vrs.ResourceID from v_R_System Vrs

inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID

where (IsActiveHW = 1 and ClientActiveStatus = 1) and Vrs.Operating_System_Name_and0 like ‘%Workstation%’) )

select @InActiveHWInv = (

select COUNT(*) as ‘Count’ from v_FullCollectionMembership where CollectionID = @CollectionID

and v_FullCollectionMembership.ResourceID in (

Select Vrs.ResourceID from v_R_System Vrs

inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID

where (IsActiveHW = 0 and ClientActiveStatus = 1) and Vrs.Operating_System_Name_and0 like ‘%Workstation%’) )

select

@TotalActive as ‘TotalActive’,

@ActiveHWInv as ‘ActiveHWInv’,

@InActiveHWInv as ‘InActiveHWInv’,

case when (@TotalActive = 0) or (@TotalActive is null) Then ‘100’ Else (round(@ActiveHWInv/ convert (float,@TotalActive)*100,2)) End as ‘ActiveHWInv%’

All Active Servers Client Hardware Inventory Status

Declare @CollectionID as Varchar(8)

Declare @TotalActive as Numeric(8)

Declare @ActiveHWInv as Numeric(8)

Declare @InActiveHWInv as Numeric(8)

Set @CollectionID = ‘SMS00001’ –Specify the collection ID

select @TotalActive = (

select COUNT(*) as ‘Count’ from v_FullCollectionMembership where CollectionID = @CollectionID

and v_FullCollectionMembership.ResourceID in (

Select Vrs.ResourceID from v_R_System Vrs

inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID

where (Ch.ClientActiveStatus = 1) and Vrs.Operating_System_Name_and0 like ‘%Servers%’) )

select @ActiveHWInv = (

select COUNT(*) as ‘Count’ from v_FullCollectionMembership where CollectionID = @CollectionID

and v_FullCollectionMembership.ResourceID in (

Select Vrs.ResourceID from v_R_System Vrs

inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID

where (IsActiveHW = 1 and ClientActiveStatus = 1) and Vrs.Operating_System_Name_and0 like ‘%Servers%’) )

select @InActiveHWInv = (

select COUNT(*) as ‘Count’ from v_FullCollectionMembership where CollectionID = @CollectionID

and v_FullCollectionMembership.ResourceID in (

Select Vrs.ResourceID from v_R_System Vrs

inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID

where (IsActiveHW = 0 and ClientActiveStatus = 1) and Vrs.Operating_System_Name_and0 like ‘%Servers%’) )

select

@TotalActive as ‘TotalActive’,

@ActiveHWInv as ‘ActiveHWInv’,

@InActiveHWInv as ‘InActiveHWInv’,

case when (@TotalActive = 0) or (@TotalActive is null) Then ‘100’ Else (round(@ActiveHWInv/ convert (float,@TotalActive)*100,2)) End as ‘ActiveHWInv%’

All Active Workstations Client Software Inventory Status

Declare @CollectionID as Varchar(8)

Declare @TotalActive as Numeric(8)

Declare @ActiveSWInv as Numeric(8)

Declare @InActiveSWInv as Numeric(8)

Set @CollectionID = ‘SMS00001’ –Specify the collection ID

select @TotalActive = (

select COUNT(*) as ‘Count’ from v_FullCollectionMembership where CollectionID = @CollectionID

and v_FullCollectionMembership.ResourceID in (

Select Vrs.ResourceID from v_R_System Vrs

inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID

where (Ch.ClientActiveStatus = 1) and Vrs.Operating_System_Name_and0 like ‘%Workstation%’) )

select @ActiveSWInv = (

select COUNT(*) as ‘Count’ from v_FullCollectionMembership where CollectionID = @CollectionID

and v_FullCollectionMembership.ResourceID in (

Select Vrs.ResourceID from v_R_System Vrs

inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID

where (IsActiveSW = 1 and ClientActiveStatus = 1) and Vrs.Operating_System_Name_and0 like ‘%Workstation%’) )

select @InActiveSWInv = (

select COUNT(*) as ‘Count’ from v_FullCollectionMembership where CollectionID = @CollectionID

and v_FullCollectionMembership.ResourceID in (

Select Vrs.ResourceID from v_R_System Vrs

inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID

where (IsActiveSW = 0 and ClientActiveStatus = 1) and Vrs.Operating_System_Name_and0 like ‘%Workstation%’) )

select

@TotalActive as ‘TotalActive’,

@ActiveSWInv as ‘ActiveSWInv’,

@InActiveSWInv as ‘InActiveSWInv’,

case when (@TotalActive = 0) or (@TotalActive is null) Then ‘100’ Else (round(@ActiveSWInv/ convert (float,@TotalActive)*100,2)) End as ‘ActiveSWInv%’

All Active Servers Client Software Inventory Status

Declare @CollectionID as Varchar(8)

Declare @TotalActive as Numeric(8)

Declare @ActiveSWInv as Numeric(8)

Declare @InActiveSWInv as Numeric(8)

Set @CollectionID = ‘SMS00001’ –Specify the collection ID

select @TotalActive = (

select COUNT(*) as ‘Count’ from v_FullCollectionMembership where CollectionID = @CollectionID

and v_FullCollectionMembership.ResourceID in (

Select Vrs.ResourceID from v_R_System Vrs

inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID

where (Ch.ClientActiveStatus = 1) and Vrs.Operating_System_Name_and0 like ‘%Servers%’) )

select @ActiveSWInv = (

select COUNT(*) as ‘Count’ from v_FullCollectionMembership where CollectionID = @CollectionID

and v_FullCollectionMembership.ResourceID in (

Select Vrs.ResourceID from v_R_System Vrs

inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID

where (IsActiveSW = 1 and ClientActiveStatus = 1) and Vrs.Operating_System_Name_and0 like ‘%Servers%’) )

select @InActiveSWInv = (

select COUNT(*) as ‘Count’ from v_FullCollectionMembership where CollectionID = @CollectionID

and v_FullCollectionMembership.ResourceID in (

Select Vrs.ResourceID from v_R_System Vrs

inner join v_CH_ClientSummary Ch on Vrs.ResourceID = ch.ResourceID

where (IsActiveSW = 0 and ClientActiveStatus = 1) and Vrs.Operating_System_Name_and0 like ‘%Servers%’) )

select

@TotalActive as ‘TotalActive’,

@ActiveSWInv as ‘ActiveSWInv’,

@InActiveSWInv as ‘InActiveSWInv’,

case when (@TotalActive = 0) or (@TotalActive is null) Then ‘100’ Else (round(@ActiveSWInv/ convert (float,@TotalActive)*100,2)) End as ‘ActiveSWInv%’

All PCs with particular software inventory Exe file

SELECT DISTINCT SYS.Netbios_Name0 as Name,

SF.FileName, SF.FileDescription, SF.FileVersion, SF.FileSize, SF.FileModifiedDate, SF.FilePath,

SYS.User_Name0 as Login, CSYS.Manufacturer0 as Manufacturer, CSYS.Model0 as Model, BIOS.SerialNumber0 as SN,

MAX(IPSub.IP_Subnets0) as ‘Subnet’, sys.AD_Site_Name0 as ADSite, MAX(SYSOU.System_OU_Name0) as ‘OU’

FROM v_GS_SoftwareFile SF

join v_R_System SYS on SYS.ResourceID = SF.ResourceID

LEFT JOIN v_RA_System_IPSubnets IPSub on SYS.ResourceID = IPSub.ResourceID

LEFT JOIN v_GS_COMPUTER_SYSTEM CSYS on SYS.ResourceID = CSYS.ResourceID

LEFT JOIN v_GS_OPERATING_SYSTEM OPSYS on SYS.ResourceID=OPSYS.ResourceID

LEFT JOIN v_GS_WORKSTATION_STATUS HWSCAN on SYS.ResourceID = HWSCAN.ResourceID

LEFT JOIN v_GS_PC_BIOS BIOS on SYS.ResourceID = BIOS.ResourceID

LEFT JOIN v_RA_System_SystemOUName SYSOU on SYS.ResourceID=SYSOU.ResourceID

LEFT JOIN v_R_User USR on SYS.User_Name0 = USR.User_Name0

LEFT JOIN v_FullCollectionMembership FCM on SYS.ResourceID = FCM.ResourceID

Where SF.FileName LIKE ‘Microsoft.ConfigurationManagement.exe’

GROUP BY SYS.Netbios_Name0,SF.FileName, SF.FileDescription, SF.FileVersion, SF.FileSize, SF.FileModifiedDate,

SF.FilePath, SYS.User_Name0, CSYS.Manufacturer0, CSYS.Model0, BIOS.SerialNumber0, sys.AD_Site_Name0

ORDER BY SYS.Netbios_Name0

All IE Version using Software Inventory

SELECT distinct

Vrs.Netbios_Name0,

Vrs.User_Name0,

Vrs.AD_Site_Name0,

CASE

WHEN Sf.FileVersion LIKE ‘5.%’ THEN ‘Internet Explorer 5’

WHEN Sf.FileVersion LIKE ‘6.%’ THEN ‘Internet Explorer 6’

WHEN Sf.FileVersion LIKE ‘7.%’ THEN ‘Internet Explorer 7’

WHEN Sf.FileVersion LIKE ‘8.%’ THEN ‘Internet Explorer 8’

WHEN Sf.FileVersion LIKE ‘9.%’ THEN ‘Internet Explorer 9’

WHEN Sf.FileVersion LIKE ’10.%’ THEN ‘Internet Explorer 10’

WHEN Sf.FileVersion LIKE ’11.%’ THEN ‘Internet Explorer 11’

ELSE ‘Other Version’ END AS ‘IE Version’,

Sf.FileName,

Sf.FileVersion

FROM v_R_System Vrs

Inner Join v_GS_SoftwareFile Sf On Vrs.ResourceID = Sf.ResourceID

WHERE Sf.FileName = ‘iexplore.exe’

and Sf.FilePath like ‘_:\Program%Internet Explorer%’

GROUP BY

Vrs.Netbios_Name0,

Vrs.User_Name0,

Vrs.AD_Site_Name0,

Sf.FileName,

Sf.FileVersion

ORDER BY Vrs.Netbios_Name0

All Site Servers Issue Hardware Inventory components status

SELECT distinct SiteCode ,

MachineName ‘ServerName’,

ComponentName ,

Case v_componentSummarizer.State When 0 Then ‘Stopped’ When 1 Then ‘Started’ When 2 Then ‘Paused’ When 3 Then ‘Installing’ When 4 Then ‘Re-Installing’ When 5 Then ‘De-Installing’ Else ‘ ‘ END AS ‘Thread State’,

Errors,

Warnings,

Infos,

Case v_componentSummarizer.Type When 0 Then ‘Autostarting’ When 1 Then ‘Scheduled’ When 2 Then ‘Manual’ ELSE ‘ ‘ END AS ‘StartupType’,

CASE AvailabilityState When 0 Then ‘Online’ When 3 Then ‘Offline’ ELSE ‘ ‘ END AS ‘State’,

Case v_ComponentSummarizer.Status When 0 Then ‘OK’ When 1 Then ‘Warning’ When 2 Then ‘Critical’ Else ‘ ‘ End As ‘Status’

from v_ComponentSummarizer Where TallyInterval = ‘0001128000100008’

and ComponentName =’SMS_INVENTORY_DATA_LOADER’

and v_ComponentSummarizer.Status = 2 Order By SiteCode

All Workstations Assets Inventory details status

Declare @CollectionID as varchar(8)

Declare @ProjectName as varchar(25)

Set @CollectionID = ‘SMS00001’ — specify scope collection ID

Set @ProjectName = ‘LAB’ — specify Project Name

Select

Distinct (VRS.Netbios_Name0) as ‘Name’,

Case when VRS.Client0 = 1 Then ‘Yes’ Else ‘No’ End ‘Client’,

Case when VRS.Active0 = 1 Then ‘Yes’ Else ‘No’ End ‘Active’,

Case when v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 1 Then ‘VMWare’

when v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 IN(‘3′,’4’)Then ‘Desktop’

when v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 IN(‘8′,’9′,’10’,’11’,’12’,’14’) Then ‘Laptop’

when v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 6 Then ‘Mini Tower’

when v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 7 Then ‘Tower’

when v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 13 Then ‘All in One’

when v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 15 Then ‘Space-Saving’

when v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 17 Then ‘Main System Chassis’

when v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 21 Then ‘Peripheral Chassis’

when v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 22 Then ‘Storage Chassis’

when v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 23 Then ‘Rack Mount Chassis’

when v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 24 Then ‘Sealed-Case PC’

Else ‘Others’ End ‘CaseType’,

LEFT(MAX(v_GS_NETWORK_ADAPTER_CONFIGUR.IPAddress0), ISNULL(NULLIF(CHARINDEX(‘,’,MAX(v_GS_NETWORK_ADAPTER_CONFIGUR.IPAddress0)) – 1, -1),LEN(MAX(v_GS_NETWORK_ADAPTER_CONFIGUR.IPAddress0))))as ‘IPAddress’,

MAX (v_GS_NETWORK_ADAPTER_CONFIGUR.MACAddress0) as ‘MACAddress’,

v_RA_System_SMSAssignedSites.SMS_Assigned_Sites0 as ‘AssignedSite’,

VRS.Client_Version0 as ‘ClientVersion’,

VRS.Creation_Date0 as ‘ClientCreationDate’,

VRS.AD_Site_Name0 as ‘ADSiteName’,

dbo.v_GS_OPERATING_SYSTEM.InstallDate0 AS ‘OSInstallDate’,

DateDiff(D, dbo.v_GS_OPERATING_SYSTEM.InstallDate0, GetDate()) ‘OSInstallDateAge’,

Convert(VarChar, v_Gs_Operating_System.LastBootUpTime0,100) as ‘LastBootDate’,

DateDiff(D, Convert(VarChar, v_Gs_Operating_System.LastBootUpTime0,100), GetDate()) as ‘LastBootDateAge’,

PC_BIOS_DATA.SerialNumber00 as ‘SerialNumber’,

v_GS_SYSTEM_ENCLOSURE.SMBIOSAssetTag0 as ‘AssetTag’,

PC_BIOS_DATA.ReleaseDate00 as ‘ReleaseDate’,

PC_BIOS_DATA.Name00 as ‘BiosName’,

PC_BIOS_DATA.SMBIOSBIOSVersion00 as ‘BiosVersion’,

v_GS_PROCESSOR.Name0 as ‘ProcessorName’,

case when Computer_System_DATA.Manufacturer00 like ‘VMware%’ Then ‘VMWare’

when Computer_System_DATA.Manufacturer00 like ‘Gigabyte%’ Then ‘Gigabyte’

when Computer_System_DATA.Manufacturer00 like ‘VIA Technologies%’ Then ‘VIA Technologies’

when Computer_System_DATA.Manufacturer00 like ‘MICRO-STAR%’ Then ‘MICRO-STAR’

Else Computer_System_DATA.Manufacturer00 End ‘Manufacturer’,

Computer_System_DATA.Model00 as ‘Model’,

Computer_System_DATA.SystemType00 as ‘OSType’,

v_GS_COMPUTER_SYSTEM.Domain0 as ‘DomainName’,

VRS.User_Domain0+’\’+ VRS.User_Name0 as ‘UserName’,

v_R_User.Mail0 as ‘EMailID’,

Case when v_GS_COMPUTER_SYSTEM.domainrole0 = 0 then ‘Standalone Workstation’

when v_GS_COMPUTER_SYSTEM.domainrole0 = 1 Then ‘Member Workstation’

when v_GS_COMPUTER_SYSTEM.domainrole0 = 2 Then ‘Standalone Server’

when v_GS_COMPUTER_SYSTEM.domainrole0 = 3 Then ‘Member Server’

when v_GS_COMPUTER_SYSTEM.domainrole0 = 4 Then ‘Backup Domain Controller’

when v_GS_COMPUTER_SYSTEM.domainrole0 = 5 Then ‘Primary Domain Controller’

End ‘Role’,

case when Operating_System_DATA.Caption00 = ‘Microsoft(R) Windows(R) Server 2003, Enterprise Edition’ Then ‘Microsoft(R) Windows(R) Server 2003 Enterprise Edition’

when Operating_System_DATA.Caption00 = ‘Microsoft(R) Windows(R) Server 2003, Standard Edition’ Then ‘Microsoft(R) Windows(R) Server 2003 Standard Edition’

when Operating_System_DATA.Caption00 = ‘Microsoft(R) Windows(R) Server 2003, Web Edition’ Then ‘Microsoft(R) Windows(R) Server 2003 Web Edition’

Else Operating_System_DATA.Caption00 End ‘OSName’,

Operating_System_DATA.CSDVersion00 as ‘ServicePack’,

Operating_System_DATA.Version00 as ‘Version’,

((v_GS_X86_PC_MEMORY.TotalPhysicalMemory0/1024)/1000) as ‘TotalRAMSize(GB)’,

max(v_GS_LOGICAL_DISK.Size0 / 1024) AS ‘TotalHDDSize(GB)’,

v_GS_WORKSTATION_STATUS.LastHWScan as ‘LastHWScan’,

DateDiff(D, v_GS_WORKSTATION_STATUS.LastHwScan, GetDate()) as ‘LastHWScanAge’,

@ProjectName as ‘AccountName’

from V_R_System VRS

Left Outer join PC_BIOS_DATA on PC_BIOS_DATA.MachineID = VRS.ResourceId

Left Outer join Operating_System_DATA on Operating_System_DATA.MachineID = VRS.ResourceId

Left Outer join v_GS_WORKSTATION_STATUS on v_GS_WORKSTATION_STATUS.ResourceID = VRS.ResourceId

Left Outer join Computer_System_DATA on Computer_System_DATA.MachineID = VRS.ResourceId

Left Outer join v_GS_X86_PC_MEMORY on v_GS_X86_PC_MEMORY.ResourceID = VRS.ResourceId

Left Outer join v_GS_PROCESSOR on v_GS_PROCESSOR.ResourceID = VRS.ResourceId

Left Outer join v_GS_SYSTEM_ENCLOSURE on v_GS_SYSTEM_ENCLOSURE.ResourceID = VRS.ResourceId

Left Outer join v_Gs_Operating_System on v_Gs_Operating_System .ResourceID = VRS.ResourceId

Left Outer join v_RA_System_SMSAssignedSites on v_RA_System_SMSAssignedSites.ResourceID = VRS.ResourceId

Left Outer join v_GS_COMPUTER_SYSTEM on v_GS_COMPUTER_SYSTEM.ResourceID = VRS.ResourceId

Left Outer join v_FullCollectionMembership on v_FullCollectionMembership.ResourceID = VRS.ResourceId

Left Outer join v_GS_NETWORK_ADAPTER_CONFIGUR on v_GS_NETWORK_ADAPTER_CONFIGUR.ResourceID = VRS.ResourceId

left outer join v_GS_LOGICAL_DISK on v_GS_LOGICAL_DISK.ResourceID = Vrs.ResourceId AND v_GS_LOGICAL_DISK.DriveType0 = 3

Left Outer join v_R_User on VRS.User_Name0 = v_R_User.User_Name0

where VRS.Operating_System_Name_and0 like ‘%Workstation%’

and (VRS.Obsolete0 = 0 or VRS.Obsolete0 is null)

and VRS.Client0 = 1

and v_FullCollectionMembership.CollectionID = @CollectionID

–and VRS.Netbios_Name0 = ‘CLIENT01’

GROUP BY VRS.Netbios_Name0,VRS.Client0,VRS.Active0,v_GS_SYSTEM_ENCLOSURE.ChassisTypes0,

v_RA_System_SMSAssignedSites.SMS_Assigned_Sites0,VRS.Client_Version0,Vrs.Creation_Date0,

Vrs.AD_Site_Name0,v_Gs_Operating_System.InstallDate0,v_Gs_Operating_System.LastBootUpTime0,

PC_BIOS_DATA.SerialNumber00,v_GS_SYSTEM_ENCLOSURE.SMBIOSAssetTag0,PC_BIOS_DATA.ReleaseDate00,

PC_BIOS_DATA.Name00,PC_BIOS_DATA.SMBIOSBIOSVersion00,v_GS_PROCESSOR.Name0,Computer_System_DATA.Manufacturer00,

Computer_System_DATA.Model00,Computer_System_DATA.SystemType00,v_GS_COMPUTER_SYSTEM.Domain0,

Vrs.User_Domain0,Vrs.User_Name0,v_R_User.Mail0,v_GS_COMPUTER_SYSTEM.DomainRole0,Operating_System_DATA.Caption00,Operating_System_DATA.CSDVersion00, Operating_System_DATA.Version00, v_GS_X86_PC_MEMORY.TotalPhysicalMemory0, v_GS_WORKSTATION_STATUS.LastHWScan

order by VRS.Netbios_Name0

All Workstations Assets Inventory details status

Declare @CollectionID as varchar(8)

Declare @ProjectName as varchar(25)

Set @CollectionID = ‘SMS00001’ — specify scope collection ID

Set @ProjectName = ‘LAB’ — specify Project Name

Select

Distinct (VRS.Netbios_Name0) as ‘Name’,

Case when VRS.Client0 = 1 Then ‘Yes’ Else ‘No’ End ‘Client’,

Case when VRS.Active0 = 1 Then ‘Yes’ Else ‘No’ End ‘Active’,

Case when v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 1 Then ‘VMWare’

when v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 IN(‘3′,’4’)Then ‘Desktop’

when v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 IN(‘8′,’9′,’10’,’11’,’12’,’14’) Then ‘Laptop’

when v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 6 Then ‘Mini Tower’

when v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 7 Then ‘Tower’

when v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 13 Then ‘All in One’

when v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 15 Then ‘Space-Saving’

when v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 17 Then ‘Main System Chassis’

when v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 21 Then ‘Peripheral Chassis’

when v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 22 Then ‘Storage Chassis’

when v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 23 Then ‘Rack Mount Chassis’

when v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 24 Then ‘Sealed-Case PC’

Else ‘Others’ End ‘CaseType’,

LEFT(MAX(v_GS_NETWORK_ADAPTER_CONFIGUR.IPAddress0), ISNULL(NULLIF(CHARINDEX(‘,’,MAX(v_GS_NETWORK_ADAPTER_CONFIGUR.IPAddress0)) – 1, -1),LEN(MAX(v_GS_NETWORK_ADAPTER_CONFIGUR.IPAddress0))))as ‘IPAddress’,

MAX (v_GS_NETWORK_ADAPTER_CONFIGUR.MACAddress0) as ‘MACAddress’,

v_RA_System_SMSAssignedSites.SMS_Assigned_Sites0 as ‘AssignedSite’,

VRS.Client_Version0 as ‘ClientVersion’,

VRS.Creation_Date0 as ‘ClientCreationDate’,

VRS.AD_Site_Name0 as ‘ADSiteName’,

dbo.v_GS_OPERATING_SYSTEM.InstallDate0 AS ‘OSInstallDate’,

DateDiff(D, dbo.v_GS_OPERATING_SYSTEM.InstallDate0, GetDate()) ‘OSInstallDateAge’,

Convert(VarChar, v_Gs_Operating_System.LastBootUpTime0,100) as ‘LastBootDate’,

DateDiff(D, Convert(VarChar, v_Gs_Operating_System.LastBootUpTime0,100), GetDate()) as ‘LastBootDateAge’,

PC_BIOS_DATA.SerialNumber00 as ‘SerialNumber’,

v_GS_SYSTEM_ENCLOSURE.SMBIOSAssetTag0 as ‘AssetTag’,

PC_BIOS_DATA.ReleaseDate00 as ‘ReleaseDate’,

PC_BIOS_DATA.Name00 as ‘BiosName’,

PC_BIOS_DATA.SMBIOSBIOSVersion00 as ‘BiosVersion’,

v_GS_PROCESSOR.Name0 as ‘ProcessorName’,

case when Computer_System_DATA.Manufacturer00 like ‘VMware%’ Then ‘VMWare’

when Computer_System_DATA.Manufacturer00 like ‘Gigabyte%’ Then ‘Gigabyte’

when Computer_System_DATA.Manufacturer00 like ‘VIA Technologies%’ Then ‘VIA Technologies’

when Computer_System_DATA.Manufacturer00 like ‘MICRO-STAR%’ Then ‘MICRO-STAR’

Else Computer_System_DATA.Manufacturer00 End ‘Manufacturer’,

Computer_System_DATA.Model00 as ‘Model’,

Computer_System_DATA.SystemType00 as ‘OSType’,

v_GS_COMPUTER_SYSTEM.Domain0 as ‘DomainName’,

VRS.User_Domain0+’\’+ VRS.User_Name0 as ‘UserName’,

v_R_User.Mail0 as ‘EMailID’,

Case when v_GS_COMPUTER_SYSTEM.domainrole0 = 0 then ‘Standalone Workstation’

when v_GS_COMPUTER_SYSTEM.domainrole0 = 1 Then ‘Member Workstation’

when v_GS_COMPUTER_SYSTEM.domainrole0 = 2 Then ‘Standalone Server’

when v_GS_COMPUTER_SYSTEM.domainrole0 = 3 Then ‘Member Server’

when v_GS_COMPUTER_SYSTEM.domainrole0 = 4 Then ‘Backup Domain Controller’

when v_GS_COMPUTER_SYSTEM.domainrole0 = 5 Then ‘Primary Domain Controller’

End ‘Role’,

case when Operating_System_DATA.Caption00 = ‘Microsoft(R) Windows(R) Server 2003, Enterprise Edition’ Then ‘Microsoft(R) Windows(R) Server 2003 Enterprise Edition’

when Operating_System_DATA.Caption00 = ‘Microsoft(R) Windows(R) Server 2003, Standard Edition’ Then ‘Microsoft(R) Windows(R) Server 2003 Standard Edition’

when Operating_System_DATA.Caption00 = ‘Microsoft(R) Windows(R) Server 2003, Web Edition’ Then ‘Microsoft(R) Windows(R) Server 2003 Web Edition’

Else Operating_System_DATA.Caption00 End ‘OSName’,

Operating_System_DATA.CSDVersion00 as ‘ServicePack’,

Operating_System_DATA.Version00 as ‘Version’,

((v_GS_X86_PC_MEMORY.TotalPhysicalMemory0/1024)/1000) as ‘TotalRAMSize(GB)’,

max(v_GS_LOGICAL_DISK.Size0 / 1024) AS ‘TotalHDDSize(GB)’,

v_GS_WORKSTATION_STATUS.LastHWScan as ‘LastHWScan’,

DateDiff(D, v_GS_WORKSTATION_STATUS.LastHwScan, GetDate()) as ‘LastHWScanAge’,

@ProjectName as ‘AccountName’

from V_R_System VRS

Left Outer join PC_BIOS_DATA on PC_BIOS_DATA.MachineID = VRS.ResourceId

Left Outer join Operating_System_DATA on Operating_System_DATA.MachineID = VRS.ResourceId

Left Outer join v_GS_WORKSTATION_STATUS on v_GS_WORKSTATION_STATUS.ResourceID = VRS.ResourceId

Left Outer join Computer_System_DATA on Computer_System_DATA.MachineID = VRS.ResourceId

Left Outer join v_GS_X86_PC_MEMORY on v_GS_X86_PC_MEMORY.ResourceID = VRS.ResourceId

Left Outer join v_GS_PROCESSOR on v_GS_PROCESSOR.ResourceID = VRS.ResourceId

Left Outer join v_GS_SYSTEM_ENCLOSURE on v_GS_SYSTEM_ENCLOSURE.ResourceID = VRS.ResourceId

Left Outer join v_Gs_Operating_System on v_Gs_Operating_System .ResourceID = VRS.ResourceId

Left Outer join v_RA_System_SMSAssignedSites on v_RA_System_SMSAssignedSites.ResourceID = VRS.ResourceId

Left Outer join v_GS_COMPUTER_SYSTEM on v_GS_COMPUTER_SYSTEM.ResourceID = VRS.ResourceId

Left Outer join v_FullCollectionMembership on v_FullCollectionMembership.ResourceID = VRS.ResourceId

Left Outer join v_GS_NETWORK_ADAPTER_CONFIGUR on v_GS_NETWORK_ADAPTER_CONFIGUR.ResourceID = VRS.ResourceId

left outer join v_GS_LOGICAL_DISK on v_GS_LOGICAL_DISK.ResourceID = Vrs.ResourceId AND v_GS_LOGICAL_DISK.DriveType0 = 3

Left Outer join v_R_User on VRS.User_Name0 = v_R_User.User_Name0

where VRS.Operating_System_Name_and0 like ‘%Server%’

and (VRS.Obsolete0 = 0 or VRS.Obsolete0 is null)

and VRS.Client0 = 1

and v_FullCollectionMembership.CollectionID = @CollectionID

–and VRS.Netbios_Name0 = ‘CLIENT01’

GROUP BY VRS.Netbios_Name0,VRS.Client0,VRS.Active0,v_GS_SYSTEM_ENCLOSURE.ChassisTypes0,

v_RA_System_SMSAssignedSites.SMS_Assigned_Sites0,VRS.Client_Version0,Vrs.Creation_Date0,

Vrs.AD_Site_Name0,v_Gs_Operating_System.InstallDate0,v_Gs_Operating_System.LastBootUpTime0,

PC_BIOS_DATA.SerialNumber00,v_GS_SYSTEM_ENCLOSURE.SMBIOSAssetTag0,PC_BIOS_DATA.ReleaseDate00,

PC_BIOS_DATA.Name00,PC_BIOS_DATA.SMBIOSBIOSVersion00,v_GS_PROCESSOR.Name0,Computer_System_DATA.Manufacturer00,

Computer_System_DATA.Model00,Computer_System_DATA.SystemType00,v_GS_COMPUTER_SYSTEM.Domain0,

Vrs.User_Domain0,Vrs.User_Name0,v_R_User.Mail0,v_GS_COMPUTER_SYSTEM.DomainRole0,Operating_System_DATA.Caption00,Operating_System_DATA.CSDVersion00, Operating_System_DATA.Version00, v_GS_X86_PC_MEMORY.TotalPhysicalMemory0, v_GS_WORKSTATION_STATUS.LastHWScan

order by VRS.Netbios_Name0

All Client and Inventory Health Report

Declare @CollectionID as Varchar(8)

Declare @TotalMachines as Numeric(5)

Declare @Healthy as Numeric(5)

Declare @UnHealthy as Numeric(5)

Declare @HWInventorySuccess as Numeric(5)

Declare @HWInventoryNotRun as Numeric(5)

Declare @SWInventorySuccess as Numeric(5)

Declare @SWInventoryNotRun as Numeric(5)

Declare @WSUSScanSuccess as Numeric(5)

Declare @WSUSScanNotRun as Numeric(5)

Set @CollectionID = ‘SMS00001’ — Specify Scope Collection ID

select @TotalMachines = (select COUNT(*) from v_FullCollectionMembership where CollectionID = @CollectionID)

select @Healthy = ( select COUNT(*) from v_FullCollectionMembership where CollectionID = @CollectionID

and IsAssigned = 1 and IsActive = 1 and IsObsolete != 1 and IsClient = 1 )

select @UnHealthy = ( select COUNT(*) from v_FullCollectionMembership where CollectionID = @CollectionID

and ResourceID Not in (select ResourceID from v_FullCollectionMembership where CollectionID = @CollectionID

and IsAssigned = 1 and IsActive = 1 and IsObsolete != 1 and IsClient = 1 ) )

select @HWInventorySuccess = ( select COUNT(*) from v_FullCollectionMembership where CollectionID = @CollectionID

and IsAssigned = 1 and IsClient = 1 and IsActive = 1 and IsObsolete != 1

and ResourceID in (select ResourceID from v_GS_WORKSTATION_STATUS where (DATEDIFF (day,LastHWScan,GetDate())<30) and ResourceID in (select ResourceID from v_AgentDiscoveries Where AgentName in (‘Heartbeat Discovery’) and DATEDIFF (day,AgentTime,GetDate())<30)) )

select @HWInventoryNotRun = ( select COUNT(*) from v_FullCollectionMembership where CollectionID = @CollectionID

and IsAssigned = 1 and IsClient = 1 and IsActive = 1 and IsObsolete != 1

and ResourceID Not In (select ResourceID from v_FullCollectionMembership where CollectionID = @CollectionID

and IsAssigned = 1 and IsClient = 1 and IsActive = 1 and IsObsolete != 1

and ResourceID in (select ResourceID from v_GS_WORKSTATION_STATUS where (DATEDIFF (day,LastHWScan,GetDate())<30) and ResourceID in (select ResourceID from v_AgentDiscoveries Where AgentName in (‘Heartbeat Discovery’) and DATEDIFF (day,AgentTime,GetDate())<30)) ) )

select @SWInventorySuccess = ( select COUNT(*) from v_FullCollectionMembership where CollectionID = @CollectionID

and IsAssigned = 1 and IsClient = 1 and IsActive = 1 and IsObsolete != 1

and ResourceID in (select ResourceID from v_GS_LastSoftwareScan where (DATEDIFF (day,LastScanDate,GetDate())<30) and ResourceID in (select ResourceID from v_AgentDiscoveries Where AgentName in (‘Heartbeat Discovery’) and DATEDIFF (day,AgentTime,GetDate())<30)) )

select @SWInventoryNotRun = ( select COUNT(*) from v_FullCollectionMembership where CollectionID = @CollectionID

and IsAssigned = 1 and IsClient = 1 and IsActive = 1 and IsObsolete != 1

and ResourceID Not In (select ResourceID from v_FullCollectionMembership where CollectionID = @CollectionID

and IsAssigned = 1 and IsClient = 1 and IsActive = 1 and IsObsolete != 1

and ResourceID in (select ResourceID from v_GS_LastSoftwareScan where (DATEDIFF (day,LastScanDate,GetDate())<30) and ResourceID in (select ResourceID from v_AgentDiscoveries Where AgentName in (‘Heartbeat Discovery’) and DATEDIFF (day,AgentTime,GetDate())<30)) ) )

select @WSUSScanSuccess = ( select COUNT(*) from v_FullCollectionMembership where CollectionID = @CollectionID

and IsAssigned = 1 and IsClient = 1 and IsActive = 1 and IsObsolete != 1

and ResourceID in (select ResourceID from v_UpdateScanStatus where lastErrorCode = 0 and (DATEDIFF (day,LastScanTime,GetDate())<30) and ResourceID in (select ResourceID from v_AgentDiscoveries Where AgentName in (‘Heartbeat Discovery’) and DATEDIFF (day,AgentTime,GetDate())<30)) )

select @WSUSScanNotRun = ( select COUNT(*) from v_FullCollectionMembership where CollectionID = @CollectionID

and IsAssigned = 1 and IsClient = 1 and IsActive = 1 and IsObsolete != 1

and ResourceID Not In (select ResourceID from v_FullCollectionMembership where CollectionID = @CollectionID

and IsAssigned = 1 and IsClient = 1 and IsActive = 1 and IsObsolete != 1

and ResourceID in (select ResourceID from v_UpdateScanStatus where lastErrorCode = 0 and (DATEDIFF (day,LastScanTime,GetDate())<30) and ResourceID in (select ResourceID from v_AgentDiscoveries Where AgentName in (‘Heartbeat Discovery’) and DATEDIFF (day,AgentTime,GetDate())<30)) ) )

select

@TotalMachines as ‘TotalMachines’,

@Healthy as ‘Healthy’,

@UnHealthy as ‘UnHealthy’,

(Select(@Healthy/@TotalMachines)*100) as ‘Healthy%’,

@HWInventorySuccess as ‘HWInventorySuccess’,

@HWInventoryNotRun as ‘HWInventoryNotRun’,

(Select(@HWInventorySuccess/@Healthy)*100) as ‘HWInventorySuccess%’,

@SWInventorySuccess as ‘SWInventorySuccess’,

@SWInventoryNotRun as ‘SWInventoryNotRun’,

(Select(@SWInventorySuccess/@Healthy)*100) as ‘SWInventorySuccess%’,

@WSUSScanSuccess as ‘WSUSScanSuccess’,

@WSUSScanNotRun as ‘WSUSScanNotRun’,

(Select(@WSUSScanSuccess/@Healthy)*100) as ‘WSUSScanSuccess%’

All Total Hardware Inventory within 30 Days machines details

Declare @CollectionID as Varchar(8)

Set @CollectionID = ‘SMS00001’ — Specify Scope Collection ID

select

Name0 as ‘MachineName’

from v_R_system where V_R_System.ResourceID

in ( select ResourceID from v_FullCollectionMembership where CollectionID = @CollectionID

and IsAssigned = 1 and IsClient = 1 and IsActive = 1 and IsObsolete != 1

and ResourceID in (select ResourceID from v_GS_WORKSTATION_STATUS where (DATEDIFF (day,LastHWScan,GetDate())<30) and ResourceID in (select ResourceID from v_AgentDiscoveries Where AgentName in (‘Heartbeat Discovery’) and DATEDIFF (day,AgentTime,GetDate())<30)) )

All Total Hardware Inventory not within 30 Days machines details

Declare @CollectionID as Varchar(8)

Set @CollectionID = ‘SMS00001’ — Specify Scope Collection ID

select

Name0 as ‘MachineName’

from v_R_system where V_R_System.ResourceID

in ( select ResourceID from v_FullCollectionMembership where CollectionID = @CollectionID

and IsAssigned = 1 and IsClient = 1 and IsActive = 1 and IsObsolete != 1

and ResourceID Not In (select ResourceID from v_FullCollectionMembership where CollectionID = @CollectionID

and IsAssigned = 1 and IsClient = 1 and IsActive = 1 and IsObsolete != 1

and ResourceID in (select ResourceID from v_GS_WORKSTATION_STATUS where (DATEDIFF (day,LastHWScan,GetDate())<30) and ResourceID in (select ResourceID from v_AgentDiscoveries Where AgentName in (‘Heartbeat Discovery’) and DATEDIFF (day,AgentTime,GetDate())<30)) ))

All Total Software Inventory within 30 Days machines details

Declare @CollectionID as Varchar(8)

Set @CollectionID = ‘SMS00001’ — Specify Scope Collection ID

select

Name0 as ‘MachineName’

from v_R_system where V_R_System.ResourceID

in ( select ResourceID from v_FullCollectionMembership where CollectionID = @CollectionID

and IsAssigned = 1 and IsClient = 1 and IsActive = 1 and IsObsolete != 1

and ResourceID in (select ResourceID from v_GS_LastSoftwareScan

where (DATEDIFF (day, LastScanDate,GetDate())<30) and ResourceID in (select ResourceID from v_AgentDiscoveries Where AgentName in (‘Heartbeat Discovery’) and DATEDIFF (day,AgentTime,GetDate())<30)) )

All Total Software Inventory not within 30 Days machines details

Declare @CollectionID as Varchar(8)

Set @CollectionID = ‘SMS00001’ — Specify Scope Collection ID

select

Name0 as ‘MachineName’

from v_R_system where V_R_System.ResourceID

in ( select ResourceID from v_FullCollectionMembership where CollectionID = @CollectionID

and IsAssigned = 1 and IsClient = 1 and IsActive = 1 and IsObsolete != 1

and ResourceID Not In (select ResourceID from v_FullCollectionMembership where CollectionID = @CollectionID

and IsAssigned = 1 and IsClient = 1 and IsActive = 1 and IsObsolete != 1

and ResourceID in (select ResourceID from v_GS_LastSoftwareScan where (DATEDIFF (day,LastScanDate,GetDate())<30) and ResourceID in (select ResourceID from v_AgentDiscoveries Where AgentName in (‘Heartbeat Discovery’) and DATEDIFF (day,AgentTime,GetDate())<30)) ))

All Software Inventory Report for Specific Computer Based on Installed Software Class

Select sys.Netbios_Name0,

vos.Caption0 AS [Operating System],

varp.ARPDisplayName0 AS [Software Name],

varp.Publisher0,

varp.ProductVersion0,

varp.TimeStamp,

varp.InstallDate0,

Varp.ProductID0,

fcm.SiteCode,

sys.User_Name0,

sys.User_Domain0

FROM v_R_System sys

JOIN v_GS_INSTALLED_SOFTWARE varp ON sys.ResourceID = varp.ResourceID

JOIN v_FullCollectionMembership fcm on sys.ResourceID=fcm.ResourceID

JOIN v_GS_OPERATING_SYSTEM vos on sys.ResourceID=vos.ResourceID

WHERE ARPDisplayName0 LIKE ‘%’ and sys.Name0 in (‘Client01’)

All SCCM Servers Inventory status

Select

Distinct (VRS.Netbios_Name0) as ‘Server Name’,

Case when VRS.Client0 = 1 Then ‘Yes’ Else ‘No’ End ‘Client’,

Case when VRS.Active0 = 1 Then ‘Yes’ Else ‘No’ End ‘Active’,

Case when v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 1 Then ‘VMWare’

when v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 IN(‘3′,’4’)Then ‘Desktop’

when v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 IN(‘8′,’9′,’10’,’11’,’12’,’14’) Then ‘Laptop’

when v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 6 Then ‘Mini Tower’

when v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 7 Then ‘Tower’

when v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 13 Then ‘All in One’

when v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 15 Then ‘Space-Saving’

when v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 17 Then ‘Main System Chassis’

when v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 21 Then ‘Peripheral Chassis’

when v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 22 Then ‘Storage Chassis’

when v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 23 Then ‘Rack Mount Chassis’

when v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 24 Then ‘Sealed-Case PC’

Else ‘Others’ End ‘CaseType’,

LEFT(MAX(v_GS_NETWORK_ADAPTER_CONFIGUR.IPAddress0), ISNULL(NULLIF(CHARINDEX(‘,’,MAX(v_GS_NETWORK_ADAPTER_CONFIGUR.IPAddress0)) – 1, -1),LEN(MAX(v_GS_NETWORK_ADAPTER_CONFIGUR.IPAddress0))))as ‘IPAddress’,

MAX (v_GS_NETWORK_ADAPTER_CONFIGUR.MACAddress0) as ‘MACAddress’,

v_RA_System_SMSAssignedSites.SMS_Assigned_Sites0 as ‘AssignedSite’,

VRS.Client_Version0 as ‘ClientVersion’,

VRS.Creation_Date0 as ‘ClientCreationDate’,

DateDiff(D, VRS.Creation_Date0, GetDate()) ‘ClientCreationDateAge’,

VRS.AD_Site_Name0 as ‘ADSiteName’,

dbo.v_GS_OPERATING_SYSTEM.InstallDate0 AS ‘OSInstallDate’,

DateDiff(D, v_GS_OPERATING_SYSTEM.InstallDate0, GetDate()) ‘OSInstallDateAge’,

Convert(VarChar, v_Gs_Operating_System.LastBootUpTime0,100) as ‘LastBootDate’,

DateDiff(D, Convert(VarChar, v_Gs_Operating_System.LastBootUpTime0,100), GetDate()) as ‘LastBootDateAge’,

PC_BIOS_DATA.SerialNumber00 as ‘SerialNumber’,

v_GS_SYSTEM_ENCLOSURE.SMBIOSAssetTag0 as ‘AssetTag’,

PC_BIOS_DATA.ReleaseDate00 as ‘ReleaseDate’,

PC_BIOS_DATA.Name00 as ‘BiosName’,

PC_BIOS_DATA.SMBIOSBIOSVersion00 as ‘BiosVersion’,

v_GS_PROCESSOR.Name0 as ‘ProcessorName’,

case when Computer_System_DATA.Manufacturer00 like ‘VMware%’ Then ‘VMWare’

when Computer_System_DATA.Manufacturer00 like ‘Gigabyte%’ Then ‘Gigabyte’

when Computer_System_DATA.Manufacturer00 like ‘VIA Technologies%’ Then ‘VIA Technologies’

when Computer_System_DATA.Manufacturer00 like ‘MICRO-STAR%’ Then ‘MICRO-STAR’

Else Computer_System_DATA.Manufacturer00 End ‘Manufacturer’,

Computer_System_DATA.Model00 as ‘Model’,

Computer_System_DATA.SystemType00 as ‘OSType’,

v_GS_COMPUTER_SYSTEM.Domain0 as ‘DomainName’,

VRS.User_Domain0+’\’+ VRS.User_Name0 as ‘UserName’,

v_R_User.Mail0 as ‘EMailID’,

Case when v_GS_COMPUTER_SYSTEM.domainrole0 = 0 then ‘Standalone Workstation’

when v_GS_COMPUTER_SYSTEM.domainrole0 = 1 Then ‘Member Workstation’

when v_GS_COMPUTER_SYSTEM.domainrole0 = 2 Then ‘Standalone Server’

when v_GS_COMPUTER_SYSTEM.domainrole0 = 3 Then ‘Member Server’

when v_GS_COMPUTER_SYSTEM.domainrole0 = 4 Then ‘Backup Domain Controller’

when v_GS_COMPUTER_SYSTEM.domainrole0 = 5 Then ‘Primary Domain Controller’

End ‘Role’,

case when Operating_System_DATA.Caption00 = ‘Microsoft(R) Windows(R) Server 2003, Enterprise Edition’ Then ‘Microsoft(R) Windows(R) Server 2003 Enterprise Edition’

when Operating_System_DATA.Caption00 = ‘Microsoft(R) Windows(R) Server 2003, Standard Edition’ Then ‘Microsoft(R) Windows(R) Server 2003 Standard Edition’

when Operating_System_DATA.Caption00 = ‘Microsoft(R) Windows(R) Server 2003, Web Edition’ Then ‘Microsoft(R) Windows(R) Server 2003 Web Edition’

Else Operating_System_DATA.Caption00 End ‘OSName’,

Operating_System_DATA.CSDVersion00 as ‘ServicePack’,

Operating_System_DATA.Version00 as ‘Version’,

((v_GS_X86_PC_MEMORY.TotalPhysicalMemory0/1024)/1000) as ‘TotalRAMSize(GB)’,

max(v_GS_LOGICAL_DISK.Size0 / 1024) AS ‘TotalHDDSize(GB)’,

v_GS_WORKSTATION_STATUS.LastHWScan as ‘LastHWScan’,

DateDiff(D, v_GS_WORKSTATION_STATUS.LastHwScan, GetDate()) as ‘LastHWScanAge’

from V_R_System VRS

Left Outer join PC_BIOS_DATA on PC_BIOS_DATA.MachineID = VRS.ResourceId

Left Outer join Operating_System_DATA on Operating_System_DATA.MachineID = VRS.ResourceId

Left Outer join v_GS_WORKSTATION_STATUS on v_GS_WORKSTATION_STATUS.ResourceID = VRS.ResourceId

Left Outer join Computer_System_DATA on Computer_System_DATA.MachineID = VRS.ResourceId

Left Outer join v_GS_X86_PC_MEMORY on v_GS_X86_PC_MEMORY.ResourceID = VRS.ResourceId

Left Outer join v_GS_PROCESSOR on v_GS_PROCESSOR.ResourceID = VRS.ResourceId

Left Outer join v_GS_SYSTEM_ENCLOSURE on v_GS_SYSTEM_ENCLOSURE.ResourceID = VRS.ResourceId

Left Outer join v_Gs_Operating_System on v_Gs_Operating_System .ResourceID = VRS.ResourceId

Left Outer join v_RA_System_SMSAssignedSites on v_RA_System_SMSAssignedSites.ResourceID = VRS.ResourceId

left outer join v_GS_COMPUTER_SYSTEM on v_GS_COMPUTER_SYSTEM.ResourceID = VRS.ResourceId

left outer join v_FullCollectionMembership on v_FullCollectionMembership.ResourceID = VRS.ResourceId

left outer join v_GS_NETWORK_ADAPTER_CONFIGUR on v_GS_NETWORK_ADAPTER_CONFIGUR.ResourceID = VRS.ResourceId

left outer join v_GS_LOGICAL_DISK on v_GS_LOGICAL_DISK.ResourceID = Vrs.ResourceId AND v_GS_LOGICAL_DISK.DriveType0 = 3

Left Outer join v_R_User on VRS.User_Name0 = v_R_User.User_Name0

where (VRS.Obsolete0 = 0 or VRS.Obsolete0 is null)

and VRS.Netbios_Name0 in (

Select LEFT(ServerName, CHARINDEX(‘.’, ServerName) – 1) from v_SystemResourceList where RoleName = ‘SMS Site System’ )

GROUP BY VRS.Netbios_Name0,VRS.Client0,VRS.Active0,v_GS_SYSTEM_ENCLOSURE.ChassisTypes0,

v_RA_System_SMSAssignedSites.SMS_Assigned_Sites0,VRS.Client_Version0,Vrs.Creation_Date0,

Vrs.AD_Site_Name0,v_Gs_Operating_System.InstallDate0,v_Gs_Operating_System.LastBootUpTime0,

PC_BIOS_DATA.SerialNumber00,v_GS_SYSTEM_ENCLOSURE.SMBIOSAssetTag0,PC_BIOS_DATA.ReleaseDate00,

PC_BIOS_DATA.Name00,PC_BIOS_DATA.SMBIOSBIOSVersion00,v_GS_PROCESSOR.Name0,Computer_System_DATA.Manufacturer00,Computer_System_DATA.Model00,Computer_System_DATA.SystemType00,v_GS_COMPUTER_SYSTEM.Domain0,

Vrs.User_Domain0,Vrs.User_Name0,v_R_User.Mail0,v_GS_COMPUTER_SYSTEM.DomainRole0,Operating_System_DATA.Caption00,Operating_System_DATA.CSDVersion00,Operating_System_DATA.Version00,v_GS_X86_PC_MEMORY.TotalPhysicalMemory0,v_GS_WORKSTATION_STATUS.LastHWScan

order by VRS.Netbios_Name0

All Workstations Assets Inventory details status

Declare @CollectionID as varchar(8)

Declare @ProjectName as varchar(25)

Set @CollectionID = ‘SMS00001’

Set @ProjectName = ‘LAB’

Select

Distinct (VRS.Netbios_Name0) as ‘Name’,

Case when VRS.Client0 = 1 Then ‘Yes’ Else ‘No’ End ‘Client’,

Case when VRS.Active0 = 1 Then ‘Yes’ Else ‘No’ End ‘Active’,

Case when v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 1 Then ‘VMWare’

when v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 IN(‘3′,’4’)Then ‘Desktop’

when v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 IN(‘8′,’9′,’10’,’11’,’12’,’14’) Then ‘Laptop’

when v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 6 Then ‘Mini Tower’

when v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 7 Then ‘Tower’

when v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 13 Then ‘All in One’

when v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 15 Then ‘Space-Saving’

when v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 17 Then ‘Main System Chassis’

when v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 21 Then ‘Peripheral Chassis’

when v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 22 Then ‘Storage Chassis’

when v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 23 Then ‘Rack Mount Chassis’

when v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 24 Then ‘Sealed-Case PC’

Else ‘Others’ End ‘CaseType’,

LEFT(MAX(v_GS_NETWORK_ADAPTER_CONFIGUR.IPAddress0), ISNULL(NULLIF(CHARINDEX(‘,’,MAX(v_GS_NETWORK_ADAPTER_CONFIGUR.IPAddress0)) – 1, -1),LEN(MAX(v_GS_NETWORK_ADAPTER_CONFIGUR.IPAddress0))))as ‘IPAddress’,

MAX (v_GS_NETWORK_ADAPTER_CONFIGUR.MACAddress0) as ‘MACAddress’,

v_RA_System_SMSAssignedSites.SMS_Assigned_Sites0 as ‘AssignedSite’,

VRS.Client_Version0 as ‘ClientVersion’,

VRS.Creation_Date0 as ‘ClientCreationDate’,

VRS.AD_Site_Name0 as ‘ADSiteName’,

dbo.v_GS_OPERATING_SYSTEM.InstallDate0 AS ‘OSInstallDate’,

DateDiff(D, dbo.v_GS_OPERATING_SYSTEM.InstallDate0, GetDate()) ‘OSInstallDateAge’,

Convert(VarChar, v_Gs_Operating_System.LastBootUpTime0,100) as ‘LastBootDate’,

DateDiff(D, Convert(VarChar, v_Gs_Operating_System.LastBootUpTime0,100), GetDate()) as ‘LastBootDateAge’,

PC_BIOS_DATA.SerialNumber00 as ‘SerialNumber’,

v_GS_SYSTEM_ENCLOSURE.SMBIOSAssetTag0 as ‘AssetTag’,

PC_BIOS_DATA.ReleaseDate00 as ‘ReleaseDate’,

PC_BIOS_DATA.Name00 as ‘BiosName’,

PC_BIOS_DATA.SMBIOSBIOSVersion00 as ‘BiosVersion’,

v_GS_PROCESSOR.Name0 as ‘ProcessorName’,

case when Computer_System_DATA.Manufacturer00 like ‘VMware%’ Then ‘VMWare’

when Computer_System_DATA.Manufacturer00 like ‘Gigabyte%’ Then ‘Gigabyte’

when Computer_System_DATA.Manufacturer00 like ‘VIA Technologies%’ Then ‘VIA Technologies’

when Computer_System_DATA.Manufacturer00 like ‘MICRO-STAR%’ Then ‘MICRO-STAR’

Else Computer_System_DATA.Manufacturer00 End ‘Manufacturer’,

Computer_System_DATA.Model00 as ‘Model’,

Computer_System_DATA.SystemType00 as ‘OSType’,

v_GS_COMPUTER_SYSTEM.Domain0 as ‘DomainName’,

VRS.User_Domain0+’\’+ VRS.User_Name0 as ‘UserName’,

v_R_User.Mail0 as ‘EMailID’,

Case when v_GS_COMPUTER_SYSTEM.domainrole0 = 0 then ‘Standalone Workstation’

when v_GS_COMPUTER_SYSTEM.domainrole0 = 1 Then ‘Member Workstation’

when v_GS_COMPUTER_SYSTEM.domainrole0 = 2 Then ‘Standalone Server’

when v_GS_COMPUTER_SYSTEM.domainrole0 = 3 Then ‘Member Server’

when v_GS_COMPUTER_SYSTEM.domainrole0 = 4 Then ‘Backup Domain Controller’

when v_GS_COMPUTER_SYSTEM.domainrole0 = 5 Then ‘Primary Domain Controller’

End ‘Role’,

case when Operating_System_DATA.Caption00 = ‘Microsoft(R) Windows(R) Server 2003, Enterprise Edition’ Then ‘Microsoft(R) Windows(R) Server 2003 Enterprise Edition’

when Operating_System_DATA.Caption00 = ‘Microsoft(R) Windows(R) Server 2003, Standard Edition’ Then ‘Microsoft(R) Windows(R) Server 2003 Standard Edition’

when Operating_System_DATA.Caption00 = ‘Microsoft(R) Windows(R) Server 2003, Web Edition’ Then ‘Microsoft(R) Windows(R) Server 2003 Web Edition’

Else Operating_System_DATA.Caption00 End ‘OSName’,

Operating_System_DATA.CSDVersion00 as ‘ServicePack’,

Operating_System_DATA.Version00 as ‘Version’,

((v_GS_X86_PC_MEMORY.TotalPhysicalMemory0/1024)/1000) as ‘TotalRAMSize(GB)’,

max(v_GS_LOGICAL_DISK.Size0 / 1024) AS ‘TotalHDDSize(GB)’,

v_GS_WORKSTATION_STATUS.LastHWScan as ‘LastHWScan’,

DateDiff(D, v_GS_WORKSTATION_STATUS.LastHwScan, GetDate()) as ‘LastHWScanAge’,

@ProjectName as ‘AccountName’

from V_R_System VRS

Left Outer join PC_BIOS_DATA on PC_BIOS_DATA.MachineID = VRS.ResourceId

Left Outer join Operating_System_DATA on Operating_System_DATA.MachineID = VRS.ResourceId

Left Outer join v_GS_WORKSTATION_STATUS on v_GS_WORKSTATION_STATUS.ResourceID = VRS.ResourceId

Left Outer join Computer_System_DATA on Computer_System_DATA.MachineID = VRS.ResourceId

Left Outer join v_GS_X86_PC_MEMORY on v_GS_X86_PC_MEMORY.ResourceID = VRS.ResourceId

Left Outer join v_GS_PROCESSOR on v_GS_PROCESSOR.ResourceID = VRS.ResourceId

Left Outer join v_GS_SYSTEM_ENCLOSURE on v_GS_SYSTEM_ENCLOSURE.ResourceID = VRS.ResourceId

Left Outer join v_Gs_Operating_System on v_Gs_Operating_System .ResourceID = VRS.ResourceId

Left Outer join v_RA_System_SMSAssignedSites on v_RA_System_SMSAssignedSites.ResourceID = VRS.ResourceId

left outer join v_GS_COMPUTER_SYSTEM on v_GS_COMPUTER_SYSTEM.ResourceID = VRS.ResourceId

left outer join v_FullCollectionMembership on v_FullCollectionMembership.ResourceID = VRS.ResourceId

left outer join v_GS_NETWORK_ADAPTER_CONFIGUR on v_GS_NETWORK_ADAPTER_CONFIGUR.ResourceID = VRS.ResourceId

left outer join v_GS_LOGICAL_DISK on v_GS_LOGICAL_DISK.ResourceID = Vrs.ResourceId AND v_GS_LOGICAL_DISK.DriveType0 = 3

Left Outer join v_R_User on VRS.User_Name0 = v_R_User.User_Name0

where VRS.Operating_System_Name_and0 like ‘%Workstation%’

and (VRS.Obsolete0 = 0 or VRS.Obsolete0 is null)

and v_FullCollectionMembership.CollectionID = @CollectionID

GROUP BY VRS.Netbios_Name0,VRS.Client0,VRS.Active0,v_GS_SYSTEM_ENCLOSURE.ChassisTypes0,

v_RA_System_SMSAssignedSites.SMS_Assigned_Sites0,VRS.Client_Version0,Vrs.Creation_Date0,

Vrs.AD_Site_Name0,v_Gs_Operating_System.InstallDate0,v_Gs_Operating_System.LastBootUpTime0,

PC_BIOS_DATA.SerialNumber00,v_GS_SYSTEM_ENCLOSURE.SMBIOSAssetTag0,PC_BIOS_DATA.ReleaseDate00,

PC_BIOS_DATA.Name00,PC_BIOS_DATA.SMBIOSBIOSVersion00,v_GS_PROCESSOR.Name0,Computer_System_DATA.Manufacturer00,

Computer_System_DATA.Model00,Computer_System_DATA.SystemType00,v_GS_COMPUTER_SYSTEM.Domain0,

Vrs.User_Domain0,Vrs.User_Name0,v_R_User.Mail0,v_GS_COMPUTER_SYSTEM.DomainRole0,Operating_System_DATA.Caption00,

Operating_System_DATA.CSDVersion00,Operating_System_DATA.Version00,v_GS_X86_PC_MEMORY.TotalPhysicalMemory0,v_GS_WORKSTATION_STATUS.LastHWScan

order by VRS.Netbios_Name0

error

Enjoy this blog? Please spread the word :)

RSS
Follow by Email
Facebook
Facebook
Twitter
Visit Us
LinkedIn