11-22-2022 06:01 PM
Good Morning,
I have a custom file scan running to return results for C:\ProgramData\CTES\Ctes.exe
I am getting results on this file scan, but want to build a report based on the results of this scan. I want to locate all Laptops in the environment that do NOT have this file. I did some searching in the forums and couldn't find a solution that matched this requirement.
Any help would be greatly appreciated!
11-23-2022 06:06 AM
I left some code in there if you want to see how to determine laptop/desktop, and left in the found/not found, in case you want to expand on those.
Select Top (1000000) tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.SP,
tblAssets.Lastseen As [Last successful scan],
tblAssets.Lasttried As [Last scan attempt],
Case
When tblPortableBattery.AssetID Is Null And tblBattery.AssetID Is Null Then
'Desktop'
Else 'Laptop'
End As [Desktop/Laptop],
Case
When filelookup.Found = 1 Then 'Yes'
When filelookup.Found = 0 Then 'Not Found'
Else 'Not scanned'
End As [Has Ctest.exe]
From tblAssets
Left Outer Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Outer Join tblPortableBattery On
tblAssets.AssetID = tblPortableBattery.AssetID
Left Outer Join tblBattery On tblAssets.AssetID = tblBattery.AssetID
Left Join
(Select
tblFileVersions.AssetID,
tblFileVersions.Found,
tblFileVersions.FilePathfull,
tblFileVersions.FileVersion,
tblFileVersions.CompanyName,
tblFileVersions.Filesize,
tblFileVersions.Lastchanged
From tblFileVersions
Where tblFileVersions.FilePathfull Like '%ProgramData\CTES\Ctes.exe') filelookup
On tblAssets.AssetID = filelookup.AssetID
Where (tblAssetCustom.Model Is Null Or tblAssetCustom.Model = '' Or
tblAssetCustom.Model Not Like '%Virtual%') And
tblAssets.Lastseen Is Not Null And tblAssets.Lastseen <> '' And
tblState.Statename = 'Active' And tsysAssetTypes.AssetTypename In ('Windows',
'Windows CE')
and tblPortableBattery.AssetID is not null
Order By tblAssets.Domain,
tblAssets.AssetName
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now