Field Extraction from particular column?

[yuvasree]

hi 

I have the below Query to get the required output except one column.

Query:

index="general_prod" source="osblogprod" sourcetype="csv" | extract pairdelim="," kvdelim="=" | where like (REASON ,"%ORA-00001%") | eval DATE=strftime(_time, "%Y-%m-%d") | eval S.NO=1 | accum S.NO |dedup MESSAGEIDENTIFIER| table S.NO,DATE,BUSINESSIDENTIFIER,MESSAGEIDENTIFIER,SERVICELAYEROPERATION,CHARGETYPE,REASON|rename BUSINESSIDENTIFIER AS "Order ID", SERVICELAYEROPERATION AS "API NAME", REASON AS "OSB Observation"

I need to extract charge type from the OSB Observation column. Sample values are RSCN4,RSCN3.

Kindly help me with the rex command.

[yuvasree]

Thanks @gcusello 

[yuvasree]

Output:

S.NO DATE Order ID MESSAGEIDENTIFIER API NAME CHARGETYPE OSB Observation

1 2023-04-25 06-02-10112022-85346-L 7ff79dca-a39b-4b00-8b76-bee72efbcb83 CreateQPBillingEvents
Insert into tables failed-ORA-00001:Duplicate Check Fail for ORDER_ID[06-02-10112022-85346-L] TROUBLE_TICKET_ID[] CHARGETYPE [RSCN4]
2 2023-04-25 06-02-10112022-85346-L a08f16a0-2789-4216-a42d-623fae84c0b7 CreateQPBillingEvents
Insert into tables failed-ORA-00001:Duplicate Check Fail for ORDER_ID[06-02-10112022-85346-L] TROUBLE_TICKET_ID[] CHARGETYPE [RSCN3]
3 2023-04-25 06-02-10112022-85346-L 9e0f0d13-488e-4e6a-934f-a5eb36c015ca CreateQPBillingEvents
Insert into tables failed-ORA-00001:Duplicate Check Fail for ORDER_ID[06-02-10112022-85346-L] TROUBLE_TICKET_ID[] CHARGETYPE [RSCN3]
4 2023-04-25 06-02-10112022-85346-L 687e81b7-30b6-490e-90e6-552d09741a54 CreateQPBillingEvents
Insert into tables failed-ORA-00001:Duplicate Check Fail for ORDER_ID[06-02-10112022-85346-L] TROUBLE_TICKET_ID[] CHARGETYPE [RSCN4]
5 2023-04-24 06-02-10112022-85346-L 85d38933-bc8f-4c56-944b-a1acd264140c CreateQPBillingEvents
Insert into tables failed-ORA-00001:Duplicate Check Fail for ORDER_ID[06-02-10112022-85346-L] TROUBLE_TICKET_ID[] CHARGETYPE [RSCN3]

[gcusello]

Hi @yuvasree,

let me understand: you want to extract the OSM_Observation field that's the value between brackets at the end of each event, In your events: "RSCN3", is it correct?

If this is your need, please try this regex:

\[(?<OSM_Observation>[^\]]*)\]$

that you can test at https://regex101.com/r/TT9bIW/1

Ciao.

Giuseppe

[yuvasree]

Hi @gcusello ,

Yes I need the mentioned field. But I need the column as well in the mentioned output. So can you modify my query itself as i am not good in rex and regex. 

[gcusello]

Hi @yuvasree,

could you highlight (in bold or with a coulour) the part of log that must be extracted as REASON?

It seems that you want as REASON the Oracle error ( e.g. ORA-00001) is it correct?

if this is the values to extract, please add my regex to your search:

index="general_prod" source="osblogprod" sourcetype="csv" 
| extract pairdelim="," kvdelim="=" 
| rex "(?<REASON>ORA-\d+)"
| where like (REASON ,"%ORA-00001%") 
| eval DATE=strftime(_time, "%Y-%m-%d") 
| eval S.NO=1 
| accum S.NO 
| dedup MESSAGEIDENTIFIER
| table S.NO,DATE,BUSINESSIDENTIFIER,MESSAGEIDENTIFIER,SERVICELAYEROPERATION,CHARGETYPE,REASON|rename BUSINESSIDENTIFIER AS "Order ID", SERVICELAYEROPERATION AS "API NAME", REASON AS "OSB Observation"

You can test the regex at https://regex101.com/r/TT9bIW/2

Ciao.

Giuseppe

[yuvasree]

Hi @gcusello ,

I confused you it seems. Apologies for the same. 

Your first understanding is correct. I need to extract the fields closed inside the bracket.

Below is the sample one event and the same has been extracted using pairdelim and kvdelim.

==============

2023-04-25 23:15:00.438, BUSINESSIDENTIFIER="06-02-10112022-85346-L", MESSAGEIDENTIFIER="42920d7b-4bde-4a7c-9704-532bc178acfd", PAYLOAD="BusinessIdentifier : 06-02-10112022-85346-L ***** MessageIdentifier : 42920d7b-4bde-4a7c-9704-532bc178acfd ***** TimeStamp : 2023-04-25T23:00:48.149+08:00 ***** ElapsedTime : 0.05 ***** InterfaceName : BRM ***** ServiceLayerName : Remedy ***** ServiceLayerOperation : CreateQPBillingEvents ***** ServiceLayerPipeline : ServiceLayerErrorHandler ***** SiteID : AWS ***** DomainName : OSBDomain ***** ServerName : OSBServer ***** FusionErrorCode : ***** FusionErrorMessage : ***** <ns2:createQPBillEventsResponse xmlns:ns2="com.alcatel.lucent.on.ws.manager"><reason>Insert into tables failed-ORA-00001:Duplicate Check Fail for ORDER_ID[06-02-10112022-85346-L] TROUBLE_TICKET_ID[] CHARGETYPE [RSCN4]</reason><response_Code>-1</response_Code></ns2:createQPBillEventsResponse>", TIMESTAMP="2023-04-25 23:00:48.149", SERVICELAYEROPERATION="CreateQPBillingEvents", ELAPSEDTIME="0.05", SERVICELAYERPIPELINE="ServiceLayerErrorHandler", REASON="Insert into tables failed-ORA-00001:Duplicate Check Fail for ORDER_ID[06-02-10112022-85346-L] TROUBLE_TICKET_ID[] CHARGETYPE [RSCN4]", RESPONSECODE="-1"

===============

using the below Query  i have extracted all the fields except CHARGETYPE. CHARGETYPE is the value which needs to be extracted from the reason field 

Sample value: CHARGETYPE [RSCN4] 

index="general_prod" source="osblogprod" sourcetype="csv" | extract pairdelim="," kvdelim="=" | where like (REASON ,"%ORA-00001%") | eval DATE=strftime(_time, "%Y-%m-%d") | eval S.NO=1 | accum S.NO |dedup MESSAGEIDENTIFIER| table S.NO,DATE,BUSINESSIDENTIFIER,MESSAGEIDENTIFIER,SERVICELAYEROPERATION,CHARGETYPE,REASON|rename BUSINESSIDENTIFIER AS "Order ID", SERVICELAYEROPERATION AS "API NAME", REASON AS "OSB Observation"

.Hope clarifies now.  

[gcusello · Accepted solution]

Hi @yuvasree,

you can add a regex to extarct the CHARGETIME field:

| rex "CHARGETYPE\s\[(?<CHARGETYPE>[^\]]*)]"

you can test this regex at https://regex101.com/r/rcXFpU/1

Ciao.

Giuseppe