Splunk Search

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

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/pxxxxj0022380.trc (incident=401806):
ORA-00600: internal error code, arguments: [qkaffsindex3], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u00/app/oracle/diag/rdbms/pxxxx/pxxxx/incident/incdir401806/pxxxxj0022380i401806.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.POID, TOCHAR(A.PODT,'YYYY-MM-DD'), A.VENDORID, E.NAME1, TOCHAR(CAST((A.LASTDTTMUPDATE) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'), TOCHAR(A.ACTIVITYDATE,'YYYY-MM-DD'), C.LINENBR, C.DISTRIBLINENUM, C.DEPTID, G.DESCR, H.CATEGORYCD, C.ACCOUNT, F.DESCR, C.MERCHANDISEAMT, C.CURRENCYCD, C.MERCHAMTBSE, C.CURRENCYCDBASE, D.QTYMATCHED, D.AMTMATCHED, D.AMTMATCHED * 100 / C.MERCHANDISEAMT, A.BUYERID,E.SETID,E.VENDORID,F.SETID,F.ACCOUNT,TOCHAR(F.EFFDT,'YYYY-MM-DD'),G.SETID,G.DEPTID,TOCHAR(G.EFFDT,'YYYY-MM-DD') FROM PSPOHDR A, PSPOLINE B, (PSPOLINEDISTRIB C LEFT OUTER JOIN PSPOLNSHIPMTCH D ON C.BUSINESSUNIT = D.BUSINESSUNIT AND C.POID = D.POID AND C.LINENBR = D.LINENBR AND C.SCHEDNBR = D.SCHEDNBR ), PSVENDOR E, PSGLACCOUNTTBL F, PSDEPTTBL G, PSITMCATTBL H WHERE ( A.BUSINESSUNIT = B.BUSINESSUNIT AND A.POID = B.POID AND B.BUSINESSUNIT = C.BUSINESSUNIT AND B.POID = C.POID AND B.LINENBR = C.LINENBR AND E.VENDORID = A.VENDORID AND E.SETID = A.VENDORSETID AND F.ACCOUNT = C.ACCOUNT AND F.EFFDT = (SELECT MAX(FED.EFFDT) FROM PSGLACCOUNTTBL FED WHERE F.SETID = FED.SETID AND F.ACCOUNT = FED.ACCOUNT AND FED.EFFDT <= SYSDATE) AND G.DEPTID = C.DEPTID AND G.EFFDT = (SELECT MAX(GED.EFFDT) FROM PSDEPTTBL GED WHERE G.SETID = GED.SETID AND G.DEPTID = GED.DEPTID AND GED.EFFDT <= SYSDATE) AND H.CATEGORYID = B.CATEGORYID AND H.EFFDT = (SELECT MAX(HED.EFFDT) FROM PSITMCATTBL HED WHERE H.SETID = HED.SETID AND H.CATEGORYTYPE = HED.CATEGORYTYPE AND H.CATEGORYCD = HED.CATEGORYCD AND H.CATEGORYID = HED.CATEGORYID AND HED.EFFDT <= SYSDATE) AND A.PODT >= TODATE(:1,'YYYY-MM-DD') AND A.PODT <= 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

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

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

Explorer

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

0 Karma