Splunk Search

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

akhanVG
Path Finder

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.

0 Karma

somesoni2
Revered Legend

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 
0 Karma

woodcock
Esteemed Legend

Like this (assuming that YEar should be Year:disappointed_face:

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
0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...