transaction with one start and several end conditions

[clorne]

Hello,
My data are organized in three main data
:
Fault Status Reset_field
FAULT1 TRUE null
FAULT2 TRUE null
null null Reset
FAULT1 TRUE null
FAULT2 TRUE null
FAULT1 FALSE null
FAULT2 FALSE null
null null Reset

I would like to get the Time (FAULTx TRUE ) - (FAULTx FALSE) when no reset occurs between the (FAULTx TRUE) and (FAULTx FALSE)
I tried with the command:
transaction source Fault endswith=eval((Status="FALSE") or(Reset="ModemDSP")) mvlist=true

I got the following groups:
FAULT2 TRUE null
null null Reset

FAULT2 TRUE null
FAULT2 FALSE null

FAULT1 TRUE null

FAULT1 TRUE null
FAULT1 FALSE null

and I was expecting for the last one :
FAULT2 TRUE null
null null Reset
and
FAULT1 TRUE null
FAULT1 FALSE null**

But it seems that the event containing the reset can be part of only of one transaction group.
I tried also:

eval StartTime = if(match(Status, "TRUE"),_time,null) | 
eval EndTime = if(match(Reset_field,"Reset") or match(Status, "FALSE") ,_time,null) | 
stats values(StartTime) as StartTime values(EndTime) as EndTime values(Status) as Status values(Reset) as Reset by Fault  

Unfortunaley this is not working, the reset_fields is never taken in account; I think this is because it has no fault field ....

If someone has an idea ...

Thanks
Regards

[woodcock · Accepted solution]

If I understand your goal, you are trying to find cases where the fault "healed itself" (where there was no Reset event between) and furthermore that you care about how long it takes to "heal itself". If so, try this:

| makeresults 
| eval raw="FAULT1,TRUE,null::FAULT2,TRUE,null::null,null,Reset::FAULT1,TRUE,null::FAULT2,TRUE,null::FAULT1,FALSE,null::FAULT2,FALSE,null::null,null,Reset" 
| makemv delim="::" raw 
| mvexpand raw 
| rex field=raw "^(?<Fault>[^,]+),(?<Status>[^,]+),(?<Reset_field>[^,]+)$" 
| foreach * [eval <<FIELD>>=if((<<FIELD>>=="null"), null(), '<<FIELD>>')]
| streamstats count AS _time
| fields - raw 
| sort 0 - _time
| rename COMMENT AS "Above is setup; below is solution"
| reverse
| streamstats count(eval(Reset_field="Reset")) AS SID
| eval TimeTrue=if((Status="TRUE"), _time, null())
| eval TimeFalse=if((Status="FALSE"), _time, null())
| streamstats max(TimeTrue) AS lastTrue BY SID
| streamstats min(TimeFalse) AS firstFalse BY SID
| eval HealTime = if((Status="FALSE"), _time - lastTrue,  null())
| search HealTime="*"
| sort 0 HealTime SID
| dedup SID

You can then add this:

| stats avg(HealTime)

[clorne]

Thansk a lot.

Regards

[woodcock]

Did this get you to your goal?

[clorne]

Yes,
Well I have taken the idea :
streamstats count(eval(Reset_field="Reset")) AS SID
stats first(TimeTrue) first(TimeFalse) by SID

[woodcock]

You should almost certainly NOT be using first because it probably does not mean what you think that it means. Make ABSOLUTELY sure that you understand it, if you use it. If you mean first in time, you should DEFINITELY be using min.

[niketn]

Not sure if the following solves, however, seems like you need something like streamstats to capture events prior to Reset

<Your Base Search>| streamstats count as EventCount min(_time) as Earliest max(_time) as Latest values(Fault) as Faults values(Status) as Status by Reset_field reset_before="("match(Reset_field,\"Reset\")")"