How to get earliest datetime

[poojak2579]

I have a field which contains first_found_date and due to some reason it keeps on changing for some of the assets.

Example:
If an asset "A1" has 3 first_found_date over a period of time:
2017-06-20 22:30:30
2016-05-25 22:30:30
2017-01-25 22:30:30

I want to use earliest first_found(i.e 2016-05-25 22:30:30) in all my reports.
If I use the following query to check the earliest first_found , it takes more than 1 hour to get the value because it has to go through all the records over the period of time.

sourcetype=a
|eval combo = Asset+"_"+ID
|stats min(FIRST_FOUND) as earliest_ff by combo

Is there any way we can correlate the asset with its earliest first_found_date without editing props.conf?

[poojak2579]

Let me make it more clear. There are vulnerabilities on the assets.
ID is the Vulnerability Id and First_Found is the date when it was first found on the asset.
Because of some reason there are multiple First_Found for the combination of Asset+Id.
I want to take earliest First_Found and assign it to the combination of Asset+Id.
I have scheduled a query to run everyday which will check Earliest First_Found in last one year and it takes long time to run.
Is there any way we can assign min of First_Found to Asset+Id so that I don't have to run that query over and over again?

[woodcock · Accepted solution]

There are several ways.

Like this:

index=YouShouldAlwaysSpecifyAnIndex sourcetype=a
| stats earliest(FIRST_FOUND) AS earliest_ff BY Asset ID
| eval combo = Asset+"_"+ID
| table combo earliest_ff

Or this:

index=YouShouldAlwaysSpecifyAnIndex sourcetype=a
| reverse
| dedup Asset ID
| eval combo = Asset+"_"+ID
| table combo FIRST_FOUND
| rename FIRST_FOUND AS earliest_ff

[poojak2579]

Thanks for the reply.
I am already using the first code but it is taking long time to run the query because I have to check million of records with a time range of more than 1 year.
I want to normalize it, assign the min(First_Found) to all the combos at once and then use it everywhere.
Please let me know if there is any other way to do it.

[woodcock]

You will have to make it an index-time field or create an accelerated data model so that you can use tstats. You might also use a summary index.

[poojak2579]

I tried accelerated Datamodel and summary index but it didn't work because of some limitations .

https://answers.splunk.com/answers/556349/how-to-join-data-from-two-indexes.html
https://answers.splunk.com/answers/569099/lookup-fields-not-updating-in-the-datamodel.html