How to merge two charts which have different stats by in Splunk

[priyaramki16]

Hi, I wanted a single graph to show values. 
One search is 
index="cumu_open_csv"  Assignee="ram"
| eval open_field=if(in(Status,"Open","Reopened","Waiting","In Progress"), 1,0)
| stats count(eval(open_field=1)) AS Open, count(eval(open_field=0)) AS closed by CW_Created

this gives me a table as 

Similarly I have another search

 index="cumu_open_csv"  Assignee="ram"
| eval open_field=if(in(Status,"Open","Reopened","Waiting","In Progress"), 1,0)
| stats count(eval(open_field=1)) As DueOpen by CW_DueDate

which gives me another table as 

I tried to combine these two using appendcols...but the X-axis has only the CW_Created and displays the second table details in wrong CW.

I wanted CW_Created and CW_Duedate to be combined and provide the result in a single table like CW, Open,Close,DueCount wherever DueCount is not for a particular CW fill it with 0, for others display the data like so..

	Open	Close	DueCount
CW27	7	0	0
CW28	2	0	0
CW29	0	0	4
CW30	0	7	3
CW31	0	0	1
CW32	0	0	1

Kindly help me with this.

[gcusello]

Hi @priyaramki16,

you have to use the same name for fields, something like this:

index="cumu_open_csv"  Assignee="ram"
| eval open_field=if(in(Status,"Open","Reopened","Waiting","In Progress"), 1,0)
| rename CW_Created AS CW
| stats count(eval(open_field=1)) AS Open, count(eval(open_field=0)) AS Closed by CW
| append [ search
     index="cumu_open_csv"  Assignee="ram"
     | eval open_field=if(in(Status,"Open","Reopened","Waiting","In Progress"), 1,0)
     | rename CW_DueDate AS CW
     | stats count(eval(open_field=1)) As DueOpen by CW ]
| sort CW
| table CW Open Closed DueOpen 

Ciao.

Giuseppe

[priyaramki16]

Hi @gcusello , This almost worked, only thing is wherever DueOpen had values those respective Calender weeks alone got repeated...Hence displaying the x-axis with two times the same CW like this.

I tried dedup CW before the sort, but that seems to remove the DueOpen . Is there a way to solve this.

[gcusello · Accepted solution]

Hi @priyaramki16,

try to add another stats (instead table) at the end of the search:

index="cumu_open_csv"  Assignee="ram"
| eval open_field=if(in(Status,"Open","Reopened","Waiting","In Progress"), 1,0)
| rename CW_Created AS CW
| stats count(eval(open_field=1)) AS Open, count(eval(open_field=0)) AS Closed by CW
| append [ search
     index="cumu_open_csv"  Assignee="ram"
     | eval open_field=if(in(Status,"Open","Reopened","Waiting","In Progress"), 1,0)
     | rename CW_DueDate AS CW
     | stats count(eval(open_field=1)) As DueOpen by CW ]
| sort CW
| stats values(Open) AS Open values(Closed) AS Closed values(DueOpen) AS DueOpen BY CW 

 Ciao.

Giuseppe

[priyaramki16]

Thank u so much @gcusello . This worked....Got a small prob alone..like there are empty cell for certain columns...I am trying to fill the empty columns with 0 but its not happening.
Tried this, But it didnot work

eval <<FIELD>> = if(isnull(<<FIELD>>) OR len(<<FIELD>>)==0, "0", <<FIELD>>)

[gcusello]

Hi @priyaramki16,

did you tried with fillnull ( https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Fillnull ) ?

Ciao.

Giuseppe

[priyaramki16]

Yeah i resolved by using filldown and fillnull. Thanks so much @gcusello