topic How to sum timechart results from multiple search queries? in Dashboards & Visualizations

How to sum timechart results from multiple search queries?

dzyfer — Fri, 10 Jun 2022 05:26:13 GMT

Hi, I have multiple timecharts which have similar search queries, sharing the same index, the only difference is that they are from different metric names, ie

| mstats max(my_Var) AS my_Var where index=* AND "internal_name"="A1" ... | timechart span=1w sum(Var) AS output

| mstats max(my_Var) AS my_Var where index=* AND "internal_name"="A2" ... | timechart span=1w sum(Var) AS output

.. and so on.

I would like to have a panel where the various "output" are summed into a combined timechart.
I have seen some similar solutions involving tokens, but I am unfamiliar with how they work, so I hope that someone can walk me through what to do, or any other solutions will be great too. Thanks

Re: How to sum timechart results from multiple search queries?

gcusello — Fri, 10 Jun 2022 06:17:04 GMT

Hi @dzyfer,

did you tried something like this?

| mstats max(my_Var) AS my_Var where index=* AND (internal_name IN ("A1","A2") | timechart span=1w sum(Var) AS output

Ciao.

Giuseppe

Re: How to sum timechart results from multiple search queries?

dzyfer — Fri, 10 Jun 2022 06:58:31 GMT

Hi @gcusello ,

thanks for the prompt reply, however, using the IN function in the first line leads to the search summing up the results at the beginning, but my search actually consists of intermediate calculations before arriving at the "Var" variable, and this method causes the final summation to be inaccurate. As such, I'm wondering if there is a way to sum up the final results only.

Thanks!

Re: How to sum timechart results from multiple search queries?

gcusello — Fri, 10 Jun 2022 07:26:22 GMT

Hi @dzyfer,

I don't think that the inaccurate summarization is fo the global data because I didn't see any where condition in your searches, anyway, you can make the time chart summarization in two steps:

| mstats max(my_Var) AS my_Var where index=* AND (internal_name IN ("A1","A2") | bin span=1w _time | stats sum(eval(if(internal_name="A1",Var,0) AS output_A1 sum(eval(if(internal_name="A2",Var,0) AS output_A2 BY _time | eval output=output_A1+output_A2

Ciao.

Giuseppe

Re: How to sum timechart results from multiple search queries?

dzyfer — Fri, 10 Jun 2022 07:52:04 GMT

Hi @gcusello ,

Apologies, in an attempt to anonymize my search earlier I have made some errors, which have made it seem confusing, so this is the corrected code with other variable so that maybe it would be easier to understand my dilemma.

| mstats span=1m earliest(monitor) AS first latest(monitor) AS last max(monitor) AS max WHERE index=organizationmetric AND internal_name="SR01" | autoregress last AS lastlast | eval diff = if(first = lastlast, 0,if(first-lastlast<0,0,first-lastlast)) | eval divmaxlast = round(max-last,0) | eval meter = round(if(max=last,last-first,if(last<max,if(divmaxlast<2,last-first,max-first+last),max-first+last))) + diff | eval date=strftime(_time,"%Y-%m-%V") | timechart span=1w sum(meter) AS output

the other metric names are "SR02" "SR03" "SR04"

Thanks and sorry again for the confusion

Re: How to sum timechart results from multiple search queries?

gcusello — Fri, 10 Jun 2022 08:07:47 GMT

Hi @dzyfer,

Ok, in this case the easiest approach is schedule all your original searches (frequency depends on how many data you receive) and saving results in a summary index, then you can use this Summary index for your global timecharting.

Ciao.

Giuseppe