![SoftTree Technologies](templates/GreenTech/images/logo.gif) |
SoftTree Technologies
Technical Support Forums
|
|
Author |
Message |
tnazz
Joined: 28 Feb 2011 Posts: 11 Country: United States |
|
e-mail Audit Alerts |
|
I am monitoring my database and requesting e-mail alerts when data is updated. I am getting the e-mail alerts, but there is no information in the alert to tell me exactly what is triggering the alert.
One example is that I have a Transaction table being audited, I have selected All Columns, and have filtering checked on AccountNumber, PostDate and TransAmt.
If I manually update a transaction amount, an e-mail notice is sent, the subject is DB Audit Change Notification but there is nothing in the message. How do I get information to show in the e-mail message?
We are running DB Audit 4.2.26.1 against Oracle 10gR2 (10.2.0.4).
|
|
Thu Dec 29, 2011 6:53 pm |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7854
|
|
|
|
Not sure which version of DB Audit you got, but recent versions include with email alert the primary key value(s) of the record being modified, plus any additional columns you selected for alert message.
|
|
Thu Dec 29, 2011 11:59 pm |
|
![](templates/GreenTech/images/spacer.gif) |
tnazz
Joined: 28 Feb 2011 Posts: 11 Country: United States |
|
|
|
We have the Enterprise version.
Is there something that I need to configure to get some information into the Alert e-mails? All I get now is an empty e-mail message with "DB Audit Change Notification" as the Subject.
|
|
Thu Jan 05, 2012 2:45 pm |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7854
|
|
|
|
Can you please check Help/About menu in the DB Audit console (GUI or web) and let me know your version numbers on the About screen?
Do you have any columns selected for the email message? See the attached screenshot as an example. Can you attach your screenshot for review?
![](http://s13.postimage.org/f6fscyik3/email_columns.jpg)
|
|
Sat Jan 07, 2012 11:16 am |
|
![](templates/GreenTech/images/spacer.gif) |
tnazz
Joined: 28 Feb 2011 Posts: 11 Country: United States |
|
e-mail Audit Alerts |
|
I have Version 4.2.26.1
I have had all sorts of columns selected, everything from just the 3 that I'm most interested in to ALL.
In this particular table (Transaction), there is no single Primary key, it is a combination of 2 columns: an account number and a transaction number. I've been trying to get it to alert me when a change is made to the transaction amount.
I do get an alert e-mail, but there is no information in it letting me know why I'm being alerted. This is still only running in test, but I need to get this resolved before I can move DBAudit into our Production environment.
Is there a permission which I need to set to allow DBAudit to select the info?
|
|
Mon Jan 09, 2012 10:42 am |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7854
|
|
|
|
I'm pretty sure the issue has nothing to do with permissions. It’s most likely related to the primary key definition and the data in the table in question.
Would you please try email alerts with some other table and check if they work for that table and you can see some data? If yes, we can accurate assume the issue is in the data and focus on that area, if not will continue looking into email setup.
Thanks
|
|
Mon Jan 09, 2012 12:05 pm |
|
![](templates/GreenTech/images/spacer.gif) |
tnazz
Joined: 28 Feb 2011 Posts: 11 Country: United States |
|
|
|
I tried another table (ACCT) where I changed the next Transaction Number. I still got a blank alert.
The e-mail says DB Audit Change Notification in the Subject but there is nothing in the e-mail to tell me what triggered the alert.
I appreciate you assistance with correcting this.
|
|
Mon Jan 09, 2012 1:05 pm |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7854
|
|
|
|
Do you have access to the back-end database? If yes, please execute SELECT * FROM db_audit.data_audit_trail WHERE source_table = 'ACCT'
In the output, please note name of the audit trigger generated for the table and containing the code of the email procedure. Please retrieve DDL of that trigger and email it to your support email for further analysis. We will need to ask you some addition questions after we see the code and know what it is trying to do.
|
|
Mon Jan 09, 2012 2:02 pm |
|
![](templates/GreenTech/images/spacer.gif) |
tnazz
Joined: 28 Feb 2011 Posts: 11 Country: United States |
|
|
|
When I pulled up the ACTION# information in the System TRIGGER$ table, I got the following output. It looks like the information is supposed to be inserted into the e-mail but it is not and the e-mail is still arriving as a blank e-mail.
"92876","3","1","1","1","41101","OLD","NEW","db_audit.audit_tr_201201091148182240
AFTER INSERT OR UPDATE OR DELETE ON "ZZZZZ"."ACCT"
FOR EACH ROW
","","DECLARE
v_curr_sid INTEGER;
v_sys_time DATE;
v_terminal VARCHAR2(64);
v_app_name VARCHAR2(80);
v_os_user VARCHAR2(64);
v_user_check INTEGER;
v_rule_type CHAR(1);
v_change_type VARCHAR2(7);
v_record_keys VARCHAR2(2000);
BEGIN
/*********************************************************
* DB Audit v4.2.26.1 auto-generated code
* Audit trigger for ZZZZZ.ACCT
* Created on Jan 9, 2012 at 11:50:26 AM
*********************************************************/
SELECT nvl(sum(decode(user_name, user, 1, 0)), 0), nvl(max(rule_type), 'X')
INTO v_user_check, v_rule_type
FROM db_audit.data_audit_users
WHERE source_db='N/A' AND source_owner='ZZZZZ' AND source_table='ACCT';
IF v_user_check = 0 AND v_rule_type = 'I' THEN RETURN; END IF;
IF v_user_check > 0 AND v_rule_type = 'E' THEN RETURN; END IF;
SELECT sid INTO v_curr_sid FROM db_audit.v$mystat WHERE rownum=1;
SELECT SYSDATE, SUBSTR(program, 1, 80), SUBSTR(osuser, 1, 64), SUBSTR(terminal, 1, 16)
INTO v_sys_time, v_app_name, v_os_user, v_terminal FROM db_audit.v$session WHERE sid=v_curr_sid;
IF INSERTING THEN
v_change_type := 'ADDED';
v_record_keys := 'ACCTNBR: ' || :new."ACCTNBR" || ', ' || 'MJACCTTYPCD: ' || :new."MJACCTTYPCD" || ', ' || 'CURRMIACCTTYPCD: ' || :new."CURRMIACCTTYPCD" || ', ' || 'CURRACCTSTATCD: ' || :new."CURRACCTSTATCD" || ', ' || 'TAXRPTFORORGNBR: ' || :new."TAXRPTFORORGNBR" || ', ' || 'TAXRPTFORPERSNBR: ' || :new."TAXRPTFORPERSNBR" || ', ' || 'ACCTDESC: ' || :new."ACCTDESC";
INSERT INTO db_audit.audit_201201091148182240
VALUES (v_sys_time, v_app_name, v_terminal, v_os_user, user, 'INSERT', 'NEW', :new."ACCTNBR", :new."STMTACCTNBR", :new."TAXRPTFOROWNYN", :new."TAXRPTFORSIGYN", :new."RETIREMENTYN", :new."MJACCTTYPCD", :new."CURRMIACCTTYPCD", :new."CURRACCTSTATCD", :new."TAXRPTFORORGNBR", :new."TAXRPTFORPERSNBR", :new."NEXTRTXNNBR");
ELSE
IF DELETING THEN
v_change_type := 'DELETED';
v_record_keys := 'ACCTNBR: ' || :old."ACCTNBR" || ', ' || 'MJACCTTYPCD: ' || :old."MJACCTTYPCD" || ', ' || 'CURRMIACCTTYPCD: ' || :old."CURRMIACCTTYPCD" || ', ' || 'CURRACCTSTATCD: ' || :old."CURRACCTSTATCD" || ', ' || 'TAXRPTFORORGNBR: ' || :old."TAXRPTFORORGNBR" || ', ' || 'TAXRPTFORPERSNBR: ' || :old."TAXRPTFORPERSNBR" || ', ' || 'ACCTDESC: ' || :old."ACCTDESC";
INSERT INTO db_audit.audit_201201091148182240
VALUES (v_sys_time, v_app_name, v_terminal, v_os_user, user, 'DELETE', 'OLD', :old."ACCTNBR", :old."STMTACCTNBR", :old."TAXRPTFOROWNYN", :old."TAXRPTFORSIGYN", :old."RETIREMENTYN", :old."MJACCTTYPCD", :old."CURRMIACCTTYPCD", :old."CURRACCTSTATCD", :old."TAXRPTFORORGNBR", :old."TAXRPTFORPERSNBR", :old."NEXTRTXNNBR");
ELSE /* UPDATING */
v_change_type := 'UPDATED';
v_record_keys := 'ACCTNBR: ' || :old."ACCTNBR" || ', ' || 'MJACCTTYPCD: ' || :old."MJACCTTYPCD" || ', ' || 'CURRMIACCTTYPCD: ' || :old."CURRMIACCTTYPCD" || ', ' || 'CURRACCTSTATCD: ' || :old."CURRACCTSTATCD" || ', ' || 'TAXRPTFORORGNBR: ' || :old."TAXRPTFORORGNBR" || ', ' || 'TAXRPTFORPERSNBR: ' || :old."TAXRPTFORPERSNBR" || ', ' || 'ACCTDESC: ' || :old."ACCTDESC";
INSERT INTO db_audit.audit_201201091148182240
VALUES (v_sys_time, v_app_name, v_terminal, v_os_user, user, 'UPDATE', 'OLD', :old."ACCTNBR", :old."STMTACCTNBR", :old."TAXRPTFOROWNYN", :old."TAXRPTFORSIGYN", :old."RETIREMENTYN", :old."MJACCTTYPCD", :old."CURRMIACCTTYPCD", :old."CURRACCTSTATCD", :old."TAXRPTFORORGNBR", :old."TAXRPTFORPERSNBR", :old."NEXTRTXNNBR");
INSERT INTO db_audit.audit_201201091148182240
VALUES (v_sys_time, v_app_name, v_terminal, v_os_user, user, 'UPDATE', 'NEW', :new."ACCTNBR", :new."STMTACCTNBR", :new."TAXRPTFOROWNYN", :new."TAXRPTFORSIGYN", :new."RETIREMENTYN", :new."MJACCTTYPCD", :new."CURRMIACCTTYPCD", :new."CURRACCTSTATCD", :new."TAXRPTFORORGNBR", :new."TAXRPTFORPERSNBR", :new."NEXTRTXNNBR");
END IF;
END IF;
/* send notification */
db_audit.sp_audit_sendmail('SERVER_NAME_OR_IP_HERE',
'db_AuditT3@***********',
'tnazarek@*********',
NULL,
'DB Audit Change Notification',
'Change Time: ' || to_char(v_sys_time, 'dd Mon yy hh24:mi:ss') || chr(10)
|| 'User ' || nvl(v_os_user, user) || ' just made changes in table ZZZZZ.ACCT' || chr(10)
|| 'Change type: RECORD ' || v_change_type || '. Record key(s): ' || v_record_keys);
END;","4265","1","128","0","0","0","","3"
|
|
Mon Jan 09, 2012 4:32 pm |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7854
|
|
|
|
I edited your post and for security reasons masked actual email addresses. Please don' post actual server names, emails, passwords, and other sensitive data that you don't want web spiders and spammers to pick from the web
|
|
Mon Jan 09, 2012 5:51 pm |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7854
|
|
|
|
Yes, it looks the message text is generated correctly but then it is lost in transition. I'm checking what could impact it on the email server side and/or in the email sending procedure.
|
|
Mon Jan 09, 2012 5:58 pm |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7854
|
|
|
|
Hi,
Which type of email server and email client do you use?
In your email client, can you see the source of the message (or Properties/Source)? If you can, can you see the message text in the source?
|
|
Tue Jan 10, 2012 12:15 pm |
|
![](templates/GreenTech/images/spacer.gif) |
tnazz
Joined: 28 Feb 2011 Posts: 11 Country: United States |
|
|
|
We are using Exchange 2007 and Outlook 2010 as the client. I have asked our Exchange admin and he does not know of any reason that Exchange would prevent the message from being populated.
This is what I see when I try to view the source of the message:
<DOCTYPE>
<HTML><HEAD>
<META></HEAD>
<BODY></BODY></HTML>
|
|
Tue Jan 10, 2012 12:47 pm |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7854
|
|
|
|
That's HTML, but the message is sent as a plain text ("text/plain" encoding). Can you switch to text view to see what's in there?
|
|
Tue Jan 10, 2012 1:23 pm |
|
![](templates/GreenTech/images/spacer.gif) |
tnazz
Joined: 28 Feb 2011 Posts: 11 Country: United States |
|
|
|
There is nothing else. If I turn on show all encoding, all that I see is a single paragraph mark. If I try to Select All in the body of the message I only get one block selected. There is no text in the body of the message.
When I run a report on the database changes, the information is in the table so it is being captured but it is not getting pulled into the e-mail.
|
|
Tue Jan 10, 2012 2:56 pm |
|
![](templates/GreenTech/images/spacer.gif) |
|
|
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
|
|
|