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

[garryclarke]

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

[bjoernjensen · Accepted solution]

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]

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

[strive]

You can use ltrim function of eval

http://docs.splunk.com/Documentation/Splunk/6.1.3/SearchReference/Commonevalfunctions