Do you think we can optimize this long search?

[venkatesh296]

The search:

index=queues sourcetype="jms:queues" "Queues.name"="road.sa**" earliest=-5m@m 
| stats max("Queues.pendingMessageCount") as "maxpendingcount_current" by "Queues.name" 
|join type=outer "Queues.name"[search index=queuesqueues sourcetype="jms:queues" 
"Queues.name"="road.sa**" earliest=-1h@-5m latest=-1h  | stats max("Queues.pendingMessageCount") 
as "maxpendingcount_Earlier" by "Queues.name"] |eval onehr_growth=round(((maxpendingcount_current-maxpendingcount_E
arlier)/maxpendingcount_Earlier)*100,2) |appendcols[search index=queuesqueues sourcetype="jms:queues" 
"Queues.name"="road.sa**" earliest=-5m@m | stats max("Queues.pendingMessageCount") as "maxpendingcou
nt_current" by "Queues.name" |join type=outer "Queues.name"[search index=queuesqueues sourcetype="jms:queu
es" "Queues.name"="road.sa**" earliest=-7d@-5m latest=-7d  | stats max("Queues.pendingMessageCount") 
as "maxpendingcount_7dEarlier" by "Queues.name"]|eval sevenday_growth=round(((maxpendingcount_current-maxpendingco
unt_7dEarlier)/maxpendingcount_7dEarlier)*100,2) ] |search onehr_growth>300 AND sevenday_growth>300 | table "Queue
s.name",maxpendingcount_current,maxpendingcount_Earlier, onehr_growth, maxpendingcount_7dEarlier, sevenday_growth

[acharlieh]

One thing that I see, is it seems you're running a search over the current timeframe multiple times. It might be more efficient if you only iterated over each timeframe once... You'll want to play with the job inspector to evaluate if these options would be more efficient or not.

These are three options I came up with that you may want to look into:

index=queues sourcetype="jms:queues" "Queues.name"="road.sa*" ((earliest=-5m latest=now) OR (earliest=-1h-5m latest=-1h) OR (earliest=-7d-5m latest=-7d))
| eval timeframe=case(_time>=relative_time(now(),"-5m"),"c", _time>=relative_time(now(),"-1h-5m"),"p1h",1=1,"p7d")
| chart max("Queues.pendingMessageCount") over "Queues.name" by timeframe
| eval onehr_growth=round((c-p1h)/p1h*100,2),sevenday_growth=round((c-p7d)/p7d*100,2) 
| where onehr_growth>300 AND sevenday_growth>300
| table "Queues.name",c,p1h,onehr_growth,p7d,sevenday_growth

The above would run a search across the time range of [-7d-5m, now], and filters based on _time...

| multisearch
  [search index=queues sourcetype="jms:queues" "Queues.name"="road.sa*" (earliest=-5m latest=now) | eval timeframe="c" | fields "Queues.pendingMessageCount","Queues.name",timeframe ]
  [search index=queues sourcetype="jms:queues" "Queues.name"="road.sa*" (earliest=-1h-5m latest=-1h) | eval timeframe="p1h" | fields "Queues.pendingMessageCount","Queues.name",timeframe]
  [search index=queues sourcetype="jms:queues" "Queues.name"="road.sa*" (earliest=-7d-5m latest=-7d) | eval timeframe="p7d" | fields "Queues.pendingMessageCount","Queues.name",timeframe]
| chart max("Queues.pendingMessageCount") over "Queues.name" by timeframe
| eval onehr_growth=round((c-p1h)/p1h*100,2),sevenday_growth=round((c-p7d)/p7d*100,2) 
| where onehr_growth>300 AND sevenday_growth>300
| table "Queues.name",c,p1h,onehr_growth,p7d,sevenday_growth

This one is similar to the previous one, but instead of searching the entire timeframe, uses multisearch to limit the timeranges being searched.

