Splunk Search

How to get earliest datetime

poojak2579
Explorer

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?

Tags (1)
0 Karma
1 Solution

woodcock
Esteemed Legend

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

View solution in original post

poojak2579
Explorer

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?

0 Karma

woodcock
Esteemed Legend

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
Explorer

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.

0 Karma

woodcock
Esteemed Legend

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.

0 Karma

poojak2579
Explorer
0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

February 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...