Report with serial numbers.

[Southpaw386]

Hi I was looking for a report with the following, but am having trouble creating it.

AssetName
OS
IP Address
Manufacturer
Model
IPLocation
Serial Number
Last successsful scan

[rader]

Not sure if it's a built-in report but here's one you can start with and edit/filter out the data you don't want.

I call it "Inventory Windows Systems".

Select Distinct Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  Case
    When (tblAssetCustom.State Like '1') Then 'Active'
    When (tblAssetCustom.State Like '2') Then 'Non-Active'
  End As State,
  tblAssets1.AssetName As Location,
  tblAssets.Domain,
  tblDomainroles.Domainrolename,
  tsysIPLocations.IPLocation,
  tblAssets.Description,
  tblAssets.Mac,
  tblAssets.IPAddress,
  tblADusers.Username,
  tblADusers.whenCreated,
  tLastLogon.[last logon],
  tblADusers.Firstname,
  tblADusers.Lastname,
  tblAssetCustom.Custom1,
  tblADusers.email,
  tblAssetCustom.Custom2,
  tblAssetCustom.Manufacturer,
  tblComputersystem.Model As Model1,
  tblAssetCustom.Model,
  tblAssetCustom.Serialnumber,
  tblAssetCustom.PurchaseDate,
  tblAssets.Processor,
  Cast(Cast(tblAssets.Memory As BigInt) / 1024 As numeric) As [Memory (GB)],
  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],
  tblFloppy.SerialNumber As SerialNumber1,
  Cast(Cast(tblFloppy.Size As BigInt) / 1024 / 1024 / 1024 As numeric)
  As [Size (GB)],
  tblFloppy.Model As Model2,
  tblVideoController.Caption,
  Case
    When tblAssets.AssetName = SubQuery3.AssetName Then 'Yes'
    Else 'No'
  End As Webcam,
  Case
    When tblAssets.AssetName = SubQuery4.AssetName Then 'Yes'
    Else 'No'
  End As WebcamInteg,
  Case
    When tblAssets.AssetName = SubQuery5.AssetName Then 'Yes'
    Else 'No'
  End As Headset,
  Case
    When tblAssets.AssetName = SubQuery6.AssetName Then 'Yes'
    Else 'No'
  End As Speakers,
  Case
    When tblAssets.AssetName = SubQuery7.AssetName Then 'Yes'
    Else 'No'
  End As Keyboard,
  Case
    When (tblKeyboard.Layout Like '%00000407%') Then 'DE'
    When (tblKeyboard.Layout Like '%00000409%') Then 'EN-US'
    When (tblKeyboard.Layout Like '%00020409%') Then 'EN-US'
    Else 'Unknown'
  End As Layout,
  Case
    When tblAssets.AssetName = SubQuery8.AssetName Then 'Yes'
    Else 'No'
  End As Mouse,
  Case
    When tblAssets.AssetName = SubQuery9.AssetName Then 'Yes'
    Else 'No'
  End As Bluetooth,
  countMonitor.numberMonitors As [Number monitors],
  Stuff((Select ', ' + Cast(t2.MonitorManufacturer As varchar(10))
    From tblMonitor t2
    Where t1.AssetID = t2.AssetID For Xml Path('')), 1, 2,
  '') MonitorManufacturer,
  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 Caption1,
  tblOperatingsystem.Version,
  tblSerialnumber.ProductKey,
  tblOperatingsystem.InstallDate,
  tblAntivirus.DisplayName,
  tblAntivirus.productUpToDate,
  Case
    When tblAssets.AssetName = SubQuery.AssetName Then 'Installed'
    Else ''
  End As PDF,
  Case
    When tblAssets.AssetName = SubQuery22.AssetName Then 'Installed'
    Else ''
  End As Skype,
  Case
    When tblAssets.AssetName = SubQuery2.AssetName Then 'Installed'
    Else ''
  End As WPKG
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,
  tblAssets.AssetName