Need help calculating total time over 24 hour timeperiod between events

[rvoninski_splun]

I have a proximity sensor that generates a logfile with time stamp for whether or not I am home via my cellphone location. A DeviceValue=0 means I am not home and a DeviceValue=100 means I am home. Can someone provide me with guidance and direction. I would like to be able to calculate the total time I am home over a specified time frame (say 24 hours). You can see a snippet of the dataset I am working with below. Assume that when the system trips from devicevalue=0 to devicevalue=100 that time on the 100 event is when I arrived home and the time when it switches from devicevalue=100 to devicevalue=0 that time I left is on the 0 event. Thank you for any help in advance.

3/20/2016 7:10:48 PM, DeviceName=Rich Presence, DeviceLocation=iPhones, DeviceValue=0
3/20/2016 7:16:48 PM, DeviceName=Rich Presence, DeviceLocation=iPhones, DeviceValue=100
3/20/2016 7:17:48 PM, DeviceName=Rich Presence, DeviceLocation=iPhones, DeviceValue=100
3/20/2016 7:18:54 PM, DeviceName=Rich Presence, DeviceLocation=iPhones, DeviceValue=0
3/20/2016 7:38:58 PM, DeviceName=Rich Presence, DeviceLocation=iPhones, DeviceValue=0
3/20/2016 7:39:43 PM, DeviceName=Rich Presence, DeviceLocation=iPhones, DeviceValue=100
3/20/2016 7:42:44 PM, DeviceName=Rich Presence, DeviceLocation=iPhones, DeviceValue=100
3/20/2016 7:48:44 PM, DeviceName=Rich Presence, DeviceLocation=iPhones, DeviceValue=0

[jruaux_splunk]

You could probably go with the stats command as the answer above explains, but for this kind of things I like to use the transaction command. Try something like this:

transaction DeviceName maxevents=2 startswith=(DeviceValue=100) endswith=(DeviceValue=0) | stats sum(duration) as TotalTimeHome

[somesoni2 · Accepted solution]

Try something like this

Your base search | sort 0 _time | streamstats current=f window=1 values(DeviceValue) as prev values(_time) as prevtime | where isnotnull(prev) | eval duration=_time-prevtime | stats sum(duration) as duration by prev | eval Status=if(prev=0,"Not Home","Home") | eval Duration=tostring(duration,"duration")| table Status Duration

[rvoninski_splun]

This works really well and I have monkeyed around with the data giving all sorts of time stamps and it works. Can you take me through the steps and help me understand how the query breaks down this data?

[somesoni2]

Best way to understand the query is the just apply is part by part to your base search and see the result (at least for small result set).

Get your result and sort in ascending order of time

Your base search | sort 0 _time

For each row, get the previous event's timestamp and DeviceValue

streamstats current=f window=1 values(DeviceValue) as prev values(_time) as prevtime

Filter first events (as that is the first status), calculate duration between current event and prev event.

 | where isnotnull(prev) | eval duration=_time-prevtime 

The field prev contains the previous status and duration contains how long the previous status was valid, so generate your summary using stats, use user friendly value for prev, format and display.

 | stats sum(duration) as duration by prev | eval Status=if(prev=0,"Not Home","Home") | eval Duration=tostring(duration,"duration")| table Status Duration

[marina_rovira]

Hi!

If you just want to calculate the average while you're home, maybe this can work.

| stats count(eval(DeviceValue="100")) as cnt and use this field in avg

   | stats avg(cnt) as avg | eval "Time average"=tostring(round(avg,0), "duration") | table "Time average"

I hope I help you, if it doesn't work, reply me and I will try harder.

Regards!

[rvoninski_splun]

Thank you for your reply. I am not sure if this is going to work for this use case. If I understand the query above it counts the number of events that are raised to the value 100 and then evaluates the sum of those as minutes at home and provides a table of the amount of time?

The only problem is that the times listed in the sample data are not for every minute and can be variable. So I need to calculate the time inbetween the beginning of the 100 events and the beginning of the next 0 event. and do that for every time I enter and leave the house.

Any idea of how this could be done? I would think we will need to use the timestamp values somehow in the calculation. But am confused about the search options available to me to accomplish this.

I appreciate your help.