topic Re: combine chart in Splunk Search

How to combine chart?

Kaiyue — Wed, 29 Mar 2023 16:05:51 GMT

I am trying to combine the results from 2 different search queries into a single chart.Is there a way to do this?

FIRST search:

source="a.csv" OR source="b.csv" OR source="c.csv" Company="x" | eval Created=substr(Created, 1, 7) | eval a=if(State="Closed",1,0) | chart sum(a) AS closed_event by Created

SECOND search:

source="a.csv" OR source="b.csv" OR source="c.csv" Company="x" | eval Created=substr(Created, 1, 7) | chart count by Created,source

I want the first search as a line chart and the second search as a column chart，combining them.

Thanks in advance

Re: combine chart

yuanliu — Wed, 29 Mar 2023 09:06:09 GMT

If the two searches have different groupby lists, this is impossible. Just try to draw a mockup and illustrate how the output will look like.

Re: combine chart

Kaiyue — Wed, 29 Mar 2023 09:24:25 GMT

Thank you very much for your answer, if there is a way to implement it in the dashboard

Re: combine chart

srauhala_splunk — Wed, 29 Mar 2023 09:25:15 GMT

Maybe something like:

source="a.csv" OR source="b.csv" OR source="c.csv" Company="x" ```Expection State is either "Created OR Closed" ``` | eval state_source = State.":".source | chart count by state_source

/Seb

Re: combine chart

Kaiyue — Wed, 29 Mar 2023 09:35:01 GMT

Sorry it didn't work, thanks for your answer

Re: combine chart

Kaiyue — Thu, 30 Mar 2023 01:20:30 GMT

If I change the second search like this，is it possible to achieve?

source="a.csv" OR source="b.csv" OR source="c.csv" Company="x"

| eval Created=substr(Created, 1, 7)

| count(eval(source="a.csv")) AS A count(eval(source="b.csv")) AS B count(eval(source="c.csv")) AS C by Created

Re: combine chart

srauhala_splunk — Thu, 30 Mar 2023 05:55:11 GMT

Hi!

Most things are possible. Let's try to figure what we are trying to achieve.

"| eval Created=substr(Created, 1, 7)"

Is this generating a state i.e. "created" or is this a user_id or similar with multiple combinations of values?

"| eval a=if(State="Closed",1,0)"

Do you want to count the number of occurrences something was created and closed?

maybe

source="a.csv" OR source="b.csv" OR source="c.csv" Company="x" | eval created_by=substr(Created, 1, 7) | eval is_closed=if(State="Closed",1,0) | eval user_source = created_by.":".source | chart sum(is_closed), count by user_source

/Seb

Re: combine chart

yuanliu — Thu, 30 Mar 2023 06:05:33 GMT

If you have the same groupby, yes. The name of the game is overlay. In fact, I gave an example in my .conf22 talk.

To help visualize, this is the effect you wanted:

Line chart and column chart

To get this, your search would look like

source="a.csv" OR source="b.csv" OR source="c.csv" Company="x" | eval Created=substr(Created, 1, 7) | eval a=if(State="Closed",1,0) | chart sum(a) AS closed_event count(eval(source="a.csv")) AS A count(eval(source="b.csv")) AS B count(eval(source="c.csv")) AS C by Created

Then, open Visualization, select column chart as your base type. Then, click Format -> Chart Overlay. Select "closed_event" into Overlay. if the numbers between closed_event and A, B, C is large, the chart will benefit from "View as Axis", which create a separately scaled Y-axis on the right side as illustrated above.

Hope this helps.