Full Report Request

[Kostas_P]

Hi all,
I am searching but I cannot find a report that includes all of the following:

Assetname
Username
Displayname or lastuser
Domain or type
DiskSizeGB
Memory in GB
Manufacturer
Model
Serialnumber
OSname
OS 64 or 32bit
Monitors
Number monitors
SerialNumbers
MonitorManufacturer

Thank you

Kostas P
Remote Support for
Nutrimedical Pharmaceuticals
Clinical Nutrition, Medical Devices and Nutritional Supplements

[RCorbeil · Accepted solution]

Most of what you're looking for is straightforward. The multiple monitor code comes courtesy of digitalexpl0it a few years back.

Select Distinct Top 1000000
  tblAssets.AssetID,
  tblAssets.Domain,
  tblAssets.AssetName,
  tsysOS.Image As Icon,
  tblAssets.Lastseen,
  tblAssets.Userdomain,
  tblAssets.Username,
  tblADusers.Displayname,
  tblDomainroles.Domainrolename,
  Convert(Int, tblAssets.Memory / 1024) AS [Memory GB],
  tsysOS.OSname,
  tblAssets.Version AS [OS Version],
  Case
    When tblComputersystem.SystemType Like 'X86%' Then '32-bit'
    When tblComputersystem.SystemType Like 'x64%' Then '64-bit'
    Else ''
  End As [OS Bitness],
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.Serialnumber,
  Case
    When countMonitor.numberMonitors Is Null
    Then 0
    Else countMonitor.numberMonitors
  End As [Number monitors],
  Stuff((Select ', ' + Cast(t2.MonitorModel As varchar(100))
         From tblMonitor t2
         Where t1.AssetID = t2.AssetID For Xml Path('')), 1, 2, '') Monitors,
  Stuff((Select ', ' + Cast(t2.SerialNumber As varchar(30))
         From tblMonitor t2
         Where t1.AssetID = t2.AssetID For Xml Path('')), 1, 2, '') AS [Monitor Serial],
  Stuff((Select ', ' + Cast(t2.MonitorManufacturer As varchar(30))
         From tblMonitor t2
         Where t1.AssetID = t2.AssetID For Xml Path('')), 1, 2, '') AS [Monitor Manufacturer],
  Stuff((Select ', ' + Cast(t2.ManufacturedDate As varchar(11))
         From tblMonitor t2
         Where t1.AssetID = t2.AssetID For Xml Path('')), 1, 2, '') AS [Manufactured Date],
  tblFloppy.Model As [Drive 0 Model],
  Convert(Int, tblFloppy.Size / Power(10, 9)) As [Drive 0 GB]
From
  tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Join tblADusers On tblAssets.Userdomain = tblADusers.Userdomain And tblAssets.Username = tblADusers.Username
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tblDomainroles On tblDomainroles.Domainrole = tblComputersystem.Domainrole
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  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) AS countMonitor On countMonitor.AssetID = tblAssets.AssetID
  Inner Join tblFloppy On tblFloppy.AssetID = tblAssets.AssetID And tblFloppy.Name = '\\.\PHYSICALDRIVE0'
Where
  tblAssetCustom.State = 1
Order by
  tblDomainroles.Domainrolename,
  tblAssets.AssetName