Include File Scanning Within another report

[brandon_jones]

I have a piece of software that doesn't appear in the add remove programs in Windows. I have set up a file scan for this software and I have a report for it, but I have written a report that show the software on computers that is out of date and I want to include the software that doesn't appear in add remove programs.

I have tried adding tblFileVersions.FilePathfull like '%pathname\filename%' but I get a entry for every file scan that I have on every computer. Does anyone know how this can be done?

[brandon_jones]

Thanks! That worked like a charm. I added a line where I filtered out the versions that were already updated using
tblfileversions.fileversion<>'updatedversion'

[RCorbeil · Accepted solution]

First, you're going to want to make a correction to the WHERE clause on your current query. As you've currently got it set up:

Where
     (tblSoftwareUni.softwareName Like '%software name%' And tblSoftware.softwareVersion < 'software version%' And tblSoftware.softwareVersion Not Like 'software version%')
  Or (tblSoftwareUni.softwareName Like '%softwarename%')
  Or (tblSoftwareUni.softwareName Like 'softwarename' And tblSoftware.softwareVersion < 'software version%' And tblSoftware.softwareVersion Not Like 'softwareversion%')
  Or (tblSoftwareUni.softwareName Like 'softwarename%' And tblSoftware.softwareVersion < '1softwareversion And tblSoftware.softwareVersion Not Like 'softwareversion')
  Or (tblSoftwareUni.softwareName Like 'softwarename%' And tblSoftware.softwareVersion < 'software version And tblSoftware.softwareVersion Not Like '5.5.0.14558')
  Or (tblSoftwareUni.softwareName Like 'softwarename%' And tblSoftware.softwareVersion < '10.13.0.0' And tblSoftware.softwareVersion Not Like '10.13.0.0')
  Or (tblSoftwareUni.softwareName Like 'softwarename%' And tblSoftware.softwareVersion Not Like 'software version' And tblSoftware.softwareVersion < 'software version')
  Or (tblSoftwareUni.softwareName Like 'softwarename%' And tblSoftware.softwareVersion Not Like 'software version' And tblSoftware.softwareVersion < 'software version')
  Or (tblSoftwareUni.softwareName Like 'softwarename%' And tblSoftware.softwareVersion < 'software version' And tblSoftware.softwareVersion Not Like 'software version')
  Or (tblSoftwareUni.softwareName Like '%softwarename%' And tblSoftware.softwareVersion < 'softwareversion' And tblSoftware.softwareVersion Not Like 'software version')
  Or (tblSoftwareUni.softwareName Like '%softwarename%' And tblSoftware.softwareVersion < 'softwareversion' And tblSoftware.softwareVersion Not Like 'softwareversoin')
  Or (tblSoftwareUni.softwareName Like '%softwarename%' And tblSoftware.softwareVersion < 'softwareversion' And tblSoftware.softwareVersion Not Like 'softwareversion')
  Or (tblSoftwareUni.softwareName Like '%softwarename%' And tblSoftware.softwareVersion < 'softwareversion' And tblSoftware.softwareVersion Not Like 'softwareversion')
  Or (tblSoftwareUni.softwareName Like '%nuance vmware%' And tblSoftware.softwareVersion < 'softwareversion' And tblSoftware.softwareVersion Not Like 'softwareversion)
  Or (tblSoftwareUni.softwareName Like '%softwarename%' And tblSoftware.softwareVersion < 'softwareversion' And tblSoftware.softwareVersion Not Like 'softwareversion')
  Or (tblSoftwareUni.softwareName Like '%softwarename%' And tblSoftware.softwareVersion < 'softwareversion' And tblSoftware.softwareVersion Not Like 'softwareversion')
  Or (tblSoftwareUni.softwareName Like 'softwarename' And tsysAssetTypes.AssetTypename = 'Windows' And tblAssetCustom.State = 1)

Correction:

Where
  (    (tblSoftwareUni.softwareName Like '%software name%' And tblSoftware.softwareVersion < 'software version%' And tblSoftware.softwareVersion Not Like 'software version%')
    Or (tblSoftwareUni.softwareName Like '%softwarename%')
    Or (tblSoftwareUni.softwareName Like 'softwarename' And tblSoftware.softwareVersion < 'software version%' And tblSoftware.softwareVersion Not Like 'softwareversion%')
    Or (tblSoftwareUni.softwareName Like 'softwarename%' And tblSoftware.softwareVersion < '1softwareversion And tblSoftware.softwareVersion Not Like 'softwareversion')
    Or (tblSoftwareUni.softwareName Like 'softwarename%' And tblSoftware.softwareVersion < 'software version And tblSoftware.softwareVersion Not Like '5.5.0.14558')
    Or (tblSoftwareUni.softwareName Like 'softwarename%' And tblSoftware.softwareVersion < '10.13.0.0' And tblSoftware.softwareVersion Not Like '10.13.0.0')
    Or (tblSoftwareUni.softwareName Like 'softwarename%' And tblSoftware.softwareVersion Not Like 'software version' And tblSoftware.softwareVersion < 'software version')
    Or (tblSoftwareUni.softwareName Like 'softwarename%' And tblSoftware.softwareVersion Not Like 'software version' And tblSoftware.softwareVersion < 'software version')
    Or (tblSoftwareUni.softwareName Like 'softwarename%' And tblSoftware.softwareVersion < 'software version' And tblSoftware.softwareVersion Not Like 'software version')
    Or (tblSoftwareUni.softwareName Like '%softwarename%' And tblSoftware.softwareVersion < 'softwareversion' And tblSoftware.softwareVersion Not Like 'software version')
    Or (tblSoftwareUni.softwareName Like '%softwarename%' And tblSoftware.softwareVersion < 'softwareversion' And tblSoftware.softwareVersion Not Like 'softwareversoin')
    Or (tblSoftwareUni.softwareName Like '%softwarename%' And tblSoftware.softwareVersion < 'softwareversion' And tblSoftware.softwareVersion Not Like 'softwareversion')
    Or (tblSoftwareUni.softwareName Like '%softwarename%' And tblSoftware.softwareVersion < 'softwareversion' And tblSoftware.softwareVersion Not Like 'softwareversion')
    Or (tblSoftwareUni.softwareName Like '%nuance vmware%' And tblSoftware.softwareVersion < 'softwareversion' And tblSoftware.softwareVersion Not Like 'softwareversion)
    Or (tblSoftwareUni.softwareName Like '%softwarename%' And tblSoftware.softwareVersion < 'softwareversion' And tblSoftware.softwareVersion Not Like 'softwareversion')
    Or (tblSoftwareUni.softwareName Like '%softwarename%' And tblSoftware.softwareVersion < 'softwareversion' And tblSoftware.softwareVersion Not Like 'softwareversion')
    Or (tblSoftwareUni.softwareName Like 'softwarename')
  )
  And tsysAssetTypes.AssetTypename = 'Windows'
  And tblAssetCustom.State = 1

Note the difference in the last few lines.

As to the files scanned, would doing a UNION of your current query with a query to identify your specific files do what you're after?

<your existing query>

UNION ALL

Select
  tblAssets.AssetID,
  tblAssets.Domain,
  tblAssets.AssetName,
  tblFileVersions.FilePathFull,
  tblFileVersions.FileVersion,
  NULL AS InstallDate,
  tblAssets.Lastseen,
  tblAssets.LastActiveScan,
  tblAssets.Username,
  tblAssets.IPAddress
From
  tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblFileVersions On tblFileVersions.AssetID = tblAssets.AssetID
WHERE
  tblFileVersions.Found = 1
  AND tblFileVersions.FilePathFull LIKE '%pathname\filename%'
  And tsysAssetTypes.AssetTypename = 'Windows'
  And tblAssetCustom.State = 1

[brandon_jones]

This is my query.

Select Distinct Top 1000000 tblAssets.AssetID,
tblAssets.Domain,
tblAssets.AssetName,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblSoftware.Installdate,
tblAssets.Lastseen,
tblAssets.LastActiveScan,
tblAssets.Username,
tblAssets.IPAddress
From tblAssets
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where (tblSoftwareUni.softwareName Like '%software name%'
And tblSoftware.softwareVersion < 'software version%' And
tblSoftware.softwareVersion Not Like 'software version%') Or
(tblSoftwareUni.softwareName Like '%softwarename%') Or
(tblSoftwareUni.softwareName Like 'softwarename' And
tblSoftware.softwareVersion < 'software version%' And
tblSoftware.softwareVersion Not Like 'softwareversion%') Or
(tblSoftwareUni.softwareName Like 'softwarename%' And
tblSoftware.softwareVersion < '1softwareversion And tblSoftware.softwareVersion Not
Like 'softwareversion') Or
(tblSoftwareUni.softwareName Like 'softwarename%' And
tblSoftware.softwareVersion < 'software version And tblSoftware.softwareVersion
Not Like '5.5.0.14558') Or
(tblSoftwareUni.softwareName Like 'softwarename%' And
tblSoftware.softwareVersion < '10.13.0.0' And tblSoftware.softwareVersion Not
Like '10.13.0.0') Or
(tblSoftwareUni.softwareName Like 'softwarename%' And
tblSoftware.softwareVersion Not Like 'software version' And
tblSoftware.softwareVersion < 'software version') Or
(tblSoftwareUni.softwareName Like 'softwarename%' And
tblSoftware.softwareVersion Not Like 'software version' And
tblSoftware.softwareVersion < 'software version') Or
(tblSoftwareUni.softwareName Like 'softwarename%' And
tblSoftware.softwareVersion < 'software version' And
tblSoftware.softwareVersion Not Like 'software version') Or
(tblSoftwareUni.softwareName Like '%softwarename%' And tblSoftware.softwareVersion <
'softwareversion' And tblSoftware.softwareVersion Not Like 'software version') Or
(tblSoftwareUni.softwareName Like '%softwarename%' And
tblSoftware.softwareVersion < 'softwareversion' And tblSoftware.softwareVersion Not
Like 'softwareversoin') Or
(tblSoftwareUni.softwareName Like '%softwarename%' And
tblSoftware.softwareVersion < 'softwareversion' And tblSoftware.softwareVersion
Not Like 'softwareversion') Or
(tblSoftwareUni.softwareName Like '%softwarename%' And
tblSoftware.softwareVersion < 'softwareversion' And
tblSoftware.softwareVersion Not Like 'softwareversion') Or
(tblSoftwareUni.softwareName Like '%nuance vmware%' And
tblSoftware.softwareVersion < 'softwareversion' And
tblSoftware.softwareVersion Not Like 'softwareversion) Or
(tblSoftwareUni.softwareName Like '%softwarename%' And
tblSoftware.softwareVersion < 'softwareversion' And tblSoftware.softwareVersion Not
Like 'softwareversion') Or
(tblSoftwareUni.softwareName Like '%softwarename%' And
tblSoftware.softwareVersion < 'softwareversion' And tblSoftware.softwareVersion Not
Like 'softwareversion') Or
(tblSoftwareUni.softwareName Like 'softwarename' And
tsysAssetTypes.AssetTypename = 'Windows' And tblAssetCustom.State = 1)
Order By tblAssets.AssetName,
tblSoftwareUni.softwareName

[Andy_Sismey]

Can you show your report so fsr ?