Lookup table question

[worldexplorer81]

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

Filename	Group
price.daily	Pricing
price.daily.vendor	Pricing
price.daily.source	Pricing
test.daily	Testing

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?

FileName	Group	Status	Time
price.daily.20201210.xml	Pricing	Delivered	2010-12-11 06.17.47
price.daily.vendor.20201210.csv	Pricing	Pending	N.A
price.daily.source.20201210.xml	Pricing	Delivered	2010-12-11 06.17.47

[renjith_nair · Accepted solution]

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