Application Patching Console Dashboard Report

[ASismey]

This example may be useful to some people, it's a simple Dashboard Report which gives access to the download location of each of the applications you patch / upgrade, just for convenience, with a link to a "how to guide", I have included some of the common applications?

Select Top 1000000 Case
    When Left(tblSoftwareUni.softwareName, 33) Like 'Mozilla Firefox %' Then
      'Mozilla Firefox'
    Else tblSoftwareUni.softwareName
  End As Software,
  Case
    When tblSoftwareUni.softwareName = 'Google Chrome' Then
      'https://chromeenterprise.google/browser/download/'
    When tblSoftwareUni.softwareName = 'LsAgent' Then
      'https://www.lansweeper.com/download/lsagent/'
    When Left(tblSoftwareUni.softwareName, 16) = 'Mozilla Firefox' Then
      'https://www.mozilla.org/en-US/firefox/all/#product-desktop-release'
    When tblSoftwareUni.softwareName Like '%Adobe Acrobat Reader DC%' Then
      'https://www.adobe.com/devnet-docs/acrobatetk/tools/ReleaseNotesDC/index.html '
    When tblSoftwareUni.softwareName Like '%Zoom%' Then
      'https://support.zoom.us/hc/en-us/articles/201362233-Upgrade-update-to-the-latest-version '
    When tblSoftwareUni.softwareName Like '%VLC%' Then
      'https://www.videolan.org/vlc/'
    When tblSoftwareUni.softwareName Like '%Teams Machine-Wide Installer' Then
      'https://docs.microsoft.com/en-us/microsoftteams/msi-deployment'
    When tblSoftwareUni.softwareName Like 'Java%' Then
      'https://java.com/en/download/manual.jsp'
  End As hyperlink_URL,
  'Update LINK' As hyperlink_name_URL,
  'Info' As hyperlink_name_Guide,
  'http://SERVER:81/application%20patching%20procedure.pdf' As
  hyperlink_Guide,
  Count(tblSoftware.AssetID) As Total
From tblSoftware
  Inner Join tblAssets On tblSoftware.AssetID = tblAssets.AssetID
  Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where (Case
      When Left(tblSoftwareUni.softwareName, 33) Like 'Mozilla Firefox %' Then
        'Mozilla Firefox'
      Else tblSoftwareUni.softwareName
    End Like 'Java 8 Update 333%' Or Case
      When Left(tblSoftwareUni.softwareName, 33) Like 'Mozilla Firefox %' Then
        'Mozilla Firefox'
      When Left(tblSoftwareUni.softwareName, 33) Like 'Mozilla Firefox %' Then
        'Mozilla Firefox'
      Else tblSoftwareUni.softwareName
    End Like 'Google Chrome' Or Case
      When Left(tblSoftwareUni.softwareName, 33) Like 'Mozilla Firefox %' Then
        'Mozilla Firefox'
      Else tblSoftwareUni.softwareName
    End Like '%Firefox%' Or Case
      When Left(tblSoftwareUni.softwareName, 33) Like 'Mozilla Firefox %' Then
        'Mozilla Firefox'
      Else tblSoftwareUni.softwareName
    End Like '%LsAgent' Or Case
      When Left(tblSoftwareUni.softwareName, 33) Like 'Mozilla Firefox %' Then
        'Mozilla Firefox'
      Else tblSoftwareUni.softwareName
    End Like 'Adobe Acrobat Reader DC' Or Case
      When Left(tblSoftwareUni.softwareName, 33) Like 'Mozilla Firefox %' Then
        'Mozilla Firefox'
      Else tblSoftwareUni.softwareName
    End Like '%Zoom' Or Case
      When Left(tblSoftwareUni.softwareName, 33) Like 'Mozilla Firefox %' Then
        'Mozilla Firefox'
      Else tblSoftwareUni.softwareName
    End Like '%Teams Machine-Wide Installer' Or Case
      When Left(tblSoftwareUni.softwareName, 33) Like 'Mozilla Firefox %' Then
        'Mozilla Firefox'
      Else tblSoftwareUni.softwareName
    End Like '%VLC%') And tblAssetCustom.State = 1
Group By Case
    When Left(tblSoftwareUni.softwareName, 33) Like 'Mozilla Firefox %' Then
      'Mozilla Firefox'
    Else tblSoftwareUni.softwareName
  End,
  Case
    When tblSoftwareUni.softwareName = 'Google Chrome' Then
      'https://chromeenterprise.google/browser/download/'
    When tblSoftwareUni.softwareName = 'LsAgent' Then
      'https://www.lansweeper.com/download/lsagent/'
    When Left(tblSoftwareUni.softwareName, 16) = 'Mozilla Firefox' Then
      'https://www.mozilla.org/en-US/firefox/all/#product-desktop-release'
    When tblSoftwareUni.softwareName Like '%Adobe Acrobat Reader DC%' Then
      'https://www.adobe.com/devnet-docs/acrobatetk/tools/ReleaseNotesDC/index.html '
    When tblSoftwareUni.softwareName Like '%Zoom%' Then
      'https://support.zoom.us/hc/en-us/articles/201362233-Upgrade-update-to-the-latest-version '
    When tblSoftwareUni.softwareName Like '%VLC%' Then
      'https://www.videolan.org/vlc/'
    When tblSoftwareUni.softwareName Like '%Teams Machine-Wide Installer' Then
      'https://docs.microsoft.com/en-us/microsoftteams/msi-deployment'
    When tblSoftwareUni.softwareName Like 'Java%' Then
      'https://java.com/en/download/manual.jsp'
  End
Order By Software,
  Total Desc

[Obi_1_Cinobi]

Hello there!

Thanks for sharing this! Looks great!