Transaction within transaction and calculations with both durations

[haph]

Hi,

I have following events from a production machine where each cycle should be one transaction. The cycle starts with the field Producing=1 and ends with Producing=0. With a transaction command I get the duration of each cycle. But now I want to know how long the machine is waiting for user input in each cycle which is determined with the field Waiting. The machine waits if Waiting=1 and stops waiting if Waiting=0 again. The waiting can occur multiple times each cycle and times may vary.

The event order looks like this (Producing and Waiting starts as 0, each event has a timestamp):

Producing=1
Waiting=1
Waiting=0
Waiting=1
Waiting=0
Waiting=1
Waiting=0
Producing=0
[...new cycle starts after some time]

I want to know the duration from Producing=1 to 0 less the summed up from Waiting=1 to 0.

The raw events looks like that:

{
  "name": "Waiting",
  "value": "true"
}
{
  "name": "Producing",
  "value": "true"
}
{
  "name": "Waiting",
  "value": "false"
}
{
  "name": "Producing",
  "value": "false"
}

With

| eval Waiting=case(name=="Waiting" AND value=="true", 1, name=="Waiting" AND value=="false", 0)
| eval Producing=case(name=="S1.Producing" AND value=="true", 1, name=="S1.Producing" AND value=="false", 0) 

I get the for-mentioned fields Producing and Waiting.

My full search so far for the duration of a cycle looks like this:

index=machinedata source=*43910* name="Waiting" OR name="S1.Producing" 
| eval Waiting=case(name=="Waiting" AND value=="true", 1, name=="Waiting" AND value=="false", 0)
| eval Producing=case(name=="S1.Producing" AND value=="true", 1, name=="S1.Producing" AND value=="false", 0) 
| transaction startswith=(Producing=1) endswith=(Producing=0)


| rename duration as durProd
| eval durProd=round(durProd/60,1)


| chart values(durProd) by _time

Now how can I expand this search to also get a transaction of the Waiting field and sum up the waiting durations for each producing transaction?

If you need more info please ask

Thanks!!
Henrik

[woodcock · Accepted solution]

Like this:

| makeresults 
| eval raw="{\"name\":\"Waiting\",\"value\":\"true\"}:::{\"name\":\"Producing\",\"value\":\"true\"}:::{\"name\":\"Waiting\",\"value\":\"false\"}:::{\"name\":\"Producing\",\"value\":\"false\"}:::{\"name\":\"Waiting\",\"value\":\"true\"}:::{\"name\":\"Producing\",\"value\":\"true\"}:::{\"name\":\"Waiting\",\"value\":\"false\"}:::{\"name\":\"Producing\",\"value\":\"false\"}", host="foo"
| makemv delim=":::" raw
| mvexpand raw
| streamstats count AS _serial
| eval _time = _time - (100 * _serial)
| rename raw AS _raw
| kv

| rename COMMENT AS "Everything above generates sample events; everything below is your solution"

| eval {name}=value
| reverse
| streamstats count(eval(Producing="true")) AS sessionID BY host
| multireport
[ search Producing="*"
| reverse
| eval which="Producing" ]
[ search Waiting="*"
| reverse
| eval which="Waiting"]
| stats min(_time) AS _time range(_time) AS duration BY which host sessionID
| where (duration>0)
| eval {which}=duration
| fields _time host sessionID Producing Waiting
| stats values(*) AS * BY host sessionID

[haph]

Hi woodcock,
thank you very much for your reply!
It wasn't quite the solution but with your input I was able to find the solution I was looking for! (Maybe because of my bad problem description)

My search looks like this now:

index=machinedata source=*43910* name="Waiting" OR name="S1.Producing"
| eval {name}=value
| rename "S1.Producing" as Producing
| fields - "S1.Producing"
| reverse
| streamstats count(eval(Producing="true")) AS sessionID
| streamstats count(eval(Waiting="true")) AS sessionID2
| eval which=name
| eval which=replace(which, "S1.", "")
| multireport
    [
| stats min(_time) AS _time range(_time) AS duration BY  sessionID 
]
    [
    | stats min(_time) AS _time range(_time) AS durationW BY  sessionID2 sessionID
    ]

| eval Producing1=round(duration/60, 2)
| eval Waiting1=round(durationW/60, 2)

| fields _time sessionID Producing1 Waiting1 
| stats sum(*) AS * BY sessionID
| eval processtime=Producing1-Waiting1
| table *
| chart values(Producing1) values(Waiting1) by sessionID

Now I can sum up the waiting times in each Producing cycle.
The calculated values don't add up yet but the raw data can be the cause maybe. I will look into this further and keep updating here.

Thanks!