topic Re: How to merge two charts which have different stats by in Splunk in Splunk Search

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

priyaramki16 — Tue, 28 Jul 2020 10:59:39 GMT

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.

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

gcusello — Tue, 28 Jul 2020 12:18:32 GMT

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

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

priyaramki16 — Tue, 28 Jul 2020 17:25:58 GMT

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.

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

gcusello — Wed, 29 Jul 2020 06:46:40 GMT

Hi @priyaramki16,

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

Ciao.

Giuseppe

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

priyaramki16 — Wed, 29 Jul 2020 08:22:45 GMT

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>>)

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

gcusello — Thu, 30 Jul 2020 12:56:08 GMT

Hi @priyaramki16,

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

Ciao.

Giuseppe

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

priyaramki16 — Thu, 30 Jul 2020 17:34:16 GMT

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