topic Re: how to get the first event time and last event time for field value in Splunk Search

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

SathyaNarayanan — Wed, 30 Sep 2020 01:36:47 GMT

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.

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

jawaharas — Wed, 30 Sep 2020 01:36:50 GMT

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

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

SathyaNarayanan — Tue, 06 Aug 2019 08:03:33 GMT

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.

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

niketn — Tue, 06 Aug 2019 08:30:45 GMT

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

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

SathyaNarayanan — Wed, 07 Aug 2019 06:24:48 GMT

@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