Inventory Windows systems

[BioFerS]

I share my little sheet. Welcome to make it better.

To work it had to be with SQL server, not compact version. Link

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%') Or
    (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

[nekrosoft13]

i'm getting this error "There was an error parsing the query. [ Token line number = 1,Token line offset = 2888,Token in error = Select ]"

[chaudharyumesh]

Please add motherboard name, serial no and softwares MS Office and Autocad