# Easiest way to pull a list of Obsolete Software and OS's **URL:** \ **Category:** InsightVM\ **Tags:** InsightVM\ **Created:** [February 22, 2024, 8:27pm UTC](https://discuss.rapid7.com/t/easiest-way-to-pull-a-list-of-obsolete-software-and-oss/33712 "2024-02-22T20:27:07Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![tpeters](https://discuss.rapid7.com/letter_avatar_proxy/v4/letter/t/51bf81/32.png) [@tpeters](https://discuss.rapid7.com/u/tpeters)\ **Post date:** [February 22, 2024, 8:27pm UTC](https://discuss.rapid7.com/t/easiest-way-to-pull-a-list-of-obsolete-software-and-oss/33712/1 "2024-02-22T20:27:07Z")
Hello! I was just curious if anyone has an easy way to find and export a list of Obsolete Software. I am able to create a report of assets that have obsolete software, but it also exports the list of other vulnerabilities those assets have. Thanks! ---
**Author:** ![sgroeneveld](https://discuss.rapid7.com/letter_avatar_proxy/v4/letter/s/a8b319/32.png) [@sgroeneveld](https://discuss.rapid7.com/u/sgroeneveld)\ **Post date:** [February 23, 2024, 8:46pm UTC](https://discuss.rapid7.com/t/easiest-way-to-pull-a-list-of-obsolete-software-and-oss/33712/2 "2024-02-23T20:46:35Z")
Same joys here; I have a task in place to do this through ICON and [https://endoflife.date/](https://endoflife.date/) API Including a workflow to inform the owners that something becomes EOL so they can prepare. This is impossible with Kafka, but better something than nothing of course. ---
**Author:** ![rrdias](https://discuss.rapid7.com/letter_avatar_proxy/v4/letter/r/d6d6ee/32.png) [@rrdias](https://discuss.rapid7.com/u/rrdias)\ **Post date:** [February 27, 2024, 6:37pm UTC](https://discuss.rapid7.com/t/easiest-way-to-pull-a-list-of-obsolete-software-and-oss/33712/3 "2024-02-27T18:37:00Z")
There is an Obsolete OS and Obsolete Software SQL query that maybe of use to you on the Rapid7 GitHub > > > //github.com/rapid7/insightvm-sql-queries/tree/master/sql-query-export ---
**Author:** ![mmusgrove](https://discuss.rapid7.com/user_avatar/discuss.rapid7.com/mmusgrove/32/2226_2.png) [@mmusgrove](https://discuss.rapid7.com/u/mmusgrove)\ **Post date:** [February 28, 2024, 8:43pm UTC](https://discuss.rapid7.com/t/easiest-way-to-pull-a-list-of-obsolete-software-and-oss/33712/4 "2024-02-28T20:43:34Z")
Most of the queries in that repo have not worked for me. Some won’t even validate which is fine, I won’t try them but some of them do validate (like the two you mentioned) and then they end up failing with no indication of what is wrong. You simply get this error: **Error:** The query is invalid. **Character:** 0 For the two you mentioned, it turned out to be fairly simple to figure out but only because I have used PostgreSQL for a while now. Just remove the spaces around the `::` cast operator. Now to figure out why the Obsolete Software query returns results but the Obsolute OS query does not… ---
**Author:** ![rrdias](https://discuss.rapid7.com/letter_avatar_proxy/v4/letter/r/d6d6ee/32.png) [@rrdias](https://discuss.rapid7.com/u/rrdias)\ **Post date:** [February 28, 2024, 8:58pm UTC](https://discuss.rapid7.com/t/easiest-way-to-pull-a-list-of-obsolete-software-and-oss/33712/5 "2024-02-28T20:58:57Z")
So Iran into that and the mistake I was making was including the description text. only include the query text example - exclude the comment text above the select statment **– Two column report with OS and asset count** **– Copy the SQL query below** select dos.name || ’ - ’ || version os\_version, count(distinct favf.asset\_id) from fact\_asset\_vulnerability\_finding favf JOIN dim\_vulnerability\_category dvc using (vulnerability\_id) JOIN dim\_asset da USING (asset\_id) JOIN dim\_asset\_operating\_system daos using (asset\_Id) JOIN dim\_operating\_system dos on dos.operating\_system\_Id = daos.operating\_system\_id where dvc.category\_name = ‘Obsolete OS’ group by dos.name || ’ - ’ || version order by count(distinct favf.asset\_id) desc ---
**Author:** ![bobsledtedd](https://discuss.rapid7.com/letter_avatar_proxy/v4/letter/b/dc4da7/32.png) [@bobsledtedd](https://discuss.rapid7.com/u/bobsledtedd)\ **Post date:** [February 29, 2024, 8:54pm UTC](https://discuss.rapid7.com/t/easiest-way-to-pull-a-list-of-obsolete-software-and-oss/33712/6 "2024-02-29T20:54:39Z")
This is intriguing. Can you provide more technical details around your approach? ---
**Author:** ![aahuja](https://discuss.rapid7.com/letter_avatar_proxy/v4/letter/a/e19b73/32.png) [@aahuja](https://discuss.rapid7.com/u/aahuja)\ **Post date:** [February 29, 2024, 9:14pm UTC](https://discuss.rapid7.com/t/easiest-way-to-pull-a-list-of-obsolete-software-and-oss/33712/7 "2024-02-29T21:14:19Z")
There is an inbuilt-dashboard card that I use and then expand out of there if it helps. ---
**Author:** ![rrdaniels](https://discuss.rapid7.com/letter_avatar_proxy/v4/letter/r/8491ac/32.png) [@rrdaniels](https://discuss.rapid7.com/u/rrdaniels)\ **Post date:** [February 18, 2026, 1:44pm UTC](https://discuss.rapid7.com/t/easiest-way-to-pull-a-list-of-obsolete-software-and-oss/33712/8 "2026-02-18T13:44:00Z")
I hate to revive a dead thread, but could anyone in this explain to me what exactly needs to be changed to get this to work: SELECT dv.title AS "Vulnerability Title", fasvi.proof AS "Proof of Vulnerability", STRING\_AGG(da.host\_name, ', ') AS "Hosts Affected", STRING\_AGG(da.ip\_address, ', ') AS "IPs Affected", count(da.asset\_id) AS "Number of Affected Assets", COALESCE(ds.summary, 'Upgrade to latest version') AS "Solution Summary", min(fasvi.date) :: date AS "Discovery Date", dv.date\_published AS "Publish Date", NOW() :: date - min(fasvi.date) :: date AS "Age in Days" FROM fact\_asset\_scan\_vulnerability\_instance fasvi JOIN dim\_asset da ON fasvi.asset\_id = da.asset\_id JOIN dim\_vulnerability dv ON dv.vulnerability\_id = fasvi.vulnerability\_id JOIN dim\_vulnerability\_category dvc ON dv.vulnerability\_id = dvc.vulnerability\_id LEFT JOIN dim\_solution ds ON dv.nexpose\_id = ds.nexpose\_id WHERE dvc.category\_name = 'Obsolete Software' GROUP BY dv.title, fasvi.proof, ds.summary, dv.date\_published