index=queues sourcetype="jms:queues" "Queues.name"="road.sa*" (earliest=-5m latest=now) | eval timeframe="c" | stats max("Queues.pendingMessageCount") as mc by "Queues.name", timeframe
| append [search index=queues sourcetype="jms:queues" "Queues.name"="road.sa*" (earliest=-1h-5m latest=-1h) | eval timeframe="p1h" | stats max("Queues.pendingMessageCount") as mc by "Queues.name", timeframe]
| append [search index=queues sourcetype="jms:queues" "Queues.name"="road.sa*" (earliest=-7d-5m latest=-7d) | eval timeframe="p7d" | stats max("Queues.pendingMessageCount") as mc by "Queues.name", timeframe]
| xyseries "Queues.name" timeframe mc
| eval onehr_growth=round((c-p1h)/p1h*100,2),sevenday_growth=round((c-p7d)/p7d*100,2) 
| where onehr_growth>300 AND sevenday_growth>300
| table "Queues.name",c,p1h,onehr_growth,p7d,sevenday_growth

This one is the closest to yours, instead of using join, using append to gather the independent sets of data, and then using xyseries to combine the statistics from all three.

[Richfez]

@venkatesh296
I edited your post and used the code (101010) button on that text so special characters won't get eaten by the rendering.

You might want to a) confirm it's still right and b) maybe even reformat it to include newlines and spacing to make it easier to read. You can do that while it is a search in your Splunk search bar by pressing Ctrl-\ (control - backslash) and it'll reformat it all pretty for you. Then paste THAT one in.

But either way, it's probably good enough now!

-Rich

[Richfez]

AND, somewhere along the line I TOTALLY did not see the fixups already applied.

Duh.

Ignore my comments. Problem sorted. 🙂

-Rich

[MonkeyK]

After that, I might remove the joins and just append the values that you are interested in. you will be able to summarize by Queue.name:

index=queues sourcetype="jms:queues" "Queues.name"="road.sa*" earliest=-5m@m 
| stats max("Queues.pendingMessageCount") as "maxpendingcount_current" by "Queues.name" 
| append [|search index=queuesqueues sourcetype="jms:queues" "Queues.name"="road.sa" earliest=-1h@-5m latest=-1h 
    | stats max("Queues.pendingMessageCount") as "maxpendingcount_Earlier" by "Queues.name"]
| append [|search index=queuesqueues sourcetype="jms:queues" "Queues.name"="road.sa**" earliest=-7d@-5m latest=-7d 
     | stats max("Queues.pendingMessageCount") as "maxpendingcount_7dEarlier" by "Queues.name"]
| stats max("maxpendingcount_current") max("maxpendingcount_Earlier") max("maxpendingcount_7dEarlier") by "Queues.name"
| eval onehr_growth=round(((maxpendingcount_current-maxpendingcount_Earlier)/maxpendingcount_Earlier)100,2) 
| eval sevenday_growth=round(((maxpendingcount_current-maxpendingcount_7dEarlier)/maxpendingcount_7dEarlier)*100,2)
| search onehr_growth>300 AND sevenday_growth>300 
| table "Queues.name",maxpendingcount_current,maxpendingcount_Earlier, onehr_growth, maxpendingcount_7dEarlier, sevenday_growth

I left the stats in the appends because I do not know how much data they are summarizing. If the totals are small, you could just append the pendingMessageCounts and do a single stats at the end:

index=queues sourcetype="jms:queues" "Queues.name"="road.sa*" earliest=-5m@m 
| stats max("Queues.pendingMessageCount") as "maxpendingcount_current" by "Queues.name" 
| append [|search index=queuesqueues sourcetype="jms:queues" "Queues.name"="road.sa" earliest=-1h@-5m latest=-1h 
    | rename "Queues.pendingMessageCount" as "maxpendingcount_Earlier" 
    | table "maxpendingcount_Earlier" "Queues.name"]
