How to return a row from lookup file, even if no events match criteria?

[gdportal]

Greetings.

I am creating a table that charts "Total Spent" over Department by Month.
This report also shows the Department Budget and the remaining Balance.
Each event in my log is a financial transaction.
Sample from event log:

"timestamp","Date","Department","Spent"
"00:00","01/31/2017","PR","100.00"

I use a lookup file to determine the Department budget.

Sample from lookup file:

"Department","Budget"
"PR","100.00"
"HR","100.00"
"IT","100.00"

Here is my sample search:

index="test" | convert timeformat="%Y-%m" ctime(_time) AS Month | stats sum(Spent) as "Total" By Month,Department | fields + Month, Department, Total | chart sum(Total) OVER Department BY Month | fillnull value=0.00 | lookup sample-department-budget Department OUTPUT Budget | appendcols [search index="test" | stats sum(Spent) as "Total Spent" BY Department] | fillnull value=0.00 | eval Balance=(Budget-'Total Spent') | table Department, Budget, 2017-01, 2017-02, 2017-03, "Total Spent", Balance

The resulting table looks similar to the following:

"Department","Budget","2017-01","2017-02","2017-03","Total Spent","Balance"
"BS",0.00,"0.00","300.00","0.00","300.00","-300.00"
"HR","100.00","210.00","0.00","210.00","420.00","-320.00"
"PR","100.00","210.00","210.00","210.00","630.00","-530.00"

My current search catches Departments (e.g. BS) that have spending in the event log, but do not have entries in the Budget lookup file.

"Department","Budget","2017-01","2017-02","2017-03","Total Spent","Balance"
"BS",0.00,"0.00","300.00","0.00","300.00","-300.00"

However, I also have a Department in the Budget lookup file (e.g. IT) that has no spending in the event log.

"Department","Budget"
"IT","100.00"

I would like my report to include any Department in the Budget file, even if there is no spending in the event log.

For example:

"Department","Budget","2017-01","2017-02","2017-03","Total Spent","Balance"
"IT","100.00","0.00","0.00","0.00","0.00","100.00"

Ideally, my final report would look similar to the following:

"Department","Budget","2017-01","2017-02","2017-03","Total Spent","Balance"
"BS",0.00,"0.00","300.00","0.00","300.00","-300.00"
"HR","100.00","210.00","0.00","210.00","420.00","-320.00"
"IT","100.00","0.00","0.00","0.00","0.00","100.00"
"PR","100.00","210.00","210.00","210.00","630.00","-530.00"

Any suggestions would be appreciated.

Thank you.

[DalJeanis · Accepted solution]

Believe it or not, you are best off looking at the issue from an accounting perspective, because it works just fine in splunk.

You are going to total up the spend by department. Then you are going to total up the budget by department. Then you are going to subtract the two to get the answer, and it will have all departments, including those with no budget but with expenses, and those with budgets but no expenses.

First big hint - do not EVER do the "chart x over y" stuff until all your calculation is done and you are ready for presentation.

Second big hint - do not EVER use appendcols if you can avoid it. I'm sure there's a use case for it, but I have never seen a case where there wasn't something else that works better and is less confusing.

Third big hint - whenever possible, just dump all the records together and let stats sort them out.

See whether this gets you close to what you need...

index="test" 
| eval Month=strftime(_time,"%Y-%m") 
| stats sum(Spent) as MonthSpend by Month, Department 
| rename COMMENT as " the above does the same as your existing query before the chart"

| rename COMMENT as "now we calculate records for the total spend and add them to the pipe"
| appendpipe [| stats sum(MonthSpend) as TotalSpend by Department]

| rename COMMENT as "prepend M to the month names to keep them in order, create fields with those month names and values, then drop the field called Month"
| eval Month="M".Month
| eval {Month} = MonthSpend
| fields - Month

| rename COMMENT as "now we add budgets for all the departments in your lookup"
| append [inputlookup sample-department-budget | table Department Budget ]

| rename COMMENT as "We roll each department together into a single record using stats, and calculate remaining budget"
| stats values(*) as * by Department
| table Department Budget M20* TotalSpend
| eval BudgetRemaining = Budget - TotalSpend

We may end up having to tweak the last field name to keep it in the right order, but I believe that should do it for you.

[gdportal]

Yes, your search worked perfectly for me. Thank you very much for the hints, as well. Much appreciated!