‎11-02-2018 11:25 AM
Select Distinct Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Description,
LTrim(RTrim(Coalesce(tblAssetCustom.Manufacturer, N'') + N' ' +
Coalesce(tblAssetCustom.Model, N''))) As CompModel,
tsysOS.OSname,
tblLanguages.Language,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.Memory,
tblAssets.Processor,
Cast(Cast(tblDiskdrives.Freespace As bigint) / 1024 / 1024 As numeric) As
[HDD Free],
Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 As numeric) As
[HDD Size],
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblSoftwareUni.softwareName
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblOperatingsystem On
tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblLanguages On tblLanguages.LanguageCode =
tblOperatingsystem.OSLanguage
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID,
tblSoftwareUni
Where tblAssets.Lastseen > GetDate() - 60 And tblSoftwareUni.softwareName Like
'%Microsoft Office%' And tblSoftwareUni.softwareName Not Like
'Microsoft Office Professional Plus 2016' And tblAssetCustom.State = 1 And
tblComputersystem.Domainrole <= 1 And tblDiskdrives.Caption = 'C:'
‎11-02-2018 04:26 PM
From
tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblOperatingsystem On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblLanguages On tblLanguages.LanguageCode = tblOperatingsystem.OSLanguage
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Inner Join tblSoftware ON tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.SoftID
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now