Splunk Search

How do you make a search rex / regex to extract outcode from UK postcodes from a lookup table?

KaneKennedyNHSD
New Member

Hi,

Could anyone help me get further with this please? I have a list of UK post codes in my event data. They will always be in UK postcode format as per this table:

Format           Example
AA9A 9AA     EC1A 1BB
A9A 9AA          W1A 0AX
A9 9AA           M1 1AE
A99 9AA          B33 8TH
AA9 9AA          CR2 6XH
AA99 9AA     DN55 1PT

I have got this far " search "postcode" NOT "{postcode}" | rex field=postcode "(?P\w{2}).*" |stats count by area "

And I get

area    count
B1  2
B2  1
B4  1
B5  1
BB  1
BD  2
L1    20
L2    5

I have a lookup table that contains data similar to this and I have a definition that points to the file,
outcode, and postcode.

B1,B
B2,B
B3,B
B4,B
L1,L
L2,L
L3,L
S1,S
S2,S
S3,S
S4,S

I can't work out how the change my search to look at the first 1 or 2 characters and replace them if they are in the table so my end result would be:

B     5
BB  1
BD  2
L     25

I have tried rex field=postcode "(?P\b[a-zA-Z]{2}[0-9]{1}\b)" |stats count by area however that just broke down the first part into individual stats and only on postcode where the first two characters were alpha BB9, BH8 etc.....

Many thanks in advance.

Kane.

0 Karma
1 Solution

kamlesh_vaghela
SplunkTrust
SplunkTrust

Please try the following search after your stats search.

| rex field=area "(?<areas>(.*)[^\d|\n])" | stats sum(count) as count by areas

like:

 search "postcode" NOT "{postcode}" | rex field=postcode "(?P\w{2}).*" |stats count by area
 | rex field=area "(?<areas>(.*)[^\d|\n])" | stats sum(count) as count by areas

My Sample Search:

| makeresults 
| eval area="B1,B2,B4,B5,BB,BD,L1,L2",count="2,1,1,1,1,2,20,5",area=split(area,","),count=split(count,",") 
| eval temp=mvzip(area,count) 
| mvexpand temp 
| eval area=mvindex(split(temp,","),0),count=mvindex(split(temp,","),1) 
| fields - temp | rex field=area "(?<areas>(.*)[^\d|\n])" | stats sum(count) as count by areas

Thanks

View solution in original post

0 Karma

somesoni2
Revered Legend

Try like this

search "postcode" NOT "{postcode}" 
| rex field=postcode "(?P<area>\w{2}).*" 
|stats count by area
| lookup yourlookupDefHere outcode as area OUTPUT postcode
| eval area=coalesce(postcode,area)
| stats sum(count) as count by areas
0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

Please try the following search after your stats search.

| rex field=area "(?<areas>(.*)[^\d|\n])" | stats sum(count) as count by areas

like:

 search "postcode" NOT "{postcode}" | rex field=postcode "(?P\w{2}).*" |stats count by area
 | rex field=area "(?<areas>(.*)[^\d|\n])" | stats sum(count) as count by areas

My Sample Search:

| makeresults 
| eval area="B1,B2,B4,B5,BB,BD,L1,L2",count="2,1,1,1,1,2,20,5",area=split(area,","),count=split(count,",") 
| eval temp=mvzip(area,count) 
| mvexpand temp 
| eval area=mvindex(split(temp,","),0),count=mvindex(split(temp,","),1) 
| fields - temp | rex field=area "(?<areas>(.*)[^\d|\n])" | stats sum(count) as count by areas

Thanks

0 Karma

KaneKennedyNHSD
New Member

Worked perfectly, Thank you very much.

0 Karma
Get Updates on the Splunk Community!

What’s new on Splunk Lantern in August

This month’s Splunk Lantern update gives you the low-down on all of the articles we’ve published over the past ...

Welcome to the Future of Data Search & Exploration

You have more data coming at you than ever before. Over the next five years, the total amount of digital data ...

This Week's Community Digest - Splunk Community Happenings [8.3.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...