topic Re: 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?! in 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 — Thu, 06 Feb 2020 10:31:35 GMT

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

Re: 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?!

nickhills — Thu, 06 Feb 2020 13:13:37 GMT

Edit:
Oh wait . misunderstood the question 🙂

Re: 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 — Thu, 06 Feb 2020 18:44:27 GMT

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

Re: 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?!

nickhills — Thu, 06 Feb 2020 18:53:18 GMT

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

Re: 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 — Thu, 06 Feb 2020 19:02:26 GMT

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

Re: 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?!

to4kawa — Fri, 07 Feb 2020 02:30:05 GMT

"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?

Re: 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 — Fri, 07 Feb 2020 10:59:26 GMT

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

Re: 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?!

to4kawa — Fri, 07 Feb 2020 11:16:08 GMT

@aryamehr360
I see, my answer is updated.

Re: 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 — Fri, 07 Feb 2020 16:33:19 GMT

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

Re: 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?!

to4kawa — Fri, 07 Feb 2020 22:50:35 GMT

| 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.

Re: 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 — Sat, 08 Feb 2020 11:06:21 GMT

Great , Thanks a lot