‎08-21-2018 09:30 AM
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
‎05-14-2019 04:35 AM
‎12-31-2018 11:43 AM
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now