→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

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

View solution in original post

5 REPLIES 5
RCorbeil
Honored Sweeper II
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.
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
Engaged Sweeper
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
Champion Sweeper III
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
Honored Sweeper II
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.