Splunk Search

TimeChart Percent Query - How to Sort on specific field count

beriwalnishant
Path Finder

Hello Experts,

I am stuck with a timechart % query and I want to sort basis a field count and not the default sort on alphabetical order it is counting

There are two queries, it be best if I can get a help or workaround in both the one

 

Query - 1

index=xyz catcode="*" (prodid="1") (prodcat="*") success="*"

| eval TheError=if(success="false" AND Error like "%%",count,0)
| timechart span="15m" eval(round(sum(TheError)*100/sum(count),2)) by catcode useother=f


In above query I want to find an option to sort it by catcode and not the default in alphabetical order

 

OR

 

Query 2

index= xyz (prodid="1")  (prodcat=*) (catcode=*) success=*

| timechart span=1w sum(count) by catcode limit=10 useother=f usenull=f

| untable _time catcode count

| eventstats sum(count) as Total by _time

| eval Fail_Percent=round(count*100/Total,2)

| table _time, catcode, Fail_Percent

| xyseries _time catcode Fail_Percent

| sort -catcode

In above query all is fine but I dont want 'eventstats count as Total' as it counts all events. I want to have this counted as Total by catcode and then calculate the %
Can you help please.


 

Thanks in advance

Nishant

Labels (4)
Tags (1)
0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

Hi @beriwalnishant,

it's very strang; I never heard a disabled rex command !

Anyway, you can use eval substr:

index=xyz catcode="*" (prodid="1") (prodcat="*") success="*"
| eval TheError=if(success="false" AND Error like "%%",count,0)
| eval catcode=case(catcode="CCat1","01|CCat1",catcode="BCat2","02|BCat2",catcode="ACat3","03|ACat3")
| timechart span="15m" eval(round(sum(TheError)*100/sum(count),2)) by catcode useother=f
| eval catcode=substr(catcode,3,20)"

Ciao.

Giuseppe

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust

Does this give you the totals you want:

| eventstats sum(count) as Total by catcode
0 Karma

beriwalnishant
Path Finder

I am afraid but i dont want to collective count using eventstats I want Total count per catcode

Tags (1)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

OK I am confused - you want the total count by catcode? This is what the eventstats by catcode is doing. How are you using it, if it is not giving you what you expect?

0 Karma

beriwalnishant
Path Finder

event stats counts all events...meaning if I put catcode=AB with catcode=CD The total will remain the same for all catcodes lets say 100

But I want catcode count as 20 which for catcode=AB only and then if out of 20 catcode=AB has 4 failed as Errors  then its 5/20

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Please share the SPL that gives you that result

0 Karma

beriwalnishant
Path Finder

yes, here this one is working but now I am unable to find how to keep top 10 only

head or top limit gets no impact on it

index=xyz (catcode="*") (prodid="1") success="*"
| bucket _time span="15m"

| eval TheError=if(success="false" AND Error="*",count,0)
| eval Success=if(success="true",count,0)
| stats sum(Error) as "Failed", sum(Success) as "Passed", sum(count) as Total by _time, catcode
| eval Failed_Percent=round((Failed/Total)*100,2)
| fields _time, catcode, Failed_Percent
| xyseries _time, catcode, Failed_Percent


Now this is working super fine if I mention different catcode like (catcode=AB OR catcode=CD OR catcode=XY)

but the moment I do catcode=*


it still works but shows all the catcodes trend lines making the graph messy and congested.....

What is not working here is the top limit or head command

if you can figure that out pls

Tags (1)
0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @beriwalnishant,

no there isn't any parameter to change the sort order in timechart, but you could use a workaround:

e.g. if you have as catCodes ACat3, BCat2 and CCat1 and you want the following order, CCat1, BCat2 and ACat3, you could do something like this:

index=xyz catcode="*" (prodid="1") (prodcat="*") success="*"
| eval TheError=if(success="false" AND Error like "%%",count,0)
| eval catcode=case(catcode="CCat1","01|CCat1",catcode="BCat2","02|BCat2",catcode="ACat3","03|ACat3")
| timechart span="15m" eval(round(sum(TheError)*100/sum(count),2)) by catcode useother=f
| rex field=catcode "^\d+\|(?<catcode>.*)"

Ciao.

Giuseppe

 

0 Karma

beriwalnishant
Path Finder

Can you check 

index=xyz catcode="*" (prodid="1") (prodcat="*") success="*"

| eval TheError=if(success="false" AND catcode="RA",count,0)
| timechart span="15m" eval(round(sum(TheError)*100/sum(count),2)) by catcode useother=f

This one could be used with multiple codes, it shows correct % for RA but also shows other catcodes as '0' is there a way to hide others that are showing as "0"


0 Karma

beriwalnishant
Path Finder

Thanks a lot but I am afraid I cant use the rex ... its disabled

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @beriwalnishant,

it's very strang; I never heard a disabled rex command !

Anyway, you can use eval substr:

index=xyz catcode="*" (prodid="1") (prodcat="*") success="*"
| eval TheError=if(success="false" AND Error like "%%",count,0)
| eval catcode=case(catcode="CCat1","01|CCat1",catcode="BCat2","02|BCat2",catcode="ACat3","03|ACat3")
| timechart span="15m" eval(round(sum(TheError)*100/sum(count),2)) by catcode useother=f
| eval catcode=substr(catcode,3,20)"

Ciao.

Giuseppe

beriwalnishant
Path Finder

Hello @gcusello 

I found a workable query but a challenge again (describe below) in case you know how to tackle it


here this one is working but now I am unable to find how to keep top 10 only

head or top limit gets no impact on it

index=xyz (catcode="*") (prodid="1") success="*"
| bucket _time span="15m"

| eval TheError=if(success="false" AND Error="*",count,0)
| eval Success=if(success="true",count,0)
| stats sum(Error) as "Failed", sum(Success) as "Passed", sum(count) as Total by _time, catcode
| eval Failed_Percent=round((Failed/Total)*100,2)
| fields _time, catcode, Failed_Percent
| xyseries _time, catcode, Failed_Percent


Now this is working super fine if I mention different catcode like (catcode=AB OR catcode=CD OR catcode=XY)

but the moment I do catcode=*


it still works but shows all the catcodes trend lines making the graph messy and congested.....

What is not working here is the top limit or head command

if you can figure that out pls

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @beriwalnishant,

the easiest way is to use a larger span in buckets command: e.g. 30 or 60 minutes instaed 15 or you could also sort and use head.

I don't know your full requirements.

Ciao.

Giuseppe

0 Karma

beriwalnishant
Path Finder

intend to use large only but just for example and testing.

 

see this graph  - large or small .... all catcode are shown....with above query the only struggle is to be able to only limit the number of carriers - rest the query is fine

beriwalnishant_0-1658237190121.png

 

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...