Archive

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

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

Revered Legend

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

Revered Legend

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

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

Revered Legend

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

New Member

Thanks Somesoni2. It works.

0 Karma