TimeChart Percent Query - How to Sort on specific field count

[beriwalnishant]

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

[ITWhisperer]

Does this give you the totals you want:

| eventstats sum(count) as Total by catcode

[beriwalnishant]

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

[ITWhisperer]

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?

[beriwalnishant]

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

[ITWhisperer]

Please share the SPL that gives you that result

[beriwalnishant]

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

[gcusello]

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

[beriwalnishant]

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"

[beriwalnishant]

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

[gcusello · Accepted solution]

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]

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

[gcusello]

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

[beriwalnishant]

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