cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
PSlagboom
Engaged Sweeper II

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.

 

2 ACCEPTED SOLUTIONS
Mister_Nobody
Honored Sweeper II

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...

View solution in original post

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

 

View solution in original post

8 REPLIES 8
Mister_Nobody
Honored Sweeper II

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
Honored Sweeper II

You can create software version analyze query from Chrome audit report

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

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

 

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

 

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'

Thanks, this works perfectly for what i need🙌

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
Honored Sweeper II

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...