Splunk Search

How to get the sum according to field values?

auaave
Communicator

Hi Guys,

I am counting the number of events from field name "LOCATION".This Field have 4 locations, Location A,B,C and D.
I need to get the count of events from Location A B and C and name it as Position 1 Events then events from Location D as Position 2.
Then after that, I need to get the percentage of events from Position 1 (ABC) and Position 2 (D).

How can I do that?

Thank you!

Tags (2)
0 Karma
1 Solution

micahkemp
Champion

One option is to use stats with count(eval()) to get counts of each location, then determine the percent at the end (I'm not sure which direction you want the percent, so you may need to flip that portion:

    | makeresults
    | append [| makeresults | eval LOCATION="A"]
    | append [| makeresults | eval LOCATION="B"]
    | append [| makeresults | eval LOCATION="C"]
    | append [| makeresults | eval LOCATION="D"]
    | stats count(eval(LOCATION="A" OR LOCATION="B" OR LOCATION="C")) AS "Position 1 Events", count(eval(LOCATION="D")) AS "Position 2 Events"
    | eval Position1Pct='Position 1 Events'/('Position 1 Events'+'Position 2 Events')*100, Position2Pct='Position 2 Events'/('Position 1 Events'+'Position 2 Events')*100

Another option is to use something closer to a lookup (faked with a case statement here) to translate LOCATION to position, then perform the same count/eval method:

    | makeresults
    | append [| makeresults | eval LOCATION="A"]
    | append [| makeresults | eval LOCATION="B"]
    | append [| makeresults | eval LOCATION="C"]
    | append [| makeresults | eval LOCATION="D"]
    | eval position=case(LOCATION="A" OR LOCATION="B" OR LOCATION="C", 1, LOCATION="D", 2)
    | stats count(eval(position=1)) AS "Position 1 Events", count(eval(position=2)) AS "Position 2 Events"
    | eval Position1Pct='Position 1 Events'/('Position 1 Events'+'Position 2 Events')*100, Position2Pct='Position 2 Events'/('Position 1 Events'+'Position 2 Events')*100

View solution in original post

micahkemp
Champion

One option is to use stats with count(eval()) to get counts of each location, then determine the percent at the end (I'm not sure which direction you want the percent, so you may need to flip that portion:

    | makeresults
    | append [| makeresults | eval LOCATION="A"]
    | append [| makeresults | eval LOCATION="B"]
    | append [| makeresults | eval LOCATION="C"]
    | append [| makeresults | eval LOCATION="D"]
    | stats count(eval(LOCATION="A" OR LOCATION="B" OR LOCATION="C")) AS "Position 1 Events", count(eval(LOCATION="D")) AS "Position 2 Events"
    | eval Position1Pct='Position 1 Events'/('Position 1 Events'+'Position 2 Events')*100, Position2Pct='Position 2 Events'/('Position 1 Events'+'Position 2 Events')*100

Another option is to use something closer to a lookup (faked with a case statement here) to translate LOCATION to position, then perform the same count/eval method:

    | makeresults
    | append [| makeresults | eval LOCATION="A"]
    | append [| makeresults | eval LOCATION="B"]
    | append [| makeresults | eval LOCATION="C"]
    | append [| makeresults | eval LOCATION="D"]
    | eval position=case(LOCATION="A" OR LOCATION="B" OR LOCATION="C", 1, LOCATION="D", 2)
    | stats count(eval(position=1)) AS "Position 1 Events", count(eval(position=2)) AS "Position 2 Events"
    | eval Position1Pct='Position 1 Events'/('Position 1 Events'+'Position 2 Events')*100, Position2Pct='Position 2 Events'/('Position 1 Events'+'Position 2 Events')*100

auaave
Communicator

Hi @ micahkemp, thanks for your reply! It worked! I am just having problems with my % calculation.
I think I didn't make it clear enough. Sorry for that.
%A = (Position1/(Position1 + Position2) * 100
%B = (Position2/(Position1 + Position2) * 100
and the number format should be in %
I can only use the division function but I can't combine it with the sum.

Thank you!

0 Karma

auaave
Communicator

@micahkemp, thanks a lot! 🙂

0 Karma

micahkemp
Champion

Updated answer to reflect the percentage.

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...