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!

Splunk Forwarders and Forced Time Based Load Balancing

Splunk customers use universal forwarders to collect and send data to Splunk. A universal forwarder can send ...

NEW! Log Views in Splunk Observability Dashboards Gives Context From a Single Page

Today, Splunk Observability releases log views, a new feature for users to add their logs data from Splunk Log ...

Last Chance to Submit Your Paper For BSides Splunk - Deadline is August 12th!

Hello everyone! Don't wait to submit - The deadline is August 12th! We have truly missed the community so ...