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

[KaneKennedyNHSD]

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.

[somesoni2]

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

[kamlesh_vaghela · Accepted solution]

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

[KaneKennedyNHSD]

Worked perfectly, Thank you very much.