topic Re: Chart multiple columns based on time and additional grouping in Splunk Search

Chart multiple columns based on time and additional grouping

tccooper — Tue, 09 Aug 2016 12:56:47 GMT

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.

Re: Chart multiple columns based on time and additional grouping

sundareshr — Tue, 09 Aug 2016 13:28:41 GMT

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

Re: Chart multiple columns based on time and additional grouping

tccooper — Tue, 29 Sep 2020 10:32:56 GMT

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.

Re: Chart multiple columns based on time and additional grouping

sundareshr — Tue, 09 Aug 2016 14:47:36 GMT

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

Re: Chart multiple columns based on time and additional grouping

javiergn — Tue, 09 Aug 2016 14:51:47 GMT

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.

Re: Chart multiple columns based on time and additional grouping

tccooper — Tue, 09 Aug 2016 18:12:12 GMT

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