On Friday, December 2, 2016 at 5:14:07 PM UTC-6, [email protected] wrote: > I'm struggling to get a count table with tags. The closest I've been able to > get to it is using continuous queries. If you read this group you will see > many questions that I've posted about my many attempts on this and the group > has been awesome in helping me. I've tried several ways but open bugs in > influx have stopped me. I've tried with a kapacitor script and join, the > problem here is that the is a bug/feature that returns null and not zero for > counts. This makes missing records. I tried doing a downsampling of a daily > and hourly. This also has the problem with the bug/feature with the null. > > What I have now is a continuous query that downsamples to counts. > > CREATE CONTINUOUS QUERY cqDailyTasks ON r1metrics RESAMPLE EVERY 15m BEGIN > SELECT count(jobRunTime) as theCount INTO metrics.yearly.dailyTasks FROM > metrics..tasks GROUP BY time(1d),* END > > then I have these 2 queries that write to the same measurement > > (these will be turned into continuous queries) > > SELECT sum(theCount) as success into rates FROM > "r1metrics"."yearly"."dailyTasks" where time > '2016-11-16T00:00:00Z' and > ("status"='FINISHED' ) group by time(1d),id, serverID, region, taskType > > SELECT sum(theCount) as error into rates FROM > "r1metrics"."yearly"."dailyTasks" where time > '2016-11-16T00:00:00Z' and > ("status"='ERROR' ) group by time(1d),id, serverID, region, taskType > > > Which yields something like this > error,success, id, serverID, region, taskType > 5 ,null,1,1,us,t1 > null,5,1,1,us,t1 > 1, 6,1,1,us,t1 > > This query only works for the last row > select succces/(error+success) from rates where region = 'us' group by time > (1d) > > how can i fill in the nulls with 0? or is there a function like > null(fieldname, value) like in other sql products then I could do > > select null(success,0)/(null(error,0) + null(success,0) from rates where > region='us' group by time (1d) > > Can this be done with kapacitor? How? > > The only thing I can think of is to run an external job that dumps the data > out periodically, using the influx command, replacing the null with 0, then > doing another select into to update the fields. > > Can I do the above in kapacitor? How? > > Thanks
I'm sure count rates are a normal use case for metrics and someone else would benefit from this. I did figure out a workaround for a daily measurement. The trick is to also calculate the total in a continuous query So the workaround is to create 3 continuous queries that write to the same measurement. This utilize the fact that points with same tag will overwrite values in new measurement continuous query that counts pass for the day continuous query that counts failed for the day continuous query that counts total (pass+fail) for the day now when you run your queries select sum(pass)/sum(total) from rates if the pass value is null you get a missing value that can be filled with 0 -- Remember to include the version number! --- You received this message because you are subscribed to the Google Groups "InfluxData" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/influxdb. To view this discussion on the web visit https://groups.google.com/d/msgid/influxdb/c0365a5c-684a-4cf9-afdb-e03af8832283%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
