Требовалось собрать базовую информацию о компьютерах в сети (материнская плата, процессор, оперативная память, HDD/SSD), которая необходима для планирования обновления парка техники. После поисков в сети Интернет и адаптации найденных решений получился такой вариант кода отчета:
Select Distinct Top 1000000 tblAssets.AssetName,
tblAssets.AssetID,
tblAssets.FQDN,
tblAssets.Domain,
tblDomainroles.Domainrolename,
tblAssets.IPAddress,
tblAssets.Mac,
tblADusers.Username,
tblADusers.whenCreated,
tLastLogon.[last logon],
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber,
tblAssets.Processor,
Cast(Cast(tblAssets.Memory As BigInt) / 1024 As numeric) As [Memory (GB)],
tblAssets.Memory,
TsysMemorytypes.MemoryName As [Memory Type],
tblPhysicalMemory.Manufacturer As [Memory Manufacturer],
tblPhysicalMemory.PartNumber,
tblPhysicalMemory.Speed,
countfloppy.numberDisks As [Number Disks],
Case
When (tblFloppy.Model Like '%SSD%' Or tblFloppy.Model Like '%Solid State%'
Or tblFloppy.Model Like '%mSS%' Or tblFloppy.Model Like '%mSATA%' Or
tblFloppy.Model Like '%KINGSTON SUV%' Or
tblFloppy.Model Like '%SAMSUNG MZ%' Or
tblFloppy.Model Like '%LITEONIT LCT%' Or tblFloppy.Model Like '%MB0%' Or
tblFloppy.Model Like '%THNSNH%') Then 'SSD'
When (tblFloppy.Model Like '%ST500LM000-1EJ16%') Then 'Solid State Hybrid'
When (tblFloppy.Model Like '%Virtual%' Or
tblFloppy.Model Like '%VBOX%') Then 'Virtual Disk'
When (tblFloppy.Model Like '%USB Device%') Then 'USB'
When (tblFloppy.Model Like '%DELL PERC%' Or
tblFloppy.Model Like '%HP LOGICAL%') Then 'RAID/SCSI/SAS'
When (tblFloppy.Model Like '%ATA Device%' Or
tblFloppy.Model Like '%ST500DM0%') Then 'HDD'
Else 'Unknown'
End As [Harddisk Type],
Cast(Cast(tblFloppy.Size As BigInt) / 1024 / 1024 / 1024 As numeric)
As [Size (GB)],
tblFloppy.Model As Model2,
Stuff((Select ', ' + Cast(t2.MonitorModel As varchar(10)) From tblMonitor t2
Where t1.AssetID = t2.AssetID For Xml Path('')), 1, 2, '') Monitors,
Stuff((Select ', ' + Cast(t2.SerialNumber As varchar(10)) From tblMonitor t2
Where t1.AssetID = t2.AssetID For Xml Path('')), 1, 2, '') SerialNumbers,
tsysOS.Image As icon,
tblOperatingsystem.Caption As OS,
tblOperatingsystem.Version,
tblAssets.Lastseen As [Last Seen],
tblAntivirus.DisplayName As Antivirus
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAssetCustom On tblAssetCustom.AssetID = tblAssets.AssetID
Left Join tblAssetRelations On tblAssetRelations.ChildAssetID =
tblAssets.AssetID
Left Join tsysAssetRelationTypes On tsysAssetRelationTypes.RelationTypeID =
tblAssetRelations.Type
Left Join tblAssets tblAssets1 On
tblAssets1.AssetID = tblAssetRelations.ParentAssetID
Left Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tsysIPLocations On tsysIPLocations.StartIP <= tblAssets.IPNumeric
And tsysIPLocations.EndIP >= tblAssets.IPNumeric
Left Join tblADusers On tblAssets.Username = tblADusers.Username
Left Join tblCPlogoninfo On tblCPlogoninfo.Username = tblADusers.Username
Left Join (Select Max(tblCPlogoninfo.logontime) As [last logon],
tblCPlogoninfo.Username,
tblCPlogoninfo.Domain
From tblCPlogoninfo
Group By tblCPlogoninfo.Username,
tblCPlogoninfo.Domain) tLastLogon On tblADusers.Username =
tLastLogon.Username And tblADusers.Userdomain = tLastLogon.Domain
Left Join (Select tblCPlogoninfo.logontime As [last logon],
tblCPlogoninfo.Username,
tblCPlogoninfo.Domain,
tblCPlogoninfo.AssetID
From tblCPlogoninfo) tLogonAsset On tLogonAsset.[last logon] =
tLastLogon.[last logon] And tLogonAsset.Username = tblADusers.Username And
tLogonAsset.Domain = tblADusers.Userdomain
Left Join tblDomainroles On tblDomainroles.Domainrole =
tblComputersystem.Domainrole
Left Join tblFloppy On tblAssets.AssetID = tblFloppy.AssetID And
tblFloppy.Name Like '\\.\PHYSICALDRIVE0'
Left Join tblVideoController On tblAssets.AssetID = tblVideoController.AssetID
And tblVideoController.DeviceID Like 'VideoController1'
Left Join tblKeyboard On tblAssets.AssetID = tblKeyboard.AssetID
Left Join tblPhysicalMemory On tblAssets.AssetID = tblPhysicalMemory.AssetID
Left Join TsysMemorytypes On TsysMemorytypes.Memorytype =
tblPhysicalMemory.MemoryType
Left Join tblMonitor t1 On tblAssets.AssetID = t1.AssetID
Left Join (Select tblMonitor.AssetID,
Count(tblMonitor.MonitorID) As numberMonitors
From tblMonitor
Group By tblMonitor.AssetID) countMonitor On countMonitor.AssetID =
tblAssets.AssetID
Left Join (Select tblFloppy.AssetID,
Count(tblFloppy.floppyID) As numberDisks
From tblFloppy
Group By tblFloppy.AssetID) countfloppy On countfloppy.AssetID =
tblAssets.AssetID
Left Join tblAntivirus On tblAssets.AssetID = tblAntivirus.AssetID And
tblAntivirus.DisplayName Not Like '%Defender%'
Left Join (Select Top 10000 tblAssets.AssetID,
tblAssets.AssetName,
tblSoftwareUni.softwareName
From tblAssets
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%PDF%') SubQuery On
SubQuery.AssetID = tblAssets.AssetID
Left Join (Select Top 10000 tblAssets.AssetID,
tblAssets.AssetName,
tblSoftwareUni.softwareName
From tblAssets
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%wpkg%') SubQuery2 On
SubQuery2.AssetID = tblAssets.AssetID
Left Join (Select Top 10000 tblAssets.AssetID,
tblAssets.AssetName,
tblSoftwareUni.softwareName
From tblAssets
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%skype%') SubQuery22 On
SubQuery22.AssetID = tblAssets.AssetID
Left Join (Select Top 10000 tblAssets.AssetID,
tblAssets.AssetName,
tblUSBDevices.Name
From tblAssets
Inner Join tblUSBDevices On tblAssets.AssetID = tblUSBDevices.AssetID
Where tblUSBDevices.Name Like '%cam%' And tblUSBDevices.Name Not Like
'%integra%' And tblUSBDevices.Name Like '%BRIO%') SubQuery3 On
SubQuery3.AssetID = tblAssets.AssetID
Left Join (Select Top 10000 tblAssets.AssetID,
tblAssets.AssetName,
tblUSBDevices.Name
From tblAssets
Inner Join tblUSBDevices On tblAssets.AssetID = tblUSBDevices.AssetID
Where tblUSBDevices.Name Like '%integrated cam%') SubQuery4 On
SubQuery4.AssetID = tblAssets.AssetID
Left Join (Select Top 10000 tblAssets.AssetID,
tblAssets.AssetName,
tblUSBDevices.Name
From tblAssets
Inner Join tblUSBDevices On tblAssets.AssetID = tblUSBDevices.AssetID
Where (tblUSBDevices.Name Like '%head%' Or tblUSBDevices.Name Like '%set%'
Or tblUSBDevices.Name Like '%H570e%')) SubQuery5 On SubQuery5.AssetID =
tblAssets.AssetID
Left Join (Select Top 10000 tblAssets.AssetID,
tblAssets.AssetName,
tblUSBDevices.Name
From tblAssets
Inner Join tblUSBDevices On tblAssets.AssetID = tblUSBDevices.AssetID
Where (tblUSBDevices.Name Like '%speaker%' Or
tblUSBDevices.Name Like '%MT202pcs%')) SubQuery6 On SubQuery6.AssetID =
tblAssets.AssetID
Left Join (Select Top 10000 tblAssets.AssetID,
tblAssets.AssetName,
tblUSBDevices.Name
From tblAssets
Inner Join tblUSBDevices On tblAssets.AssetID = tblUSBDevices.AssetID
Where tblUSBDevices.Name Like '%keyboard%') SubQuery7 On SubQuery7.AssetID =
tblAssets.AssetID
Left Join (Select Top 10000 tblAssets.AssetID,
tblAssets.AssetName,
tblUSBDevices.Name
From tblAssets
Inner Join tblUSBDevices On tblAssets.AssetID = tblUSBDevices.AssetID
Where tblUSBDevices.Name Like '%mouse%') SubQuery8 On SubQuery8.AssetID =
tblAssets.AssetID
Left Join (Select Top 10000 tblAssets.AssetID,
tblAssets.AssetName,
tblUSBDevices.Name
From tblAssets
Inner Join tblUSBDevices On tblAssets.AssetID = tblUSBDevices.AssetID
Where tblUSBDevices.Name Like '%Bluetooth%') SubQuery9 On
SubQuery9.AssetID = tblAssets.AssetID
Left Join tblOperatingsystem On tblAssets.AssetID = tblOperatingsystem.AssetID
Left Join tblSerialnumber On tblSerialnumber.ProductID =
tblOperatingsystem.SerialNumber
Where tsysAssetTypes.AssetTypename = 'windows'
Order By tblAssets.Domain
P.S. Модель монитора и его серийный номер - это бонус, полезный при инвентаризации.