Hi, assume I have the following type of data for pressure sensors in multiple sites. What we need to do (preferably without using transactions) is calculate the duration for a site, UID when the pressure value drops below say 3 as a threshold. Any help or ideas appreciated.
Date Time Site UID Pressure
01/02/2017 12:01:00 abc 123 3.2
01/02/2017 12:02:00 abc 123 3.1
01/02/2017 12:03:00 abc 123 3
01/02/2017 12:04:00 abc 123 2.9
01/02/2017 12:05:00 abc 123 2.9
01/02/2017 12:06:00 abc 123 3.2
01/02/2017 12:07:00 abc 234 3
01/02/2017 12:08:00 abc 234 2.6
01/02/2017 12:09:00 abc 234 2.6
01/02/2017 12:10:00 abc 234 2.5
01/02/2017 12:01:00 def 123 3.2
01/02/2017 12:02:00 def 123 3.1
01/02/2017 12:03:00 def 123 3
01/02/2017 12:04:00 ghi 123 2.9
01/02/2017 12:05:00 ghi 123 2.9
01/02/2017 12:06:00 ghi 123 3.2
01/02/2017 12:07:00 ghi 234 3
01/02/2017 12:08:00 ghi 234 2.6
01/02/2017 12:09:00 ghi 234 2.6
01/02/2017 12:10:00 ghi 234 2.5
01/02/2017 12:11:12 ghi 234 3
01/02/2017 12:12:22 ghi 234 3.1
01/02/2017 12:13:12 ghi 234 3.2
02/02/2017 10:12:12 ghi 234 2.9
02/02/2017 10:13:12 ghi 234 3
02/02/2017 10:14:32 ghi 234 31
Hi,
It would be great to understand why you don't want to use transaction for this? It feels like it would be well suited to the job?
| where Pressure<3
| eval timedate=Date." ".Time
| eval _time=strptime(timedate,"%d/%m/%Y %H:%M:%S")
| transaction UID,Site maxpause=2m mvlist=t
| sort +_time,Site,UID
| eval start=min(timedate)
| eval end=max(timedate)
| eval Site=mvdedup(Site)
| eval UID=mvdedup(UID)
| eval duration=duration+60
| table Site UID start end Pressure duration
| eval duration=tostring(duration,"duration")
Which would result in a table like this:
Note: As the duration field in the transaction is the elapsed time between the first and last event, you can decided if this is OK, or if you want to 'fill' it with an extra minute.
For example, if you've got one pressure reading that's less than 3, was the 'duration' 0 seconds, or was it 60 seconds? It's more a preference thing on how you want to interpret your data.
If anyone else has a non-transactional way, I'd be interested too.
Thanks for your response. Whilst transaction seems the obvious choice I was looking to see if there was a way of doing it without using transactions.
Yes that would be great and perhaps including the pressure readings for that duration as well
I assume that you want output that looks something like this?
Site UID Start End Duration
abc 123 1/2/17 12:04 1/2/2017 12:05 2:00
abc 234 1/2/17 12:08 1/2/2017 12:10 3:00