→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
tonyb99
Engaged Sweeper
I'm trying to run a report to show machines that have 2 custom scanned file path outcomes (I MEAN BOTH OUTCOMES EXIST AND ARE FOUND VALUE IS 0), and some additional options. As you can see I'm not doing this right. Can you suggest how I can accomplish this. The subsequent conditions are the same for each filepath.

Thanks

Select Top 1000000 upgrade_tblComputers.Computername,
upgrade_tblComputers.ComputerUnique,
upgrade_tblFileVersions.FilePathfull,
upgrade_tblFileVersions.Found,
upgrade_tblFileVersions.FileVersion,
upgrade_tblFileVersions.CompanyName,
upgrade_tblFileVersions.Filesize,
upgrade_tblComputers.Computer,
upgrade_tblComputersystem.Domainrole,
upgrade_tblComputers.Lastseen,
upgrade_tblComputers.LastActiveScan,
upgrade_tblComputers.Domain
From upgrade_tblComputers
Inner Join upgrade_tblFileVersions On upgrade_tblComputers.Computername =
upgrade_tblFileVersions.Computername
Inner Join upgrade_tblComputersystem On upgrade_tblComputers.Computername =
upgrade_tblComputersystem.Computername
Where (upgrade_tblFileVersions.FilePathfull Like'%c:\windows\ccm\ccmexec.exe%' And upgrade_tblFileVersions.Found Like '0'And upgrade_tblComputers.Computer Not Like 'HP-TC%' And upgrade_tblComputersystem.Domainrole <= 1)
And (upgrade_tblFileVersions.FilePathfull Like'%c:\windows\system32\ccm\ccmexec.exe%' And upgrade_tblFileVersions.Found Like '0'And upgrade_tblComputers.Computer Not Like 'HP-TC%' And upgrade_tblComputersystem.Domainrole <= 1)
Order by Computer
1 ACCEPTED SOLUTION
RCorbeil
Honored Sweeper II
From the tables you're referring to, I'm going to assume that you're running v5.

I'm going to suggest a two-step build. First, overbroad to make sure that you're seeing the filenames that you expect, then with the constraints you're looking for.
SELECT Top 1000000
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.Domain,
tbldomainroles.Domainrolename,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblAssets.LastActiveScan,
tblFileVersions.Found,
tblFileVersions.FilePathfull,
tblFileVersions.FileVersion,
tblFileVersions.CompanyName,
tblFileVersions.Filesize
FROM
tblAssets
INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
INNER JOIN tblFileVersions ON tblAssets.AssetID = tblFileVersions.AssetID
INNER JOIN tblComputersystem ON tblAssets.AssetID = tblComputersystem.AssetID
INNER JOIN tbldomainroles ON tbldomainroles.Domainrole = tblComputersystem.Domainrole
WHERE
tblAssetCustom.State = 1

That should return all the custom files you've configured your LANSweeper to look for. You should see the files you're looking to not-find in there (Found=0).

Next, build on the WHERE clause to limit your result set.
WHERE
-- State = Active
tblAssetCustom.State = 1
-- DomainRole = workstation
AND tblComputersystem.Domainrole <= 1
-- Computer name doesn't start with 'HP-TC'
AND tblAssets.AssetName NOT LIKE 'HP-TC%'
-- first path has been searched for
AND Exists ( SELECT Top 1 tblFileVersions.Found
FROM tblFileVersions
WHERE tblFileVersions.AssetID = tblAssets.AssetID
AND tblFileVersions.FilePathfull = 'c:\windows\ccm\ccmexec.exe'
)
-- first path has been not-found
AND ( SELECT Count(*)
FROM tblFileVersions
WHERE tblFileVersions.AssetID = tblAssets.AssetID
AND tblFileVersions.FilePathfull = 'c:\windows\ccm\ccmexec.exe'
AND tblFileVersions.Found = 0
) > 0

-- second path has been searched for
AND Exists ( SELECT Top 1 tblFileVersions.Found
FROM tblFileVersions
WHERE tblFileVersions.AssetID = tblAssets.AssetID
AND tblFileVersions.FilePathfull = 'c:\windows\system32\ccm\ccmexec.exe'
)
-- second path has been not-found
AND ( SELECT Count(*)
FROM tblFileVersions
WHERE tblFileVersions.AssetID = tblAssets.AssetID
AND tblFileVersions.FilePathfull = 'c:\windows\system32\ccm\ccmexec.exe'
AND tblFileVersions.Found = 0
) > 0

