Splunk Search

correlation of events (different sourcetypes) with "from" and "to"

swe
Path Finder

Hi there,

i have planning events which i need to correlate with "actual" values from sensoring.
The planning data says something like "ID" "planned status" "status valid from" "status valid to".
The sensors say something like "ID" "sensing date" "sensing value".

As both sources change frequently i habe both indexed (no lookup).

I want a Table View which gives for each planning status and validity timerange (valid from and valid to) the sum of the sensed value: ID "planned status" "status valid from" "status valid to" "sensing value sum"

i am actually trying with streamstats but dont get the results i want. So i realy hope someone can help me out here:

(index="planning" sourcetype="planning" ID=03 ) 
OR (index="sensing" sourcetype="sensing" ID=03)


| eval sensorvalue = 'my.sensorvalue' /16384*256

| eval starttime = substr(strptime(planning_from, "%Y-%m-%d %H:%M:%S.%1Q"),1,10)

| eval endtime = substr(strptime(planning_to, "%Y-%m-%d %H:%M:%S.%1Q"),1,10) 


| sort _time 


| streamstats window=1 current=t reset_after="("_time>s_endtime")"  count as scount sum(sensorvalue) as sum_sensorvalue first(endtime) as s_endtime first(starttime) as s_starttime
| stats sum(sensorvalue) by ID sourcetype s_endtime s_starttime 
Tags (1)
0 Karma

DalJeanis
SplunkTrust
SplunkTrust

1) Looks like that substr that @richgalloway pointed out may be left over from a strftime that was pulling just the dates, and the coder didn't know they could just change the format string from "%Y-%m-%d %H:%M:%S.%1Q" to "%Y-%m-%d" to achieve it.

On the other hand, it's possible you are just looking for this -

strptime(substr(planning_from,1,10), "%Y-%m-%d")

Please verify the format of the date/time string that is coming in, and step by step build up the method to turn that into a valid epoch time format.

2) This is problematic code -

= 'my.sensorvalue' /16384*256

You should not depend on order of operations for division and multiplication, which have equal priority so will be evaluated either left to right or right to left depending on language. It will probably be evaluated as equivalent to this ...

= ('my.sensorvalue'  /16384) * 256

... when you probably meant this ...

= 'my.sensorvalue' /(16384*256)
0 Karma

swe
Path Finder

Hi DalJeanis
thanks for your answer!
my planning time looks like: "2017-03-21 19:00:12.0"

i changed the problematic code to 'my.sensorvalue' /(16384*256)

thanks
swe

0 Karma

richgalloway
SplunkTrust
SplunkTrust

The strptime function returns an integer so it makes no sense to pass the result to substr. See what happens when you remove the substr calls.

---
If this reply helps you, an upvote would be appreciated.
0 Karma

swe
Path Finder

Hello richgalloway,

thanks for your answer!

the substr part was left from experimenting with the MAP command and filtering there with the timelimits (eg. earliest=$starttime$). Map did not like my date without the substr.
with substr i get something like "1490119212" without it i get "1490119212.000000"

thanks
swe

0 Karma

richgalloway
SplunkTrust
SplunkTrust

So, since you're not using map in this query, what happens when you remove the substr calls?

---
If this reply helps you, an upvote would be appreciated.
0 Karma