Splunk Search

Percentage per each MEMBER per row not per all of them in chart/timechart

psp_admins
New Member

Hi,
I'm newbie here and read a little about my issue in docs and answers here but got no clue for now.
I've got couple of lines

eventtype=audit MEMBER IN (100,200)
| bucket _time span=2h
| eval glue = if(STATUS="SUCCESS", "SUCCESSes in ", "ERRORs in "). MEMBER
| chart count(CODE) OVER _time by glue usenull=false
| addtotals row=t col=f
| rename Total AS _total
| foreach * [eval "<<FIELD>>_%"=if(isnum('<<FIELD>>'), round('<<FIELD>>'/_total*100,2), null())]

They gave me

_time|ERRORs in 100|ERRORs in 200|SUCCESSes in 100|SUCCESSes in 200|ERRORs_% in 100|ERRORs_% in 200|SUCCESSes_% in 100|SUCCESSes_% in 200
2018-07-09 00:00|10|0|18|1|34.48|0.00|62.07|3.45
2018-07-09 02:00|0|0|4|8|0.00|0.00|33.33|66.67
2018-07-09 04:00|1|3|17|12|3.03|9.09|51.52|36.36
2018-07-09 06:00|16|10|43|82|10.60|6.62|28.48|54.30
2018-07-09 08:00|35|20|130|202|9.04|5.17|33.59|52.20 

Unfortunately it's not what I would like to have.

What I would like to achieve - to have a percentage columns with correct values inside per each MEMBER per each STATUS for those MEMBERS not per all of them.
Moreover quantity of MEMBERS can be different
e.g.

_time|ERRORs in 100|ERRORs in 200|SUCCESSes in 100|SUCCESSes in 200|ERRORs_% in 100|ERRORs_% in 200|SUCCESSes_% in 100|SUCCESSes_% in 200
2018-07-09 00:00|10|0|18|1|35,71|0,00|64,29|100,00
2018-07-09 02:00|0|0|4|8|0,00|0,00|100,00|100,00
2018-07-09 04:00|1|3|17|12|5,56|20,00|94,44|80,00
2018-07-09 06:00|16|10|43|82|27,12|10,87|72,88|89,13
2018-07-09 08:00|35|20|130|202|21,21|9,01|78,79|90,99

I would be very grateful for any help

cheers
Greg

0 Karma

woodcock
Esteemed Legend

First of all do not use spaces or % in your field names, Splunk hates them and they are incredibly problematic. So fix that first and then add this to the bottom of your search:

| makeresults 
| eval raw="2018-07-09/00:00|10|0|18|1|34.48|0.00|62.07|3.45 2018-07-09/02:00|0|0|4|8|0.00|0.00|33.33|66.67 2018-07-09/04:00|1|3|17|12|3.03|9.09|51.52|36.36 2018-07-09/06:00|16|10|43|82|10.60|6.62|28.48|54.30 2018-07-09/08:00|35|20|130|202|9.04|5.17|33.59|52.20" 
| makemv raw 
| mvexpand raw 
| rename raw AS _raw 
| rex "^(?<_time>[^\|]+)\|(?<ERRORs_in_100>[^\|]+)\|(?<ERRORs_in_200>[^\|]+)\|(?<ESUCCESSes_in_100>[^\|]+)\|(?<ESUCCESSes_in_200>[^\|]+)\|(?<EERRORs_pct_in_100>[^\|]+)\|(?<EERRORs_pct_in_200>[^\|]+)\|(?<ESUCCESSes_pct_in_100>[^\|]+)\|(?<ESUCCESSes_pct_in_200>.*)$" 
| eval _time = strptime(_time, "%Y-%m-%d/%H:%M")

| rename COMMENT AS "Everything above generates similar results to your existing search; everything below is what you tack on to that"

| table _time ERRORs_in_100 ERRORs_in_200 ESUCCESSes_in_100 ESUCCESSes_in_200 EERRORs_pct_in_100 EERRORs_pct_in_200 ESUCCESSes_pct_in_100 ESUCCESSes_pct_in_200
| foreach ESUCCESSes_pct_in_* [ eval <<FIELD>> = round((100 * ESUCCESSes_in_<<MATCHSTR>> / (ERRORs_in_<<MATCHSTR>> + ESUCCESSes_in_<<MATCHSTR>>)), 2) ]
0 Karma

DalJeanis
SplunkTrust
SplunkTrust

It's probably more effective to do the calculations yourself rather than use chart to do them, then to rename afterwards.

If we understand you correctly, you want your numbers split by errors or successes, by MEMBER, by _time, and then for each _time, for each member, you want to calculate percentage of errors and successes.

So, to start with, we'll get the data, bin the _time, and stats it up.

 index=foo eventtype=audit MEMBER IN (100,200)
| bucket _time span=2h
| eval myStatus=if(STATUS="SUCCESS","SUCCESS","ERROR")
| stats count(CODE) as myCount by _time myStatus MEMBER

| rename COMMENT as "create a zero record for each combination of _time, myStatus and MEMBER"
| appendpipe 
   [ | rename _time as Time
     | table Time MEMBER myStatus 
     | stats values(*) as * 
     | mvexpand Time
     | mvexpand myStatus 
     | mvexpand MEMBER  
     | rename Time as _time 
     | eval myCount = 0 
   ]

| rename COMMENT as "roll in the zero records - this could also be done with stats "
| dedup _time myStatus MEMBER


