PostgreSQL End of Life Audit

Find End of Life PostgreSQL Installations

PostgreSQL is one of the most popular object-relational database systems. It is an open source solution containing many features used for complex data workloads. To help you keep an eye on the end-of-life dates for the various PostgreSQL versions we’ve created a special color-coded report that lists your PostgreSQL installations along with the EOL date and how many days are remaining.

Read all about the PostgreSQL lifecycle in our PostgreSQL blog post.

PostgreSQL End of Life Query

Select Distinct Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tsysAssetTypes.AssetTypename As AssetType,
tblAssets.Username,
tblAssets.Userdomain,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.Version as OSVersion,
tblAssets.SP,
subquery1.Software,
subquery1.Version,
subquery1.Publisher,
subquery1.EOLDate,
Case
When GetDate() < subquery1.EOLDate Then Cast(DateDiff(DAY, GetDate(),
subquery1.EOLDate) As NVARCHAR) + ' days remaining'
End As [Days Remaining],
tblAssets.Lastseen,
tblAssets.Lasttried,
Case
When GetDate() > subquery1.EOLDate Then '#ffadad'
When GetDate() >= DateAdd(month, -1, subquery1.EOLDate) Then '#ffd152'
Else '#d4f4be'
End As backgroundcolor
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
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 tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join (Select tblSoftwareUni.softwareName As Software,
tblsoftware.softwareVersion As Version,
tblSoftwareUni.SoftwarePublisher As Publisher,
Case
When Cast(Left(tblsoftware.softwareVersion, PatIndex('%[^0-9]%',
tblsoftware.softwareVersion + 't') - 1) As bigint) < 6 Then
'2003-01-01'
When tblsoftware.softwareVersion Like '6.3%' Then '2003-03-01'
When tblsoftware.softwareVersion Like '6.4%' Then '2003-10-30'
When tblsoftware.softwareVersion Like '6.5%' Then '2004-06-09'
When tblsoftware.softwareVersion Like '7.0%' Then '2005-05-08'
When tblsoftware.softwareVersion Like '7.1%' Then '2006-04-13'
When tblsoftware.softwareVersion Like '7.2%' Then '2007-02-07'
When tblsoftware.softwareVersion Like '7.3%' Then '2007-11-27'
When tblsoftware.softwareVersion Like '7.4%' Then '2010-10-01'
When tblsoftware.softwareVersion Like '8.0%' Then '2010-10-01'
When tblsoftware.softwareVersion Like '8.1%' Then '2010-11-08'
When tblsoftware.softwareVersion Like '8.2%' Then '2011-12-05'
When tblsoftware.softwareVersion Like '8.3%' Then '2013-02-07'
When tblsoftware.softwareVersion Like '8.4%' Then '2014-07-24'
When tblsoftware.softwareVersion Like '9.0%' Then '2015-10-08'
When tblsoftware.softwareVersion Like '9.1%' Then '2016-10-27'
When tblsoftware.softwareVersion Like '9.2%' Then '2017-11-09'
When tblsoftware.softwareVersion Like '9.3%' Then '2018-11-08'
When tblsoftware.softwareVersion Like '9.4%' Then '2020-02-13'
When tblsoftware.softwareVersion Like '9.5%' Then '2021-02-11'
When tblsoftware.softwareVersion Like '9.6%' Then '2021-11-11'
When tblsoftware.softwareVersion Like '10%' Then '2022-11-10'
When tblsoftware.softwareVersion Like '11%' Then '2023-11-09'
When tblsoftware.softwareVersion Like '12%' Then '2024-11-14'
When tblsoftware.softwareVersion Like '13%' Then '2025-11-13'
When tblsoftware.softwareVersion Like '14%' Then '2026-11-12'
When Cast(Left(tblsoftware.softwareVersion, PatIndex('%[^0-9]%',
tblsoftware.softwareVersion + 't') - 1) As bigint) > 14 Then
'2027-01-01'
End As EOLDate,
tblsoftware.AssetID
From tblsoftware
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblsoftware.softID
Where tblSoftwareUni.softwareName Like '%PostgreSQL%') As subquery1 On
subquery1.AssetID = tblAssets.AssetID
Where subquery1.Software Like '%PostgreSQL%' And tblState.Statename = 'Active'

Show

Hide