topic Re: How to display the latest event as a result? in Splunk Search

How to display the latest event as a result?

kartm2020 — Thu, 17 Oct 2019 12:25:58 GMT

Search query :1
index="main" earliest=06/01/2019:00:00:00 latest=now | stats first(status) by src destination port
Search query : 2
index="main" earliest=06/01/2019:00:00:00 latest=now | stats latest(status) by src destination port
I have used first and latest command in stats.

There 2 status in the events like "STATE UP" and "STATE DOWN". I would like fetch the latest event with latest status field. But if i am searching the above query it is showing the both.(STATE UP and STATE DOWN). I would like display the latest either "status up or status down".
Someone help me to find the solution.
Current Results:
src destination port first(status)
XXX YYY 443 State DOWN
XXX YYY 443 State UP

Re: How to display the latest event as a result?

jacobpevans — Thu, 17 Oct 2019 13:19:41 GMT

Query 2 is the one you want, and it works fine for me in 7.2.4

          | makeresults | eval _time = now()-1, src="XXX", destination="YYY", port=443, status="DOWN"
| append [| makeresults | eval _time = now()  , src="XXX", destination="YYY", port=443, status="UP" ]
| stats latest(status) by src destination port

src   destination   port   latest(status)
XXX   YYY           443    UP

edit: Unless your timestamp on both rows is the exact same? Try this:

| stats latest(status) by _time src destination port
| sort -_time

Re: How to display the latest event as a result?

kartm2020 — Thu, 17 Oct 2019 13:39:39 GMT

Hi Jacobevans,
Thank you for the query. But i am expecting the different results. In netscaler, The events will trigger when there is a status change from UP to DOWN or DOWN to UP. I would like to display the latest status with respect to source and destination. You query gives all the results which is not expected one

Example : I have 3 events
10/17/19 7:05 PM : Status was DOWN
10/17/19 7:06 PM : Status was UP
10/17/19 7:07 PM : Status was DOWN

expected out put should be the current status, Like below:
STATE was DOWN(Because this status is the latest)

Re: How to display the latest event as a result?

moliminous — Fri, 18 Oct 2019 03:46:34 GMT

The way to get the most recent status would be to end your query with:

| stats latest(status) by src destination port

If you wish to also see the time of the event in the results you would add it as a value rather than split the stats command by _time, to avoid seeing every entry for that src/dest/port combo, by adding this to the end of your query instead:

| stats values(_time) as _time, latest(status) by src destination port

If either of these result in more than one status, there is something else wrong with the values that are in src, destination, and/or port. The only ways you would see more than 1 status for these is if:

'status' field has multiple values per event (perhaps due to commands earlier in your search
The values are unique between src, destination, port

You can test if the 'status' field has multiple values using this command:

| eval n = mvcount(status)
| where n>1
| sort - n

I would also note that 'destination' is not a CIM-compliant field, it should be normalized to just 'dest' using an alias or other method.

Hope this helps!

Re: How to display the latest event as a result?

kartm2020 — Fri, 18 Oct 2019 05:47:16 GMT

Hi moliminous,
I have used your query but still i am getting both values like state up and state down for each src and destination.
But i need the latest event field value.

Re: How to display the latest event as a result?

kartm2020 — Fri, 18 Oct 2019 09:57:08 GMT

Status field has only one value(either "STATE UP" or STATE DOWN" in each event.
Values are unique between src destination and port

Re: How to display the latest event as a result?

moliminous — Fri, 18 Oct 2019 10:41:23 GMT

The only way that is possible is something else is wrong with either the data or your environment. Please post your entire SPL and a screenshot of results.
As I mentioned, either the values aren’t actually unique or there are many events at once and an issue with duplicate events on your indexers.

Re: How to display the latest event as a result?

woodcock — Fri, 18 Oct 2019 19:21:58 GMT

Under normal circumstances those 2 searches should return the same results. Occasionally, you will see a warning that says something like There was a problem and your search results might not be returned in time sorted order or some such, and in that case, they will differ. You should stick with using first(status) presuming you have not re-sorted your events (they are returned in latest-to-oldest order with latest on top, e.g. "first"), because it is more efficient. What do you get with these?

index="main" earliest=06/01/2019:00:00:00 latest=now | sort 0 - _time | stats first(status)    BY src destination port

vs.

index="main" earliest=06/01/2019:00:00:00 latest=now | sort 0 - _time | stats latest(status) BY src destination port

These should absolutely be the same.

Re: How to display the latest event as a result?

moliminous — Sat, 19 Oct 2019 01:59:53 GMT

I respectfully disagree. Though first(stats) is faster, it doesn’t guarantee the latest event chronologically, which is what the goal is here. First is faster because it doesn’t wait for the entire set of results, it will return the first event that streams in. Although that is often the most recent, it isn’t necessarily the most recent.

Re: How to display the latest event as a result?

kartm2020 — Mon, 21 Oct 2019 06:09:13 GMT

Hi Moliminous,
Please find the below link of entire search result.

https://ibb.co/cbRGzyw

Re: How to display the latest event as a result?

kartm2020 — Mon, 21 Oct 2019 06:10:02 GMT

Hi Woodcock,

I am getting the same result even if i include sort command. Please find the below url of entire search.

https://ibb.co/cbRGzyw

Re: How to display the latest event as a result?

moliminous — Mon, 21 Oct 2019 06:33:06 GMT

I don’t see an issue in your image. The destinations are unique so that search shows the result of latest state per src dest combination. If that’s not what you wanted please list what you’d like to display instead.

Re: How to display the latest event as a result?

moliminous — Mon, 21 Oct 2019 06:34:38 GMT

Dest is MPGDR vs MPGPR

Re: How to display the latest event as a result?

kartm2020 — Mon, 21 Oct 2019 06:36:17 GMT

if you see the image STATE UP and STATE DOWN status are showing in it. I would like display the single row with current status(Either STATE UP or STATE DOWN not the both)
for all source and destination

Re: How to display the latest event as a result?

kartm2020 — Mon, 21 Oct 2019 06:37:41 GMT

I would like to display the current status(either state up or state down) with respect to src destination and port

Re: How to display the latest event as a result?

moliminous — Mon, 21 Oct 2019 06:42:50 GMT

Okay so you don’t want to split by destination then. Move destination to before the ‘by’ clause and use either latest(destination) or values(destination) if you want the whole list for that src and port. If you put destination after the BY clause, Splunk shows every unique combination of the fields after BY.

Re: How to display the latest event as a result?

kartm2020 — Mon, 21 Oct 2019 06:45:27 GMT

can you please modify the query and paste it.

Re: How to display the latest event as a result?

moliminous — Mon, 21 Oct 2019 06:51:26 GMT

| stats latest(destination) as destination, latest(status) as status, by src port

| stats values(destination) as destination, latest(status) as status by src port

Re: How to display the latest event as a result?

kartm2020 — Mon, 21 Oct 2019 07:04:25 GMT

It is working as partial. I would like to display all the source and destination with respect to status(Either STATE UP or STATE DOWN.
Your query display the latest destination (Ignoring some destination).

Re: How to display the latest event as a result?

moliminous — Mon, 21 Oct 2019 07:22:03 GMT

Use the second one I just listed for all destinations. If you want the status for each combination of src port and destination, you already had that in your image link. Note that your destinations were different which is why there were 2 states. The destinations are so similar perhaps you thought they were the same?