Hi,
I have the following tables:
asset table:
asset_id
solution_id
vulnerability_id
solution table:
solution_id
solution summary
vulnerability table:
vulnerability_id
title
severity
How do I write a splunk query to show a table that has the asset_id, solution summary, title, and severity?
This would be the SQL query that I use:
SELECT a.asset_id, b.solution_id, b.summary as solution_summary, c.vulnerability_id, c.title as vulnerability_title, c.severity
FROM asset a
JOIN solution b USING (solution_id)
JOIN vulnerability c USING(vulnerability_id)
Where is this data? In SQL DB? In Splunk events? In a Splunk lookup file?
Give this a try
(index=asset_index sourcetype=asset_sourcetype)
| fields asset_id, solution_id, vulnerability_id
| join type=left solution_id [search index=solution_index sourcetype=solution_sourcetype | fields solution_id, solution_summary]
| join type=left vulnerability_id [search index=vulnerability_index sourcetype=vulnerability_sourcetype | fields vulnerability_id, title, severity | rename title as vulnerability_title]
| table asset_id solution_summary vulnerability_title severity
I ran the query, but it does not return all the data. I was able to get a complete list of the asset_id, solution_id, and vulnerability_id, however, there are a few that are empty under solution_summary, vulnerability_title, and severity. Am I missing something in the query?
asset_id | solution_id | vulnerability_id | vulnerability_title | solution_summary | severity |
1234 | 456 | 234 | Certificate expired | Obtain new certifcate | Severe |
1234 | 678 | 567 | Security Update for Windows | Critical | |
1234 | 890 | 893 |