Is it possible to segregate Vulnerability report for Applications & Operationg System Based using SQL query with all the necessary details for the report. Can someone in the group please suggest. Thanks in Advance !!
We don’t really have anything for Application or Software based stuff but you could use the below query to get a start for OS based reporting.
SELECT fa.vulnerabilities, da.ip_address, da.host_name, dos.description FROM fact_asset fa JOIN dim_asset da ON fa.asset_id=da.asset_id JOIN dim_asset_operating_system daos ON fa.asset_id=daos.asset_id AND da.asset_id=daos.asset_id JOIN dim_operating_system dos ON daos.operating_system_id=dos.operating_system_id GROUP BY fa.vulnerabilities, da.ip_address, da.host_name, dos.description
Sorry to bump up an old thread, but is there a similar query for non-OS related vulnerabilities?
The above query isn’t filtering vulnerabilities themselves, it’s looking at all vulnerabilities but adding a column for the OS description as that is also OS info.
@svakharia what exactly are you looking for?
As a managed service provider, I am in a model where vendor A is responsible for OS patching but vendor B is responsible for application patching.
If there is a way to segregate this and produce reports/ views for the two parties, then it becomes easier for respective teams to take care of their vulnerabilities.
I know that we can filter on ‘microsoft’ or ‘windows’ or ‘kernel’ or … bunch of keywords for Linux, but that is a crude way of achieving this.
So, this thread seems to be relevant to the problem I have.
So we don’t have anything in our database that specifically categorizes the vulnerabilities into an overarching criteria of either OS or Application.
We do have all vulnerabilities loaded into specific categories though which you can see by doing an SQL report and running the below query:
select * from dim_vulnerability_category
Use that report to look at all of the categories available and you can create tags based off of those categories. With those tags you could filter your reports.
Or just separate with the report scope, aka a Dynamic Asset Group (DAG) (based on OS or vuln category), and not try to build the separation within the query itself.
I guess thats basically what John is saying above