topic Re: How do I add an additional calculation to a chart? in Splunk Search

How do I add an additional calculation to a chart?

andrewtrobec — Mon, 21 Nov 2016 22:53:37 GMT

Hello,

I have two separate chart calculations that I would like to combine into a single chart. The first is an avg calculation on a field grouped by two fields while the second is a distinct_count calculation on another field grouped by one field. So assuming a have four different fields, the two separate chart commands are:

chart avg(FIELD1) by FIELD2, FIELD3
chart distinct_count(FIELD4) by FIELD2

I am trying to combine them so that the distinct_count ends up as the final column of the generated table. I have tried using:

chart avg(FIELD1) distinct_count(FIELD4) by FIELD2, FIELD3

but all that accomplishes is splitting the distinct_count over FIELD3 as well, which is what I don't want.

Is there a way of combining the two so that the distinct_count appears as a column at the end being grouped by FIELD2?

Thank you!

Andrew

Re: How do I add an additional calculation to a chart?

bshuler_splunk — Mon, 21 Nov 2016 23:01:08 GMT

somesearch | chart avg(FIELD1) by FIELD2, FIELD3 | append [search somesearch | chart distinct_count(FIELD4) by FIELD2]

Re: How do I add an additional calculation to a chart?

sundareshr — Tue, 22 Nov 2016 00:12:45 GMT

Try this

*UPDATED*

.... | eventstats dc(field4) as dc_f4 by field2 | eval field2= field2."#".dc_f4 | chart avg(field1) as average over field2 by field3 | rex field=field2 "(?<field2>[^#]+)#(?<distinct_count>.*)"

Re: How do I add an additional calculation to a chart?

andrewtrobec — Tue, 22 Nov 2016 06:18:10 GMT

Thanks bshuler. The append command adds a new column to the chart, but the values are appended at the bottom as an entire new table. So basically the first half of the table is

chart avg(FIELD1) by FIELD2, FIELD3

with the distinct_count column blank, while the second half of the table is

chart distinct_count(FIELD4) by FIELD2

with all the avg columns blank.

Any ideas?

Thanks!

Andrew

Re: How do I add an additional calculation to a chart?

andrewtrobec — Tue, 22 Nov 2016 06:25:52 GMT

Hello sunhareshr. This works, but adds a new column for each value of FIELD3 instead of just adding one. There is no difference between

.... | eventstats dc(field4) as dc_f4 by field2 | chart avg(field1) as average values(dc_f4) as dist_count over field2 by field3

and

.... | eventstats dc(field4) as dc_f4 by field2 | chart avg(field1) as average values(dc_f4) as dist_count by field2, field3

any ideas?

Thanks!

Re: How do I add an additional calculation to a chart?

sundareshr — Tue, 22 Nov 2016 08:53:29 GMT

Try the updated query

Re: How do I add an additional calculation to a chart?

andrewtrobec — Tue, 22 Nov 2016 11:26:37 GMT

Thanks, this works! I will do some reverse engineering to figure out the logic behind it. I appreciate your help!

Re: How do I add an additional calculation to a chart?

lnn2204 — Sat, 16 Oct 2021 02:54:19 GMT

Hi Sundareshr, i got this problem, i want to add value 1 to 2 and remove the Shift0, do you have any solution? Thanks