| inputlookup kv_adani | where (tag="CHP.Device1.C1 BELT VW" ) | eval _time=tagtime |dedup _time| stats max(_time) as maxtime, min(_time) as mintime,avg(value) as avgtph,count(value) as count by tag |eval td=(maxtime-mintime)/3600,tv=td*avgtph|stats sum(tv) as total_valueC1| eval total_valueC1= round(total_valueC1,0)
| eval total_valueC2= [search | inputlookup kv_adani | eval Tag=tag,_time=tagtime, Value=value |(Tag="CHP.Device1.C2 BELT VW" ) | dedup _time| stats max(_time) as maxtime, min(_time) as mintime,avg(Value) as avgtph,count(Value) as count by Tag |eval td=(maxtime-mintime)/3600,tv=td*avgtph|stats sum(tv) as total_value | eval total_valueC2= round(total_valueC2,0)| eval search = total_value] | eval total_value=total_valueC1+total_valueC2
Note sure exactly what your question is, but here are some errors in your code -
Section 1 -
| inputlookup kv_adani
| where (tag="CHP.Device1.C1 BELT VW" )
| eval _time=tagtime
| dedup _time
| stats max(_time) as maxtime, min(_time) as mintime,avg(value) as avgtph,count(value) as count by tag
There is no reason to assign tagtime
to _time
if you are then going to dedup
it and lose it in a stats
command. Wasted cycles. Also, be aware that the first record in the lookup
file with any given tagtime
will be the one retained. There is only one value of tag
, so by tag
is redundant.
| inputlookup kv_adani
| where (tag="CHP.Device1.C1 BELT VW" )
| dedup tagtime
| stats max(tagtime) as maxtime, min(tagtime) as mintime, avg(value) as avgtph
Since count
from the above stats command was not used in the next section, it also has been removed.
Section 2 -
| eval td=(maxtime-mintime)/3600
| eval tv=td*avgtph
| stats sum(tv) as total_valueC1
| eval total_valueC1= round(total_valueC1,0)
Since there is only one tag, there is only one record, so the stats command has only the affect of eliminating all the other fields.
| eval total_valueC1= round(avgtph*(maxtime-mintime)/3600,0)
| table total_valueC1
Section 3 -
| eval total_valueC2=
[ search
| inputlookup kv_adani
| eval Tag=tag,_time=tagtime, Value=value
| (Tag="CHP.Device1.C2 BELT VW" )
| dedup _time
| stats max(_time) as maxtime, min(_time) as mintime, avg(Value) as avgtph, count(Value) as count by Tag
| eval td=(maxtime-mintime)/3600,tv=td*avgtph
| stats sum(tv) as total_value
| eval total_valueC2= round(total_valueC2,0)
| eval search = total_value
]
In this section, search
is unnecessary since inputlookup
is a generating command. The verb where
is missing after the third pipe in the subsearch. The renames are completely redundant, since only the total_value will be passed out. Again, since there is only one tag
, the by tag
is unneeded. Your final eval search=
passed out a value that didn't exist.
| eval total_valueC2=
[ | inputlookup kv_adani
| where (tag = "CHP.Device1.C2 BELT VW" )
| dedup tagtime
| stats max(tagtime) as maxtime, min(tagtime) as mintime, avg(value) as avgtph
| eval total_valueC2= round(avgtph*(maxtime-mintime)/3600,0)
| eval search = total_valueC2
]
Section 4 is just fine
| eval total_value=total_valueC1+total_valueC2
Now, having gone through all that, it looks like you are just doing the exact same calculation on two different tags from the same file. Also, since you don't care what the actual min()
or max()
tagtime
was, you can just use range()
to calculate the difference. The whole query therefore simplifies to this:
| inputlookup kv_adani
| where (tag="CHP.Device1.C1 BELT VW" ) OR (Tag="CHP.Device1.C2 BELT VW" )
| dedup tagtime tag
| stats range(tagtime) as duration, avg(value) as avgtph by tag
| eval total = round(avgtph*duration/3600,0)
| stats sum(total) as total_value
Assuming your above search is not working. The issue is the eval command in the subsearch | eval total_valueC2= round(total_valueC2,0)
is not using the field total_value which is the only field available after stats. Give this a try
| inputlookup kv_adani | where (tag="CHP.Device1.C1 BELT VW" ) | eval _time=tagtime |dedup _time| stats max(_time) as maxtime, min(_time) as mintime,avg(value) as avgtph,count(value) as count by tag |eval td=(maxtime-mintime)/3600,tv=td*avgtph|stats sum(tv) as total_valueC1| eval total_valueC1= round(total_valueC1,0)
| eval total_valueC2= [| inputlookup kv_adani | eval Tag=tag,_time=tagtime, Value=value | search (Tag="CHP.Device1.C2 BELT VW" ) | dedup _time| stats max(_time) as maxtime, min(_time) as mintime,avg(Value) as avgtph,count(Value) as count by Tag |eval td=(maxtime-mintime)/3600,tv=td*avgtph|stats sum(tv) as search | eval search= round(search,0)] | eval total_value=total_valueC1+total_valueC2
What is your question? The title of your posting mentions outputlookup, but the query in the body does not. We can help you better if you provide a little more detail about what you are trying to do.