cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
BioFerS
Engaged Sweeper II
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
2 REPLIES 2
nekrosoft13
Engaged Sweeper III
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
Engaged Sweeper
Please add motherboard name, serial no and softwares MS Office and Autocad