Splunk Search

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

Path Finder

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 

priyaramki16_0-1595933451628.png

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 

priyaramki16_1-1595933573701.png

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

 OpenCloseDueCount
CW27700
CW28200
CW29004
CW30073
CW31001
CW32001


Kindly help me with this.

Labels (4)
0 Karma
1 Solution

Legend

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

View solution in original post

Legend

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

0 Karma

Path Finder

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.

priyaramki16_0-1595957062423.pngpriyaramki16_1-1595957103461.png

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

0 Karma

Legend

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

View solution in original post

Path Finder

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>>)
Tags (2)
0 Karma

Legend

Path Finder

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

0 Karma