Old software report

[PSlagboom]

Hi All,

I would like to have a very simple report that checks software versions on all machines and reports all machines having older versions than the newest in the report. It should report all software.

[Mister_Nobody · Accepted solution]

There is no method to sort non-standard version formats.

My query for Bios versions (max version via sorting by release date):

https://community.lansweeper.com/t5/reports-analytics/bios-version-audit-report-for-microsoft-window...

[Mister_Nobody]

Linux version. *Buggy:

Select Top 1000000 tblAssets.AssetID,
  Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
  tblAssets.AssetName,
  tblSoftwareUni.softwareName,
  tblLinuxSoftware.Version,
  b.LatestVersion As [Highest Version Detected],
  tblLinuxSoftware.Lastchanged,
  tblAssets.Lastseen
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Inner Join tblLinuxSoftware On tblAssets.AssetID = tblLinuxSoftware.AssetID
  Inner Join tblSoftwareUni On
      tblSoftwareUni.SoftID = tblLinuxSoftware.SoftwareUniID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Outer Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join (Select __.softId,
      __.Major,
      __.Minor,
      __.Build,
      __.Revision,
      LatestVersion = __.softwareVersion
    From (Select *,
          r = Row_Number() Over (Partition By _.softId Order By _.Major Desc,
          _.Minor Desc, _.Build Desc, _.Revision Desc)
        From (Select Distinct tblsoftware1.softId,
              Major = Convert(bigint,ParseName(tblsoftware1.softwareVersion,
              4)),
              Minor = Convert(bigint,ParseName(tblsoftware1.softwareVersion,
              3)),
              Build = Convert(bigint,ParseName(tblsoftware1.softwareVersion,
              2)),
              Revision = Convert(bigint,ParseName(tblsoftware1.softwareVersion,
              1)),
              tblsoftware1.softwareVersion
            From (Select it1.AssetId,
                  it1.SoftwareUniId As softId,
                  SubString(Left(it1.version, PatIndex('%[^:.0-9]%',
                  it1.version + 't') - 1), CharIndex(':', Left(it1.version,
                  PatIndex('%[^:.0-9]%', it1.version + 't') - 1)) + 1,
                  Len(Left(it1.version, PatIndex('%[^:.0-9]%', it1.version +
                  't') - 1))) As softwareVersion
                From tblLinuxSoftware it1) tblsoftware1
              Where IsNumeric(Replace(tblsoftware1.softwareVersion, '.',
              '')) = 1) _) __
    Where __.r = 1) b On tblLinuxSoftware.SoftwareUniID = b.softId And
      tblLinuxSoftware.Version <> b.LatestVersion
Order By tblSoftwareUni.softwareName

[Mister_Nobody]

You can create software version analyze query from Chrome audit report

https://www.lansweeper.com/resources/report/software/google-chrome-version-audit/

[PSlagboom]

But that is for Chrome. I would like to see all software and then in red or green to see if its older then the neweset version installed on any machine

[Mister_Nobody · Accepted solution]

After deep dive into the problem I have created report based on LS Chrome audit report logics:

Select Top 1000000 tblAssets.AssetID,
  Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
  tblAssets.AssetName,
  tblSoftwareUni.softwareName As Software,
  tblSoftwareUni.SoftwarePublisher As Publisher,
  tblSoftware.softwareVersion As Version,
  b.LatestVersion As [Highest Version Detected],
  tblSoftware.Lastchanged,
  tblAssets.Lastseen
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
  Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Outer Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join (Select __.SoftID,
      __.Major,
      __.Minor,
      __.Build,
      __.Revision,
      LatestVersion = __.softwareVersion
    From (Select *,
          r = Row_Number() Over (Partition By _.SoftID Order By _.Major Desc,
          _.Minor Desc, _.Build Desc, _.Revision Desc)
        From (Select Distinct tblSoftware.SoftID,
              Major = Convert(int,ParseName(tblSoftware.softwareVersion, 4)),
              Minor = Convert(int,ParseName(tblSoftware.softwareVersion, 3)),
              Build = Convert(int,ParseName(tblSoftware.softwareVersion, 2)),
              Revision = Convert(int,ParseName(tblSoftware.softwareVersion, 1)),
              tblSoftware.softwareVersion
            From tblSoftware
              Inner Join tblSoftwareUni On tblSoftware.SoftID =
                  tblSoftwareUni.SoftID
            Where
              tblSoftware.softwareVersion Not Like N'%[а-Я,a-Z,/,",(,),_,+,*]%'
              And tblSoftware.softwareVersion Not Like '%-%') _) __
    Where __.r = 1) b On tblSoftware.SoftID = b.SoftID And
      tblSoftware.softwareVersion <> b.LatestVersion
