Find the value of the last field and use that field as a filter to find matching records

[darth_mango_97]

Hi,

I am trying to build a result in tabular format.

timestamp	prcs_nm	outcome	date
normal time stamp	prcs_nm	Fail	2020-10-19
normal time stamp	prcs_nm	Fail	2020-10-19
normal time stamp	prcs_nm	Fail	2020-10-20
normal time stamp	prcs_nm	Fail	2020-10-21
normal time stamp	prcs_nm	Pass	2020-10-21

This is the table, that I am currently getting.

But I need the query to take the last value from the date field, and should get all the records that shares the same date field. The date field has custom input, and it changes always.

The query, that must be built, must give the table in this way and not in the way I am currently getting it.

I tried dedup, but i cannot give a sure count as to how many records will be there at the events log.

timestamp	prcs_nm	outcome	date
normal time stamp	prcs_nm	Fail	2020-10-21
normal time stamp	prcs_nm	Pass	2020-10-21

The case always would be to have the last updated date in the date column and find all the records that shares the same date, irrespective of the prcs_nm and outcome there is.

And I need the results in tabular format.

Would really appreciate for any help.

Thanks a lot in advance.

[kennetkline]

This is when I resort to stats command:

There is some items to know first/last  versus earliest/latest.  First/Last is when data arrived to splunk (delivery order of events) where earliest/latest is time order of the events.

If the events are in order and the dates are such the same and not having seconds / nanoseconds.

I would try:

your query
| stats last(timestamp) as timestamp, last(prcs_nm) as prcs_nm, last(outcome) as outcome by date
| table timestamp prcs_nm outcome date

most of the time I want the actual latest event:

your query
| stats lastest(timestamp) as timestamp, lastest(prcs_nm) as prcs_nm, lastest(outcome) as outcome by date
| table timestamp prcs_nm outcome date

These can have different result (especially when you are looking at events (not grouping by date)  as last versus latest.  I am not doing justice to the Stats documentation:

https://docs.splunk.com/Documentation/Splunk/8.0.6/SearchReference/Stats

Recommend to use stats for any deduplication of collections of similar events

[darth_mango_97]

Hi @kennetkline ,

The usage of stats yields a single row result.
I wanted to create a field out of the last indexed field's value, and use that in the table.
Not the efficient way, I know.

But my logs work that way.
Thanks a lot for your response. 🙂

[kgarigipati]

your query | sort - date | head 1

Intent here is use your query to build the table as explained in your example, then sort command to sort date column, which will bring the latest date as the first value in your table and then use head command, this will fetch the first row, which is what you need.

Caveat : I assume that will have one date per column.

[darth_mango_97]

Hi @kgarigipati,

Hah, Thanks for the reply.

If I use head 1, I will get only one row. 

I need to get all the records that has the date 2020-10-21.

And the date is not a constant, it varies, and I need a dynamic way to capture that date and filter out the other records that share the same date. 

The example here has two such rows. 

But the actual data has a bigger row count, the number of attributes I may get, varies by process.

If there is an alternate way, by which i can capture the records that were last indexed based on the same date column, that solution too would be way too helpful. 

[ITWhisperer · Accepted solution]

your query
| eventstats latest(date) as lastdate
| where date=lastdate

[darth_mango_97]

Hi @ITWhisperer,

This didn't yield any result. 😞

[ITWhisperer]

If you try the eventstats (without the where clause) do you at least get an extra column with the last date on all the rows?

[darth_mango_97]

Hey @ITWhisperer ,

Thank you, it worked.
Sorry, I searched in the wrong index previously.

creating the new field with eventstats worked.
Thank you.