topic Re: How to search two sourcetypes to return fields in each sourcetype? in Splunk Search

How to search two sourcetypes to return fields in each sourcetype?

Makinde — Tue, 29 Sep 2020 08:33:47 GMT

I don't know how best to do this, so any advice would work. Here is a brief explanation.

We have the Qualys vulnerability management app. If anyone here is familiar with it, you will realize it has two source types;

The two source types are the host_vulnerability source type and the knowledge_base source type. The Host_vulnerability source type contains the vulnerability it found on each device, but it contains mostly the device name, IP address, and the QID (which is just a number of the vulnerability). The knowledge_base source type on the other hand contains information about the vulnerability, so it also has the QID and give information about the implication of the vulnerability and the solution to fix it.

I want to run a report that will return values from both source types, say for example, I run a search like this;

index=main sourcetype=qualys:host_detection  | stats count by qid, first_found_datetime, | sort -count

This will generate a report of the most vulnerability in my environment in descending order, but all I have is the vulnerability ID and count. I know nothing about the vulnerability except I look it up in the knowledge_base source type. How can I generate this report in such a way that it will give me the solution and title field from the knowledge_base source type, along with the information I am getting already in the search above?

I want both the best way to do this, either by using a lookup table or using a subsearch, or any other idea that will be the best way to go about it.

I would like the sample search as well. so I can modify it and see how it works in my environment.

Re: How to search two sourcetypes to return fields in each sourcetype?

somesoni2 — Wed, 27 Jan 2016 17:27:06 GMT

Try something like this

index=main sourcetype=qualys:host_detection | stats count by qid, first_found_datetime | join qid [search index=main sourcetype=qualys:knowledge_base | stats values(fieldYouWantForVulnerability1) as fieldYouWantForVulnerability1 values(fieldYouWantForVulnerability2) as fieldYouWantForVulnerability2... by quid]

Re: How to search two sourcetypes to return fields in each sourcetype?

Makinde — Wed, 27 Jan 2016 21:34:06 GMT

Hi Somesoni2,

Thanks, it works like magic.

How about you if it were a lookup table instead of a second source type, how would I achieve the same thing?

Thanks,

Re: How to search two sourcetypes to return fields in each sourcetype?

somesoni2 — Wed, 27 Jan 2016 22:43:33 GMT

That's even easier/faster. (assuming lookup also has field name called qid)

 index=main sourcetype=qualys:host_detection | stats count by qid, first_found_datetime | lookup qid OUTPUT fieldYouWantForVulnerability1  fieldYouWantForVulnerability2...

Re: How to search two sourcetypes to return fields in each sourcetype?

Makinde — Thu, 28 Jan 2016 21:05:32 GMT

Thanks Somesoni2. It works.