‎12-31-2014 09:49 AM
Solved! Go to Solution.
‎01-05-2015 03:22 PM
Select Top 1000000 tblAssetUserRelations.Username As [Employee Name],
tblAssets.AssetID,
tblAssets.AssetName As Computer,
tblAssetCustom.Custom3 As [SAP Asset code],
tblAssetCustom.Serialnumber As [Service Tag],
Case When tblAssets.AssetID In (Select tblPortableBattery.AssetID
From tblPortableBattery) Then tblAssetCustom.Model Else 'o'
End As [Model Laptop],
Case When tblAssets.AssetID In (Select tblPortableBattery.AssetID
From tblPortableBattery) Then 'o' Else tblAssetCustom.Model
End As [Model Desktop],
tblAssets.Processor As CPU,
tblAssets.Memory As RAM,
Cast(Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 / 1024 As
numeric) As nvarchar) + 'GB' As HDD,
tCustomMonitor.Model As [Monitor Model],
tCustomMonitor.Serialnumber As [Monitor Serial#],
tCustomMonitor.Custom3 As [Monitor SAP Asset code],
tsysOS.OSname As OS,
tblAssets.Lastseen,
tblAssets.Lasttried
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tblAssetUserRelations
On tblAssets.AssetID = tblAssetUserRelations.AssetID
Left Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Left Join tblPortableBattery On tblAssets.AssetID = tblPortableBattery.AssetID
Left Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Left Join tblAssetRelations
On tblAssets.AssetID = tblAssetRelations.ParentAssetID
Inner Join tblAssets tAssetsMonitor On tAssetsMonitor.AssetID =
tblAssetRelations.ChildAssetID
Inner Join tsysAssetTypes tTypeMonitors On tAssetsMonitor.Assettype =
tTypeMonitors.AssetType
Inner Join tblAssetCustom tCustomMonitor On tAssetsMonitor.AssetID =
tCustomMonitor.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblAssetCustom.State = 1 And (tblAssetUserRelations.Type = 1 Or
Coalesce(tblAssetUserRelations.Type, '') = '') And
tblComputersystem.Domainrole < 2 And tblDiskdrives.Caption = 'c:' And
(tTypeMonitors.AssetTypename = 'Monitor' Or
Coalesce(tTypeMonitors.AssetTypename, '') = '')
‎01-06-2015 01:41 PM
‎01-06-2015 06:36 AM
‎01-05-2015 03:22 PM
Select Top 1000000 tblAssetUserRelations.Username As [Employee Name],
tblAssets.AssetID,
tblAssets.AssetName As Computer,
tblAssetCustom.Custom3 As [SAP Asset code],
tblAssetCustom.Serialnumber As [Service Tag],
Case When tblAssets.AssetID In (Select tblPortableBattery.AssetID
From tblPortableBattery) Then tblAssetCustom.Model Else 'o'
End As [Model Laptop],
Case When tblAssets.AssetID In (Select tblPortableBattery.AssetID
From tblPortableBattery) Then 'o' Else tblAssetCustom.Model
End As [Model Desktop],
tblAssets.Processor As CPU,
tblAssets.Memory As RAM,
Cast(Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 / 1024 As
numeric) As nvarchar) + 'GB' As HDD,
tCustomMonitor.Model As [Monitor Model],
tCustomMonitor.Serialnumber As [Monitor Serial#],
tCustomMonitor.Custom3 As [Monitor SAP Asset code],
tsysOS.OSname As OS,
tblAssets.Lastseen,
tblAssets.Lasttried
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tblAssetUserRelations
On tblAssets.AssetID = tblAssetUserRelations.AssetID
Left Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Left Join tblPortableBattery On tblAssets.AssetID = tblPortableBattery.AssetID
Left Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Left Join tblAssetRelations
On tblAssets.AssetID = tblAssetRelations.ParentAssetID
Inner Join tblAssets tAssetsMonitor On tAssetsMonitor.AssetID =
tblAssetRelations.ChildAssetID
Inner Join tsysAssetTypes tTypeMonitors On tAssetsMonitor.Assettype =
tTypeMonitors.AssetType
Inner Join tblAssetCustom tCustomMonitor On tAssetsMonitor.AssetID =
tCustomMonitor.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblAssetCustom.State = 1 And (tblAssetUserRelations.Type = 1 Or
Coalesce(tblAssetUserRelations.Type, '') = '') And
tblComputersystem.Domainrole < 2 And tblDiskdrives.Caption = 'c:' And
(tTypeMonitors.AssetTypename = 'Monitor' Or
Coalesce(tTypeMonitors.AssetTypename, '') = '')
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now