| rename COMMENT as "add up the count for each member for each time period, then calculate percentage"
| eventstats sum(myCount) as mySum by _time MEMBER   
| eval myPercent=if(mySum>0,round(100*myCount/mySum,2), true(),0.00)
| fields - mySum

This gives you a table that looks something like this...

 _time            MEMBER   myStatus  |  myCount    myPercent
 2018-07-09 00:00    100    ERROR       10         35.71
 2018-07-09 00:00    100    SUCCESS     18         64.29
 2018-07-09 00:00    200    ERROR        0          0.00
 2018-07-09 00:00    200    SUCCESS      1        100.00
 2018-07-09 02:00    100    ERROR        0          0.00
 2018-07-09 02:00    100    SUCCESS      4        100.00
 2018-07-09 02:00    200    ERROR        0          0.00
 2018-07-09 02:00    200    SUCCESS      8        100.00
 2018-07-09 04:00    100    ERROR        1          5.56
 2018-07-09 04:00    100    SUCCESS     17         94.44
 2018-07-09 04:00    200    ERROR        3         20.00
 2018-07-09 04:00    200    SUCCESS     12         80.00

Next, you have to decide how you are going to present this information.

It seems to me, if you have an arbitrary number of members in this, that the number of columns would quickly make the data unreadable. I would proceed either by setting the data up to use a trellis for presentation, or by having each line be one _time for one member, with the columns being _time, MEMBER, ERROR_COUNT, SUCCESS_COUNT, ERROR_PERCENT, and SUCCESS_PERCENT

 | eval countfield=myStatus."_COUNT"
 | eval {countfield}=myCount
 | eval pctfield=myStatus."_PERCENT"
 | eval {pcttfield}=myPercent
 | fields _time MEMBER ERROR_COUNT ERROR_PERCENT SUCCESS_COUNT SUCCESS_PERCENT
 | stats values(*) as * by _time MEMBER

Now, if the above all makes sense, then here's a slightly more compact way to accomplish the same result...

 index=foo eventtype=audit MEMBER IN (100,200)
| bucket _time as TIME span=2h
| eval STATUS=if(STATUS="SUCCESS",STATUS,"ERROR")
| stats count(CODE) as COUNT by TIME STATUS MEMBER

| rename COMMENT as "create a zero record for each combination of _time, STATUS and MEMBER"
| appendpipe 
   [ | stats values(TIME) as TIME values(MEMBER) as MEMBER values(STATUS) as STATUS 
     | mvexpand TIME
     | mvexpand STATUS 
     | mvexpand MEMBER  
     | eval COUNT = 0 
   ]

| rename COMMENT as "roll in the zero records and calc the percentages "
| stats SUM(eval(case(STATUS="SUCCESS",COUNT))) as SUCCESS_COUNT,
        SUM(eval(case(STATUS="ERROR",COUNT))) as ERROR_COUNT,
        SUM(COUNT) as TOTAL_COUNT
        by TIME MEMBER
| eval ERROR_PERCENT=if(TOTAL_COUNT>0,round(100*ERROR_COUNT/TOTAL_COUNT,2),0.00)
| eval SUCCESS_PERCENT=if(TOTAL_COUNT>0,round(100*SUCCESS_COUNT/TOTAL_COUNT,2),0.00)
| rename TIME as _time
| table _time MEMBER ERROR_COUNT SUCCESS_COUNT ERROR_PERCENT SUCCESS_PERCENT
0 Karma

woodcock
Esteemed Legend

Describe EXACTLY how the math is supposed to work.

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

Hi @psp_admins,

If you have definite number of columns , then you could just add up the fields and calculate the %

for e.g.

 eventtype=audit MEMBER IN (100,200)
 | bucket _time span=2h
 | eval glue = if(STATUS="SUCCESS", "SUCCESSes in ", "ERRORs in "). MEMBER
 | chart count(CODE) OVER _time by glue usenull=false
 |eval tot_100="ERRORs in 100" + "SUCCESSes in 100",tot_200="ERRORs in 200" + "SUCCESSes in 200"
 |eval perc_error_100=round(("ERRORs in 100" /tot_100)*100,2),perc_success_100=round(("SUCCESSes in 100" /tot_100)*100,2)
 |eval perc_error_200=round(("ERRORs in 200" /tot_200)*100,2),perc_success_200=round(("SUCCESSes in 200" /tot_200)*100,2)

or you could do that with <>

     eventtype=audit MEMBER IN (100,200)
     | bucket _time span=2h
     | eval glue = if(STATUS="SUCCESS", "SUCCESSes in ", "ERRORs in "). MEMBER
     | chart count(CODE) OVER _time by glue usenull=false
     | eval tot_100=0,tot_200=0|foreach *100 [eval tot_100=tot_100 + <<FIELD>>] |foreach *200 [eval tot_200=tot_200 + <<FIELD>>]
     |eval perc_error_100=round(("ERRORs in 100" /tot_100)*100,2),perc_success_100=round(("SUCCESSes in 100" /tot_100)*100,2)
     |eval perc_error_200=round(("ERRORs in 200" /tot_200)*100,2),perc_success_200=round(("SUCCESSes in 200" /tot_200)*100,2)
Happy Splunking!
0 Karma

psp_admins
New Member

Hi,
thanks for the reply renjith.nair
Unfortunately I do not have known upfront numbers of MEMBERS so it means definite numbers of columns.
I could have 2, 3 or dozen of columns so that's the obstacle for me to make it automated in SPLUNK... 😞

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