‎02-11-2020 10:06 PM
Solved! Go to Solution.
‎02-11-2020 11:09 PM
Select Top 1000000
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
softwareX.FileStatus AS X_status,
softwareX.FilePathfull AS X_name,
softwareX.FileVersion AS X_version,
softwareY.FileStatus AS Y_status,
softwareY.FilePathfull AS Y_name,
softwareY.FileVersion AS Y_version
From
tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblComputerSystem On tblComputerSystem.AssetID = tblAssets.AssetID
Left Join (SELECT
tblFileVersions.AssetID,
Case tblFileVersions.Found
When 0 Then 'Absent'
When 1 Then 'Present'
Else '(not checked)'
END AS FileStatus,
tblFileVersions.FilePathfull,
tblFileVersions.FileVersion,
tblFileVersions.CompanyName
FROM
tblFileVersions
WHERE
tblFileVersions.FilePathfull Like '%file_1%') AS softwareX On softwareX.AssetID = tblAssets.AssetID
Left Join (SELECT
tblFileVersions.AssetID,
Case tblFileVersions.Found
When 0 Then 'Absent'
When 1 Then 'Present'
Else '(not checked)'
END AS FileStatus,
tblFileVersions.FilePathfull,
tblFileVersions.FileVersion,
tblFileVersions.CompanyName
FROM
tblFileVersions
WHERE
tblFileVersions.FilePathfull Like '%file_2%') AS softwareY On softwareY.AssetID = tblAssets.AssetID
Where
tblAssetCustom.State = 1 -- active
AND tblAssets.AssetType = -1 -- Windows machines
AND tblComputerSystem.Domainrole <= 1 -- workstations
AND softwareX.FileStatus IN ('Present', '(not checked)') -- file 1 found or not yet checked for
AND (softwareY.AssetID IS NULL
OR SoftwareY.FileStatus IN ('Absent', '(not checked)') ) -- file 2 not found or not yet checked for
‎02-12-2020 01:35 AM
‎02-11-2020 11:09 PM
Select Top 1000000
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
softwareX.FileStatus AS X_status,
softwareX.FilePathfull AS X_name,
softwareX.FileVersion AS X_version,
softwareY.FileStatus AS Y_status,
softwareY.FilePathfull AS Y_name,
softwareY.FileVersion AS Y_version
From
tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblComputerSystem On tblComputerSystem.AssetID = tblAssets.AssetID
Left Join (SELECT
tblFileVersions.AssetID,
Case tblFileVersions.Found
When 0 Then 'Absent'
When 1 Then 'Present'
Else '(not checked)'
END AS FileStatus,
tblFileVersions.FilePathfull,
tblFileVersions.FileVersion,
tblFileVersions.CompanyName
FROM
tblFileVersions
WHERE
tblFileVersions.FilePathfull Like '%file_1%') AS softwareX On softwareX.AssetID = tblAssets.AssetID
Left Join (SELECT
tblFileVersions.AssetID,
Case tblFileVersions.Found
When 0 Then 'Absent'
When 1 Then 'Present'
Else '(not checked)'
END AS FileStatus,
tblFileVersions.FilePathfull,
tblFileVersions.FileVersion,
tblFileVersions.CompanyName
FROM
tblFileVersions
WHERE
tblFileVersions.FilePathfull Like '%file_2%') AS softwareY On softwareY.AssetID = tblAssets.AssetID
Where
tblAssetCustom.State = 1 -- active
AND tblAssets.AssetType = -1 -- Windows machines
AND tblComputerSystem.Domainrole <= 1 -- workstations
AND softwareX.FileStatus IN ('Present', '(not checked)') -- file 1 found or not yet checked for
AND (softwareY.AssetID IS NULL
OR SoftwareY.FileStatus IN ('Absent', '(not checked)') ) -- file 2 not found or not yet checked for
‎02-11-2020 10:29 PM
‎02-11-2020 10:36 PM
RC62N wrote:
See this thread. You should be able to use the same approach.
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now