Custom report for software version

[dhicks]

Hi -

I'm looking for a report that will show the following:

• Asset Name - all assets
  • Installed software - each piece of software in a separate column, versus multiple rows for the same asset for every piece of sofware installed
    • Version number for each piece of software
      • For each returned value, select most current version from an authoritative source
      • Compare installed version to most current know version
      • Compare installed version to any versions with know vulnerabilites

Any help would be greatly appreciated!

[ASismey]

Hi , Not sure of an easy way to do this, something which may get you started, but it would be a bit of a manual process to add each application, unless you only want a few applications, this will list each asset and a column for Chrome , column for Adobe with versions ?

Select Top 1000000 tblassets.AssetID,
  tblassets.AssetName,
  Chrome.softwareVersion As [Google Chrome],
  Adobe.softwareVersion As [Adobe Reader]
From tblassets
  Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
  Inner Join tsysassettypes On tsysassettypes.AssetType = tblassets.Assettype
  Right Join (Select tblSoftwareUni.softwareName,
      tblSoftware.AssetID,
      tblSoftware.softwareVersion
    From tblSoftware
      Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
    Where tblSoftwareUni.softwareName = 'Google Chrome') Chrome On
      Chrome.AssetID = tblassets.AssetID
  Right Join (Select tblSoftware.AssetID,
      tblSoftware.softwareVersion,
      tblSoftwareUni.softwareName
    From tblSoftwareUni
      Inner Join tblSoftware On tblSoftwareUni.SoftID = tblSoftware.softID
    Where tblSoftwareUni.softwareName Like 'Adobe Acrobat%') Adobe On
      Adobe.AssetID = tblassets.AssetID
Where tblassetcustom.State = 1

[dhicks]

Thank you, I had come up with something similar. Any thoughts on the piece needed to compare against an authoritative source to show whether the installed version of up to date or has vulnerabilities?

[ASismey]

Not a great fix but you could use , Background Colour, Foreground Colour or have text against the version , so something like 

 Case
    When Chrome.Softwareversion Not Like '%109.0.5414.75%' Then 'red'
  End As foregroundcolor,
  Case
    When Chrome.Softwareversion Not Like '%109.0.5414.75%' Then '#fbfb86'
  End As backgroundcolor,
  Case
    When Chrome.Softwareversion Not Like '%109.0.5414.75%' Then
      'OLD - ' + Chrome.Softwareversion
    Else Chrome.Softwareversion

Complete Report 

Select Top 1000000 tblassets.AssetID,
  tblassets.AssetName,
  Chrome.softwareVersion As [Google Chrome],
  Case
    When Chrome.Softwareversion Not Like '%109.0.5414.75%' Then 'red'
  End As foregroundcolor,
  Case
    When Chrome.Softwareversion Not Like '%109.0.5414.75%' Then '#fbfb86'
  End As backgroundcolor,
  Case
    When Chrome.Softwareversion Not Like '%109.0.5414.75%' Then
      'OLD - ' + Chrome.Softwareversion
    Else Chrome.Softwareversion
  End As Version
From tblassets
  Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
  Inner Join tsysassettypes On tsysassettypes.AssetType = tblassets.Assettype
  Left Join (Select tblSoftwareUni.softwareName,
      tblSoftware.AssetID,
      tblSoftware.softwareVersion
    From tblSoftware
      Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
    Where tblSoftwareUni.softwareName = 'Google Chrome') Chrome On
      Chrome.AssetID = tblassets.AssetID
  Left Join (Select tblSoftware.AssetID,
      tblSoftware.softwareVersion,
      tblSoftwareUni.softwareName
    From tblSoftwareUni
      Inner Join tblSoftware On tblSoftwareUni.SoftID = tblSoftware.softID
    Where tblSoftwareUni.softwareName Like 'Adobe Acrobat%') Adobe On
      Adobe.AssetID = tblassets.AssetID
Where tblassetcustom.State = 1