Select
VRS.Netbios_Name0 as 'Computer Name',
Case
when SE.ChassisTypes0 = 1 Then 'VMWare'
when SE.ChassisTypes0 IN('3','4')Then 'Desktop'
when SE.ChassisTypes0 IN('8','9','10','11','12','14') Then 'Laptop'
when SE.ChassisTypes0 = 6 Then 'Mini Tower'
when SE.ChassisTypes0 = 7 Then 'Tower'
when SE.ChassisTypes0 = 13 Then 'All in One'
when SE.ChassisTypes0 = 15 Then 'Space-Saving'
when SE.ChassisTypes0 = 17 Then 'Main System Chassis'
when SE.ChassisTypes0 = 21 Then 'Peripheral Chassis'
when SE.ChassisTypes0 = 22 Then 'Storage Chassis'
when SE.ChassisTypes0 = 23 Then 'Rack Mount Chassis'
when SE.ChassisTypes0 = 24 Then 'Sealed-Case PC'
Else 'Others'
End 'CaseType',
LEFT(MAX(NAC.IPAddress0),
ISNULL(NULLIF(CHARINDEX(',',MAX(NAC.IPAddress0)) - 1, -1),LEN(MAX(NAC.IPAddress0))))as 'IPAddress',
MAX (NAC.MACAddress0) as 'MACAddress',
SAS.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',
OS.InstallDate0 AS 'OSInstallDate',
DateDiff(D, OS.InstallDate0, GetDate()) 'OSInstallDateAge',
Convert(VarChar, OS.LastBootUpTime0,10) as 'LastBootDate',
DateDiff(D, Convert(VarChar, OS.LastBootUpTime0,10), GetDate()) as 'LastBootDateAge',
BIOS.SerialNumber0 as 'SerialNumber',
SE.SMBIOSAssetTag0 as 'AssetTag',
BIOS.ReleaseDate0 as 'ReleaseDate',
BIOS.Name0 as 'BiosName',
BIOS.SMBIOSBIOSVersion0 as 'BiosVersion',
PRO.Name0 as 'ProcessorName',
case
when CS.Manufacturer0 like 'VMware%' Then 'VMWare'
when CS.Manufacturer0 like 'Gigabyte%' Then 'Gigabyte'
when CS.Manufacturer0 like 'VIA Technologies%' Then 'VIA Technologies'
when CS.Manufacturer0 like 'MICRO-STAR%' Then 'MICRO-STAR'
Else CS.Manufacturer0 End 'Manufacturer',
CS.Model0 as 'Model',
CS.SystemType0 as 'OSType',
CS.Domain0 as 'DomainName',
VRS.User_Domain0+'\'+ VRS.User_Name0 as 'UserName',
U.Mail0 as 'EMailID',
Case
when CS.domainrole0 = 0 then 'Standalone Workstation'
when CS.domainrole0 = 1 Then 'Member Workstation'
when CS.domainrole0 = 2 Then 'Standalone Server'
when CS.domainrole0 = 3 Then 'Member Server'
when CS.domainrole0 = 4 Then 'Backup Domain Controller'
when CS.domainrole0 = 5 Then 'Primary Domain Controller'
End 'Role',
case
when OS.Caption0 = 'Microsoft(R) Windows(R) Server 203, Enterprise Edition' Then 'Microsoft(R) Windows(R) Server 203 Enterprise Edition'
when OS.Caption0 = 'Microsoft(R) Windows(R) Server 203, Standard Edition' Then 'Microsoft(R) Windows(R) Server 203 Standard Edition'
when OS.Caption0 = 'Microsoft(R) Windows(R) Server 203, Web Edition' Then 'Microsoft(R) Windows(R) Server 203 Web Edition'
Else OS.Caption0
End 'OSName',
OS.CSDVersion0 as 'ServicePack',
OS.Version0 as 'Version',
RAM.TotalPhysicalMemory0,
((RAM.TotalPhysicalMemory0/1024)/1024) as 'TotalRAMSize(GB)',
max(LD.Size0 / 1024) AS 'TotalHDDSize(GB)',
WS.LastHWScan as 'LastHWScan',
DateDiff(D, WS.LastHwScan, GetDate()) as 'LastHWScanAge'
from
dbo.v_R_System_Valid VRS
Left Outer join dbo.v_GS_PC_BIOS BIOS on BIOS.ResourceId = VRS.ResourceId
Left Outer join dbo.v_GS_OPERATING_SYSTEM OS on OS.ResourceId = VRS.ResourceId
Left Outer join dbo.v_GS_WORKSTATION_STATUS WS on WS.ResourceID = VRS.ResourceId
Left Outer join dbo.v_GS_COMPUTER_SYSTEM CS on CS.ResourceId = VRS.ResourceId
Left Outer join dbo.v_GS_X86_PC_MEMORY RAM on RAM.ResourceID = VRS.ResourceId
Left Outer join dbo.v_GS_PROCESSOR PRO on PRO.ResourceID = VRS.ResourceId
Left Outer join dbo.v_GS_SYSTEM_ENCLOSURE SE on SE.ResourceID = VRS.ResourceId
Left Outer join dbo.v_RA_System_SMSAssignedSites SAS on SAS.ResourceID = VRS.ResourceId
left outer join dbo.v_FullCollectionMembership FCM on FCM.ResourceID = VRS.ResourceId
left outer join dbo.v_GS_NETWORK_ADAPTER_CONFIGUR NAC on NAC.ResourceID = VRS.ResourceId
left outer join dbo.v_GS_LOGICAL_DISK LD on LD.ResourceID = Vrs.ResourceId AND LD.DriveType0 = 3
Left Outer join dbo.v_R_User U on VRS.User_Name0 = U.User_Name0
GROUP BY
VRS.Netbios_Name0,
SE.ChassisTypes0,
SAS.SMS_Assigned_Sites0,
VRS.Client_Version0,Vrs.Creation_Date0,
Vrs.AD_Site_Name0,
OS.InstallDate0,
OS.LastBootUpTime0,
BIOS.SerialNumber0,
SE.SMBIOSAssetTag0,
BIOS.ReleaseDate0,
BIOS.Name0,
BIOS.SMBIOSBIOSVersion0,
PRO.Name0,
CS.Manufacturer0,
CS.Model0,
CS.SystemType0,
CS.Domain0,
Vrs.User_Domain0,
Vrs.User_Name0,
U.Mail0,
CS.DomainRole0,
OS.Caption0,
OS.CSDVersion0,
OS.Version0,
RAM.TotalPhysicalMemory0,
WS.LastHWScan
order by
VRS.Netbios_Name0
