cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Single Line report with H/D

Ioannis_Simeoni
Engaged Sweeper
Hello, and thank you in advance.
the system inventory report i created creates a new line for every H/D on the system, so if a systems has 3 hard drives it creates 3 lines with a same info except the Drive letter /size of the disk ext.
i need help combine all the information of a system in a single line.
plus how can i make the size number from bytes to GB ?

1 REPLY 1

TimAlex
Engaged Sweeper II
See 44-48 line:

Select Distinct Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysOS.OSname,
Case
When tsysOS.OScode Like '10.0.10240%' Then '1507'
When tsysOS.OScode Like '10.0.10586%' Then '1511'
When tsysOS.OScode Like '10.0.14393%' Then '1607'
When tsysOS.OScode Like '10.0.15063%' Then '1703'
When tsysOS.OScode Like '10.0.16299%' Then '1709'
When tsysOS.OScode Like '10.0.17134%' Then '1803'
When tsysOS.OScode Like '10.0.17763%' Then '1809'
When tsysOS.OScode Like '10.0.18362%' Then '1903'
When tsysOS.OScode Like '10.0.18363%' Then '1909'
When tsysOS.OScode Like '10.0.19041%' Then '2004'
When tsysOS.OScode Like '10.0.19042%' Then '20H2'
When tsysOS.OScode Like '10.0.19043%' Then '21H2'
End As Version,
tblAssets.Userdomain,
tblAssets.Username,
tblAssets.Description,
tblBaseBoard.Manufacturer As MBManufacturer,
tblBaseBoard.Product,
tblBIOS.ReleaseDate,
tblProcessor.Name,
tblProcessor.Caption,
tblProcessor.MaxClockSpeed,
tblAssets.Memory,
Ceiling(tblOperatingsystem.TotalVisibleMemorySize / 1024) As OSMemory,
Stuff((Select ', ' + IsNull(it.Caption, ' ') + '/' +
IsNull(it.VideoModeDescription, ' ') From tblVideoController As it
Where it.Caption Not Like '%dameware%' And it.Caption Not Like '%ConfigMgr%'
And it.Caption Not Like '%radmin%' And it.Caption Not Like '%Hyper-V%' And
it.AssetID = tblAssets.AssetID Order By it.Caption For Xml Path('')), 1,
1, '') Videocards,
Stuff((Select ', ' + IsNull(t1.MonitorManufacturer, ' ') + ' ' +
IsNull(t1.MonitorModel, ' ') From tblMonitor t1
Where t1.AssetID = tblAssets.AssetID For Xml Path('')), 1, 2, '') Monitors,
Stuff((Select ', ' + IsNull(tblNetworkAdapter.Name, ' ')
From tblNetworkAdapter
Where tblAssets.AssetID = tblNetworkAdapter.AssetID And
tblNetworkAdapter.Name Not Like '%virtual%' And
tblNetworkAdapter.Name Not Like '%VPN%' For Xml Path('')), 1, 2,
'') NetworkAdapters,
Stuff((Select ', ' + IsNull(tblFloppy.Model, ' ') + '(' +
IsNull(Cast(Ceiling(tblFloppy.Size / 1024 / 1024 / 1024) As varchar(30)),
'') + 'GB)' From tblFloppy
Where tblAssets.AssetID = tblFloppy.AssetID And tblFloppy.Size > 0
For Xml Path('')), 1, 2, '') HDDs,
Stuff((Select ', ' + IsNull(tblUSBDevices.Name, ' ') From tblUSBDevices
Where tblAssets.AssetID = tblUSBDevices.AssetID And
tblUSBDevices.Name Not Like N'%Стандарт%' And
tblUSBDevices.Name Not Like '%HID%' And tblUSBDevices.Name Not Like
'%hub%' And tblUSBDevices.Name Not Like N'%концентратор%' And
tblUSBDevices.Name Not Like N'%совместим%' And
tblUSBDevices.Name Not Like N'%устройств%' And
tblUSBDevices.Name Not Like '%generic%' And tblUSBDevices.Name Not Like
N'%мышь%' And tblUSBDevices.Name Not Like N'%клавиатура%' And
tblUSBDevices.Name Not Like '%bluetooth%' And
tblUSBDevices.Name Not Like '%headset%' And tblUSBDevices.Name Not Like
'%printing support%' And tblUSBDevices.Name Not Like N'%поддержка%' And
tblUSBDevices.Name Not Like '%mouse%' And tblUSBDevices.Name Not
Like '%keyboard%' And tblUSBDevices.Name Not Like '%USB Composite Device%'
And tblUSBDevices.Name Not Like '%USB Pointing Device%' And
tblUSBDevices.Name Not Like '%USB Input Device%' And
tblUSBDevices.Name Not Like '%USB Human Interface Device%' And
tblUSBDevices.Name Not Like '%IEEE 1284.4 compatible printer%' And
tblUSBDevices.Name Not Like '%fax%' And tblUSBDevices.Name Not Like
'%EWS%' And tblUSBDevices.Name Not Like '%DOT4%' And
tblUSBDevices.Name Not Like '%LEDM%' And tblUSBDevices.Name Not Like
N'%фильтр%' And tblUSBDevices.Name Not Like '%windows%' And
tblUSBDevices.Name Not Like N'%дисковый%' And
tblUSBDevices.Name Not Like '%storage%' And tblUSBDevices.Name <> '' And
tblUSBDevices.Name Not Like '%unknown%' And tblUSBDevices.Name Not Like
'%HP Universal Printing%' And tblUSBDevices.Name Not Like '%wia driver%'
For Xml Path('')), 1, 2, '') USBs,
tblAssets.Lastseen
From tblAssets
Left Join tblBaseBoard On tblAssets.AssetID = tblBaseBoard.AssetID
Left Join tblProcessor On tblAssets.AssetID = tblProcessor.AssetID
Left Join tblOperatingsystem On tblAssets.AssetID = tblOperatingsystem.AssetID
Left Join tblBIOS On tblAssets.AssetID = tblBIOS.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblAssets.Assettype = -1 And tblAssetCustom.State = 1
Order By tblAssets.AssetName