Chart multiple columns based on time and additional grouping

[tccooper]

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.

[tccooper]

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

[javiergn]

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.

[sundareshr]

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

[tccooper]

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.

[sundareshr]

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