Splunk Search

Output first event and last event.

ferza
Explorer

I want to be able to put in a SessionID along with specific parameters, that will immediately show me the full timestamp of the first event in the session log and the timestamp of the last event in the session log.

I have something like this which does work in a way:

UNIQUESESSIONID | stats earliest(_time) AS Earliest, latest(_time) AS Latest | eval FirstEvent=strftime(Earliest,"%+") | eval LastEvent=strftime(Latest,"%+")

It gives me the first and last timestamp under "FirstEvent" and "LastEvent" , however under Earliest and Latest, it shows some phone numbers which I don't need to see. I'm not sure how to refine that so it removes that Earliest and Latest and just show me the FirstEvent and LastEvent. Can anyone figure that out?

Additionally, is there a way where I can put in the UNIQUE session ID and then pass parameters where it can show me the output of the first event and last event? The only item that will be similar in the first and last event is just the Session ID. All I'll really want to gather out of this is just the timestamp of first/last event of the session.

1 Solution

Runals
Motivator

I think what you are thinking are phone numbers is actually how Splunk stores/understands/interprets time - in Epoch format. What your query is doing is for a particular sessionid getting the first and last time of the event and as the output naming the fields Earliest and Latest respectively. Your eval statements are then creating NEW fields called FirstEvent and LastEvent giving your output a total of 4 fields. There are a couple ways to get down to just one set of time fields and in many cases all are equally valid

  1. at the end of your query have another pipe and do | fields - Earliest Latest this will remove those from the output
  2. adjust your eval field names to match what is in your stats command or vice versa. Something like | eval Earliest=strftime(Earliest,"%+") . At some level how you work this option is based on what you want your field names to be called in the final result. You could always use a | rename but why bother when you are naming the fields in the first place in either your stats or your eval.
  3. There is another command you can use called convert which would allow you to adjust both times in one command | convert ctime(Earliest) ctime(Latest) but that is simply doing differently what you are already accomplishing with the eval commands

To get a feel for all of your data you could run a search like

sourcetype = foo  | stats earliest(_time) AS Earliest, latest(_time) AS Latest by sessionid | convert ctime(Earliest) ctime(Latest)

Which will show the first and last event times for all of your session ids during the time period covered. If you want to specify a session id you can try to put it into your initial search or by something like this

sourcetype = foo sessionid=123456 | stats earliest(_time) AS Earliest, latest(_time) AS Latest by sessionid | convert ctime(Earliest) ctime(Latest)

View solution in original post

markthompson
Builder

Hello ferza,
I assume from this question that you have this search in a dashboard, as you said you wanted to pass parameters to it, for this, you could use a form element of a multi pick dropdown, and then use the Table command at the end of your search, and use the input from the multi select dropdown

0 Karma

Runals
Motivator

I think what you are thinking are phone numbers is actually how Splunk stores/understands/interprets time - in Epoch format. What your query is doing is for a particular sessionid getting the first and last time of the event and as the output naming the fields Earliest and Latest respectively. Your eval statements are then creating NEW fields called FirstEvent and LastEvent giving your output a total of 4 fields. There are a couple ways to get down to just one set of time fields and in many cases all are equally valid

  1. at the end of your query have another pipe and do | fields - Earliest Latest this will remove those from the output
  2. adjust your eval field names to match what is in your stats command or vice versa. Something like | eval Earliest=strftime(Earliest,"%+") . At some level how you work this option is based on what you want your field names to be called in the final result. You could always use a | rename but why bother when you are naming the fields in the first place in either your stats or your eval.
  3. There is another command you can use called convert which would allow you to adjust both times in one command | convert ctime(Earliest) ctime(Latest) but that is simply doing differently what you are already accomplishing with the eval commands

To get a feel for all of your data you could run a search like

sourcetype = foo  | stats earliest(_time) AS Earliest, latest(_time) AS Latest by sessionid | convert ctime(Earliest) ctime(Latest)

Which will show the first and last event times for all of your session ids during the time period covered. If you want to specify a session id you can try to put it into your initial search or by something like this

sourcetype = foo sessionid=123456 | stats earliest(_time) AS Earliest, latest(_time) AS Latest by sessionid | convert ctime(Earliest) ctime(Latest)

ferza
Explorer

This works, thanks!

0 Karma

Isaias_Garcia
Path Finder

or you can try;
| reverse

0 Karma

ramdaspr
Contributor

Use the fields command to specify which fields you want to see.
Use transaction for grouping events

PS: its not a phone number but the time in unix time format.

0 Karma

ferza
Explorer

Doesn't seem to be what I'm looking for after trying it. I'm not looking to add a certaiin field, I just want to see the timestamp of the first event and last event of the log. Each time the first event in the log for every session ID will contain something different. Can you show me an example of how it would work.

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...