Notification

Icon
Error

Report on 2 custom scanned files

Posted: Tuesday, February 11, 2020 10:06:11 PM(UTC)
jwood.mls

jwood.mls

Member Original PosterPosts: 48
0
Like
This issue has been solved! Click here to view the solution
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.
RC62N
#1RC62N Member Posts: 429  
posted: 2/11/2020 10:29:57 PM(UTC)
See this thread. You should be able to use the same approach.
jwood.mls
#2jwood.mls Member Original PosterPosts: 48  
posted: 2/11/2020 10:36:58 PM(UTC)
Originally Posted by: RC62N Go to Quoted Post
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?


RC62N
#3RC62N Member Posts: 429  
posted: 2/11/2020 11:09:26 PM(UTC)
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.
Code:
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
jwood.mls
#4jwood.mls Member Original PosterPosts: 48  
posted: 2/12/2020 1:35:14 AM(UTC)
Thank you - that is exactly what I was needing. I appreciate it!

Active Discussions

Lansweeper SNMPv3 global credentials ?
by  JeremySG   Go to last post Go to first unread
Last post: Today at 9:01:16 AM(UTC)
Lansweeper Help Desk not disabling for regular users?
by  Hagobian  
Go to last post Go to first unread
Last post: Yesterday at 11:20:17 PM(UTC)
Lansweeper Wake on Lan wol.exe issue
by  Socal_s197   Go to last post Go to first unread
Last post: Yesterday at 10:08:02 PM(UTC)
Lansweeper Changing DNS Servers via CMD or PowerShell
by  TheITGuy  
Go to last post Go to first unread
Last post: Yesterday at 8:46:06 PM(UTC)
Lansweeper Scanning IP Range Subnet
by  TheITGuy   Go to last post Go to first unread
Last post: Yesterday at 8:42:04 PM(UTC)
Lansweeper Access LS Knowledge base from outside network
by  TheITGuy  
Go to last post Go to first unread
Last post: Yesterday at 8:36:55 PM(UTC)
Lansweeper Problems with deploying PowerShell script
by  TheITGuy   Go to last post Go to first unread
Last post: Yesterday at 8:29:45 PM(UTC)
Lansweeper Swap asset details between 2 computers?
by  Mindspiked  
Go to last post Go to first unread
Last post: Yesterday at 7:47:03 PM(UTC)