appendcols not give proper answer

[sandeep_thosar]

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

[aholzer]

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 ]

[sandeep_thosar]

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

[aholzer]

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