CODEWITHSUNDEEP
statisticssplunksplunk-query
Hari Priya
Hari Priya

Reputation: 27

How to merge two stats by in Splunk?

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

OpenCloseTable

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

DUeDatetable

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.

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

Upvotes: 1

Views: 7706

Answers (2)

warren
warren

Reputation: 33435

This may be what you are looking for

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
| rename CW_Created as CW
| join type=outer CW
    [| 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
    | rename CW_DueDate as CW ]

Or possibly this:

index="cumu_open_csv"  Assignee="ram"
| eval open_field=if(in(Status,"Open","Reopened","Waiting","In Progress"), 1,0)
| eval CW=if(len(CW_Created)>1,CW_Created,CW_DueDate)
| stats count(eval(open_field=1)) AS Open, count(eval(open_field=0)) AS closed, count(eval(open_field=1)) as DueOpen by CW

Sample data would make this substantially easier to try to help you with

Upvotes: 0

RichG
RichG

Reputation: 9906

The appendcols command is a bit tricky to use. Events from the main search and subsearch are paired on a one-to-one basis without regard to any field value. This means event CW27 will be matched with CW29, CW28 with CW30, and so on.

Try the append command, instead. The results of the subsearch will follow the results of the main search, but a stats command can be used to merge them.

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
| append [ 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 ]
| eval CW = coalesce(CW_Created, CW_DueDate)
| stats values(*) as * by CW

Upvotes: 1

Related Questions