Splunk Search

how to get the first event time and last event time for field value

SathyaNarayanan
Path Finder

Hi Splunkers,

My events will look like below.

2019-08-06 10:14:00 TYPE="PLB_1", STATUS="true", CAR="A", PLACE="ABC"
2019-08-06 10:16:00 TYPE="PLB_1", STATUS="false", CAR="A", PLACE="ABC"
2019-08-06 10:19:00 TYPE="PLB_1", STATUS="true", CAR="A", PLACE="ABC"
2019-08-06 10:25:00 TYPE="PLB_1", STATUS="false", CAR="A", PLACE="ABC"
2019-08-06 10:40:00 TYPE="PLB_1", STATUS="false", CAR="C", PLACE="ABC"
2019-08-06 10:49:00 TYPE="PLB_1", STATUS="false", CAR="C", PLACE="ABC"
2019-08-06 10:50:00 TYPE="PLB_2", STATUS="false", CAR="B", PLACE="DEF"
2019-08-06 11:14:00 TYPE="PLB_2", STATUS="false", CAR="B", PLACE="DEF"
2019-08-06 11:25:00 TYPE="PLB_2", STATUS="false", CAR="B", PLACE="DEF"

Now i need to get the when the first status as true time till and last status time it was falst as per the PLACE

So i tried transaction command to combine it startswith=STATUS="true" endswith=STATUS="false" , but in my case status will be changed many time, i need to get the first and last STATUS time.

Eg: I need to know from time and End time the CAR was parked in that place, so tat i can calculate the duration of parking per PLACE and TYPE perCAR.

Thanks in advance.

Tags (1)
0 Karma

niketn
Legend

@SathyaNarayanan try the following run anywhere example. You can play around with the search filter as per your need.

| makeresults 
| eval data="2019-08-06 10:14:00 TYPE=\"PLB_1\", STATUS=\"true\", CAR=\"A\", PLACE=\"ABC\";2019-08-06 10:16:00 TYPE=\"PLB_1\", STATUS=\"false\", CAR=\"A\", PLACE=\"ABC\";2019-08-06 10:19:00 TYPE=\"PLB_1\", STATUS=\"true\", CAR=\"A\", PLACE=\"ABC\";2019-08-06 10:25:00 TYPE=\"PLB_1\", STATUS=\"false\", CAR=\"A\", PLACE=\"ABC\";2019-08-06 10:40:00 TYPE=\"PLB_1\", STATUS=\"false\", CAR=\"C\", PLACE=\"ABC\";2019-08-06 10:49:00 TYPE=\"PLB_1\", STATUS=\"false\", CAR=\"C\", PLACE=\"ABC\";2019-08-06 10:50:00 TYPE=\"PLB_2\", STATUS=\"false\", CAR=\"B\", PLACE=\"DEF\";2019-08-06 11:14:00 TYPE=\"PLB_2\", STATUS=\"false\", CAR=\"B\", PLACE=\"DEF\";2019-08-06 11:25:00 TYPE=\"PLB_2\", STATUS=\"false\", CAR=\"B\", PLACE=\"DEF\";"
| makemv data delim=";"
| mvexpand data
| rename data as _raw
| rex "^(?<time>[^\s]+\s[^\s]+)\s"
| eval _time=strptime(time,"%Y-%m-%d %H:%M:%S")
| fields - time
| KV
| sort _time
| stats min(_time) as _time max(_time) as LatestTime first(STATUS) as FirstStatus last(STATUS) as LastStatus values(STATUS) as STATUS by TYPE
| search FirstStatus="true" AND LastStatus=false STATUS IN ("true","false")
| eval duration=tostring(LatestTime-_time,"duration")
| fields - LatestTime

PS: Commands from | makeresults till | sort _time generate data as per your use case. Please try out and confirm!

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

SathyaNarayanan
Path Finder

@niketnilay

thanks,

It worked for me, now i have one more doubt, for example when the park in not used for some time in between, the duration calculates that also, how to more accurate in the duration.

Eg:-

10:00 - True
10:15 - false
10:20 - True
10:23 - False
10:26 - True
10:50 - False

i need to calculate 10:00 to 10:15. then 10:20 to 10:23 and 10:26 to 10:50 , so the duration will be 15 + 3 + 24 = 42.

As per your query it will be 10:00 to 10:50 , so duration will be 50 mins.

thanks in advance

0 Karma

jawaharas
Motivator

Bit more clarification will make it easy to help you.

It's unclear from below statement, how you gonna find start and end time of car parking.

"So i tried transaction command to combine it startswith=STATUS="true" endswith=STATUS="false" , **but in my case status will be changed many time, i need to get the first and last STATUS time."

0 Karma

SathyaNarayanan
Path Finder

I need to start and end time , by First when status was true and last when status was false, i need to ignore between changes.

Eg:
10:00 - True
10:15 - false
10:20 - True
10:23 - False
10:26 - True
10:50 - False

I need to calculate first event as 10:00 and last even as 10:50 , duration wil be 50 mins.
To get it , i need unique value as car details in all events

I need to know how to get it.

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 ...