Report to convert software version to integer and look for less than

[sullivane]

I have several reports that show me all assets with a software (let's say Java), that is NOT LIKE a version number. The version is stored as a string, so it's not possible to do a less than.

I am hoping there is someone out there who is an SQL guru who can write a query that would maybe convert it to an integer or somehow do a less than on it.

The reason for this, is we have an after scan deployment in LanSweeper that we use to keep stuff like Java\Flash\Adobe updated. Using the not like is working ok now, but I just into a problem if there is an update to Java and a user runs it before I know and update the report. Then the are on a higher version and it gets downgraded and they run it again, rinse and repeat.

If this was something built into LanSweeper I am sure other people would find it useful, but I will settle for a report at this point.

Thanks!

[RCorbeil]

Yup, slice-and-dice is pretty much the only way to deal with it and only works if you can be certain of the format of the version "number".

Depending on the potential size of what you're working with, you might want to consider zero-padding the sections to produce padded-out strings. See this thread. I had considered multiplying each element to produce a numeric value, but if you have a four-element or larger "number" you run the risk over overflowing a numeric representation.

[TheMachine]

I took your advice and kept it as an expanded string. I also used nested derived tables to create pseudo-variables to avoid code repetition.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  ThirdPeriodQuery.softwareName,
  ThirdPeriodQuery.softwareVersion,
  Right('00000' + Left(ThirdPeriodQuery.softwareVersion, ThirdPeriodQuery.FirstPeriodPosition - 1), 5) + 
  Right('00000' + SubString(ThirdPeriodQuery.softwareVersion, ThirdPeriodQuery.FirstPeriodPosition + 1, ThirdPeriodQuery.SecondPeriodPosition - ThirdPeriodQuery.FirstPeriodPosition - 1), 5) + 
  Right('00000' + 
    Case
      When ThirdPeriodQuery.ThirdPeriodPosition = 0 Then
        Right(ThirdPeriodQuery.softwareVersion, ThirdPeriodQuery.VersionLength - ThirdPeriodQuery.SecondPeriodPosition)
      Else 
	    SubString(ThirdPeriodQuery.softwareVersion, ThirdPeriodQuery.SecondPeriodPosition + 1, ThirdPeriodQuery.ThirdPeriodPosition - ThirdPeriodQuery.SecondPeriodPosition - 1)
    End, 5) + 
  Right('00000' + 
    Case
      When ThirdPeriodQuery.ThirdPeriodPosition = 0 Then 
	    ''
      Else 
	    Right(ThirdPeriodQuery.softwareVersion, ThirdPeriodQuery.VersionLength - ThirdPeriodQuery.ThirdPeriodPosition)
    End, 5) As ExpandedVersion
From tblAssets
  Inner Join (Select tblAssets.AssetID,
        SecondPeriodQuery.softwareName,
        SecondPeriodQuery.softwareVersion,
        SecondPeriodQuery.FirstPeriodPosition,
        SecondPeriodQuery.SecondPeriodPosition,
		SecondPeriodQuery.VersionLength,
        CharIndex('.', SecondPeriodQuery.softwareVersion, SecondPeriodQuery.SecondPeriodPosition + 1) As ThirdPeriodPosition        
      From tblAssets
        Inner Join (Select tblAssets.AssetID,
              FirstPeriodQuery.softwareName,
              FirstPeriodQuery.softwareVersion,
              FirstPeriodQuery.FirstPeriodPosition,
			  FirstPeriodQuery.VersionLength,
              CharIndex('.', FirstPeriodQuery.softwareVersion, FirstPeriodQuery.FirstPeriodPosition + 1) As SecondPeriodPosition
            From tblAssets
              Inner Join (Select tblAssets.AssetID,
                    tblSoftwareUni.softwareName,
                    tblSoftware.softwareVersion,
					Len(tblSoftware.softwareVersion) As VersionLength,
                    CharIndex('.', tblSoftware.softwareVersion) As FirstPeriodPosition                    
                  From tblAssets
                    Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
                    Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
                  Where tblSoftwareUni.softwareName Like 'Java [678]%')
              FirstPeriodQuery On tblAssets.AssetID = FirstPeriodQuery.AssetID)
        SecondPeriodQuery On tblAssets.AssetID = SecondPeriodQuery.AssetID)
  ThirdPeriodQuery On tblAssets.AssetID = ThirdPeriodQuery.AssetID

[TheMachine]

I needed to do this so here's what I came up with. I'm quite proud of this SQL statement so had to share. Since we can't use variables the charindex functions have to be repeated a gazillion times. Caution: It really bogs down the report editor.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblSoftwareUni.softwareName,
  tblSoftware.softwareVersion,
  Convert(int,SubString(tblSoftware.softwareVersion, 1, CharIndex('.',
  tblSoftware.softwareVersion) - 1)) * 10000000 +
  Convert(int,SubString(tblSoftware.softwareVersion, CharIndex('.',
  tblSoftware.softwareVersion) + 1, CharIndex('.', tblSoftware.softwareVersion,
  CharIndex('.', tblSoftware.softwareVersion) + 1) - CharIndex('.',
  tblSoftware.softwareVersion) - 1)) * 1000000 + Convert(int,Case
    When CharIndex('.', tblSoftware.softwareVersion, CharIndex('.',
      tblSoftware.softwareVersion, CharIndex('.', tblSoftware.softwareVersion) +
      1) + 1) = 0 Then SubString(tblSoftware.softwareVersion, CharIndex('.',
      tblSoftware.softwareVersion, CharIndex('.', tblSoftware.softwareVersion) +
      1) + 1, Len(tblSoftware.softwareVersion) - CharIndex('.',
      tblSoftware.softwareVersion, CharIndex('.', tblSoftware.softwareVersion) +
      1))
    Else SubString(tblSoftware.softwareVersion, CharIndex('.',
      tblSoftware.softwareVersion, CharIndex('.', tblSoftware.softwareVersion) +
      1) + 1, CharIndex('.', tblSoftware.softwareVersion, CharIndex('.',
      tblSoftware.softwareVersion, CharIndex('.', tblSoftware.softwareVersion) +
      1) + 1) - CharIndex('.', tblSoftware.softwareVersion, CharIndex('.',
      tblSoftware.softwareVersion) + 1) - 1)
  End) * 100 + Case
    When CharIndex('.', tblSoftware.softwareVersion, CharIndex('.',
      tblSoftware.softwareVersion, CharIndex('.', tblSoftware.softwareVersion) +
      1) + 1) = 0 Then 0
    Else Convert(int,SubString(tblSoftware.softwareVersion, CharIndex('.',
      tblSoftware.softwareVersion, CharIndex('.', tblSoftware.softwareVersion,
      CharIndex('.', tblSoftware.softwareVersion) + 1) + 1) + 1,
      Len(tblSoftware.softwareVersion) - CharIndex('.',
      tblSoftware.softwareVersion, CharIndex('.', tblSoftware.softwareVersion,
      CharIndex('.', tblSoftware.softwareVersion) + 1) + 1)))
  End As VersionNumberInteger
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
  Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblSoftwareUni.softwareName Like 'Java [678]%' And tblAssetCustom.State =
  1

[sullivane]

Holy crap, I completely forgot I attempted this once before! Sorry!! I tried on another forum a couple of weeks ago, but forgot I tried here first.

I think I am stuck with what I have. Like you said java has a.b.c.d and a.b.c and I have tried several suggestions but never comes out right.

I think Lansweeper would have to collect and store them in a specific way and do the work for what I want to work, SQL will not.

Thanks and sorry again!

[RCorbeil · Accepted solution]

If you're hoping for a single "transform the reported version to a number" function, it's not going to happen. From my inventory, I've got "version number" values of

• 15.14.00.0
  
• 1.5
  
• 1.4.2_09
  
• 8.19.4-051206a-031438C-Dell
  
• 1.3.10909.11001d
  
• 2001-02
  
• V5.7
  
• r11 v0
  
• 1.02.17.00(2012.01.11)
  
• 10.86.xx05.0035
  
• 3.5.5 (en-US)
  
• %DisplayVersion%
  
• V6.5L30
  
• Version 6.00a of 2009-Dec-03 (Build 129) (Setup)
  
• 12/03/2008 8.3.1.1011
  

I've got some software where the version is part of the title and nothing is reported as the version number.

The best you can do is set up custom handlers for the specific software you're interested in. Even there it can be tricky. Using your Java example, I've got some records in my inventory that report a version in the form "a.b.c.d" and others that report only "a.b.c" (e.g. 8.0.920.14 and 8.0.510), so each of those would need to be handled slightly differently.

Refer to your previous query on the subject. I included sample code there that could be used to slice a 4-part, decimal-separated numeric version number of the form "8.0.770.3" into four distinct numeric values. It's not elegant, but it will work.