Software Version Inventory by Host

[SysAdm492]

I'm trying to build a report for my software inventory.  I'm able to get it to the point of 

Host 1	Title 1	Version
Host 1	Title 2	Version
Host 1	Title 3	Version
Host 2	Title 1	Version
Host 2	Title 3	Version

But I'd like to get it to be 

HostName	Title 1	Title 2	Title 3
Host 1	Version	Version	Version
Host 2	Version		Version

I can do this transposition in Excel with a lot of copying and pasting of the version numbers but I seems like SQL can do it for me.

Any help from you wizards?

[soapofett · Accepted solution]

The simplest way would probably be joining tblSoftware and tblSoftwareUni to tblAssets and then grabbing the MAX() for each piece of software in the Select statement. Although doing it this way you would need to put a different column name for each piece of software you want a version for. I don't believe it's possible to dynamically make columns in Lansweeper reports for all software, if that's what you were looking for. But if you only have some software you want to find, here's a quick example of a report that should work for you. 

In order to compare the version numbers correctly, if multiple versions of the software are installed on a machine, you need to replace the '.' with a '/' and use hierarchyID to compare them. This report will also put it back to decimals after the comparison for readability. 

Select Top 1000000 tblassets.AssetID,
  tblassets.AssetName,
  tsysassettypes.AssetTypeIcon10 As icon,
  IsNull(Replace(Trim('/' From Cast(Max(Try_Cast('/' + Replace(Case
    When tblSoftwareUni.SoftwareName Like '%Chrome%' Then
      tblSoftware.SoftwareVersion
  End, '.', '/') + '/' As hierarchyid)) As nvarchar(100))), '/', '.'),
  'Not Installed') As [Google Chrome],
  IsNull(Replace(Trim('/' From Cast(Max(Try_Cast('/' + Replace(Case
    When tblSoftwareUni.SoftwareName Like '%Lansweeper%' Then
      tblSoftware.SoftwareVersion
  End, '.', '/') + '/' As hierarchyid)) As nvarchar(100))), '/', '.'),
  'Not Installed') As Lansweeper,
  tblassets.IPAddress,
  tblassets.Lastseen
From tblassets
  Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
  Inner Join tsysassettypes On tsysassettypes.AssetType = tblassets.Assettype
  Left Join tblSoftware On tblassets.AssetID = tblSoftware.AssetID
  Left Join tblSoftwareUni On tblSoftware.SoftID = tblSoftwareUni.SoftID
Where tsysassettypes.AssetTypeName Like '%Windows%'
Group By tblassets.AssetID,
  tblassets.AssetName,
  tsysassettypes.AssetTypeIcon10,
  tblassets.IPAddress,
  tblassets.Lastseen
Order By tblassets.AssetName

The part you would need to edit and add in the Select statement is this, just replace SOFTWARENAME:

  IsNull(Replace(Trim('/' From Cast(Max(Try_Cast('/' + Replace(Case
    When tblSoftwareUni.SoftwareName Like '%SOFTWARENAME%' Then
      tblSoftware.SoftwareVersion
  End, '.', '/') + '/' As hierarchyid)) As nvarchar(100))), '/', '.'),
  'Not Installed') As [SOFTWARENAME]

You'd have to modify it further if you want to look for Linux software by Joining the tblLinuxSoftware table and modifying the Select statement as needed. But hopefully this helps. 

[SysAdm492]

Fantastic!  Thank you so much.  I was able to also filter the AssetNames further using Like Criteria.

It's not working for When tblSoftwareUni.SoftwareName Like '%7-Zip%'.  I don't know what it's not liking about the number and the dash.

[soapofett · Accepted solution]

Yeah, 7-Zip is an odd one. Sometimes the version is stored in tblSoftwareUni.Softwarename along with '7-Zip' so it looks like '7-Zip 25.01 (x64)' and tblSoftware.SoftwareVersion is null. Other times its separated as you would expect. My only guess is that the hyphen or parenthesis is causing issues with the hierarchyid comparison. This subquery seemed to have worked for me:

  IsNull((Select Top 1 s.SoftwareVersion
    From tblSoftware s Inner Join tblSoftwareUni su On s.SoftID = su.SoftID
    Where s.AssetID = tblassets.AssetID And (su.SoftwareName Like '7-Zip%' Or
        su.SoftwareName Like '7 Zip%') Order By s.SoftwareVersion Desc),
  'Not Installed') As [7-Zip]

[SysAdm492]

Perfect!  Thanks a million.

[soapofett · Accepted solution]

The simplest way would probably be joining tblSoftware and tblSoftwareUni to tblAssets and then grabbing the MAX() for each piece of software in the Select statement. Although doing it this way you would need to put a different column name for each piece of software you want a version for. I don't believe it's possible to dynamically make columns in Lansweeper reports for all software, if that's what you were looking for. But if you only have some software you want to find, here's a quick example of a report that should work for you. 

In order to compare the version numbers correctly, if multiple versions of the software are installed on a machine, you need to replace the '.' with a '/' and use hierarchyID to compare them. This report will also put it back to decimals after the comparison for readability. 

Select Top 1000000 tblassets.AssetID,
  tblassets.AssetName,
  tsysassettypes.AssetTypeIcon10 As icon,
  IsNull(Replace(Trim('/' From Cast(Max(Try_Cast('/' + Replace(Case
    When tblSoftwareUni.SoftwareName Like '%Chrome%' Then
      tblSoftware.SoftwareVersion
  End, '.', '/') + '/' As hierarchyid)) As nvarchar(100))), '/', '.'),
  'Not Installed') As [Google Chrome],
  IsNull(Replace(Trim('/' From Cast(Max(Try_Cast('/' + Replace(Case
    When tblSoftwareUni.SoftwareName Like '%Lansweeper%' Then
      tblSoftware.SoftwareVersion
  End, '.', '/') + '/' As hierarchyid)) As nvarchar(100))), '/', '.'),
  'Not Installed') As Lansweeper,
  tblassets.IPAddress,
  tblassets.Lastseen
From tblassets
  Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
  Inner Join tsysassettypes On tsysassettypes.AssetType = tblassets.Assettype
  Left Join tblSoftware On tblassets.AssetID = tblSoftware.AssetID
  Left Join tblSoftwareUni On tblSoftware.SoftID = tblSoftwareUni.SoftID
Where tsysassettypes.AssetTypeName Like '%Windows%'
Group By tblassets.AssetID,
  tblassets.AssetName,
  tsysassettypes.AssetTypeIcon10,
  tblassets.IPAddress,
  tblassets.Lastseen
Order By tblassets.AssetName

The part you would need to edit and add in the Select statement is this, just replace SOFTWARENAME:

  IsNull(Replace(Trim('/' From Cast(Max(Try_Cast('/' + Replace(Case
    When tblSoftwareUni.SoftwareName Like '%SOFTWARENAME%' Then
      tblSoftware.SoftwareVersion
  End, '.', '/') + '/' As hierarchyid)) As nvarchar(100))), '/', '.'),
  'Not Installed') As [SOFTWARENAME]

You'd have to modify it further if you want to look for Linux software by Joining the tblLinuxSoftware table and modifying the Select statement as needed. But hopefully this helps.