cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
jwood_mls
Champion Sweeper
I have a report based on a custom scanned files. Based on those items, I've done a deployment of another file which I am now scanning. What I'd like to do is a report of items that have file #1 but NOT file #2 so that I can make sure that everything has gotten the deployed files.
1 ACCEPTED SOLUTION
RCorbeil
Honored Sweeper II
You'll need to change the sub-selects to query against tblFileVersions instead of tblSoftware. The approach is the same: create a query to check whether file X has been scanned for and/or found; create a second query to check whether file Y has been scanned for and/or found. Those are your sub-selects. Link the main select of your assets against those two.

e.g.
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

View solution in original post

4 REPLIES 4
jwood_mls
Champion Sweeper
Thank you - that is exactly what I was needing. I appreciate it!
RCorbeil
Honored Sweeper II
You'll need to change the sub-selects to query against tblFileVersions instead of tblSoftware. The approach is the same: create a query to check whether file X has been scanned for and/or found; create a second query to check whether file Y has been scanned for and/or found. Those are your sub-selects. Link the main select of your assets against those two.

e.g.
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
RCorbeil
Honored Sweeper II
See this thread. You should be able to use the same approach.
RC62N wrote:
See this thread. You should be able to use the same approach.


Is that going to be different with a custom scanned item versus installed software, though? In other words, would it be listed in tblSoftware?


New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now