how wrirte search query for status change

[mahendra559]

hear if we have a multiple same status is there it will pick only first status event and if the different status events will come then it will pick the other status events how we can write the query for this

ex: status values and its dates
False positive (25 may)
False positive (24 may)
Investigating (23 may)
Investigating (22 may)
Service degradation (21 may)

thanks

[mahendra559]

if 22nd and 23rd is same status then it will pick 22nd event only
and then 23rd and 24th are different status at that time it will pick 23rd and 24th events as well
and 24th, 25th same status so it will pick 24th one only
(simply when ever status change at that time it will pick the event other wise it will not pick the same event again and again )

[Richfez · Accepted solution]

You'll have to take some liberties with the searches below - I had to "pseudo-code" up some SPL because I'm sure that's not the way your data actually looks*.

So, I'm going to pretend it IS how your data looks for a second, and we'll even handwave away the dates in the format they're in.

I think the easiest way is with stats, and I'll show you that way, but streamstats could be an option too in certain cases depending on how your source data actually looks.

Using stats might be

my base search index=foo sourcetype=bar
|stats last(date) by status

You might need to swap last(date) with first(date) I can never keep those straight, but testing will uncover if that's what you need.

That would take the list you have above, and return "False Positive" for 25 May, and "Investigating" for 23 May.

BUT that only works for your actual example above. What it'll REALLY do is take any arbitrarily large list of dates and status, and return only the last date for any status, not the last date in any group of statuses. So if your list above had added to it "Investigating" for 27 May, then the ONLY row returned by "Investigating" will be 27 May, because that's the last for it.

Streamstats is a lot like stats, but is calculated as the events "stream through" so works a bit differently. You can look that up in Splunk docs if you need that way instead, and specifically check out the on_change parameter. Otherwise it'll be a lot the same.

Lastly, here's a link to a blog on Stats that Laura S wrote that may help, it sort of extends the main Docs to give a bit more background and help

*Best would have been to include a small subset of the actual data you have, so that we can tell you how to get from that to your answer without having to guess.