PowerShell EOL Audit
Security SoftwareFind End of Life PowerShell Versions
Powershell is one of the most popular command-line tools in the world. Aside from being used by most system administrators, it is also installed by default on Windows devices and is extremely powerful making it the go-to tool to quickly get anything done from small tasks to large automated processes. Over the years Powershell has developed itself into an automation and configuration tool/framework that is optimized for dealing with structured data (e.g. JSON, CSV, XML, etc.), REST APIs, and object models. Additionally, it includes a command-line shell, an associated scripting language, and a framework for processing cmdlets. You can read more about the PowerShell EOL status in the blog post.The audit below provides an overview of all your Windows, Linux or Mac devices along with which PowerShell version was detected through software scanning. It is color-coded to provide an easy overview of which devices are at risk of running an outdated version soon to manage your upgrade progress better.
PowerShell EOL Audit 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, tblOperatingsystem.Caption As OS, tblAssets.Version as OSVersion, 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 tblOperatingsystem On tblOperatingsystem.AssetID = tblAssets.AssetID 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(ParseName(tblSoftware.softwareVersion, 4) As bigint) < 6 then '2019-02-13' When tblsoftware.softwareVersion Like '7.2%' Then '2024-11-08' When tblsoftware.softwareVersion Like '7.1%' Then '2022-05-08' When tblsoftware.softwareVersion Like '7.0%' Then '2022-12-03' When tblsoftware.softwareVersion Like '6.2%' Then '2020-09-04' When tblsoftware.softwareVersion Like '6.1%' Then '2019-09-28' When tblsoftware.softwareVersion Like '6.0%' Then '2019-02-13' End As EOLDate, tblsoftware.AssetID From tblsoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblsoftware.softID Where tblSoftwareUni.softwareName Like '%PowerShell%') As subquery1 On subquery1.AssetID = tblAssets.AssetID Where subquery1.Software Like '%PowerShell%' And tblState.Statename = 'Active' Union 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, tblmacosinfo.SystemVersion As OS, tblAssets.Version as OSVersion, 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 tblmacosinfo on tblmacosinfo.AssetID = tblassets.AssetID Inner Join tblState On tblState.State = tblAssetCustom.State Left Join tsysOS On tsysOS.OScode = tblAssets.OScode Left Join (Select tblSoftwareUni.softwareName As Software, tblMacApplications.Version As Version, tblSoftwareUni.SoftwarePublisher As Publisher, Case When Cast(ParseName(tblMacApplications.Version, 4) As bigint) < 6 then '2019-02-13' When tblMacApplications.Version Like '7.2%' Then '2024-11-08' When tblMacApplications.Version Like '7.1%' Then '2022-05-08' When tblMacApplications.Version Like '7.0%' Then '2022-12-03' When tblMacApplications.Version Like '6.2%' Then '2020-09-04' When tblMacApplications.Version Like '6.1%' Then '2019-09-28' When tblMacApplications.Version Like '6.0%' Then '2019-02-13' End As EOLDate, tblMacApplications.AssetID From tblMacApplications Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblMacApplications.softid Where tblSoftwareUni.softwareName Like '%PowerShell%') As subquery1 On subquery1.AssetID = tblAssets.AssetID Where subquery1.Software Like '%PowerShell%' And tblState.Statename = 'Active' Union Select Distinct Top 1000000 tblAssets.AssetID, tblAssets.AssetName, tblAssets.Domain, tsysAssetTypes.AssetTypename As AssetType, tblLinuxUser.UserName, tblAssets.Userdomain, tsysAssetTypes.AssetTypeIcon10 As icon, tblAssets.IPAddress, tsysIPLocations.IPLocation, tblAssetCustom.Manufacturer, tblAssetCustom.Model, tblLinuxSystem.OSRelease As OS, tblAssets.Version as OSVersion, 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 tblLinuxSystem On tblLinuxSystem.AssetID = tblAssets.AssetID Inner Join tblLinuxUser On tblAssets.AssetID = tblLinuxUser.AssetID Inner Join tblLinuxUserLogon On tblLinuxUserLogon.Id = tblLinuxUser.id Inner Join (Select tblLinuxUser.AssetId, Max(tblLinuxUserLogon.LogonTime) As MaxLogontime From tblLinuxUser Inner Join tblLinuxUserLogon On tblLinuxUserLogon.Id = tblLinuxUser.id Group By tblLinuxUser.assetid) As subquery2 On subquery2.MaxLogontime = tblLinuxUserLogon.LogonTime Inner Join tblState On tblState.State = tblAssetCustom.State Left Join tsysOS On tsysOS.OScode = tblAssets.OScode Left Join (Select tblSoftwareUni.softwareName As Software, tblLinuxSoftware.Version As Version, tblSoftwareUni.SoftwarePublisher As Publisher, Case When tblLinuxSoftware.Version = '_._._' and Cast(ParseName(tblLinuxSoftware.Version, 3) As bigint) < 6 then '2019-02-13' When tblLinuxSoftware.Version Like '7.2%' Then '2024-11-08' When tblLinuxSoftware.Version Like '7.1%' Then '2022-05-08' When tblLinuxSoftware.Version Like '7.0%' Then '2022-12-03' When tblLinuxSoftware.Version Like '6.2%' Then '2020-09-04' When tblLinuxSoftware.Version Like '6.1%' Then '2019-09-28' When tblLinuxSoftware.Version Like '6.0%' Then '2019-02-13' End As EOLDate, tblLinuxSoftware.AssetID From tblLinuxSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblLinuxSoftware.SoftwareUniID Where tblSoftwareUni.softwareName Like '%PowerShell%') As subquery1 On subquery1.AssetID = tblAssets.AssetID Where subquery1.Software Like '%PowerShell%' And tblState.Statename = 'Active' Order By Domain, AssetName, Software