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
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)
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
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.
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
So, since you're not using map in this query, what happens when you remove the substr
calls?