Get sum of counts for measurement grouped by tags and time


Hello Community,

I’m new to TICK stack and have some questions regarding TICKscripts.

I have written one script to detect anomaly in the number of requests received at server. The anomaly detection works fine. The script is below.

        | query('''
           SELECT count(timetaken) as count
           FROM "telegraf".two_months.responsetimes
           WHERE "responsecode" != '10018' AND "responsecode" != '10097' AND "responsecode" != '10181' AND "responsecode" != '10256' AND "responsecode" != '10285' AND "merchant" != 'Sevasys'
     .groupBy(time(60s),  'qcinstance', 'txntype', 'merchant')
     .cron('*/1 * * * *')
     // Configure a single Sigma fingerprinter
  // Morgoth returns any anomalous windows
  | eval(lambda: strReplace("txntype", ' ','%20', -1), lambda: strReplace("merchant", ' ', '%20', -1), lambda: int(unixNano(now())/1000000), lambda: int((unixNano(now())-two_hours)/1000000))
        .as('txntype2', 'merchant2', 'now', 'two_hours_ago')
        .keep('anomalous', 'txntype2', 'txntype', 'merchant2', 'merchant', 'now', 'two_hours_ago')
     .crit(lambda: "anomalous")

Here I’m grouping the requests received in the last 15 minutes by 1 minutes and some other tags.

In the alert message I would like give the total number of requests received per tags(merchant, txntype, qcinstance).

I thought of writing another batch query to get sum and join it with the above query. It is not working because of the timestamp mismatch.

Is there any other way to find the no of requests ?