Filtering 2 fields with multiple values

[LionSplunk]

Hello , 
i am new in Splunk and need help 🙂

i get every week a vulnerability scan log with 2 main fields: "extracted_Host" and "Risk" 

Risk values are: Critical, High and Medium (in the log is often Medium so i must only search for Risk Medium and everything else is excluded)

Extracted_Host: i get many different Host IP 

I must filter which Host get which Risk (Hosts can have multiple Risk values) and what risk is falling away on which date and what risk is new 

right now i am here: 
Problem is i get only one host with all value fields and not how many Risk classification are really on this Host without any Time 

index=nessus Risk IN (Critical,High,Medium)
| fields extracted_Host Risk
| eval Host=coalesce(extracted_Host,Risk,)
| stats values(*) as * by Host

thanks for the help

[yuanliu]

---

I must filter which Host get which Risk (Hosts can have multiple Risk values) and what risk is falling away on which date and what risk is new 

---

You need to first refine your requirement to a point you can mathematically, perhaps even visually represent the desired outcome. (This is really not about Splunk, but about data analytics.)  I cannot think of a single table to represent the above sentence.  Can you illustrate with a mock results table, and illustrate some mock data to derive that mock table?  Are you looking for multiple charts to represent each element in that sentence?

[LionSplunk · Accepted solution]

With this filter i see all combined risk classification per host. 

index=test Risk IN (Critical,High,Medium)
| timechart span=30 count by extracted_Host 

I'm now trying to filter and visualize so I can see how often the host has the rating Critical and how often high etc.  and  not like now that i see only the combined value of all risk classification

index=test
| stats count by extracted_Host, Risk
| stats values(Risk) as Risk by extracted_Host
| eval has_Critical=mvcount(split(Risk, ",")) > 0
| eval has_High=mvcount(split(Risk, ",")) > 0
| eval has_Medium=mvcount(split(Risk, ",")) > 0
| stats sum(has_Critical) as Critical_Count, sum(has_High) as High_Count, sum(has_Medium) as Medium_Count by extracted_Host

but i dont get an output

Thanks for the help

[yuanliu]

First, mvcount(split(Risk, ",")) will not give you any value if the host has more than one Risk because split function operates on string, not multivalue.  Second, I failed to see the logic to calculate has_Critical, has_High, and has_Mediium.  If the split works, wouldn't all three give the same value?

You still need to explain your actual use case in plain words AND illustrated mock outputs because the last stats makes the end goal even less clear.  Suppose everything before that works, how can, say sum(has_Critical) as Critical_Count by extracted_Host give you anything EXCEPT 1?  Do you want a count of number of host that has Critical risks?  In that case, you cannot have by extracted_Host.  Or do you mean you want a count of vulnerabilities on each extracted_Host that have risk rating of Critical?  In that case, your first stat, namely stats count by extracted_Host Risk is all you need.

If you want the former, all that is needed is

index=test Risk IN (Critical, High, Medium)
| stats dc(extracted_Host) as hostCount by Risk

[LionSplunk]

Hello, 
sorry for the missing information. i am realy new to splunk and its complicated with all parameters.

I get one event per host per risk 

Means the host with the IP 10.10.10.10 get scanned with a vulnerability tool and after this i get a log with 20 different vulnerability events. Example maybe 2 with the risk classification Critical - 10 with the risk classification High and 8 with Medium. Every Risk is one event for this host means i get 20 different events on the same Host. 

[ITWhisperer]

It is a little unclear how to help you as you haven't provided (anonymised) examples of the events you are dealing with. For example, do you get one event per host, with all their risks; one event per risk, with all the hosts; or, one event per host per risk, i.e. one host, one risk in each event.

Also, coalesce() does not function the way you seem to be using it - it doesn't concatenate the fields, it merely finds the first non-null field in the list.