cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
arnolds_trd
Engaged Sweeper
I am trying to run a report that lets me know when a cert file is missing from a group of PC's with certain software. I am able to get results, but I realize that some of the results are duplicates due to it finding the file in the program files (x86) folder but not in the primary program files folder. Is there any way to reduce this down if the file is found in one location but not the other.

Here is the code that I am using:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblSoftwareUni.softwareName,
tblFileVersions.FilePathfull,
Case
When tblFileVersions.Found Is Null Then 'Not checked'
When tblFileVersions.Found = 0 Then 'Not found'
Else 'Found'
End As FilePresent
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Inner Join tblFileVersions On tblAssets.AssetID = tblFileVersions.AssetID
Where tblSoftwareUni.softwareName Like 'GSG Windows Application' And
tblFileVersions.FilePathfull Like '%certadddate.txt%' And
(tblFileVersions.Found Is Null Or tblFileVersions.Found = 0)
Order By tblAssets.AssetName


Thank you
1 REPLY 1
Jelly
Engaged Sweeper
Subqueries should be useful for this. Code is commented, it will no longer show systems that have the cert in at least one of the filepaths. Sounded like that should be fine.

Select Top 1000000
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblSoftwareUni.softwareName,
--tblFileVersions.FilePathfull, report has been changed to not show found paths
--was likely a source of you seeing duplicates
Case
When tblFileVersions.Found Is Null Then 'Not checked'
Else 'Not found'
--changed name
End As FilePresence
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
--Changed to Left Join
Left Join tblFileVersions On tblAssets.AssetID = tblFileVersions.AssetID
Where
tblSoftwareUni.softwareName Like 'GSG Windows Application'
--might be good to remove the below line as well, not sure
And tblFileVersions.FilePathfull Like '%certadddate.txt%'
--And (tblFileVersions.Found Is Null Or tblFileVersions.Found = 0)
--This should make it so the report will not show assets that have the cert file
--in at least one location
And tblAssets.AssetID not in (
Select tblFileVersions.AssetID
From tblFileVersions
Where
tblFileVersions.FilePathfull Like '%certadddate.txt%'
)
And tblFileVersions.Found = 1
Order By tblAssets.AssetName

New to Lansweeper?

Try Lansweeper For Free

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

Try Now