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.
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
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
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.
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
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>>)
Hi @priyaramki16,
did you tried with fillnull ( https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Fillnull ) ?
Ciao.
Giuseppe
Yeah i resolved by using filldown and fillnull. Thanks so much @gcusello