TickScript - Not quite getting the result I want

influxdb
kapacitor

#1

I’m trying to emulate a graph I have in Grafana with TICKscript.

This is my query in Grafana:

SELECT derivative(mean("ifHCInOctets"), 1s) *8 FROM "53_weeks"."asa_firewalls_ifxtable" WHERE ("agent_host" = '10.10.10.1' AND "asa_firewalls_ifAlias" = 'outside') AND $timeFilter GROUP BY time($interval) fill(null)

I’ve tried with a batch and stream node but I still can’t seem to get the same figure for an average(mean) of ifHCInOctets.

I use SNMP input with Telegraf and InfluxDB as my storage.

Batch script;

batch
    |query('SELECT mean("ifHCInOctets") *8 FROM "telegraf"."53_weeks"."asa_firewalls_ifxtable" WHERE ("agent_host" = "10.10.10.1" AND "asa_firewalls_ifAlias" = "outside")')
        .groupBy(5m)
        .every(10s)
        .period(5m)
    |log()

This runs in the kapactior editor but doesn’t ‘complete’ and never returns any data as far as I can see…

Stream script:

var freq = 10s

var window_size = 2h

var data = stream
    |from()
        .database('telegraf')
        .measurement('asa_firewalls_ifxtable')
        .retentionPolicy('53_weeks')
    |default()
        .tag('asa_firewalls_ifAlias', 'NotNamed')
    |where(lambda: "asa_firewalls_ifAlias" == 'outside' AND "agent_host" == '10.10.10.1')
    |window()
        .period(window_size)
        .every(freq)
    |derivative('ifHCOutOctets')
        .unit(1s)
        .as('deriative')
    |log()

var humanData = data
    |eval(lambda: humanBytes(int("mean") * 8), lambda: int("mean") * 8)
        .as('humanMean', 'avgMean')

data
    |join(humanData)
        .as('mean', 'human')
    |alert()
        .message('Outside.out is currently averaging {{ index .Fields "human.humanMean" }}/s over the last 2 hours')
        .info(lambda: "human.avgMean" > 9000000000000000)
        .slack()
        .iconEmoji(':exclamation:')
        .workspace('slack2')
        .channel('#alerts')

Can anyone offer any tips or advice please?


#2

Can anyone give me any ideas? Thanks!


#3

Hi meaton ,
What are the values of the variabels $timeFilter And $interval in your grafana query ?
What is the output of kapacitor list tasks ?


#4

Hi Marc,

Example query from Grafana:

SELECT derivative(mean("ifHCInOctets"), 1s) *8 FROM "53_weeks"."asa_firewalls_ifxtable" WHERE ("agent_host" = '10.10.10.1' AND "asa_firewalls_ifAlias" = 'outside') AND time >= now() - 1h GROUP BY time(5s) fill(null);

Kapacitor tasks:

ID                                                 Type      Status    Executing Databases and Retention Policies
ASAOutsideOut                                      stream    disabled  false     ["telegraf"."53_weeks"]
ASAOutsideOut.Batch                                batch     disabled  false     ["telegraf"."53_weeks"]
chronograf-v1-a621880d-8e19-4e25-b9ce-9852cdc89f3b stream    disabled  false     ["telegraf"."53_weeks"]
chronograf-v1-c5dac4be-5bab-46b6-91b4-7e008b6e951b batch     disabled  false     ["telegraf"."53_weeks"]

Thanks,

Michael


#5

Hi Michael,
All tasks in Kapacitor seem to have have a status disabled …that can explain why it never returns any data.


#6

Yes, of course, it’s currently disabled as it’s not giving me what I expect. Running it as a batch job it never seems to complete…


#7

Sorry about that ,
I think adding “and time > now()-1h”
to the query in the batch script may help …


#8

Thanks Marc.

I’ve attempted the following:

var data = batch
    |query('SELECT mean("ifHCOutOctets") as "ifHCOutOctets" FROM "telegraf"."53_weeks"."asa_firewalls_ifxtable" WHERE "agent_host" = "10.10.10.1" AND "asa_firewalls_ifAlias" = "outside" AND time > now() - 1h ')
        .groupBy(*)
        .every(10s)
        .period(5m)
    |log()
    	.level('DEBUG')
data
    |alert()
        .message('Outside.out is currently averaging {{ index .Fields "ifHCOutOctets" }}/s over the last 2 hours')
        .info(lambda: "ifHCOutOctets" > 9000000000000000)
        .slack()
        .iconEmoji(':exclamation:')
        .workspace('slack2')
        .channel('#alerts')

All I see in chronograf Editor+Logs view is 'Starting next batch query" every 10 seconds (my .every) however, I’m seeing no alerts coming through to Slack nor do I see anything from the |log() node - nor anything in kapactior.log file on disk…

Any ideas?


#9

Hi Marc

For completeness, I’ve got this to work and here is what I have:

var data = batch
    |query('''SELECT "ifHCOutOctets" *8 as "ifHCOutOctets" FROM "telegraf"."53_weeks"."asa_firewalls_ifxtable" WHERE ("agent_host" = '10.10.10.1' AND "asa_firewalls_ifAlias" = 'outside') AND time >= now() - 2h fill(null)''')
      .every(15m)
      .period(2h)
    |derivative('ifHCOutOctets')
    	.unit(1s)
	|mean('ifHCOutOctets')
    	.as('mean_ifHCOutOctets')
	|log()
data
	|eval(lambda: humanBytes("mean_ifHCOutOctets"))
    	.as('humanBytes')
        .keep('mean_ifHCOutOctets', 'humanBytes')
    |alert()
        .message('Outside.Out is currently averaging {{ index .Fields "humanBytes"}}/s over the last 2 hours')
        .info(lambda: "mean_ifHCOutOctets" > 9000000)
        .warn(lambda: "mean_ifHCOutOctets" > 11000000)
        .crit(lambda: "mean_ifHCOutOctets" > 15000000)
        .slack()
        .iconEmoji(':exclamation:')
        .workspace('slack2')
        .channel('#alerts')