Splunk Search

Transaction within transaction and calculations with both durations

haph
Path Finder

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

0 Karma
1 Solution

woodcock
Esteemed Legend

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

View solution in original post

woodcock
Esteemed Legend

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
Path Finder

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!

Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...