How filter by source name and keep only the last version

[bvivi57]

We use splunk to generate reports and provide them to an external application (Tableau). The data source are csv file using the following naming standard:

tableau_idwallet_workdate_iddollaru.csv

idwallet=A unique identification number of a customer portfolio
workdate=A processing date
iddollaru=A session number that increases with each generation of csv

Most often we have to provide a report that returns all provided with simple queries:
index = idx_ofi_tableau_rec | fields - _raw, _span, sourcetype, indexes, source, splunk_server, splunk_server_group

But sometimes trades change a value in their tool and generates a new csv with the same identifier idwallet and the same workdate. Only iddollaru changes and increments.

It is then that we generate a splunk report that contains all the datas but only csv updated with iddollaru most recent for the same idwallet and the same workdate.

I try this
index=idx_ofi_tableau_rec | eval id_trt = idwallet."-".workdate | stats first(*) by id_trt

but it doesn't work. Also I must not change the name of the field (With first, the field rename "first(field)"

Can you help me ? Thanks

[bvivi57]

Hi

With this request, we have this error:

"Error in 'rex' command: Encountered the following error while compiling the regex '(?.)_(?.)': Regex: unrecognized character after (? or (?- "

Is it normal to have no in the regex queries ?

[sundareshr]

Please add your responses as a comment to asnwers and not as a new answer. Gets confusing 🙂 Sometimes, when the code block is not used correctly, certain special chars are not displayed correctly in the answer. That's the reason you are seeing '(?.)_(?.)'

If you look at my answer below, you should see field name for each segment, which is what was missing earlier

[sundareshr · Accepted solution]

Try this

index=idx_ofi_tableau_rec | rex field=source "(?<base>.*)_(?<dt>.*)" | eventstats max(dt) as current by base | where current=dt

[bvivi57]

Hi,
Sorry for my error when I use "New Answer"

Ans thank you for your help ! Your request is OK !!!!

[bvivi57]

Hi,
Thanks for your respond and your help.
Excuse me for the delay in my response.

I work with three test files

File A : Tableau_139815_20160708_0389564
File B : Tableau_139815_20160708_0389565
File C : Tableau_139815_20160712_0389566

The query should return only the file contents B and C

With
index=idx_ofi_tableau_rec | rex field=source "(?

[^\.]+)." | eventstats max(dt) as current | where current=dt
Only C content is found
Same result with
index=idx_ofi_tableau_rec | eval idxtime=_indextime | eventstats max(idxtime) as current | where current=idxtime

Same for the last requests.
index = idx_ofi_tableau_rec [| tstats latest(source) as source WHERE index = idx_ofi_tableau_rec ]| fields - _raw, _span, sourcetype, indexes, source, splunk_server, splunk_server_group

I continued searching...

[somesoni2]

Another option (will pickup the latest available source/file data)

index = idx_ofi_tableau_rec [| tstats latest(source) as source WHERE index = idx_ofi_tableau_rec ]| fields - _raw, _span, sourcetype, indexes, source, splunk_server, splunk_server_group

[sundareshr]

Try either of these approaches...

index=idx_ofi_tableau_rec | rex field=source "_(?<dt>[^\_\.]+)\." | eventstats max(dt) as current | where current=dt

*OR*

index=idx_ofi_tableau_rec | eval idxtime=_indextime | eventstats max(idxtime) as current | where current=idxtime