Splunk Search

How to sum the percentage of this two value in the table and rename it as X and show it in the table next to other values?!

aryamehr360
New Member

alt text
As you can see in the picture there is 2 value (ChargeInProgress & Charging) which I know they are same (but whit the different name),
So I wish to Sum the percentage of this two value and rename it as X and showi it in the table next to other values,

I have try this :

"Set status * for charging point"
| rex "charging point\s*(?P<charging_point>.*)" 
| rex "Set status\s*(?P<status>.*?,*)\s" 
| eventstats sum(ChargeInProgress,Charging) as X
| dedup charging_point
| top limit=20 status 
| fields status, percent

but not works, and same result :

status                                        percent
Idle                                          71.397640
SuspendedEV                           11.518077
Charging                                  9.131379
ChargeInProgress                  4.996529
Preparing                                 1.456150
SuspendedEVSE                 0.547640
Error                                         0.442961
Finishing                                 0.386764
Unavailable                       0.122310
NotMonitored                          0.000551

I would like to see this as a result

status                                        percent
Idle                                          71.397640
X                                                 14.127908
SuspendedEV                           11.518077
Preparing                                 1.456150
SuspendedEVSE                 0.547640
Error                                         0.442961
Finishing                                 0.386764
Unavailable                       0.122310
NotMonitored                          0.000551
Tags (1)
0 Karma
1 Solution

to4kawa
Ultra Champion
"Set status * for charging point" 
| rex "charging point\s*(?P<charging_point>.*)" 
| rex "Set status\s*(?P<status>.*?,*)\s" 
| eval status=case(status="Idle",status,like(status,"Charg%"),"X",true(),"others")
| dedup charging_point 
| top status 
| fields status, percent

That's enough.

Sample:

| makeresults 
| eval _raw="status percent
Idle 71.397640
SuspendedEV 11.518077
Charging 9.131379
ChargeInProgress 4.996529
Preparing 1.456150
SuspendedEVSE 0.547640
Error 0.442961
Finishing 0.386764
Unavailable 0.122310
NotMonitored 0.000551" 
| multikv forceheader=1 
| table status percent 
 `comment("this is your result sample. from here, the logic")`
| transpose 0 header_field=status 
| eval X=Charging+ChargeInProgress 
| table Idle  X SuspendedEV SuspendedEVSE Preparing Error  Finishing  Unavailable NotMonitored
| transpose 0 column_name=status
| rename "row 1" as percent

Hi, folks. how about this?

View solution in original post

0 Karma

to4kawa
Ultra Champion
"Set status * for charging point" 
| rex "charging point\s*(?P<charging_point>.*)" 
| rex "Set status\s*(?P<status>.*?,*)\s" 
| eval status=case(status="Idle",status,like(status,"Charg%"),"X",true(),"others")
| dedup charging_point 
| top status 
| fields status, percent

That's enough.

Sample:

| makeresults 
| eval _raw="status percent
Idle 71.397640
SuspendedEV 11.518077
Charging 9.131379
ChargeInProgress 4.996529
Preparing 1.456150
SuspendedEVSE 0.547640
Error 0.442961
Finishing 0.386764
Unavailable 0.122310
NotMonitored 0.000551" 
| multikv forceheader=1 
| table status percent 
 `comment("this is your result sample. from here, the logic")`
| transpose 0 header_field=status 
| eval X=Charging+ChargeInProgress 
| table Idle  X SuspendedEV SuspendedEVSE Preparing Error  Finishing  Unavailable NotMonitored
| transpose 0 column_name=status
| rename "row 1" as percent

Hi, folks. how about this?

0 Karma

aryamehr360
New Member

Thank It workt, I also try to do some thing els to get the totaal percentage of this values :
SuspendedEV, Preparing, Error, Finishing, SuspendedEVSE, Unavailable
and show it as "others",

So I want this as a result :

 status    percent    
 Idle    66.049679     
 X          28.704325     
others  5.245997

I try that but not working:

"Set status * for charging point" 
| rex "charging point\s*(?P<charging_point>.*)" 
| rex "Set status\s*(?P<status>.*?,*)\s" 
| eval status=if(like(status,"Charg%"),"X",status)
| eval status=if(like(status,"SuspendedEV","Preparing","Error","Finishing","SuspendedEVSE","Unavailable"),"total",status)
| dedup charging_point 
| top limit=20 status
| fields status, percent
0 Karma

to4kawa
Ultra Champion

@aryamehr360
I see, my answer is updated.

0 Karma

aryamehr360
New Member

Can you tell me what is the right command to get this as well;
status percent

Idle 66.049679

X 28.704325

others 5.245997

0 Karma

to4kawa
Ultra Champion
| eval status=case(status="Idle",status,like(status,"Charg%"),"X",true(),"others")

cf. eval case() and true()

case() works from left side.
As I don't want to write many args, everything else is selected with true ().

In your first query, like() has only two args and second arg is SQL-LIKE REGEX.
you should use other function.

0 Karma

aryamehr360
New Member

Great , Thanks a lot

0 Karma

nickhills
Ultra Champion

Edit:
Oh wait . misunderstood the question 🙂

If my comment helps, please give it a thumbs up!
0 Karma

aryamehr360
New Member

this is not working,
CODE:

"Set status * for charging point"
  | rex "charging point\s*(?P<charging_point>.*)" 
  | rex "Set status\s*(?P<status>.*?,*)\s" 
  | eval X = ChargeInProgress+Charging
  | fields - ChargeInProgress,Charging
  | dedup charging_point
  | top limit=20 status 
  | fields status, percent

Result :

status  percent
Idle    66.026646
ChargeInProgress    24.098746
Charging    4.555251
SuspendedEV 3.389498
SuspendedEVSE   0.597571
Error   0.538793
Preparing   0.489812
Finishing   0.225313
Unavailable 0.068574
NotMonitored    0.009796
0 Karma

nickhills
Ultra Champion

yeah, i misunderstood what you were initially asking, this would have produced a result, just not the one you were after:

"Set status * for charging point"
   | rex "charging point\s*(?P<charging_point>.*)" 
   | rex "Set status\s*(?P<status>.*?,*)\s" 
   | eval X = ChargeInProgress+Charging
   | fields - ChargeInProgress,Charging
   | dedup charging_point
   | top limit=20 status 
   | fields status, percent, X
If my comment helps, please give it a thumbs up!
0 Karma

aryamehr360
New Member

No this is not working too see the result :

status  percent             X
Idle    66.049679    
ChargeInProgress    24.379016    
Charging    4.325309     
SuspendedEV 3.366261     
SuspendedEVSE   0.604201     
Error   0.556248     
Preparing   0.450753     
Finishing   0.191810     
Unavailable 0.076724

this add a field by X,
I want to Sum the percentage of "ChargeInProgress"24.379016 + "Charging":4.325309 = 28.704325 and show it in the table just with other status name for example: X,
so I want this as a result :

status  percent 
Idle    66.049679    
X          28.704325     
SuspendedEV 3.366261     
SuspendedEVSE   0.604201     
Error   0.556248     
Preparing   0.450753     
Finishing   0.191810     
Unavailable 0.076724
0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...