Splunk Search

How to graph and chart Oracle Error Code (ORA-nnnnn) from mutiple sources

mm977g
Explorer

I have multiple logs from different sources (app server, database server,etc) where they can contain a error response from the Oracle database. These error codes all begin with ORA- followed by 1 to 5 numeric values. I want to be able to count the number of distinct errors over time and place into a bar chart. Can someone suggest some ideas on how to accomplish this? Below are a couple samples of the incoming log files:


Fri Jul 17 16:15:36 2015
Errors in file /u00/app/oracle/diag/rdbms/pxxxx/pxxxx/trace/pxxxx_j002_2380.trc (incident=401806):
ORA-00600: internal error code, arguments: [qkaffsindex3], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u00/app/oracle/diag/rdbms/pxxxx/pxxxx/incident/incdir_401806/pxxxx_j002_2380_i401806.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.


PSQRYSRV.6154 (1475) 07/17/15 15:45:28 LENA_M@10.216.0.76 (IE 9.0; WIN7) ICQuery File: /vob/peopletools/src/pssys/qpm.cppSQL error. Stmt #: 6577 Error Position: 402 Return: 1476 - ORA-01476: divisor is equal to zero
Failed SQL stmt:SELECT DISTINCT A.PO_ID, TO_CHAR(A.PO_DT,'YYYY-MM-DD'), A.VENDOR_ID, E.NAME1, TO_CHAR(CAST((A.LAST_DTTM_UPDATE) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'), TO_CHAR(A.ACTIVITY_DATE,'YYYY-MM-DD'), C.LINE_NBR, C.DISTRIB_LINE_NUM, C.DEPTID, G.DESCR, H.CATEGORY_CD, C.ACCOUNT, F.DESCR, C.MERCHANDISE_AMT, C.CURRENCY_CD, C.MERCH_AMT_BSE, C.CURRENCY_CD_BASE, D.QTY_MATCHED, D.AMT_MATCHED, D.AMT_MATCHED * 100 / C.MERCHANDISE_AMT, A.BUYER_ID,E.SETID,E.VENDOR_ID,F.SETID,F.ACCOUNT,TO_CHAR(F.EFFDT,'YYYY-MM-DD'),G.SETID,G.DEPTID,TO_CHAR(G.EFFDT,'YYYY-MM-DD') FROM PS_PO_HDR A, PS_PO_LINE B, (PS_PO_LINE_DISTRIB C LEFT OUTER JOIN PS_PO_LN_SHIP_MTCH D ON C.BUSINESS_UNIT = D.BUSINESS_UNIT AND C.PO_ID = D.PO_ID AND C.LINE_NBR = D.LINE_NBR AND C.SCHED_NBR = D.SCHED_NBR ), PS_VENDOR E, PS_GL_ACCOUNT_TBL F, PS_DEPT_TBL G, PS_ITM_CAT_TBL H WHERE ( A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.PO_ID = B.PO_ID AND B.BUSINESS_UNIT = C.BUSINESS_UNIT AND B.PO_ID = C.PO_ID AND B.LINE_NBR = C.LINE_NBR AND E.VENDOR_ID = A.VENDOR_ID AND E.SETID = A.VENDOR_SETID AND F.ACCOUNT = C.ACCOUNT AND F.EFFDT = (SELECT MAX(F_ED.EFFDT) FROM PS_GL_ACCOUNT_TBL F_ED WHERE F.SETID = F_ED.SETID AND F.ACCOUNT = F_ED.ACCOUNT AND F_ED.EFFDT <= SYSDATE) AND G.DEPTID = C.DEPTID AND G.EFFDT = (SELECT MAX(G_ED.EFFDT) FROM PS_DEPT_TBL G_ED WHERE G.SETID = G_ED.SETID AND G.DEPTID = G_ED.DEPTID AND G_ED.EFFDT <= SYSDATE) AND H.CATEGORY_ID = B.CATEGORY_ID AND H.EFFDT = (SELECT MAX(H_ED.EFFDT) FROM PS_ITM_CAT_TBL H_ED WHERE H.SETID = H_ED.SETID AND H.CATEGORY_TYPE = H_ED.CATEGORY_TYPE AND H.CATEGORY_CD = H_ED.CATEGORY_CD AND H.CATEGORY_ID = H_ED.CATEGORY_ID AND H_ED.EFFDT <= SYSDATE) AND A.PO_DT >= TO_DATE(:1,'YYYY-MM-DD') AND A.PO_DT <= TO_DATE(:2,'YYYY-MM-DD') )


PSPRCSRV.2380 (0) 09/06/15 16:28:54 Explain: ORA-12170: TNS:Connect timeout occurred


0 Karma
1 Solution

gcato
Contributor

Hi mm977g

Use the rex command to extract the ORA error code from your search results then timechart to count results by time. For example,

...<search>... | rex "(?i)(?<ora_err_code>ORA-\d{1,5}):"  |timechart dc(ora_err_code) as err_count

I'd recommend you set up automatic field extractions to pull the ORA error code out of the different events (per sourcetype) and then you can get rid of the rex command.

Hope this helps.

View solution in original post

0 Karma

gcato
Contributor

Hi mm977g

Use the rex command to extract the ORA error code from your search results then timechart to count results by time. For example,

...<search>... | rex "(?i)(?<ora_err_code>ORA-\d{1,5}):"  |timechart dc(ora_err_code) as err_count

I'd recommend you set up automatic field extractions to pull the ORA error code out of the different events (per sourcetype) and then you can get rid of the rex command.

Hope this helps.

0 Karma

mm977g
Explorer

While this isn't exactly what I was looking for it did give me enough information to achieve the desired goal.

0 Karma
Get Updates on the Splunk Community!

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...

March Community Office Hours Security Series Uncovered!

Hello Splunk Community! In March, Splunk Community Office Hours spotlighted our fabulous Splunk Threat ...

Stay Connected: Your Guide to April Tech Talks, Office Hours, and Webinars!

Take a look below to explore our upcoming Community Office Hours, Tech Talks, and Webinars in April. This post ...