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!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...