How to create a report on the results of the latest events only?

[KJDII]

Hello,

I am trying to create a report that only looks at the latest events by a sourcetype.
The sourcetype is an indexed text file, and it its pulls in the events every time the file changes.

This is the working search:

index=ops sourcetype="csv-marketData" earliest=-12h@h |  where Price!="NA" 
| eval cal_mkt_cap=round(Share_Outstanding * Price,3) 
| eval rnd_MKT_CAP = round(MARKET_CAP,3) 
| eval perc_range = (cal_mkt_cap / rnd_MKT_CAP)*100
| where perc_range < 99
| eval rnd_perc = round(perc_range,2)
| rename cal_mkt_cap as "Calculated MKT CAP" 
| rename rnd_MKT_CAP as "Provided MKT CAP"
| rename rnd_perc as "%"
| table ID "Calculated MKT CAP" "Provided MKT CAP" "%"

I would like this table to only show results from the latest set of events. each event set has the same _time value. new events can come in minutes apart of once daily, so I would like to always be reviewing the indexed file.

Thanks for your help.

[vr2312]

You can run your search on the basis of indexed time and only use the events that are latest. That is, if you run the search every 4 hours, you can write a query to search for events in indexed time-stamps in those four hours and sort it. That should give you a headstart.

[somesoni2]

So you want to see latest record for each (assuming ID is something unique) OR latest records for a day?

[KJDII]

Latest records for each ID. Yes ID is Unique.

Each time the file is updated every row is indexed, i want to always be working with the latest version of the file.

for example,
There could be data for at 8:53AM and 9:01AM, I want the 9:01AM (latest) version of all the ID's to then run my evaluations and reports against.

Hopefully thats clear, I can try to get a snippet of the events if that makes it easier to explain.

[somesoni2]

I'm assuming the file name remains the same, it's being updated. Based on a statement that you wrote in the question (new events can come in minutes apart of once daily), I believe something like this could work:

index=ops sourcetype="csv-marketData" [| tstats max(_time) as _time WHERE index=ops sourcetype="csv-marketData" earliest=-12h | bucket span=1m _time | eval earliest=_time | eval latest=_time+60 | table earliest latest | format "" "" "" "" "" "" ] 
 |  where Price!="NA" 
 | eval cal_mkt_cap=round(Share_Outstanding * Price,3) 
 | eval rnd_MKT_CAP = round(MARKET_CAP,3) 
 | eval perc_range = (cal_mkt_cap / rnd_MKT_CAP)*100
 | where perc_range < 99
 | eval rnd_perc = round(perc_range,2)
 | rename cal_mkt_cap as "Calculated MKT CAP" 
 | rename rnd_MKT_CAP as "Provided MKT CAP"
 | rename rnd_perc as "%"
 | table ID "Calculated MKT CAP" "Provided MKT CAP" "%"

[KJDII]

Spot on! Thank you.

I admit my attempt to solve this was not even close.