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!

Enhance Your Splunk App Development: New Tools & Support

UCC FrameworkAdd-on Builder has been around for quite some time. It helps build Splunk apps faster, but it ...

Prove Your Splunk Prowess at .conf25—No Prereqs Required!

Your Next Big Security Credential: No Prerequisites Needed We know you’ve got the skills, and now, earning the ...

Splunk Observability Cloud's AI Assistant in Action Series: Observability as Code

This is the sixth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how to ...