Splunk Search

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

Makinde
New Member

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.

0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

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] 

View solution in original post

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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] 
0 Karma

Makinde
New Member

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,

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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...
0 Karma

Makinde
New Member

Thanks Somesoni2. It works.

0 Karma
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...