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
Expert SCCM
Passionné des solutions Microsoft System Center
