cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
brandon_jones
Champion Sweeper III
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?
1 ACCEPTED SOLUTION
RCorbeil
Honored Sweeper II
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

View solution in original post

4 REPLIES 4
brandon_jones
Champion Sweeper III
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
Honored Sweeper II
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
Champion Sweeper III
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
Champion Sweeper III
Can you show your report so fsr ?