How to search mstats and then filter the results?

[JoshMc]

The goal is to fire off an alert if there is a lag in metrics for a given index. I can calculate this for each "app" which is reporting metrics:

| mstats latest_time(application_ready_time.value) as latest_ts 
where
index=my-metrics-index
host=some-host
by app.name
| eval past_threshold=if(now() - latest_ts >= 30, "Y", "N")
| eval latest=strftime(latest_ts, "%Y-%m-%d %H:%M:%S")
| table app.name latest past_threshold x

This works, but next I would like to limit the results to only certain values of `app.name`.  For instance, only return a result where `in(app.name, "app1", "app2")` and where `past_threshold="Y"`. In that case, we've detected that the "important" apps have lagged in metric reporting and can trigger an alert.

I assume I need to use a subsearch but I could not get it to work / I'm not sure on the proper formatting.

Thank you.

[Richfez]

Maybe it's as easy as adding it in the where clause:

| mstats latest_time(application_ready_time.value) as latest_ts 
WHERE
index=my-metrics-index
host=some-host
app.name IN ("appname1", "appname2")
BY app.name
| eval past_threshold=if(now() - latest_ts >= 30, "Y", "N")
| eval latest=strftime(latest_ts, "%Y-%m-%d %H:%M:%S")
| table app.name latest past_threshold x

Give that a try.

If it doesn't work, you can just post filter it with something like

| mstats latest_time(application_ready_time.value) as latest_ts 
where
index=my-metrics-index
host=some-host
by app.name
| search app.name IN ("appname1", "appname2")
| eval past_threshold=if(now() - latest_ts >= 30, "Y", "N")
| eval latest=strftime(latest_ts, "%Y-%m-%d %H:%M:%S")
| table app.name latest past_threshold x

The former with the app.names as part of the WHERE clause is probably preferable.  Splunk can often push these sorts of search terms down into the search itself, but I'm not sure if it does that with mstats.  Meaning, if it CAN do that, it'll perform the same as the first one (more or less).  But if it can't do that, it'll run quite a bit slower because it'll get all those stats off disk, then throw away all but the two sets you want to keep.

But it would work, either way.  🙂