Compare the final value of two searches against each other using data from first search

[akhanVG]

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.

[somesoni2]

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 

[woodcock]

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