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

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
Happy Splunking!

View solution in original post

renjith_nair
SplunkTrust
SplunkTrust

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
Happy Splunking!
Get Updates on the Splunk Community!

Splunk APM & RUM | Upcoming Planned Maintenance

There will be planned maintenance of Splunk APM’s and Splunk RUM’s streaming infrastructure in the coming ...

Part 2: Diving Deeper With AIOps

Getting the Most Out of Event Correlation and Alert Storm Detection in Splunk IT Service Intelligence   Watch ...

User Groups | Upcoming Events!

If by chance you weren't already aware, the Splunk Community is host to numerous User Groups, organized ...