Splunk Search

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

darth_mango_97
Explorer

Hi,

I am trying to build a result in tabular format.

timestampprcs_nm outcomedate
normal time stampprcs_nmFail2020-10-19
normal time stampprcs_nmFail2020-10-19
normal time stampprcs_nmFail2020-10-20
normal time stampprcs_nmFail2020-10-21
normal time stampprcs_nmPass

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.

timestampprcs_nm outcomedate
normal time stampprcs_nmFail2020-10-21
normal time stampprcs_nmPass

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.

Labels (1)
0 Karma
1 Solution

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

View solution in original post

kennetkline
Path Finder

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
Explorer

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

0 Karma

kgarigipati
Loves-to-Learn

 

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.

0 Karma

darth_mango_97
Explorer

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. 

0 Karma

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

View solution in original post

darth_mango_97
Explorer

Hi @ITWhisperer,

This didn't yield any result. 😞

0 Karma

ITWhisperer
Ultra Champion

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
Explorer

Hey @ITWhisperer ,

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

creating the new field with eventstats worked.
Thank you.

0 Karma
.conf21 CFS Extended through 5/20!

Don't miss your chance
to share your Splunk
wisdom in-person or
virtually at .conf21!

Call for Speakers has
been extended through
Thursday, 5/20!