topic Re: How to edit my regular expression to include a space in order for Splunk to extract data? in Getting Data In

How to edit my regular expression to include a space in order for Splunk to extract data?

IRHM73 — Thu, 08 Dec 2016 08:08:13 GMT

Hi, I wonder whether someone could help me please.

I'm trying to create a Splunk regular expression to extract the postcode from the sample as shown below.

{"line1":"1 THE STREET","line2":"TOWN","line3":"CITY","line4":null,"postcode":"AB1 1AB","country":"GREAT BRITAIN","startDate":944697600000,"type":"Residential"}

I'm using Regex101 and in this I can create the following which extracts the data:

\"postcode\"\:\"(?[^\.]{0,7})\"

However, when I run this in Splunk, it doesn't extract the data.

I think it may have something to do with the space between the two elements of the postcode, but could someone please look at this and let me know where I've gone wrong.

Many thanks and kind regards

Chris

Re: How to edit my regular expression to include a space in order for Splunk to extract data?

gokadroid — Thu, 08 Dec 2016 08:16:57 GMT

I am assuming from your sample data that you are trying to extract AB11AB from "postcode":"AB11AB" hence I have written regex to capture POSTCODE as string between "postcode":"POSTCODE "

your query to return events
| rex field=_raw "\"postcode\"\:\"(?<myPostcode>[^\"]+)\""
| table myPostcode

See regex in action here

In case you want to go json way, try this:

your query to return json events
 | spath output=myPostcode path=postcode
 | table postcode, myPostcode

Re: How to edit my regular expression to include a space in order for Splunk to extract data?

IRHM73 — Thu, 08 Dec 2016 08:19:50 GMT

Hi thank you for coming back to me with this, but it doesn't work. I've looked at our regex101 example and it doesn't include the space between the two elements of the postcode. I think that may be the issue.

Many thanks and kind regards

Chris

Re: How to edit my regular expression to include a space in order for Splunk to extract data?

gokadroid — Thu, 08 Dec 2016 08:22:14 GMT

Did you see the link I posted...Regex is to capture everything between "postcode":" and ending ". So even if there are spaces in between it will capture. Can you please paste the sample postcode, an actual event containing this postcode.

Re: How to edit my regular expression to include a space in order for Splunk to extract data?

IRHM73 — Thu, 08 Dec 2016 08:32:12 GMT

Hi @gokadroid, thank you for coming back to me and my sincere apologies because my sample didn't show a space between two elements of the postcode. I've now changed this.

So this is where I have the problem, both solutions don't extract the postcode data.

Your solution works great in regex101 for postcodes with and without a space, but doesn't in Splunk.

Many thanks and kind regards

Chris

Re: How to edit my regular expression to include a space in order for Splunk to extract data?

gokadroid — Thu, 08 Dec 2016 08:36:37 GMT

Where in Splunk are you using it? Are you writing it like the SPL query I have written?

Re: How to edit my regular expression to include a space in order for Splunk to extract data?

IRHM73 — Thu, 08 Dec 2016 08:46:35 GMT

Hi @gokadroid, thank you for this.

My query line for this is as follows:

| rex field=detail.responseMessage ""

I then use the regex name, in this case to display in a table.

Kind Regards

Chris

Re: How to edit my regular expression to include a space in order for Splunk to extract data?

gokadroid — Thu, 08 Dec 2016 08:49:17 GMT

Can you try to use the field=_raw rather than field=detail.responseMessage?

If your data is json then try to use spath which can also assist like this:

your query to return json events
| spath output=myPostcode path=postcode
| table postcode, myPostcode

Re: How to edit my regular expression to include a space in order for Splunk to extract data?

IRHM73 — Thu, 08 Dec 2016 09:10:04 GMT

Hi, this is very strange.

Because I've found that the field is extracting the data as an "interesting filed", but when I try to use this in a table, the column returns blank values.

Kind Regards

Chris

Re: How to edit my regular expression to include a space in order for Splunk to extract data?

IRHM73 — Thu, 08 Dec 2016 09:17:15 GMT

Hi @gokadroid.

I've cracked it. I discovered because the field was missing from a "stats values) row further down in the query, it wasn't appearing in the table.

My sincere apologies for messing you around and many thanks for your help.

Kind Regards

Chris

Re: How to edit my regular expression to include a space in order for Splunk to extract data?

javiergn — Thu, 08 Dec 2016 09:21:36 GMT

I've deleted my answer as I just noticed there was already one suggesting spath.

Re: How to edit my regular expression to include a space in order for Splunk to extract data?

gokadroid — Thu, 08 Dec 2016 09:23:25 GMT

That's fine @IRHM73. We all are here to help each other.
Let me get rid of all the back and forth comments and streamline the original answer with the json bit, in case someone else in future needs to refer it.

Its all well if it works out well for you!!

Re: How to edit my regular expression to include a space in order for Splunk to extract data?

IRHM73 — Thu, 08 Dec 2016 09:25:52 GMT

Ok, once again, sincere thanks.