topic Re: How do I create a piechart visualization with the count of two field values and the sum of both displayed? in Splunk Search

How do I create a piechart visualization with the count of two field values and the sum of both displayed?

zacksoft — Tue, 29 Sep 2020 16:42:56 GMT

I have a query that gives me the count of certain events with keyword 'ab' OR with keyword 'pq'.
The query is like this

host="somehost1" OR "somehost2" OR "somehost3"
("ab" OR "pq")
| eval count_1 = if(like(_raw,"%ab%")
| eval count_2 = if(like(_raw,"%pq%")
| stats count(count_1) as ab_EventCount sum(count_2) as pq_EventCount

This query gives me the event count with keyword 'ab' and keyword 'pq' in a tabular format in the statistics tab.
I wanted a visualization in Pie chart with showing the percentage of ab_EventCount in one slice , pq_Eventcount in another slice out of the total events this query gives us. But when I choose visualization tab it doesn't happen. The pie shows all wrong.
Could anyone guide me ?

Re: How do I create a piechart visualization with the count of two field values and the sum of both displayed?

gcusello — Mon, 13 Nov 2017 11:30:44 GMT

Hi zacksoft,
try something like this:

host="somehost1" OR "somehost2" OR "somehost3" ("ab" OR "pq") 
| eval word1=if(like(_raw,"%ab%"),"ab"," "),word2=if(like(_raw,"%pq%"),"pq"," "),word=coalesce(word1,word2)
| stats count by word

If it doesn't run put a char (e.g."-") in the eval if)
and then shot them in a pie.
Bye.
Giuseppe

Re: How do I create a piechart visualization with the count of two field values and the sum of both displayed?

zacksoft — Tue, 29 Sep 2020 16:42:59 GMT

@Anonymous
Hi Giuseppe- Thank you . this works.
However, If I am to search two words instead of just ab, how would I tweak the string.
Example : I want (ab AND null1) to be counted and (pq AND null2) to be counted.
Is the following line correct (syntax wise) ?

| eval word=case(like(_raw,"%ab%") AND like(_raw,"%null1%),"ab",like(_raw,"%pq%") AND like(_raw,"%null2%),"pq")
| stats count by word

The above gives me Error in 'eval' command: The expression is malformed.

Re: How do I create a piechart visualization with the count of two field values and the sum of both displayed?

kamlesh_vaghela — Mon, 13 Nov 2017 11:51:52 GMT

HI
Can you please try this one?

host="somehost1" OR "somehost2" OR "somehost3" ("ab" OR "pq") | eval count_1 = if(like(_raw,"%ab%")) | eval count_2 = if(like(_raw,"%pq%")) | stats count(count_1) as ab_EventCount sum(count_2) as pq_EventCount  | transpose

I have just added | transpose to your search.

Thanks

Re: How do I create a piechart visualization with the count of two field values and the sum of both displayed?

zacksoft — Mon, 13 Nov 2017 12:00:20 GMT

Thanks Giuseppe. The solution you provided works like a charm.

Re: How do I create a piechart visualization with the count of two field values and the sum of both displayed?

zacksoft — Mon, 13 Nov 2017 12:03:36 GMT

Thanks kamlesh. The solution you provided works 🙂

Re: How do I create a piechart visualization with the count of two field values and the sum of both displayed?

kamlesh_vaghela — Mon, 13 Nov 2017 12:07:17 GMT

Welcome @zacksoft,

Please accept @cusello or my answer and upvote the comments that help you to close this question.

Happy Splunking

Re: How do I create a piechart visualization with the count of two field values and the sum of both displayed?

niketn — Mon, 13 Nov 2017 12:47:59 GMT

Since there is already an accepted answer, I am adding searchmatch() example which is used for searching pattern within raw data.

 host="somehost1" OR "somehost2" OR "somehost3" ("ab" OR "pq")
| stats count(eval(searchmatch("ab"))) as ab_count count(eval(searchmatch("pq"))) as pq_count
| transpose

http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/CommonEvalFunctions