Unable to fetch Oracle logs

[AtulChavan]

Hi Guys,

I have integrated Oracle 11.2.0.4 using Database Auditing but not receiving logs from oracle.

DSN is able to connect to data source but not able publish new events. Also last tracking ID is same and old for all the connections.

Can anyone tell me why last tracking id is old and same for all the connections?

[DavidWaugh1]

Hello

If you go to the log collector and find out the ODBC file that is responsible for the connection.

Normally this will be saved in the directory:

/etc/netwitness/ng/logcollection/content/collection/odbc/

For each type of datasource you should see an xml file.

Within this xml file is the tracking query that is responsible for generating the tracking ID.

Looking in the Oracle 11g XML file then the Tracking Query is:

SELECT TO_CHAR(MAX(cast((from_tz("SYS"."AUD$"."NTIMESTAMP#" ,'00:00') at local) as d
ate)),'yyyy-mm-dd hh24:mi:ss') FROM "SYS"."AUD$"

If you manually run this query against the oracle database do you get a different value to the one that is being presented?

[JoeGumke]

Hi Atul, Can you provide a little bit more detail into what is occurring? What does your collector state in terms of logging? Its able to authenticate/connect to the db, but NOT poll events? Id recommend setting your odbc to debug and tailing the messages file to further determine the issue as to whats going on . You can do this by going into the ODBC event sources, and advanced , debug == VERBOSE.

[AtulChavan]

Yes. It is able to connect to DB but not poll events. 

Event:

[odbc:WrkUnit[1]:32385] [anyNewEvents:307] [oracle_11g_auditing.rsidcporcdb01] [processing] [rsidcporcdb01] Found events for this cycle; dataQuery: SELECT "SYS"."V_$INSTANCE"."VERSION", '-', "SYS"."V_$INSTANCE"."HOST_NAME", '-', "SYS"."V_$INSTANCE"."INSTANCE_NAME", '-', '-', "SYS"."AUD$"."SESSIONID", "SYS"."AUD$"."ENTRYID", "SYS"."AUD$"."STATEMENT", CASE "SYS"."AUD$"."ACTION#" WHEN 101 THEN TO_CHAR("SYS"."AUD$"."LOGOFF$TIME",'yyyy-mm-dd hh24:mi:ss') WHEN 102 THEN TO_CHAR("SYS"."AUD$"."LOGOFF$TIME",'yyyy-mm-dd hh24:mi:ss') ELSE TO_CHAR(cast((from_tz("SYS"."AUD$"."NTIMESTAMP#" ,'00:00') at local) as date),'yyyy-mm-dd hh24:mi:ss') END "timestamp", "SYS"."AUD$"."USERID", "SYS"."AUD$"."USERHOST", "SYS"."AUD$"."TERMINAL", "SYS"."AUD$"."ACTION#", "SYS"."AUD$"."RETURNCODE", "SYS"."AUD$"."OBJ$CREATOR", "SYS"."AUD$"."OBJ$NAME", "SYS"."AUD$"."AUTH$PRIVILEGES", "SYS"."AUD$"."AUTH$GRANTEE", "SYS"."AUD$"."NEW$OWNER", "SYS"."AUD$"."NEW$NAME", "SYS"."AUD$"."SES$ACTIONS", "SYS"."AUD$"."SES$TID", "SYS"."AUD$"."LOGOFF$LREAD", "SYS"."AUD$"."LOGOFF$PREAD", "SYS"."AUD$"."LOGOFF$LWRITE", "SYS"."AUD$"."LOGOFF$DEAD", COALESCE(TO_CHAR("SYS"."AUD$"."LOGOFF$TIME",'yyyy-mm-dd hh24:mi:ss'),' ') as LOGOFF$TIME, "SYS"."AUD$"."COMMENT$TEXT", "SYS"."AUD$"."CLIENTID", "SYS"."AUD$"."SPARE1", "SYS"."AUD$"."SPARE2", "SYS"."AUD$"."OBJ$LABEL", "SYS"."AUD$"."SES$LABEL", "SYS"."AUD$"."PRIV$USED", "SYS"."AUD$"."SESSIONCPU" , cast("SYS"."AUD$"."SQLBIND" as varchar(2000)) as SQL_BIND, cast("SYS"."AUD$"."SQLTEXT" as varchar(2000)) as SQL_TEXT FROM "SYS"."AUD$", "SYS"."V_$INSTANCE" WHERE (cast((from_tz("SYS"."AUD$"."NTIMESTAMP#" ,'00:00') at local) as date) > to_date('2016-07-25 11:30:07','yyyy-mm-dd hh24:mi:ss') AND SYSDATE-(2/(3600*24)) > cast((from_tz("SYS"."AUD$"."NTIMESTAMP#" ,'00:00') at local) as date) AND "SYS"."AUD$"."ACTION#" NOT IN (101,102)) OR ("SYS"."AUD$"."LOGOFF$TIME" > to_date('2016-07-25 11:30:07','yyyy-mm-dd hh24:mi:ss') AND "SYS"."AUD$"."ACTION#" IN (101,102) AND SYSDATE-(2/(3600*24)) > "SYS"."AUD$"."LOGOFF$TIME" ) ORDER BY 11 ASC; maxTrackingId: 2016-07-25 11:30:07

As you can max tracking ID is too old.

[DavidWaugh1]

What is the result of running the tracking query directly against the database?

SELECT TO_CHAR(MAX(cast((from_tz("SYS"."AUD$"."NTIMESTAMP#" ,'00:00') at local) as date)),'yyyy-mm-dd hh24:mi:ss') FROM "SYS"."AUD$"

Does the tracking query report: 2016-07-25 11:30:07

[AtulChavan]

Hi David,

I have asked DB owner to run this query. What will be the next steps if output is 2016-07-25 11:30:07 and in other case?

[DavidWaugh1]

Hi if the output is 2016-07-25 11:30:07 then this would suggest that auditing of the database stopped at this time.If it is different then it may be some issue on the Netwitness side.

Is this a new data source?

Was it previously working?

For how long was it previously working?

Have any changes been made to either the Netwitness configuration settings for the event source?

Have any changes been made to the Event Source around this date?

[AtulChavan]

This is new data source. Configuration are done on the date 26-07-2016.

We have some other DB's configured in same Hybrid which are working fine. 

Will it create any impact if I run below query directly on Database?

SELECT TO_CHAR(MAX(cast((from_tz("SYS"."AUD$"."NTIMESTAMP#" ,'00:00') at local) as date)),'yyyy-mm-dd hh24:mi:ss') FROM "SYS"."AUD$"