Apologies for the confusing title
We have 1 search that gives us Revenue To Date -
(*s are to mask sensitrive info)
index =**** source="/srv/***/SRR.csv" | dedup "Document Number" | search "Class_ Name"=Retail | rex mode=sed field=Credit "s/[^\d\.-]//g" | rex mode=sed field=Debit "s/[^\d\.-]//g" | stats sum(Credit) as Revenue sum(Debit) as Returns | eval GrossRevenue=Revenue-Returns | table GrossRevenue
We also have a file called Budget.csv that has our budget in the following format
Class, Month, Month Number, Rev Expected, YEar
Retail, October, 10, 10000000, 2015
Web, October, 10, 10000000, 2015
Retail, November, 11, 10000000, 2015
Web, November, 11, 10000000, 2015
We want to use the GrossRevenue from the first search and compare it against the second file Budget.csv. The _time field in the first search would give us the month and year to compare against in the budget. Class would be compared based on Class in the budget.csv file against the Class_Name in the first search.
We tried using eval and strftime to extract and create new fields representing the Month and Year from the first search to insert into a subsearch and create a table but it didn't work the way we expected.
Any tips would be appreciated.
Give this a try
index =**** source="/srv/***/SRR.csv" | dedup "Document Number" | search "Class_Name"=Retail | rex mode=sed field=Credit "s/[^\d\.-]//g" | rex mode=sed field=Debit "s/[^\d\.-]//g" | stats sum(Credit) as Revenue sum(Debit) as Returns max(_time) as _time by "Class_ Name" | rename "Class_Name" as Class | eval GrossRevenue=Revenue-Returns | eval temp=strftime(_time,"%B#%m#%Y") | rex field=temp "(?<Month>.*)#(?<MonthNo>\d+)#(?<Year>\d+)"| table Class Month MonthNo Year GrossRevenue | append [ Your base search get data from Budget.csv | rename "Month Number" as MonthNo "Rev Expected" as ExpectedRevenue | table Class, Month, MonthNo, ExpectedRevenue,Year ] | stats values(*) as * by Class Month MonthNo Year
Like this (assuming that YEar
should be Year
😞
index =**** source="/srv/***/SRR.csv" | dedup "Document Number" | search "Class_ Name"=Retail | rex mode=sed field=Credit "s/[^\d\.-]//g" | rex mode=sed field=Debit "s/[^\d\.-]//g" | bucket _time span=1m | stats sum(Credit) as Revenue sum(Debit) as Returns by _time Class_Name | eval GrossRevenue=Revenue-Returns | eval type="search" | table _time Class_Name GrossRevenue type | rename _time AS time Class_Name AS Class | appendpipe [|inputlookup Budget.csv | eval time=strfime($Month Number$ . "/" . Year, "%m/%Y")] | eval type=coalesce(type, "csv") | stats values(*) AS * dc(type) AS numTypes by time Class