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!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...