Compliance Report - Software Versions

[DamonB]

Hi

I'm wanting to create a report of our Windows devices and their versions of different software and windows itself. I've tried to manipulate other reports by changing things around without luck

Was hoping for the data for each device to be in one row, eg

Then I could just add what software I liked moving forward to help with overall view

Thanks in advance, hope this makes sense

[rom]

Easiest way is to LEFT JOIN on an embedded select that looks up whatever software/etc.

Here's an example I made that checks for software, a windows service, and a file.  You can do registry keys/etc as well.

Select 
  Top 1000000 Coalesce(
    tsysOS.Image, tsysAssetTypes.AssetTypeIcon10
  ) As icon, 
  tblAssets.AssetID, 
  tblAssets.AssetName, 
  tsysOS.OSname As OS, 
  tblAssets.Domain, 
  tblAssets.IPAddress, 
  tsysIPLocations.IPLocation, 
  tbladcomputers.OU, 
  Case When hasnotepadplusplus.AssetID Is Null Then 'No' Else 'Yes' End As [Has Notepad ++ ], 
  Case When hasnotepadplusplus.AssetID Is Null Then '' Else hasnotepadplusplus.softwareversion End As [Notepad ++ Version], 
  Case When checkforxdr.AssetID Is Null Then 'No' Else 'Yes' End As [Has XDR], 
  Case When hassccm.Found = 1 Then 'Yes' Else 'No' End As SCCM, 
  Convert(
    nvarchar(10), 
    Ceiling(
      Floor(
        Convert(integer, tblAssets.Uptime) / 3600 / 24
      )
    )
  ) + ' days ' + Convert(
    nvarchar(10), 
    Ceiling(
      Floor(
        Convert(integer, tblAssets.Uptime) / 3600 % 24
      )
    )
  ) + ' hours ' + Convert(
    nvarchar(10), 
    Ceiling(
      Floor(
        Convert(integer, tblAssets.Uptime) % 3600 / 60
      )
    )
  ) + ' minutes' As Uptime, 
  tblAssets.Firstseen, 
  tblAssets.Lastseen, 
  tblAssets.Lasttried, 
  Case When (
    tblErrors.ErrorText Is Not Null 
    Or tblErrors.ErrorText != ''
  ) 
  And tsysasseterrortypes.ErrorMsg Not Like '%PrinterSpoolDisabledError%' Then 'Scanning Error: ' + tsysasseterrortypes.ErrorMsg Else '' End As ScanningErrors, 
  tblAssetCustom.Comments 
From 
  tblComputersystem 
  Inner Join tblAssets On tblComputersystem.AssetID = tblAssets.AssetID 
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID 
  Left Join tbladcomputers On tblAssets.AssetID = tbladcomputers.AssetID 
  Left Join tsysOS On tblAssets.OScode = tsysOS.OScode 
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype 
  Inner Join tsysIPLocations On tsysIPLocations.LocationID = tblAssets.LocationID 
  Inner Join tblState On tblState.State = tblAssetCustom.State 
  Left Join (
    Select 
      Distinct Top 1000000 tblErrors.AssetID As ID, 
      Max(tblErrors.Teller) As ErrorID 
    From 
      tblErrors 
    Group By 
      tblErrors.AssetID
  ) As ScanningError On tblAssets.AssetID = ScanningError.ID 
  Left Join tblErrors On ScanningError.ErrorID = tblErrors.Teller 
  Left Join tsysasseterrortypes On tsysasseterrortypes.Errortype = tblErrors.ErrorType 
  Left Join (
    Select 
      Top 1000000 tblServices.AssetID 
    From 
      tblServices 
      Inner Join tblServicesUni On tblServicesUni.ServiceuniqueID = tblServices.ServiceuniqueID 
    Where 
      tblServicesUni.Name = 'cyserver'
  ) As checkforxdr On tblAssets.AssetID = checkforxdr.AssetID 
  Left Join (
    Select 
      tblFileVersions.AssetID, 
      tblFileVersions.Found, 
      tblFileVersions.FilePathfull, 
      tblFileVersions.FileVersion, 
      tblFileVersions.CompanyName, 
      tblFileVersions.Filesize, 
      tblFileVersions.Lastchanged 
    From 
      tblFileVersions 
    Where 
      tblFileVersions.FilePathfull Like '%ccmsetup.exe%'
  ) hassccm On tblAssets.AssetID = hassccm.AssetID 
  Left Join (
    Select 
      Top 1000000 tblSoftware.AssetID, 
      tblSoftware.softwareVersion 
    From 
      tblSoftware 
      Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID 
    Where 
      tblSoftwareUni.softwareName Like '%notepad++%'
  ) as hasnotepadplusplus on tblAssets.AssetID = hasnotepadplusplus.AssetID 
Where 
  tsysOS.OSname Like '%win%' 
  And tblAssetCustom.State = 1 
Order By 
  tblAssets.Domain, 
  tblAssets.AssetName