That will return all custom-file matches for machines that match the two not-found conditions. Assuming you only want to see results about those two matches, you'll want to add another WHERE condition:
WHERE
tblAssetCustom.State = 1
AND tblComputersystem.Domainrole <= 1
AND tblAssets.AssetName NOT LIKE 'HP-TC%'
-- custom file is one of those we're testing for
AND tblFileVersions.FilePathfull LIKE '%\ccm\ccmexec.exe'
...

The "Exists (SELECT Top 1..." returns true if any attempt has been made to find the file, whether it was found or not.

The "SELECT Count(*)..." counts the number of entries where Found=0, so a count total of >0 means that there is an entry in the table where Found=0.

View solution in original post

1 REPLY 1
RCorbeil
Honored Sweeper II
From the tables you're referring to, I'm going to assume that you're running v5.

I'm going to suggest a two-step build. First, overbroad to make sure that you're seeing the filenames that you expect, then with the constraints you're looking for.
SELECT Top 1000000
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.Domain,
tbldomainroles.Domainrolename,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblAssets.LastActiveScan,
tblFileVersions.Found,
tblFileVersions.FilePathfull,
tblFileVersions.FileVersion,
tblFileVersions.CompanyName,
tblFileVersions.Filesize
FROM
tblAssets
INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
INNER JOIN tblFileVersions ON tblAssets.AssetID = tblFileVersions.AssetID
INNER JOIN tblComputersystem ON tblAssets.AssetID = tblComputersystem.AssetID
INNER JOIN tbldomainroles ON tbldomainroles.Domainrole = tblComputersystem.Domainrole
WHERE
tblAssetCustom.State = 1

That should return all the custom files you've configured your LANSweeper to look for. You should see the files you're looking to not-find in there (Found=0).

Next, build on the WHERE clause to limit your result set.
WHERE
-- State = Active
tblAssetCustom.State = 1
-- DomainRole = workstation
AND tblComputersystem.Domainrole <= 1
-- Computer name doesn't start with 'HP-TC'
AND tblAssets.AssetName NOT LIKE 'HP-TC%'
-- first path has been searched for
AND Exists ( SELECT Top 1 tblFileVersions.Found
FROM tblFileVersions
WHERE tblFileVersions.AssetID = tblAssets.AssetID
AND tblFileVersions.FilePathfull = 'c:\windows\ccm\ccmexec.exe'
)
-- first path has been not-found
AND ( SELECT Count(*)
FROM tblFileVersions
WHERE tblFileVersions.AssetID = tblAssets.AssetID
AND tblFileVersions.FilePathfull = 'c:\windows\ccm\ccmexec.exe'
AND tblFileVersions.Found = 0
) > 0

-- second path has been searched for
AND Exists ( SELECT Top 1 tblFileVersions.Found
FROM tblFileVersions
WHERE tblFileVersions.AssetID = tblAssets.AssetID
AND tblFileVersions.FilePathfull = 'c:\windows\system32\ccm\ccmexec.exe'
)
-- second path has been not-found
AND ( SELECT Count(*)
FROM tblFileVersions
WHERE tblFileVersions.AssetID = tblAssets.AssetID
AND tblFileVersions.FilePathfull = 'c:\windows\system32\ccm\ccmexec.exe'
AND tblFileVersions.Found = 0
) > 0

That will return all custom-file matches for machines that match the two not-found conditions. Assuming you only want to see results about those two matches, you'll want to add another WHERE condition:
WHERE
tblAssetCustom.State = 1
AND tblComputersystem.Domainrole <= 1
AND tblAssets.AssetName NOT LIKE 'HP-TC%'
-- custom file is one of those we're testing for
AND tblFileVersions.FilePathfull LIKE '%\ccm\ccmexec.exe'
...

The "Exists (SELECT Top 1..." returns true if any attempt has been made to find the file, whether it was found or not.

The "SELECT Count(*)..." counts the number of entries where Found=0, so a count total of >0 means that there is an entry in the table where Found=0.