Where tblSoftware.softwareVersion Not Like N'%[а-Я,a-Z,/,",(,),_,+,*]%' And
  tblSoftware.softwareVersion Not Like '%-%' And tblAssets.OScode Like '%s' And
  tblState.Statename = 'Active'

Report has bugs or features:

NSClient++ (x64) MySolutions NORDIC 0.5.1044 < 0.4.3.143 - doesn't allign version to 4 number spans

Ignore cyrillic and latin letters, special symbols (/,",(,),_,+,*,-) in software version.

Remove 

And tblAssets.OScode Like '%s'

to show all windows assets software

[Mister_Nobody]

Select Top 1000000 tblAssets.AssetID,
  Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
  tblAssets.AssetName,
  tblSoftwareUni.softwareName As Software,
  tblSoftwareUni.SoftwarePublisher As Publisher,
  tblSoftware.softwareVersion As Version,
  b.LatestVersion As [Highest Version Detected],
  tblSoftware.Lastchanged,
  tblAssets.Lastseen
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
  Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Outer Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join (Select __.SoftID,
      __.Major,
      __.Minor,
      __.Build,
      __.Revision,
      LatestVersion = __.softwareVersion
    From (Select *,
          r = Row_Number() Over (Partition By _.SoftID Order By _.Major Desc,
          _.Minor Desc, _.Build Desc, _.Revision Desc)
        From (Select Distinct tblSoftware.SoftID,
              Major = Convert(bigint,ParseName(tblSoftware.softwareVersion, 4)),
              Minor = Convert(bigint,ParseName(tblSoftware.softwareVersion, 3)),
              Build = Convert(bigint,ParseName(tblSoftware.softwareVersion, 2)),
              Revision = Convert(bigint,ParseName(tblSoftware.softwareVersion,
              1)),
              tblSoftware.softwareVersion
            From tblSoftware
              Inner Join tblSoftwareUni On tblSoftware.SoftID =
                  tblSoftwareUni.SoftID
            Where IsNumeric(Replace(tblSoftware.softwareVersion, '.', '')) =
              1) _) __
    Where __.r = 1) b On tblSoftware.SoftID = b.SoftID And
      tblSoftware.softwareVersion <> b.LatestVersion
Where tblState.Statename = 'Active'

[PSlagboom]

Thanks, this works perfectly for what i need🙌

[Mister_Nobody]

It very simple to change name of software.

For Example:

Select Top 1000000 tblAssets.AssetID,
  Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
  tblAssets.AssetName,
  tblSoftwareUni.softwareName As Software,
  tblSoftwareUni.SoftwarePublisher As Publisher,
  tblSoftware.softwareVersion As Version,
  b.LatestVersion As [Highest Version Detected],
  tblSoftware.Lastchanged,
  tblAssets.Lastseen
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
  Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Outer Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join (Select __.SoftID,
      __.Major,
      __.Minor,
      __.Build,
      __.Revision,
      LatestVersion = __.softwareVersion
    From (Select *,
          r = Row_Number() Over (Partition By _.SoftID Order By _.Major Desc,
          _.Minor Desc, _.Build Desc, _.Revision Desc)
        From (Select Distinct tblSoftware.SoftID,
              Major = Convert(int,ParseName(tblSoftware.softwareVersion, 4)),
              Minor = Convert(int,ParseName(tblSoftware.softwareVersion, 3)),
              Build = Convert(int,ParseName(tblSoftware.softwareVersion, 2)),
              Revision = Convert(int,ParseName(tblSoftware.softwareVersion, 1)),
              tblSoftware.softwareVersion
            From tblSoftware
              Inner Join tblSoftwareUni On tblSoftware.SoftID =
                  tblSoftwareUni.SoftID
            Where tblSoftwareUni.softwareName Like ('%K-Lite Mega Codec Pack%')) _) __
    Where __.r = 1) b On tblSoftware.SoftID = b.SoftID And
      tblSoftware.softwareVersion <> b.LatestVersion
Where tblSoftwareUni.softwareName Like ('%K-Lite Mega Codec Pack%') And
  tblState.Statename = 'Active'

[Mister_Nobody · Accepted solution]

There is no method to sort non-standard version formats.

My query for Bios versions (max version via sorting by release date):

https://community.lansweeper.com/t5/reports-analytics/bios-version-audit-report-for-microsoft-window...