Splunk Search

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

priyaramki16
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

gcusello
SplunkTrust
SplunkTrust

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

gcusello
SplunkTrust
SplunkTrust

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

priyaramki16
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

gcusello
SplunkTrust
SplunkTrust

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

gcusello
SplunkTrust
SplunkTrust

priyaramki16
Path Finder

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...