0

please I Have a log message that contains a serial with a fuzzy pattern that hard to follow like the below

|LogMsg                                                                                    |
|------------------------------------------------------------------------------------------|
|Customer Receive CPE Indoor. serial 21530369847SKA011094, user:ahmed.o.haraz              |
|Customer Receive CPE Indoor as change. serial :21530369847SK9078291, user:Abdullah.M160275|
|Customer Receive CPE Indoor. serial:ZTERRT1H9202990                                       |
|Customer Receive CPE Indoor. serial 21530369847SKB333996 .UserName :TEDST.mohamed.badry   |
|Customer Receive CPE Indoor as change. serial :21530373727skc298302, user:Frass.m195577   |
|Customer Receive CPE Indoor. serial 21530369847SKA267112 .UserName :seller.160002         |

I need to extract the Serial from the string like the below

|Serial|
|21530369847SKA011094|
|21530369847SK9078291|
|ZTERRT1H9202990     |
|21530369847SKB333996|
|21530373727skc298302|
|21530369847SKA267112|

I got the mentioned query using regexp_replace() but it missed some of them

select replace(replace(regexp_extract(logmsg, 'serial [^,]+'), 'serial ', ''), ':', '')
Ahmed Mohammed Abdel Kader
  • 149
  • 2
  • 12
  • 1
    You would probably be better off solving this at the application level. You could encapsulate the logic in a `LogMsgParser` class or something. If not, declare a SQL function to handle the parsing if you can. That log writer is pretty horrible btw. Can't it log structured messages? – plalx Oct 08 '20 at 00:59

1 Answers1

2

Try

select get_value_varchar(
        regexp_extract_all_sp(logmsg,
          '(serial\s*:?)([^,\s]+)'),3) as serial
from serial;

It should give you the result you are looking for

        SERIAL
----------------------
 21530369847SKA011094
 21530369847SKA267112
 21530369847SKB333996
 21530369847SK9078291
 ZTERRT1H9202990
 21530373727skc298302
(6 rows)
Aniket Kulkarni
  • 471
  • 2
  • 2