How to use regex to extract strings for a field instead of eval?

[akshaykaul]

hi,

I am trying to extract billing info from a field and use them as two different columns in my stats table.

Example field values:

SC=$170 Service IDL120686730
SNC=$170 Service IDL120686730

Currently I am using eval:

| eval fee=substr(Work_Notes,1,8)
| eval service_IDL=substr(Work_Notes,16,32) 
|table fee service_IDL

to get fee as SC=$170 and service_IDL as IDL120686730, but since the original string is manually entered hence using substr ma not be efficient in case user puts extra spaces extra or if SNC=$0

So is there a way I can use regex to extract the two fields from original string "SNC=$170 Service IDL120686730"
Don't have much experience using regex so would appreciate any help!

thank you in advance.

[sundareshr]

See if this works for you

.... | rex field=Work_Notes "(?<grp>[SNC]+)\s*=\s*(?<fee>\$\s*\d+)\s*Service\s*IDL(?<idl>\d+)" | table grp fee idl

[esix_splunk]

If your raw event contains:

SNC=$170 Service IDL120686730

You can use the following regex..

<base search> | regex field=_raw "SNC=(?<snc>[^\s]+)\sService\s(?<idl>.*)" | table snc idl

Note that this assumes the end of the message is the IDL120686730

If there is more text after this, you need to change the regex a bit..

[akshaykaul]

Thank you for your response.

I am intrested in raw event containing both:
SNC=$170 Service IDL120686730 OR
SC=$170 Service IDL120686730
which I filter using the CASE statement as shown below.

I tried to use the regex for SNC but I might be missing something.
Error in 'SearchOperator:regex': Usage: regex (=|!=)

  index=sdp-fr sourcetype=sdp_remedy_workorders_worknotes | join Work_Order_ID [search  index=sdp-fr sourcetype=sdp_remedy_Workorders] 
     | search Company="*"  Work_Order_ID=* Status="*" 
     | eval TARGET=CASE(
                       Work_Notes LIKE "%SC=%",1,       
                       Work_Notes LIKE "%SNC=%",2) 
     | search TARGET=1 OR TARGET=2
     | regex Work_Notes=_raw "SNC=(?<snc>[^\s]+)\sService\s(?<idl>.*)" 
     | rename Actual_End_Date AS Closing_Date 
     | table Company Customer Work_Order_ID Submit_Date Closing_Date  Work_Order_Type Status Submitter Summary Support_Group  Work_Notes  Work_Log_ID snc idl

[esix_splunk]

This should be field=_raw, not Work_Notes=_raw.

[akshaykaul]

| regex field=_raw "SNC=(?[^\s]+)\sService\s(?.*)"

still got the same error.
as you can see I am trying to fetch the fields IDL and SNC from the Work_Notes field.
the whole raw event is :

 Work_Log_ID=CWL000000164885 Work_Order_ID=WO0000000050093 Submit_Date=1462945570 Work_Log_Type="General Information" Submit_Time="2016-05-11 17:46:10.000       " Work_Notes="SC=$170 Service IDL100611944\x00"

[akshaykaul]

index=sdp-fr sourcetype=sdp_remedy_workorders_worknotes | join Work_Order_ID [search  index=sdp-fr sourcetype=sdp_remedy_Workorders] 
 | search Company="*"  Work_Order_ID=* Status="*" 
 | eval TARGET=CASE(
                   Work_Notes LIKE "%SC=%",1,       
                   Work_Notes LIKE "%SNC=%",2) 
 | search TARGET=1 OR TARGET=2
 | regex field=_raw "SNC=(?<snc>[^\s]+)\sService\s(?<idl>.*)" 
 | rename Actual_End_Date AS Closing_Date 
 | table Company Customer Work_Order_ID Submit_Date Closing_Date  Work_Order_Type Status Submitter Summary Support_Group  Work_Notes  Work_Log_ID snc idl

[esix_splunk]

Is the raw event SC= or SNC= ..

You have posted both. If its both, you should adjust the regex.. to

.. | regex field=_raw "(SN|SNC)=(?<snc>[^\s]+)\sService\s(?<idl>.*)" | ..

[akshaykaul]

the raw event can have either SC or SNC
but not both for an individual event
thats why i am fetching both the events by using
| eval TARGET=CASE(
Work_Notes LIKE "%SC=%",1,

Work_Notes LIKE "%SNC=%",2)
| search TARGET=1 OR TARGET=2