topic Re: How filter by source name and keep only the last version in Getting Data In

How filter by source name and keep only the last version

bvivi57 — Tue, 29 Sep 2020 10:13:41 GMT

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

Re: How filter by source name and keep only the last version

sundareshr — Wed, 13 Jul 2016 18:34:44 GMT

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

Re: How filter by source name and keep only the last version

somesoni2 — Wed, 13 Jul 2016 19:05:16 GMT

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

Re: How filter by source name and keep only the last version

bvivi57 — Tue, 29 Sep 2020 10:16:36 GMT

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

Re: How filter by source name and keep only the last version

sundareshr — Tue, 19 Jul 2016 13:42:58 GMT

Try this

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

Re: How filter by source name and keep only the last version

bvivi57 — Tue, 19 Jul 2016 14:33:17 GMT

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 ?

Re: How filter by source name and keep only the last version

sundareshr — Tue, 19 Jul 2016 16:11:09 GMT

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

Re: How filter by source name and keep only the last version

bvivi57 — Mon, 25 Jul 2016 15:58:52 GMT

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

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