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