We are trying to chart multiple results with some success. I am able to have everything sorted based off the Device
correctly. My issue is this is going in a summary index
and I need to include the timestamp
so we can have accurate results of when everything broke. Here is the existing query:
index="XXXXXXXXXX" Device="*AAAAA*" Point_Name="BBBBBB" OR Point_Name="CCCCC" OR Point_Name="DDDDD"|chart limit=0 last(Value) over Device by Point_Name|eval difference = if(isnull(CCCCC),(AAAAA-DDDDD),(AAAAA-CCCCC))|where difference > 2
This gives me once record per Device
where the difference
is greater then 2
. However, I know there are multiple times throughout the day where this condition is broken. I need to sort out each record so we can count the number of consecutive 15 minute windows that this query returns results based on Device
.
Thanks in advance for help.
Here is the query that was the "money maker":
index="XXXXX" Device="*YYYYY*" Point_Name="aaaaa"
|bin span=15m _time
|stats last(Value) as AAAAA by Device, _time
|appendcols [|search index="XXXXX" Device="*YYYYY*" Point_Name="bbbbb" |bin span=15m _time |stats last(Value) as BBBBB by Device, _time ]
|appendcols [|search index="XXXXX" Device="*YYYYY*" Point_Name="ccccc" |bin span=15m _time |stats last(Value) as CCCCC by Device, _time ]
|eval DDDDD = coalesce(BBBBB, CCCCC)
|eval difference = (AAAAA - DDDDD)
|where difference > 2
|fields _time, Device, difference
NOT TESTED
Have you tried with bucket and stats instead?
index="XXXXXXXXXX" Device="AAAAA" Point_Name="BBBBBB" OR Point_Name="CCCCC" OR Point_Name="DDDDD"
| bucket _time span=15m
| stats last(Value) by Device, Point_Name, _time
| eval difference = if(isnull(CCCCC),(AAAAA-DDDDD),(AAAAA-CCCCC))
| where difference > 2
And then maybe if you need the format back in the same way as chart use xyseries for instance.
Not quite sure I understand what you're trying to achieve, but see if this helps
index="XXXXXXXXXX" Device="AAAAA" Point_Name="BBBBBB" OR Point_Name="CCCCC" OR Point_Name="DDDDD"|chart limit=0 values(Value) as Value over Device by Point_Name|mvexpand Value |eval diff=AAAAA-coalesce(CCCC, DDDDD) | where diff>2
This is an example of what the original query returns:
Device Point_Name-D Point_Name-B Point_Name-C difference
Dev_1 57.53 55 2.53
Dev_2 57.25 55 2.25
Dev_3 58.01 55 3.01
Dev_4 77.71 58 19.71
Dev_5 64.12 58 6.12
The format here is ideal for what we are trying to achieve, but we also need the "_time" field appended as well. I have tried replicating this in a timechart query with a span=15m variable, but that was not printing out what we needed. Since not all these records are coming in at the same moment, I thought the "timechart span=15m" would give us a nicely rounded _time field to work work for writing to the summary index.
Try bin
& stats
. Something like this
index="XXXXXXXXXX" Device="AAAAA" Point_Name="BBBBBB" OR Point_Name="CCCCC" OR Point_Name="DDDDD"| bin span=15m _time | stats latest(Value) as Value by DeviceId Point |eval diff=AAAAA-coalesce(CCCC, DDDDD) | eval td=_time."#".DeviceId | xyseries td Point Value | rex field=td "(?<time>[^#]+)#(?<Device>.*)" | fields - td