topic Re: Field Extraction from particular column in Splunk Search

Field Extraction from particular column?

yuvasree — Tue, 25 Apr 2023 15:31:59 GMT

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.

Re: Field Extraction from particular column

yuvasree — Tue, 25 Apr 2023 07:03:14 GMT

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]

Re: Field Extraction from particular column

gcusello — Tue, 25 Apr 2023 08:47:24 GMT

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

Re: Field Extraction from particular column

yuvasree — Tue, 25 Apr 2023 13:35:15 GMT

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.

Re: Field Extraction from particular column

gcusello — Tue, 25 Apr 2023 15:28:24 GMT

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

Re: Field Extraction from particular column

yuvasree — Tue, 25 Apr 2023 17:16:08 GMT

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.

Re: Field Extraction from particular column

gcusello — Wed, 26 Apr 2023 06:53:01 GMT

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

Re: Field Extraction from particular column?

yuvasree — Wed, 26 Apr 2023 07:15:26 GMT

Thanks @gcusello