Custom Report - End of Life / End of Support application installs

Document created by MarkPotts Advocate on Nov 16, 2017
Version 1Show Document
  • View in full screen mode

This report returns all of the installed applications where the End of Life (EOL), End of Support (EOS) or End of Extended Support (EES) dates are 30 days (or less) from expiry, this will also include those which have already expired.  I have designed them as 3 separate reports, however it would be easy enough to amend to putt all the data in a single report if required.

 

The purpose of the report is to allow us to identify the computers that we need to target for software replacement.

 

You can load this into Snow License Manager using the post I provided earlier. https://community.snowsoftware.com/docs/DOC-1283-adding-custom-sql-reports-in-snow-license-manager

 

End of Life

 

select tco.[HostName], tco.[Manufacturer], tco.[Model], tco.[BiosSerialNumber], tco.[OperatingSystem], tco.[LastScanDate], tap.[name] as 'Application Name', tma.[Name], tap.EndofLifeDate, tap.EndofSupportDate, tap.EndofExtendedSupportDate,

tca.[InstallDate], tca.[FirstUsed], tca.[LastUsed]

from tblComputerApplications tca

left join tblApplication tap on tca.ApplicationID = tap.ApplicationID

left join tblComputer tco on tca.ComputerID = tco.ComputerID

left join tblManufacturer tma on tap.manufacturerID = tma.ManufacturerID

where datediff(dd, getdate(), tap.EndOfLifeDate) <= 30 and tca.isinstalled = 1

 

End of Support

 

 

select tco.[HostName], tco.[Manufacturer], tco.[Model], tco.[BiosSerialNumber], tco.[OperatingSystem], tco.[LastScanDate], tap.[name] as 'Application Name', tma.[Name], tap.EndofLifeDate, tap.EndofSupportDate, tap.EndofExtendedSupportDate,

tca.[InstallDate], tca.[FirstUsed], tca.[LastUsed]

from tblComputerApplications tca

left join tblApplication tap on tca.ApplicationID = tap.ApplicationID

left join tblComputer tco on tca.ComputerID = tco.ComputerID

left join tblManufacturer tma on tap.manufacturerID = tma.ManufacturerID

where datediff(dd, getdate(), tap.EndOfSupportDate) <= 30 and tca.isinstalled = 1

 

End of Extended Support

 

 

select tco.[HostName], tco.[Manufacturer], tco.[Model], tco.[BiosSerialNumber], tco.[OperatingSystem], tco.[LastScanDate], tap.[name] as 'Application Name', tma.[Name], tap.EndofLifeDate, tap.EndofSupportDate, tap.EndofExtendedSupportDate,

tca.[InstallDate], tca.[FirstUsed], tca.[LastUsed]

from tblComputerApplications tca

left join tblApplication tap on tca.ApplicationID = tap.ApplicationID

left join tblComputer tco on tca.ComputerID = tco.ComputerID

left join tblManufacturer tma on tap.manufacturerID = tma.ManufacturerID

where datediff(dd, getdate(), tap.EndOfExtendedSupportDate) <= 30 and tca.isinstalled = 1

6 people found this helpful

Attachments

    Outcomes