‎11-30-2020 08:19 PM
Solved! Go to Solution.
‎12-03-2020 07:58 PM
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)
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
<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
‎12-04-2020 06:44 PM
‎12-03-2020 07:58 PM
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)
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
<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
‎12-03-2020 07:16 PM
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
‎12-03-2020 03:43 PM
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now