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
SplunkTrust
SplunkTrust

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😞

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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...