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.
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]
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]
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,
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...
Thanks Somesoni2. It works.