Getting Data In

How filter by source name and keep only the last version

bvivi57
Observer

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

0 Karma
1 Solution

sundareshr
Legend

Try this

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

View solution in original post

0 Karma

bvivi57
Observer

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 ?

0 Karma

sundareshr
Legend

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

0 Karma

sundareshr
Legend

Try this

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

bvivi57
Observer

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

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

0 Karma

bvivi57
Observer

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

0 Karma

somesoni2
Revered Legend

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

sundareshr
Legend

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
0 Karma
Get Updates on the Splunk Community!

Using Machine Learning for Hunting Security Threats

WATCH NOW Seeing the exponential hike in global cyber threat spectrum, organizations are now striving more for ...

New Learning Videos on Topics Most Requested by You! Plus This Month’s New Splunk ...

Splunk Lantern is a customer success center that provides advice from Splunk experts on valuable data ...

How I Instrumented a Rust Application Without Knowing Rust

As a technical writer, I often have to edit or create code snippets for Splunk's distributions of ...