Summary report for all assets

[Daniel_B]

The following reports can be used to get a summary on all Windows computers hardware and software configuration. On individual asset pages, this summary is available for print-out under tab Report.

All reports meet the following criteria:
- Windows asset
- Only workstations, no servers


HW summary report
Note: This report can contain duplicate rows as it lists each installed processor, video card etc. per asset.

Sorted on:
- Asset name

Select Top 1000000 tblAssets.AssetID, 
  tblAssets.AssetName, 
  Case When Coalesce(tblPortableBattery.AssetID, 0) = 0 then 'Desktop' Else 'Laptop' End as Type,
  tblOperatingsystem.Caption As [Operating system], 
  tblOperatingsystem.ServicePackMajorVersion As [Service Pack], 
  tblOperatingsystem.WindowsDirectory As [Windows folder], 
  tblOperatingsystem.RegisteredUser As [Registered user], 
  tblOperatingsystem.Organization, 
  tblAssets.Domain, 
  tblComputersystem.SystemType, 
  tblOperatingsystem.SerialNumber As [OS serial No], 
  tblOperatingsystem.InstallDate As [OS Install date], 
  tblAssetCustom.Manufacturer, 
  tblAssetCustom.Model, 
  tblAssetCustom.Serialnumber As [Service tag], 
  TsysChassisTypes.ChassisName As Chassis, 
  tblProcessor.Caption As Processor, 
  tblProcessor.NumberOfCores, 
  tblProcessor.NumberOfLogicalProcessors, 
  tblProcessor.Manufacturer As Manufacturer1, 
  tblProcessor.DataWidth, 
  tblProcessor.L2CacheSize, 
  tblProcessor.MaxClockSpeed, 
  tblVideoController.Caption As [Video card], 
  tblBIOS.Caption As BIOS, 
  tblBIOS.Manufacturer As [BIOS manufacturer], 
  tblBIOS.Version As [BIOS version], 
  tblBIOS.SerialNumber As [BIOS Serial No], 
  CorrectMemory.Memory, 
  Ceiling(tblPhysicalMemoryArray.MaxCapacity / 1024) As [Max Memory Capacity], 
  Cast(CorrectMemory.Used As numeric) As [Slots  used], 
  tblPhysicalMemoryArray.MemoryDevices As [Slots available]
From tblAssets 
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID 
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype 
  Left Join tblOperatingsystem On tblAssets.AssetID = tblOperatingsystem.AssetID 
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID 
  Left Join tblSystemEnclosure On tblAssets.AssetID = tblSystemEnclosure.AssetID 
  Left Join TsysChassisTypes On TsysChassisTypes.Chassistype = 
    tblSystemEnclosure.ChassisTypes 
  Left Join tblProcessor On tblAssets.AssetID = tblProcessor.AssetID 
  Left Join tblVideoController On tblAssets.AssetID = tblVideoController.AssetID 
  Left Join tblBIOS On tblAssets.AssetID = tblBIOS.AssetID 
  Left Join tblPhysicalMemoryArray On tblAssets.AssetID = 
    tblPhysicalMemoryArray.AssetID 
  Inner Join (Select tblAssets.AssetID, 
    Sum(Ceiling(tblPhysicalMemory.Capacity / 1024 / 1024)) As Memory, 
    Count(tblPhysicalMemory.Win32_PhysicalMemoryid) As Used 
  From tblAssets 
    Left Outer Join (TsysMemorytypes 
    Right Outer Join tblPhysicalMemory On TsysMemorytypes.Memorytype = 
      tblPhysicalMemory.MemoryType) On tblAssets.AssetID = 
      tblPhysicalMemory.AssetID 
  Group By tblAssets.AssetID, 
    tblPhysicalMemory.MemoryType 
  Having tblPhysicalMemory.MemoryType <> 11) CorrectMemory 
    On CorrectMemory.AssetID = tblAssets.AssetID 
  Left Join tblPortableBattery on tblPortableBattery.AssetID = tblAssets.AssetID
Where tblAssetCustom.State = 1 and tblcomputersystem.domainrole < 2
Order By tblAssets.AssetName

Disk drives report

Sorted on:
- Asset name
- Disk drive caption (drive letter)

Select Top 1000000 tblAssets.AssetID, 
  tblAssets.AssetName, 
  Case When Coalesce(tblPortableBattery.AssetID, 0) = 0 then 'Desktop' Else 'Laptop' End as Type,
  tblAssets.Domain, 
  tblAssetCustom.Manufacturer, 
  tblAssetCustom.Model, 
  tblAssetCustom.Serialnumber As [Service tag], 
  tDiskdrives.Caption As [Disk drive], 
  tDiskdrives.Description As [Disk drive Description], 
  tDiskdrives.FileSystem, 
  Cast(Cast(tDiskdrives.Freespace As bigint) / 1024 / 1024 / 1024 As Numeric) As 
  [Diskdrive Free space], 
  Cast(Cast(tDiskdrives.Size As bigint) / 1024 / 1024 / 1024 As Numeric) As 
  [Diskdrive Size], 
  tDiskdrives.Volumeserialnumber As [Diskdrive Serial No] 
From tblAssets 
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID 
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype 
  Left Join tblOperatingsystem On tblAssets.AssetID = tblOperatingsystem.AssetID 
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID 
  Left Join (Select * 
  From tblDiskdrives 
  Where tblDiskdrives.DriveType = 3) tDiskdrives On tblAssets.AssetID = 
    tDiskdrives.AssetID 
  Left Join tblPortableBattery on tblPortableBattery.AssetID = tblAssets.AssetID
Where tblAssetCustom.State = 1 and tblcomputersystem.domainrole < 2
Order By tblAssets.AssetName, 
  [Disk drive] 

Network adapters report

Sorted on:
- Asset name
- Network adapter name

Select Top 1000000 tblAssets.AssetID, 
  tblAssets.AssetName, 
  tsysAssetTypes.AssetTypename, 
  tsysAssetTypes.AssetTypeIcon10 As icon, 
  tblNetwork.Description As [Network adapter], 
  tblNetwork.IPAddress As [IP Address], 
  tblNetwork.MACaddress As [MAC Address], 
  Case When tblNetwork.DHCPenabled = 1 Then 'True' Else 'False' 
  End As [DHCP enabled], 
  tblNetwork.DefaultIPGateway, 
  tblNetwork.DNSHostname, 
  tblNetwork.IPSubnet 
From tblAssets 
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID 
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID 
  Left Join tblNetwork On tblAssets.AssetID = tblNetwork.AssetID 
Where tblAssetCustom.State = 1 and tblComputersystem.Domainrole < 2
Order By tblAssets.AssetName, 
  [Network adapter] 

Software installations report

Sorted on:
- Asset name
- Software name
- Software version

Select Top 1000000 tblAssets.AssetID, 
  tblAssets.AssetUnique, 
  tblAssets.Domain, 
  tblSoftwareUni.softwareName As software, 
  tblSoftware.softwareVersion As version, 
  tblSoftwareUni.SoftwarePublisher As publisher, 
  tsysOS.Image As icon 
From tblSoftware 
  Inner Join tblAssets On tblSoftware.AssetID = tblAssets.AssetID 
  Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID 
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID 
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID 
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode 
Where tblAssetCustom.State = 1 and tblComputersystem.Domainrole < 2
Order By tblAssets.AssetName, 
  software, 
  version