How to combine chart?

[Kaiyue]

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

[srauhala_splunk]

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 

[Kaiyue]

Sorry it didn't work, thanks for your answer

[yuanliu]

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.

[Kaiyue]

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

[yuanliu]

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.

[srauhala_splunk]

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

OR 

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 created_by, source

/Seb 

[Kaiyue]

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