How to generate a search to calculate the duration when a pressure value drops below a certain threshold?

[westonaj1]

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

[gvmorley]

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.

[westonaj1]

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.

[westonaj1]

Yes that would be great and perhaps including the pressure readings for that duration as well

[lguinn2]

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