topic Re: TimeChart Percent Query - Sort on specific field count in Splunk Search

TimeChart Percent Query - How to Sort on specific field count

beriwalnishant — Tue, 19 Jul 2022 14:59:11 GMT

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

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

Re: TimeChart Percent Query - Sort on specific field count

gcusello — Tue, 19 Jul 2022 09:20:11 GMT

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:

Ciao.

Giuseppe

Re: TimeChart Percent Query - Sort on specific field count

ITWhisperer — Tue, 19 Jul 2022 09:24:01 GMT

Does this give you the totals you want:

| eventstats sum(count) as Total by catcode

Re: TimeChart Percent Query - Sort on specific field count

beriwalnishant — Tue, 19 Jul 2022 10:09:38 GMT

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

Re: TimeChart Percent Query - Sort on specific field count

beriwalnishant — Tue, 19 Jul 2022 10:10:09 GMT

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

Re: TimeChart Percent Query - Sort on specific field count

beriwalnishant — Tue, 19 Jul 2022 10:11:56 GMT

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"

Re: TimeChart Percent Query - Sort on specific field count

ITWhisperer — Tue, 19 Jul 2022 10:14:18 GMT

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?

Re: TimeChart Percent Query - Sort on specific field count

gcusello — Tue, 19 Jul 2022 10:15:53 GMT

Hi @beriwalnishant,

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

Anyway, you can use eval substr:

Ciao.

Giuseppe

Re: TimeChart Percent Query - Sort on specific field count

beriwalnishant — Tue, 19 Jul 2022 11:12:02 GMT

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

Re: TimeChart Percent Query - Sort on specific field count

ITWhisperer — Tue, 19 Jul 2022 11:51:59 GMT

Please share the SPL that gives you that result

Re: TimeChart Percent Query - Sort on specific field count

beriwalnishant — Tue, 19 Jul 2022 12:20:09 GMT

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

Re: TimeChart Percent Query - Sort on specific field count

beriwalnishant — Tue, 19 Jul 2022 12:21:27 GMT

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

Re: TimeChart Percent Query - Sort on specific field count

gcusello — Tue, 19 Jul 2022 12:32:34 GMT

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

Re: TimeChart Percent Query - Sort on specific field count

beriwalnishant — Tue, 19 Jul 2022 13:27:04 GMT

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