| append [|search index=queuesqueues sourcetype="jms:queues" "Queues.name"="road.sa**" earliest=-7d@-5m latest=-7d 
    | rename "Queues.pendingMessageCount" as "maxpendingcount_7dEarlier" 
    | table "maxpendingcount_7dEarlier" "Queues.name"]
| stats max("maxpendingcount_current") max("maxpendingcount_Earlier") max("maxpendingcount_7dEarlier") by "Queues.name"
| eval onehr_growth=round(((maxpendingcount_current-maxpendingcount_Earlier)/maxpendingcount_Earlier)100,2) 
| eval sevenday_growth=round(((maxpendingcount_current-maxpendingcount_7dEarlier)/maxpendingcount_7dEarlier)*100,2)
| search onehr_growth>300 AND sevenday_growth>300 
| table "Queues.name",maxpendingcount_current,maxpendingcount_Earlier, onehr_growth, maxpendingcount_7dEarlier, sevenday_growth

[acharlieh]

I didn't see this thread before answering... so I converted it to an answer 🙂

[venkatesh296]

Hi,
Before doing statistics I just tried this but it showing values for one field i.e; maxpendingcount_current. can I know why?

index=queues sourcetype="jms:queues" "Queues.name"="road.sa*" earliest=-5m@m
| rename Queues.pendingMessageCount as maxpendingcount_current
| table maxpendingcount_current Queues.name
| append
[| search index=queues sourcetype="jms:queues" "Queues.name"="road.sa*" earliest=-1h@-5m latest=-1h
| rename Queues.pendingMessageCount as maxpendingcount_Earlier
| table maxpendingcount_Earlier Queues.name]
| append
[| search index=queues sourcetype="jms:queues" "Queues.name"="road.sa*" earliest=-7d@-5m latest=-7d
| rename Queues.pendingMessageCount as maxpendingcount_7dEarlier
| table maxpendingcount_7dEarlier Queues.name]

[MonkeyK]

My first guess would be that the appending searches are not returning data. Can you try them separately. If they return nothing, then we need to fix them first.

One difference that I noticed is that in your original query, those searches were against
index=queuesqueues
while in your most recent example, they are searching against
index=queues

[venkatesh296]

when I running the individual searches its working, after that I run the stats command, then it showing results
but after that I'm running eval but these fields are not showing in interesting fields.

[MonkeyK]

interesting fields is influenced by your search mode. If you are in "fast mode" or "smart mode", and you do stats, you will not get interesting fields.

I have found that depending on the nature of the query, you may even get nothing back in "verbose mode". I think that this is just because there are so few fields to analyze.

That said, I would like to make sure that I have solved your original problem. It sounds like you are getting the stats with max values for maxpendingcount_current, maxpendingcount_Earlier, and maxpendingcount_7dEarlier. Is that correct?

[venkatesh296]

after getting the max values I need to get the difference between them, are you in slack?

[venkatesh296]

This is what I'm seeing from stats, here I missing data for max(maxpendingcount_7dEarlier)

index=queues sourcetype="jms:queues" "Queues.name"="road.sa*" earliest=-5m@m
| rename Queues.pendingMessageCount as maxpendingcount_current
| table maxpendingcount_current Queues.name
| append
[| search index=queues sourcetype="jms:queues" "Queues.name"="road.sa*" earliest=-1h@-5m latest=-1h
| rename Queues.pendingMessageCount as maxpendingcount_Earlier
| table maxpendingcount_Earlier Queues.name]
| append
[| search index=queues sourcetype="jms:queues" "Queues.name"="road.sa*" earliest=-7d@-5m latest=-7d
| rename Queues.pendingMessageCount as maxpendingcount_7dEarlier
| table maxpendingcount_7dEarlier Queues.name] | stats max("maxpendingcount_current") max("maxpendingcount_Earlier") max("maxpendingcount_7dEarlier") by "Queues.name"

Queues.name max(maxpendingcount_current)    max(maxpendingcount_Earlier)    max(maxpendingcount_7dEarlier)

SCAN00 552 889

