SCADA bitwise registers time on and time off search

[phoenixdigital]

Got posed a tricky question today for a search.

We are monitoring a diesel generator which generates a number of alarms which are sent as a single integer with each bit of that integer meaning a particular alarm.

ie
bit 0 - high_rpm
bit 1 - high_temp
bit 2 - low_temp
etc....

Now this generator is being polled every 5 seconds and data results are begin fed back into Splunk.

So an event would look like this (where sectionCode identifies the generator)
time_of_event, sectionCode=generator1, alarmCode, rpm, output_kw

So I was asked to show a list of when alarms went on and off so I came up with this search.

sourcetype="holdingRegisters" sectionCode=gen* | sort 0 sectionCode, _time asc | delta alarmCode AS alarmChange | search alarmChange!=0 | `gen_alarm_decode(alarmCode)` | sort 0 _time sectionCode desc | table _time, sectionCode, alarmCode, description

and the macro gen_alarm_decode decodes the bitwise values into human readable terms. (this works fine)

eval description=if(floor($bitVar$)%2>0,"High RPM, ","") | eval description=description + if(floor($bitVar$/2)%2>0,"High Temp, ","") | eval description=description + if(floor($bitVar$/4)%2>0,"Low Temp, ","")

Now this search returns me a list of all entries where the alarmCode changed and what the remaining alarmCodes that were still set were.

So results would look like this

Sun 10:35pm, generator1, 0, 
Sun 10pm, generator1, 2, High Temp
Sun 8pm, generator1, 3, High RPM, High Temp
etc.....

All well and good and I was pleased with my effort on that search. But I have been asked to change this to a more SCADA style output. Where the client sees a column for each alarm and its status.

If the alarm is on it will list the time it turned on... when it goes off it will list the start and end times

So it should be something like this (put in a pseudo csv/table format for display here)

Time, sectionCode, alarmCode, high_rpm, high_temp, low_temp
Sun 10:35pm, generator1, 0, - , 8pm-10:35pm, -  
Sun 10pm, generator1, 2, 8pm-10pm, 8pm-?, -  
Sun 8pm, generator1, 3, 8pm-?, 8pm-?, -  
etc.....

Note ? above are for where the alarm is still on. The - are for when alarm is off.

If it helps matters I have also decoded the bitwise field on our custom modbus poller. So really events look like this
time_of_event, sectionCode=generator1, alarmCode, rpm, output_kw, bitAlarm0, bitAlarm1, bitAlarm2

The only reason I didn't mention it until now was because I didn't need those bitfields yet.

Anyone have any thoughts. It has me slightly stumped. If I find a solution will post here regardless.

[martin_mueller · Accepted solution]

This should do it:

| gentimes start=-1 increment=5m | eval code1 = if(starttime % 1800 > 600, 0, 1) | fields starthuman code1 | delta code1 as delta1 | eval output1 = case(delta1 == 1, starthuman." - ?", code1 == 0 AND delta1 == 0, "-") | filldown output1 | eval output1 = if(delta1 == -1, replace(output1, "\?", starthuman), output1) | reverse

Consider placing the bit from delta to the last eval (plus the fields - delta1 I omitted) into a macro with a variable for the field name/number to avoid your query growing hugely.

[phoenixdigital]

That looks perfect thankyou. You sir are a genius!

Good to see the 'gentimes' command too. Great way to similate results.

Thanks again.

Due to the frequency/volume of data coming from the engines I have discovered this search needs to actually run on a summary search which will only keep track of changes in the alarmCode which will be a much smaller data set.

[phoenixdigital]

Thats exactly it Martin that it what I am trying to achieve. It was probably a long winded explanation but I wanted to give as much information as possible in order to assist with a solution.

Jonuwz that is how this SCADA solution I am replicating in Splunk behaves. You will probably find a google image search doesnt really give you too much detail on how SCADA works. SCADA systems really shine when you use historical data to perform predictive failure analysis.

[jonuwz]

You should probably re-word your problem - This has nothing to do with scada or bitwise operations - an google image search of SCADA doesn't show anything other than "whats happenening right now" - there's no historical data at all.

[martin_mueller]

To sum up, you're trying to turn this:

time code1 code2 code3
  10     0     0     0
   9     1     1     0
   8     1     0     1
   7     0     0     1
   6     0     0     0

Into this:

time code1 code2 code3
  10  8-10  9-10     -
   9   8-?   9-?   6-9
   8   8-?     -   6-?
   7     -     -   6-?
   6     -     -     -

?

[phoenixdigital]

Whoops didn't see this until I responded 🙂

[jonuwz]

Ignore the answer below - I only read the 1st part of the question and got excited by binary conversions...

[jonuwz]

Nice question.

It boils down to converting a decimal into a bitfield yes ?

put this in etc//bin/dec2bin.py

import re,sys,time, splunk.Intersplunk

def dogetbin(results, settings):

  try:
    fields, argvals = splunk.Intersplunk.getKeywordsAndOptions()

    for r in results:
      for f in fields:
        if f in r:

          try:
            r[f]=int(r[f])
            if r[f] >=0:
              r[f]=str(bin(r[f]))[2:]
            else:
              r[f]="-" + str(bin(r[f]))[3:]

          except:
            r[f]=""

          splunk.Intersplunk.outputResults(results)

  except:
    import traceback
    stack =  traceback.format_exc()
    results = splunk.Intersplunk.generateErrorResults("Error : Traceback: " + str(stack))

results, dummyresults, settings = splunk.Intersplunk.getOrganizedResults()
results = dogetbin(results, settings)

Then put this in etc//local/commands.conf

[dec2bin]
retainsevents = true
streaming = true
filename = dec2bin.py

Now you can do

... | dec2bin bitVar | ...

and it'll turn it into something like "1001"

Why is this useful ?

well, you can define a multivalue field like this :

... | eval measures="high_rpm,low_rpm,low_temp" | makemv delim="," measures

and then split your bitfield like this :

... | bitVar=split(bitVar,"")

If you do this you can see where this is heading ....

... | table bitVar measures

Example

* | head 1 
  | eval bitVar="9" 
  | dec2bin bitVar 
  | eval bitVar=split(bitVar,"")
  | eval measure="temp_high,tem_low,ps_borked,no_cement"
  | makemv delim="," measure
  | table bitVar measure

[phoenixdigital]

Looking at your solution I like the use of 'makemv'. Never seen or used that before and its a good example of how it works.

Thanks I will use makemv in the future for something I am sure.

[phoenixdigital]

You do bring up an interesting option though. I have thought about using a python script to perform the start-stop time detection with my existing search.

I was just hoping to do it all in Splunk.

It should be pretty straight forward with a python script so I may end up going down that route.

There are also options for us to put that sort of detection in our data collection scripts however that then makes them stateful which we would prefer to avoid.

[phoenixdigital]

Thanks for the response and the suggestion. But I have the decimal to bit sorted with that macro.

eval bit0=if(floor($bitVar$)%2>0,"High RPM, ","")
eval bit1=if(floor($bitVar$/2)%2>0,"High Temp, ","")
etc....

The bit decoding I can get sorted fine plus have access to them as their own variable if needed.

The part I cant work out is the last part which will show the start and end times row as bits are turned on and off over time. With a column for each bit.

[phoenixdigital]

It should be noted there are actually 32 bitwise alarmCodes so I am trying to keep the search as simple as possible 🙂