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 -
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)
strptime function returns an integer so it makes no sense to pass the result to
substr. See what happens when you remove the
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"