dit.SCAN01 536 863

].SCAN02 503 763

t.SCAN03 486 620

N04 530 802

dit.SCAN05 524 768

FAN06 479 632

FSCAN07 473 593

XSawEdit.SCAN07.temp 0 0

FXS.SCAN08 447 832

Fit.SCAN09 595 844

RawN10 472 745

FEdit.SCAN11 476 766

F.SCAN12 0 0

after this I using eval but its not working , the query shown below
index=queues sourcetype="jms:queues" "Queues.name"="road.sa*" earliest=-5m@m
| rename Queues.pendingMessageCount as maxpendingcount_current
| table maxpendingcount_current Queues.name
| append
[| search index=queues sourcetype="jms:queues" "Queues.name"="road.sa*" earliest=-1h@-5m latest=-1h
| rename Queues.pendingMessageCount as maxpendingcount_Earlier
| table maxpendingcount_Earlier Queues.name]
| append
[| search index=queues sourcetype="jms:queues" "Queues.name"="road.sa*" earliest=-7d@-5m latest=-7d
| rename Queues.pendingMessageCount as maxpendingcount_7dEarlier
| table maxpendingcount_7dEarlier Queues.name] | stats max("maxpendingcount_current") max("maxpendingcount_Earlier") max("maxpendingcount_7dEarlier") by "Queues.name" | eval onehr_growth=round(((maxpendingcount_current-maxpendingcount_Earlier)/maxpendingcount_Earlier)*100,2) |eval sevenday_growth=round(((maxpendingcount_current-maxpendingcount_7dEarlier)/maxpendingcount_7dEarlier)*100,2)

[MonkeyK]

On the eval, the problem is that stats has named the columns differently. I apologize, since that is how I wrote it, but the stats need to be changed to name the columns appropriately, like this:
stats max("maxpendingcount_current") as maxpendingcount_current max("maxpendingcount_Earlier") as maxpendingcount_Earlier max("maxpendingcount_7dEarlier") as maxpendingcount_7dEarlier by "Queues.name"
| eval onehr_growth=round(((maxpendingcount_current-maxpendingcount_Earlier)/maxpendingcount_Earlier)*100,2)
|eval sevenday_growth=round(((maxpendingcount_current-maxpendingcount_7dEarlier)/maxpendingcount_7dEarlier)*100,2)

I am not clear on what is up with the 7 day search, maybe a sample of what that returns? It feels like a minor typo, but I do not see it. Can you please run this part?
search index=queues sourcetype="jms:queues" "Queues.name"="road.sa*" earliest=-7d@-5m latest=-7d
| rename Queues.pendingMessageCount as maxpendingcount_7dEarlier
| table maxpendingcount_7dEarlier Queues.name

if that returns data, then the error is in the names between the 7day query and stats. If it returns nothing, then either there is no data, or we have a typo in the 7day query.

[venkatesh296]

Thank you so much for your response, but I got it, I saw that what you mentioned that is columns named differently. For 7days we have no data. Merry Christmas

Cheers

[MonkeyK]

Venkatesh, can you please mark this question as answered?

[MonkeyK]

Nice! Glad it is working for you!

[MonkeyK]

Sorry, I am not in slack (and my work firewall would prevent it if I were).

So you are getting stats and now are having trouble with eval? Can you post a sample of what you see from stats and also the eval statement that is problematic?

[venkatesh296]

this is how the results showing for above query

maxpendingcount_current Queues.name maxpendingcount_7dEarlier maxpendingcount_Earlier
0 .SCAN06

0 t.SCAN00

0 it.SCAN11

0 FAN10

0 FSCAN09

0 N08

0 N07

0 N06

0 AN05

0 AN04

0 FXS3

[venkatesh296]

Thank you

[MonkeyK]

Does it do what you are hoping for?

[venkatesh296]

yes but here we are doing for only specific queues but if I want to do multiple queues, what I need to do?
I'm thinking that I need to use tokens for multiple queues, is that right?