topic appendcols not give proper answer in Splunk Search

appendcols not give proper answer

sandeep_thosar — Mon, 18 May 2015 13:58:15 GMT

Hi Team,

We used appendcols and hence write following query, but when we run following query then Overall counts get fine but the problem with Unique counts the appendcols function write data for overall but unique counts are blank.

sourcetype="A1"  "test " | eval CompletedCt = "Overall" | stats count(userID) As Heats by Date,CompletedCt,Flow| appendcols [search sourcetype="A1"  "test1" Completed | eval CompletedCt = "Overall" | stats count(UserId) As Completed by Date,CompletedCt,Flow]  | APPEND [search sourcetype="A1"  "test" | eval CompletedCt = "Unique" | stats dc(UserID) As Heatsby Date,CompletedCt,Flow] | appendcols [search sourcetype="A1"  "test" Completed | eval CompletedCt = "Unique" | stats dc(UserID) As Completed by Date,CompletedCt,Flow ] | table Date,CompletedCt,IdRecMethod,Attempt,Completed

Please help me out.

Thanks in advance.

Regards,

Sandeep Thosar

Re: appendcols not give proper answer

aholzer — Mon, 18 May 2015 17:41:10 GMT

The problem is with the way you have written your query. You need to nest the appendcols inside of the append, otherwise Splunk will treat it as an appendcols for the full query up to that point.

However there is a better solution. Extract the "Completed" into a field, name it Status if you will:

sourcetype="A1" "test " | stats count As OverallHeats count(eval(Status=="Completed")) as OverallCompleted dc(userID) as UniqueHeats by Date,CompletedCt,Flow | appendcols [search sourcetype="A1" "test " "Completed" | stats dc(userID) as UniqueCompleted by Date,CompletedCt,Flow ]

OR without extracting "Completed" into a field:

sourcetype="A1" "test " | stats count As OverallHeats dc(userID) as UniqueHeats by Date,CompletedCt,Flow | appendcols [search sourcetype="A1" "test " "Completed" | stats count as OverallCompleted dc(userID) as UniqueCompleted by Date,CompletedCt,Flow ]

Re: appendcols not give proper answer

sandeep_thosar — Tue, 19 May 2015 07:48:07 GMT

Hi,

Thanks for the prompt reply. i have tried your solution but anable to write netsed appendcols. Please send me sample exmaple for nested appendcols if possible. and also need to show unique and overall counts seperately. need following output.

Date Unique/Overall Flow heats Completed
2015-05-17 overall Flow1 20 30
2015-05-17 overall Flow2 50 40
2015-05-17 Unique Flow1 20 30
2015-05-17 Unique Flow2 50 40

But i have getting following output

Date Unique/Overall Flow heats Completed
2015-05-17 overall Flow1 20 30
2015-05-17 overall Flow2 50 40
2015-05-17 Unique Flow1 20

2015-05-17 Unique Flow2 50

Please help me out.

Thanks in advance.

Regards,

Sandeep

Re: appendcols not give proper answer

aholzer — Wed, 20 May 2015 17:12:15 GMT

This should do it:

sourcetype="A1"  "test " | eval CompletedCt = "Overall" | stats count(userID) As Heats by Date,CompletedCt,Flow| appendcols [search sourcetype="A1"  "test1" Completed | eval CompletedCt = "Overall" | stats count(UserId) As Completed by Date,CompletedCt,Flow]  | APPEND [search sourcetype="A1"  "test" | eval CompletedCt = "Unique" | stats dc(UserID) As Heatsby Date,CompletedCt,Flow | appendcols [search sourcetype="A1"  "test" Completed | eval CompletedCt = "Unique" | stats dc(UserID) As Completed by Date,CompletedCt,Flow ]] | table Date,CompletedCt,IdRecMethod,Attempt,Completed