Report Help - Equal to or less than

[sullivane]

This report gives me all the computers with Flash player that is a certain version. How can I modify it so that it's a equal to or less than that version?

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblSoftwareUni.softwareName,
tblSoftwareUni.SoftwarePublisher,
tblSoftware.softwareVersion,
tblAssets.AssetName
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
Where tblSoftwareUni.softwareName Like '%Adobe Flash Player 19 NPAPI%'
And tblSoftware.softwareVersion Not Like '%20.0.0.235%' And
tblAssetCustom.State = 1
Order By tblAssets.AssetName

Thanks!

[Susan_A]

Just for future reference: the data type is listed next to the field name in the upper section of the report builder. An nvarchar value is a string.

[sullivane]

I need to filter for exact version. I have an after scan deployment set to update Flash if it's less than the recent version. Until now it's been fine, but the current ActiveX and plugin versions are now different. Another + for the fine people at Adobe.

I have found a way around it, but thank you both for your help! I was hoping it would be something easy, but I did not know that the version is stored as a string.

[RCorbeil]

Susan.A's suggestion is probably the simplest way to filter for specific versions.

If all you want to do is check the major version, i.e. identify those running versions older than 20.x.y.z, you can add something like this to your WHERE clause:

WHERE
   ...
   AND Cast(Left(s.softwareVersion, CharIndex('.', s.softwareVersion) - 1) As BigInt) < 20

[sullivane]

Thanks for the suggestions, however I am not an SQl guy. The only thing that made sense to me was, it's stored as a string.

I would never be able to write anything with conversions, I was hoping for < or something.

Thanks a lot, I will try and find another way to do what I want to do.

[RCorbeil · Accepted solution]

The version "number" is stored as a string, so trying to do comparisons as if it were a number is of limited use.

From my network's inventory, Adobe Flash Player's version info appears to be consistently recorded as 'a.b.c.d'. As long as that doesn't change, you can slice that up, convert the component pieces to integers and then do your comparisons against that.

Given the dots can be located as:

  CharIndex('.', s.softwareVersion) AS dot1,
  CharIndex('.', s.softwareVersion, CharIndex('.', s.softwareVersion)+1) AS dot2,
  CharIndex('.', s.softwareVersion, CharIndex('.', s.softwareVersion, CharIndex('.', s.softwareVersion)+1)+1) AS dot3,

You should be able to pull the numbers with something like:

  Cast(Left(s.softwareVersion, CharIndex('.', s.softwareVersion) - 1) As BigInt) AS ver_1,

  Cast(Substring(s.softwareVersion,
            CharIndex('.', s.softwareVersion) + 1,
            CharIndex('.', s.softwareVersion, CharIndex('.', s.softwareVersion)+1) - CharIndex('.', s.softwareVersion) -1) AS BigInt) AS ver_2,

  Cast(Substring(s.softwareVersion,
            CharIndex('.', s.softwareVersion, CharIndex('.', s.softwareVersion)+1)+1,
            CharIndex('.', s.softwareVersion, CharIndex('.', s.softwareVersion, CharIndex('.', s.softwareVersion)+1)+1) - CharIndex('.', s.softwareVersion, CharIndex('.', s.softwareVersion)+1) -1) AS BigInt) AS ver_3,

  Cast(Right(s.softwareVersion, Len(s.softwareVersion) - CharIndex('.', s.softwareVersion, CharIndex('.', s.softwareVersion, CharIndex('.', s.softwareVersion)+1)+1)) AS BigInt) AS ver_4

There may be a cleaner way to do it, but that's a quick-and-dirty solution that can get you started.

Depending on how precise you want to get, your WHERE clause might end up big and ugly, but you should be able to make it work.

[Susan_A]

RC62N wrote:
The version "number" is stored as a string, so trying to do comparisons as if it were a number is of limited use.

From my network's inventory, Adobe Flash Player's version info appears to be consistently recorded as 'a.b.c.d'. As long as that doesn't change, you can slice that up, convert the component pieces to integers and then do your comparisons against that.

We really appreciate you responding to and helping other forum users. We've mentioned this before, but the information you post is always correct and helpful. Kudos to you.

sullivane wrote:
This report gives me all the computers with Flash player that is a certain version. How can I modify it so that it's a equal to or less than that version?

As mentioned by RC62N, adding a filter like this to the software version field does not make sense, as this is a text field. For more information on why this will not work as you would expect it to, I would recommend reviewing my last post in this forum topic.

You can try chopping up the version field and converting the parts to numeric values as RC62N suggested. Another possible solution would be to "hard code" the software versions you *do* want the machines to be running. I assume you only want your machines to be running a recent version of the software and your report is meant to list machines that are not. If you insert the version numbers that are "compliant" into the report below, the query will return machines that are non-compliant. The downside is that you'll have to update the report with each software release. The upside is that this approach will work for all software packages, regardless of the version number format.

Select Top 1000000 tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssets.IPAddress,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblSoftwareUni.softwareName As Software,
  tblSoftware.softwareVersion As Version,
  tblSoftwareUni.SoftwarePublisher As Publisher
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
  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 '%your software name%' And
    tblSoftware.softwareVersion In ('compliant software version 1', 'compliant software version 2', 'compliant software version 3')) And
  tblSoftwareUni.softwareName Like '%your software name%' And tblAssetCustom.State = 1
Order By tblAssets.Domain,
  tblAssets.AssetName