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

[SathyaNarayanan]

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.

[niketn]

@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!

[SathyaNarayanan]

@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

[jawaharas]

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

[SathyaNarayanan]

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.