How to display the latest event as a result?

[kartm2020]

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

[damiensurat]

I know this is an old post, but figured I can contribute with a valid solution.   This should do the trick:

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

| eval latest_time = strftime(latest_time, “%m/%d/%y %H:%M:%S”)

| sort 0 - latest_time

[lmonahan]

Super helpful! Works great!

[woodcock]

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.

[kartm2020]

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

[moliminous]

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.

[moliminous]

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:

1. 'status' field has multiple values per event (perhaps due to commands earlier in your search
2. 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!

[kartm2020]

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.

[kartm2020]

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

[moliminous]

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.

[kartm2020]

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

https://ibb.co/cbRGzyw

[moliminous]

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.

[kartm2020]

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

[moliminous]

Dest is MPGDR vs MPGPR

[moliminous]

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.

[kartm2020]

can you please modify the query and paste it.

[moliminous]

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

OR

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

[kartm2020]

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

[moliminous]

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?

[kartm2020]

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

[jacobpevans]

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