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!

Aligning Observability Costs with Business Value: Practical Strategies

 Join us for an engaging Tech Talk on Aligning Observability Costs with Business Value: Practical ...

Mastering Data Pipelines: Unlocking Value with Splunk

 In today's AI-driven world, organizations must balance the challenges of managing the explosion of data with ...

Splunk Up Your Game: Why It's Time to Embrace Python 3.9+ and OpenSSL 3.0

Did you know that for Splunk Enterprise 9.4, Python 3.9 is the default interpreter? This shift is not just a ...