Easiest way to pull a list of Obsolete Software and OS's

InsightVM
1

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!

2

Same joys here; I have a task in place to do this through ICON and 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.

1 Like
3

There is an Obsolete OS and Obsolete Software SQL query that maybe of use to you on the Rapid7 GitHub

1 Like
4

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…

5

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

1 Like
6

This is intriguing. Can you provide more technical details around your approach?

7

There is an inbuilt-dashboard card that I use and then expand out of there if it helps.