How to convert SQL query to Splunk search?

[idkgirly]

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)

[woodcock]

Where is this data?  In SQL DB?  In Splunk events?  In a Splunk lookup file?

[somesoni2]

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

[idkgirly]

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