Nice! Glad it is working for you! ... View more

interesting. Not my question, but if one SH is skipping, how do we check for what is creating load on that SH? Or generally what to look at to reduce load on the overloaded SH? ... View more

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. ... View more

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? ... View more

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? ... View more

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 ... View more

FYI, I verified that the lookup is only loaded/evaluated when the search gets parsed by doing this: | makeresults | eval data= "ITEM=1 ;ITEM=2; ITEM=3" | makemv data delim=";" | mvexpand data | rename data as _raw | KV | table ITEM | outputlookup items.csv | append [|inputlookup items.csv] If the lookukp were processed for each reference, I would get two records each for ITEM=1, ITEM=2, ITEM=3 Instead the results look like: ITEM 1 2 3 Run the same query again and I get: ITEM 1 2 3 1 2 3 ... View more

PC1234, looks like you just want to modify your CSV and add a row to it. I am guessing that the csv gets evaluated once in the query, so try this: | inputlookup item.csv | eval input_item = 3 | eval itemnumber = if(itemnumber >= input_item, itemnumber +1, itemnumber) | append [|makeresults | eval itemnumber = input_item | table itemnumber] | fields - input_item | outputlookup item.csv ... View more

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) ... View more

Paries. I've never tried alerting on 0 results, but what I do for that is to append a single result and look for just 1 base search | append [| makeresults | eval msg="nothing else found"] ... View more

Does it do what you are hoping for? ... View more

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 ... View more

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 ... View more

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 ... View more

2powder, does the above answer help you? If not is there something different that you were looking for? ... View more

a new command! thank you! This will be useful in a number of my existing queries ... View more

Yeah, it is a funny thought that making the query more complex leaves it easier to read. On the other hand, there is a cost associated with multiple lookups. I'd try racing the solutions against each other to find out if one works better than the other (very probably related to result set and lookup table size) ... View more

christoffertoft, your original terms are still in the record, so you just need to expand the results. As deepashri_123 notes, this can be done with mvexpand: base search | eval searchTerm=mvappend(word, otherword) | lookup my_lookup_command word as searchTerm OUTPUT lookup_value as interesting_field | mvexpand interesting_field ... View more

I see, so I am missing a condition on the sourcetype for index=main. I also see that I missed that TraceUID has two field names (TraceUID and traceUID), so I will account for that as well It seems to me that the main problem that you are having is with the normalization of your data. If you could count on the fields that you want to use, you could do stats properly. That is why my answer mostly just normalizes your data (based on available info). The coalesce will allow us to normalize SegmentID so that we can treat it the same in all records eval SegmentID=coalesce(SegmentID,Segment,"0") says to use Sement ID if available, otherwise use Segment, and if neither of those exists in the record, assume that SegmentID is "0" To add the extra condition on sourcetype, we can just use some compound conditions in the base query (I also renamed traceUID to normalize that field): (index IN ("dst", "mtl") OR (index="main" sourcetype="txn")) TransactionStatus="success" | eval SegmentID=coalesce(SegmentID,Segment,"0") | rename traceUID as TraceUID | stats dc(TraceUID) as SuccessCount by SegmentID This should result in results that look like SuccessCount SegmentID 15 0 13 1 10 2 5 3 But I am thinking that you want to see something more. Since you note dedup on TraceUID, lets figure that you want those values in the results: (index IN ("dst", "mtl") OR (index="main" sourcetype="txn")) TransactionStatus="success" | eval SegmentID=coalesce(SegmentID,Segment,"0") | rename traceUID as TraceUID | stats dc(TraceUID) as SuccessCount values(TraceUID) as UIDs by SegmentID ... View more

If I am reading you right, I think that you want the total count of successful TraceUID values in each segment. I can do that with this query: index IN ("dst", "mtl", "main") TransactionStatus="success" | eval SegmentID=coalesce(SegmentID,Segment,"0") | stats dc(TraceUID) as SuccessCount by SegmentID ... View more

or if all you really want is just the results starting with AWS: | rex field=sentence "^.*(?<show>AWS.*$)" | table show (thanks niketnilay, I keep forgetting to use the code button) ... View more

must be frustrating. What do you have so far, and what are you seeing? I have verified the end part of my query if I assume that the value for "d" has already been calculated. I am not able to share a picture, but the query results in a chart like this: date pct Sun 9 Mon 22 Tue 20 Wed 20 Thu 20 Fri 17 Sat 12 (admittedly, pct is not really percent in my case though) which visualizes into a bar chart by weekday ... View more

did my answer below do what you needed? ... View more