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

[mm977g]

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

---

[gcato · Accepted solution]

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.

[mm977g]

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