topic Re: Do you think we can optimize this long search? in Splunk Search

Do you think we can optimize this long search?

venkatesh296 — Thu, 21 Dec 2017 19:56:24 GMT

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

Re: Do you think we can optimize this long search?

MonkeyK — Thu, 21 Dec 2017 21:51:38 GMT

Yes. Here is an optimization for readability ( you can do the same by pressing ctrl+\ in your Splunk 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_Earlier)/maxpendingcount_Earlier)100,2) 
| appendcols 
    [ search index=queuesqueues 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=-7d@-5m latest=-7d 
        | stats max("Queues.pendingMessageCount") as "maxpendingcount_7dEarlier" by "Queues.name"] 
    | 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

Re: Do you think we can optimize this long search?

micahkemp — Thu, 21 Dec 2017 22:11:24 GMT

It's pretty difficult to modify searches without knowing:

a) the intent of the search (what you want the results to look like and represent)
b) some sample data to test with

Re: Do you think we can optimize this long search?

MonkeyK — Thu, 21 Dec 2017 22:12:29 GMT

next, you should be able to get rid of one of your searches for "Queues.name"="road.sa" for the last 5m

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_Earlier)/maxpendingcount_Earlier)100,2) 
| join type=outer "Queues.name" 
     [ 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"]
| 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

Re: Do you think we can optimize this long search?

MonkeyK — Thu, 21 Dec 2017 22:20:04 GMT

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

Re: Do you think we can optimize this long search?

venkatesh296 — Fri, 22 Dec 2017 00:58:00 GMT

Thank you

Re: Do you think we can optimize this long search?

venkatesh296 — Fri, 22 Dec 2017 01:02:16 GMT

Hi,
My intention was the result should compare the queues with the last 7 days, current and then if the growth is greater than some X then the result need to display.Let me know if you need anything.
Thanks,
-Venkat

Re: Do you think we can optimize this long search?

MonkeyK — Fri, 22 Dec 2017 02:57:07 GMT

Does it do what you are hoping for?

Re: Do you think we can optimize this long search?

venkatesh296 — Fri, 22 Dec 2017 03:51:24 GMT

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?

Re: Do you think we can optimize this long search?

MonkeyK — Fri, 22 Dec 2017 12:14:19 GMT

I guess that depends on how the queue names are related. I think that they are the same
for each part of the query since you joined on Queues.name
If queue name is really common across all queries, you should be able to remove or refine the search term and it will work just fine.

if there is a more complicated relationship, accounting for it will take some more work (not necessarily requiring a token, though)

Re: Do you think we can optimize this long search?

Richfez — Fri, 22 Dec 2017 12:36:19 GMT

@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

Re: Do you think we can optimize this long search?

Richfez — Fri, 22 Dec 2017 12:37:35 GMT

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

Duh.

Ignore my comments. Problem sorted. 🙂

-Rich

Re: Do you think we can optimize this long search?

venkatesh296 — Tue, 29 Sep 2020 17:21:28 GMT

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]

Re: Do you think we can optimize this long search?

venkatesh296 — Tue, 29 Sep 2020 17:21:30 GMT

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

Re: Do you think we can optimize this long search?

MonkeyK — Fri, 22 Dec 2017 15:35:24 GMT

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

Re: Do you think we can optimize this long search?

venkatesh296 — Fri, 22 Dec 2017 16:25:03 GMT

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.

Re: Do you think we can optimize this long search?

MonkeyK — Tue, 29 Sep 2020 17:25:36 GMT

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?

Re: Do you think we can optimize this long search?

venkatesh296 — Fri, 22 Dec 2017 17:03:29 GMT

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

Re: Do you think we can optimize this long search?

MonkeyK — Fri, 22 Dec 2017 18:43:40 GMT

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?

Re: Do you think we can optimize this long search?

acharlieh — Fri, 22 Dec 2017 20:28:04 GMT

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.