Splunk Search

How to strip leading zeros from a field and join with another set of events to create a new field?

garryclarke
Path Finder

I have a set of events on an input stream which I need to query and want to carry out a join with another data set which needs a leading zero stripped from it. For example the number 0209999948 below needs to key with another field 209999948

LW,0209999948,,NOR,1,L,00137

I am currently looking at extracting another separate field in props.conf but I’m having difficulty in stripping the leading zero in the new field call DN_STRIP.
EXTRACT-DN_STRIP = (?i),(?P[^,]+)
Can this stripping also be done in the props.conf, or do I need to be looking at a regex in the transforms.conf

Tags (4)
1 Solution

bjoernjensen
Contributor

Hi Garry!

Amongst other things you could:

  • remove the leading zero at index time by creating/overwriting a field (link) - don't do this

    transforms.conf:

    [my_unique_stanza]
    REGEX = ^\w{2},0(?<myfield>\d*),.*$
    

    props.conf:

    [host::myhost]
    TRANSFORMS-mytransformation = <my_unique_stanza>
    

    fields:conf:

    [myfield]
    INDEXED=true
    
  • do the join (link) at search time. Supposing your data "LW,0209999948,,NOR,1,L,00137" is in some source called "data1" and the field you want to do a join against in data2 is named "myfield":

    source=data1 | rex "^\w{2},0(?<myfield>\d*),.*$" | join myfield [search source=data2]
    

Have fun - Bjoern

View solution in original post

bjoernjensen
Contributor

Hi Garry!

Amongst other things you could:

  • remove the leading zero at index time by creating/overwriting a field (link) - don't do this

    transforms.conf:

    [my_unique_stanza]
    REGEX = ^\w{2},0(?<myfield>\d*),.*$
    

    props.conf:

    [host::myhost]
    TRANSFORMS-mytransformation = <my_unique_stanza>
    

    fields:conf:

    [myfield]
    INDEXED=true
    
  • do the join (link) at search time. Supposing your data "LW,0209999948,,NOR,1,L,00137" is in some source called "data1" and the field you want to do a join against in data2 is named "myfield":

    source=data1 | rex "^\w{2},0(?<myfield>\d*),.*$" | join myfield [search source=data2]
    

Have fun - Bjoern

garryclarke
Path Finder

Thanks Bjoern the regex in the second part of your reply sorted it out

0 Karma

strive
Influencer
0 Karma
Get Updates on the Splunk Community!

There's No Place Like Chrome and the Splunk Platform

Watch On DemandMalware. Risky Extensions. Data Exfiltration. End-users are increasingly reliant on browsers to ...

The Great Resilience Quest: 5th Leaderboard Update

The fifth leaderboard update for The Great Resilience Quest is out &gt;&gt; &#x1f3c6; Check out the ...

Devesh Logendran, Splunk, and the Singapore Cyber Conquest

At this year’s Splunk University, I had the privilege of chatting with Devesh Logendran, one of the winners in ...