Splunk Search

How to transform a series of events down to the just the first and last events

wegscd
Contributor

I have a search yielding a series of events:

2017-05-15  68.222609
2017-05-16  68.243478
2017-05-17  68.276522
2017-05-18  68.292174
2017-05-19  68.326957
2017-05-20  68.333913
2017-05-21  68.333913
2017-05-22  68.356522
2017-05-23  68.382609
2017-05-24  68.419130
2017-05-25  68.436522
2017-05-26  68.448696
2017-05-27  68.450435
2017-05-28  68.448696
2017-05-29  68.457391
2017-05-30  68.570435
2017-05-31  68.593043
2017-06-01  67.612174
2017-06-02  67.622609
2017-06-03  67.626087

I want to transform it down to just the first and last

2017-05-15  68.222609
2017-06-03  67.626087

I can use earliest and latest to get everything down to a single event, like this

e_t                 e_v       l_t                   l_v
05/15/2017 00:00:00 68.222609   06/03/2017 00:00:00 67.626087

and can probably figure out how to blow it into 2 events,

I can also use transaction and mvextract to get the first and last values out: How do I refer to the first, nth or last value of a multivalue field? , but I still have the "how do I turn this into 2 events" problem then

is there a better way than either of the above to do this?

0 Karma
1 Solution

woodcock
Esteemed Legend

Just add this to it:

... | multireport [| head 1] [| tail 1]

View solution in original post

0 Karma

woodcock
Esteemed Legend

Just add this to it:

... | multireport [| head 1] [| tail 1]
0 Karma

wegscd
Contributor

cool; don't need to run preceding searches twice!

...now if Splunk would just document multireport! That command is an interesting solution to a number of problems.....

0 Karma

maciep
Champion

Another option might be to use eventstats to get the total number of events, streamstats to get a running count, and then just keep the first and last

... | eventstats count as total | streamstats count | where count=1 OR count=total
0 Karma

wegscd
Contributor

Another really good solution! I was thinking:

| streamstats count as _c1 | sort - _c1 | streamstats count as _c2 | where _c1=1 OR _c2=1 | sort c1

but I really wasn't aware of eventstats.

Repost this as an answer and I'll throw it some points!

0 Karma

niketn
Legend

@wegscd, you can do this by using head 1 and tail 1 along with append to stitch results together. Provided results are time-series data in chronological or reverse chronological order(default).

Since the field names are not mentioned I am including a run-anywhere search similar to your scenario based on splunkd Metrics logs from Splunk's _internal index. This uses _time for time and average_kbps for value.

index=_internal sourcetype=splunkd log_level="INFO" component="Metrics" average_kbps=*
| head 1
| table _time average_kbps
| append [search index=_internal sourcetype=splunkd log_level="INFO" component="Metrics" average_kbps=*
          | tail 1
          | table _time average_kbps] 

PS: The result gets only one event per search before correlating them through append. So this should perform well.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

wegscd
Contributor

does this run search index=_internal sourcetype=splunkd log_level="INFO" component="Metrics" average_kbps=* twice?

0 Karma

niketn
Legend

Yes first time to get first result and second time to get last result

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

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

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...