Can't make join to compare the presence of a field value

[3DGjos]

Hello, i'm trying to make a dashboard for a client, the dashboard consists basically in a table, which should show a list of the branches that had shown information by date, and the ones that hasnt.

something like:

"date" "branch" "Has events?"
DATE A BRANCH1 YES
DATE A BRANCH2 YES
DATE A BRANCH3 NO
DATE B BRANCH1 YES
DATE B BRANCH2 NO
DATE B BRANCH3 YES
DATE C BRANCH1 NO
DATE D BRANCH4 YES

So far I've got this query:

index=myindex DATE="$datetoken$" | stats latest(branch) AS branch2 by DATE, branch

I have a lookup with the whole branches list, to compare if there is data for the date. The logic is: if a branch pops in my search, "has events" will be YES, but if a branch is in the lookup, but splunk does not find events for that branch, it means the branch has no events, so "has events" will be "NO".

but I still need to join it or append my query with the list from the lookup with inputlookup, in order to have the branches with no data for the DATEs, or the ones with data.
the thing is I tried a lot of querys but I can't make the results match.

tried this:

index=myindex DATE="$tokendate$" | stats latest(branch) AS branch2 by date, branch| appendcols [| inputlookup mylookup.csv | where inactive=0 | table branch]

But I can't order or match the columns.

Thanks!!

[maciep · Accepted solution]

appendcols is dangerous my friend - you need to be absolutely SURE that the you have the same number of branches in the same order, because splunk isn't going to join those up for you at all - it's literally going to append the columns to the right without a care for your well being. And so based on your scenario...it's not the way to go.

you could either start with inputlookup, then left join to your search and then decide yes no on whether the branch2 field is there.

or you could append instead of appendcols, then stats values() by branch and then decide yes/no from there.

If you need more help, i can put answers post together too. But if you figure it out on your own, post your answer here and accept it.

[3DGjos]

Thanks for your answer, I can go to my client environment only on fridays, so i will try in 3 days.

I was thinking going for stats values or stats count yes. Wil comment how it went on friday, thanks !