How do I add an additional calculation to a chart?

[andrewtrobec]

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

[sundareshr · Accepted solution]

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>.*)" 

[lnn2204]

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

[andrewtrobec]

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!

[sundareshr]

Try the updated query

[andrewtrobec]

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

[bshuler_splunk]

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

[andrewtrobec]

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