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.
... View more