ā02-16-2024 03:26 PM - edited ā02-16-2024 03:27 PM
I am trying to create a report that shows systems that have an application installed "x" and when installed the version is not "y" or "z". I have several of these type reports that work fine when I am only looking at one version. With the version being a string and not a number I can't use greater than to evaluate. Below is what I have but it returns no systems, and I am 100% sure there are systems that don't have either version of the application installed. Does anyone know how to make this work, assuming it is even possible?
Thank you very much.
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 'x' And
tblSoftware.softwareVersion Not Like 'y%' And
tblSoftware.softwareVersion Not Like 'z%' And tblAssetCustom.State = 1
ā02-21-2024 05:26 AM
what is the full version standard? X.X.X.X , or X.X.X ?
Thanks
ā02-19-2024 05:36 AM
Try this:
Select Top 1000000 tblassets.AssetID,
tblassets.AssetName,
tblassets.IPAddress,
tblassets.Lastseen,
tblassets.Lasttried,
tblSoftwareUni.softwareName
From tblassets
Inner Join tblSoftware On tblassets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblassets.AssetID Not In (Select Top 1000000 tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where (tblSoftwareUni.softwareName Like '%finereader 9%' Or
tblSoftwareUni.softwareName Like '%finereader 11%')) And
tblSoftwareUni.softwareName Like '%finereader 12%'
ā02-19-2024 05:52 PM
unfortunately for the application I am trying to generate the report on, the version is not stored with the software name so including the version with the name did not work.
ā02-19-2024 06:03 PM
You can change code of sql to version
ā02-19-2024 06:07 PM
ok, I tried this as well and it too returns nothing. When I try to add the extra parentheses that your response has, the report editor just removes them.
Where tblSoftwareUni.softwareName Like 'x' And
(tblSoftware.softwareVersion Not Like 'y%' Or
tblSoftware.softwareVersion Not Like 'z%') And tblAssetCustom.State = 1
ā02-19-2024 06:19 PM - edited ā02-20-2024 04:56 PM
Don't use 'not like' with 'or'
ā02-19-2024 06:18 PM
You should use my query logics.
Please send full code not cut
ā02-19-2024 06:29 PM
I apologize for not understanding your query logic. I thought I was correctly interpreting your suggestion and updating the query. Below is the full code of the query.
Select Top 1000000 tblAssets.AssetID,
tblAssets.Domain,
tblAssets.AssetName,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblSoftware.Installdate,
tblAssets.Lastseen,
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 'x' And
(tblSoftware.softwareVersion Not Like 'y%' Or
tblSoftware.softwareVersion Not Like 'z%') And tblAssetCustom.State = 1
Order By tblAssets.Domain,
tblAssets.AssetName
ā02-20-2024 05:08 AM
Obviously,
expression (tblSoftware.softwareVersion Not Like 'y%' Or tblSoftware.softwareVersion Not Like 'z%') is always true.
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now