topic Re: Help to convert SQL query to Splunk search in Splunk Search

How to convert SQL query to Splunk search?

idkgirly — Tue, 11 Apr 2023 22:34:49 GMT

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)

Re: Help to convert SQL query to Splunk search

somesoni2 — Tue, 04 Apr 2023 18:09:51 GMT

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

Re: Help to convert SQL query to Splunk search

idkgirly — Tue, 04 Apr 2023 19:16:44 GMT

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

Re: Help to convert SQL query to Splunk search?

woodcock — Tue, 11 Apr 2023 20:26:36 GMT

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