Splunk Search

Lookup table question

worldexplorer81
Explorer

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

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

View solution in original post

Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!