Splunk Search

Lookup table question

worldexplorer81
Path Finder

Hi, 

I have multiple files being delivered on a daily basis are in the below format:

<filename>.<yyyymmdd>.xml - Example: price.daily.20201218.xml

Example of an event when a file has been delivered is:

 

2020-12-11 06:17:47 INFO : File_created=/current/price.daily.20201210.xml; file_size=86324624
2020-12-11 06:17:47 INFO : File_created=/current/test.daily.20201210.xml; file_size=6896548
2020-12-11 06:17:47 INFO : File_created=/current/price.daily.sources.20201210.xml; file_size=48526

 

 

I am trying to build a query to check for some specific set of files (I'm not interested in all files), whether they have been delivered and report the status in a table. 

To achieve this, I have a lookup file with Filename and Group as columns

FilenameGroup
price.dailyPricing
price.daily.vendorPricing
price.daily.sourcePricing
test.dailyTesting

 

I would like to have a table to display if the files belonging to group Pricing have been delivered for a specific date. Can anyone please advise the best way to achieve this?

FileNameGroupStatusTime
price.daily.20201210.xmlPricingDelivered2010-12-11 06.17.47
price.daily.vendor.20201210.csvPricingPendingN.A
price.daily.source.20201210.xmlPricingDelivered2010-12-11 06.17.47
Labels (1)
0 Karma
1 Solution

renjith_nair
Legend

Try

index=<your index> File_created=* 
| rex field=File_created "current\/(?<FullFileName>.+)" 
| stats latest(_time) as _time by FullFileName 
| rex field=FullFileName "(?<Filename>[^\d]+)\." 
| append 
    [| inputlookup pricing.csv 
    | eval source="lookup"] 
| stats max(_time) as _time,first(Group) as Group,first(FullFileName) as FullFileName,first(source) as Source by Filename 
| where Source="lookup" 
| fields - Source 
| eval Status=if(isnull(_time),"Pending","Delivered") 
| fillnull value="NA" _time

 

  • Created events and lookup table based on your sample events and lookup
  • Filename is a field available from the events
  • Extension of files are not considered for comparison, assuming that you have only one type of file per Filename
  • Searches' time range should be decided to make sure that you get only the required events. If that's not the case, we need to extract date from filename and compare it
---
What goes around comes around. If it helps, hit it with Karma 🙂

View solution in original post

renjith_nair
Legend

Try

index=<your index> File_created=* 
| rex field=File_created "current\/(?<FullFileName>.+)" 
| stats latest(_time) as _time by FullFileName 
| rex field=FullFileName "(?<Filename>[^\d]+)\." 
| append 
    [| inputlookup pricing.csv 
    | eval source="lookup"] 
| stats max(_time) as _time,first(Group) as Group,first(FullFileName) as FullFileName,first(source) as Source by Filename 
| where Source="lookup" 
| fields - Source 
| eval Status=if(isnull(_time),"Pending","Delivered") 
| fillnull value="NA" _time

 

  • Created events and lookup table based on your sample events and lookup
  • Filename is a field available from the events
  • Extension of files are not considered for comparison, assuming that you have only one type of file per Filename
  • Searches' time range should be decided to make sure that you get only the required events. If that's not the case, we need to extract date from filename and compare it
---
What goes around comes around. If it helps, hit it with Karma 🙂
Get Updates on the Splunk Community!

Why You Can't Miss .conf25: Unleashing the Power of Agentic AI with Splunk & Cisco

The Defining Technology Movement of Our Lifetime The advent of agentic AI is arguably the defining technology ...

Deep Dive into Federated Analytics: Unlocking the Full Power of Your Security Data

In today’s complex digital landscape, security teams face increasing pressure to protect sprawling data across ...

Your summer travels continue with new course releases

Summer in the Northern hemisphere is in full swing, and is often a time to travel and explore. If your summer ...