Notification

Icon
Error

Inventory Windows systems

Posted: Tuesday, August 21, 2018 8:30:24 AM(UTC)
BioFerS

BioFerS

Member Original PosterPosts: 3
3
Like
I share my little sheet. Welcome to make it better.

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


Code:
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
chaudharyumesh
#1chaudharyumesh Member Posts: 3  
posted: 12/31/2018 11:43:20 AM(UTC)
Please add motherboard name, serial no and softwares MS Office and Autocad
nekrosoft13
#2nekrosoft13 Member Posts: 6  
posted: 5/14/2019 3:35:41 AM(UTC)
i'm getting this error "There was an error parsing the query. [ Token line number = 1,Token line offset = 2888,Token in error = Select ]"

Active Discussions

Lansweeper non-active computer report
by  Apaulcolypse   Go to last post Go to first unread
Last post: Yesterday at 5:16:21 PM(UTC)
Lansweeper Report for showing CPUs below or above
by  GBA Craig  
Go to last post Go to first unread
Last post: 7/18/2019 3:38:41 PM(UTC)
Lansweeper Hardware list
by  GBA Craig   Go to last post Go to first unread
Last post: 7/18/2019 3:34:43 PM(UTC)
Lansweeper Top 10 users submitting tickets
by  LGuth  
Go to last post Go to first unread
Last post: 7/17/2019 9:29:44 PM(UTC)
Lansweeper Microsoft Patch Tuesday Report - July 2019
by  Noobmode   Go to last post Go to first unread
Last post: 7/17/2019 8:48:08 PM(UTC)
Lansweeper Custom Scanning - file not existing
by  Bruce Garoutte  
Go to last post Go to first unread
Last post: 7/17/2019 8:19:50 PM(UTC)
Lansweeper MouseJack vulnerability affected devices report
by  Viper   Go to last post Go to first unread
Last post: 7/16/2019 5:19:14 PM(UTC)
Lansweeper TPM support
by  Sander Eerdekens  
Go to last post Go to first unread
Last post: 7/16/2019 1:38:17 PM(UTC)