Monitoring and auditing system

• US 7,885,934 B2
• Filed: 08/17/2004
• Issued: 02/08/2011
• Est. Priority Date: 08/17/2004
• Status: Active Grant

• Current Assignee: Telean Technologies Limited
• Original Assignee: Teleran Technologies, Inc.
• Inventors: Karch, Robert
• Primary Examiner(s): Wong; Don
• Assistant Examiner(s): Brown; Sheree N
• Application Number:
  US10/919,643
• Publication Number:
  US 20060041547A1
• Time in Patent Office: 2,366 Days
• Field of Search: 707 1- 10
• US Class Current: 707/648
• CPC Class Codes: G06F 16/2358 Change logging, detection, and notification replication G06F16/27
• CPC Class Codes: G06F 16/2455 Query execution
• CPC Class Codes: G06F 16/24575 using context

Abstract

An improved method and apparatus for auditing database queries, wherein comments are added by a server prior to the server forwarding the queries to the database system. The comments are then used to derive audit information, which is correlated with other audit information in a different server, to produce a more complete audit record.

Claims

1. A method comprising:

utilizing a first server to accept transactions, database queries, and/or requests for reports transmitted from plural users to said first server, extracting first audit information from said transactions, said database queries, and/or said requests for reports, and logging said first audit information in a first log;

modeling one or more transactions, queries and/or reports to be requested in advance of one or more said transactions, said database queries, and/or said reports being requested;

transmitting said transactions, said database queries, and/or said requests for reports from said first server to a database system after logging said first audit information and after adding comment information to said transactions, said database queries, and/or said requests for reports, said comment information not being present in said transactions, said database queries and/or said requests for reports when said transactions, said database queries and/or said requests for reports are transmitted from plural users to said first server;

determining one or more communications taking place via a communications channel between said first server and said database system, ascertaining second audit information from said comment information and from information ascertained and/or compiled by said modeling, and capturing said second audit information into a second log from said communications channel between said first server and said database system, said second audit information including at least some of said added comment information; and

constructing an audit record for at least one of said transactions, said database queries, and/or said requests for reports via a Business Intelligence (“

BI”

) tool by correlating and combining said first audit information and said second audit information, said BI tool operates to monitor said first and second audit information, said step of combining comprises utilizing one or more identifiers added to at least one of;

said transactions, said database queries, said requests for reports, said first audit information, said comment information, said information ascertained and/or compiled by said modeling, and said second audit information, wherein;

said constructing step further comprises recording a user identification, a report name, and a universe/catalogue name associated with each of said transactions, said database queries, and/or said requests for reports, at least one of said user identification, said report name, and said universe/catalogue name being compiled from said comment information added to said transactions, said database queries, and/or said requests for reports after said transactions, said database queries, and/or said requests for reports are generated by one or more said plural users, and at least one of said user identification, said report name, and said universe/catalogue name being compiled from information not added to said transactions, said database queries, and/or said requests for reports after said transactions, said database queries, and/or said requests for reports are generated by one or more said plural users.

2. The method of claim 1 wherein said second audit information includes at least some information that is added to said queries after said queries are transmitted from said plural users to said first server but prior to transmitting said queries from said first server to said database system, and wherein said database system resides on a second server.

3. The method of claim 2 wherein said first audit information includes at least one of said report name, said universe/catalogue name, and said user identification of a user of said plural users initiating at least one of said transactions, said database queries, and/or said requests for reports through said first server.

4. The method of claim 2 wherein at least one of said first and second audit information is added as a comment in said comment information.

5. The method of claim 3 wherein said correlating and combining includes matching a time of transaction recorded in said first log with a time of transaction recorded in said second log.

6. The method of claim 5 wherein said user of said plural users initiating said at least one of said transactions, said database queries, and/or said requests for reports does so through a thin client in communication with said first server.

7. The method of claim 1 wherein said database system is accessed via a Software Query Language (SQL) query.

8. The method of claim 1 wherein at least one of:

said first server comprises a BI server;

said first server includes a BI tool and/or said BI tool; and

said BI tool operates on a FAT client.

9. The method of claim 1 wherein said modeling includes determining what SQL statements do and/or will result from a particular transaction, database query, and/or report that is requested.

10. The method of claim 1 further comprising matching said modeled one or more transactions, database queries and/or requests for reports to said monitored one or more said transactions, database queries, and/or requests for reports being requested, thereby obtaining knowledge what particular transactions, database queries, and/or requested reports are actually requested.

11. The method of claim 10 wherein at least one of said first log, said second log, and said audit record includes a timestamp for at least one of said transactions, said database queries, and/or said requests for reports, thereby indicating a particular run time.

12. The method of claim 11 wherein the BI tool creates and records a BI event log wherein the event log operates to be read off line and correlated with information in said audit record.

13. The method of claim 11 wherein the matching step further comprises utilizing a time difference adjustment when matching said timestamp of the modeled and monitored one or more said transactions, said database queries, and/or said requests for reports, thereby allowing and/or adjusting for logs to be located and/or generated on one or more different physical servers.

14. An apparatus for logging information related to transactions, database queries, and/or requests for reports, said apparatus comprising:

a first server that accepts transactions, database queries, and/or requests for reports from plural users, extracts first audit information from said transactions, said database queries, and/or said requests for reports, generates a first audit log from said transactions, said database queries, and/or said requests for reports, and transmits said transactions, said database queries, and/or said requests for reports from said first server to a database system after logging said first audit information and after adding comment information to said transactions, said database queries, and/or said requests for reports, said comment information not being present in said transactions, said database queries and/or said requests for reports when said transactions, said database queries and/or said requests for reports are transmitted from plural users to said first server;

a monitor that operates to determine the occurrence of and monitor one or more communications taking place via a communications channel between said first server and said database system, said monitor in communication with a second log for recording information embedded-in messages transmitted from said first server to said database system via said communications channel in said second log, said embedded information not being present in said database queries when said database queries are originally sent by said plural users but being added thereafter, said embedded information including at least some of said added comment information; and

software for;

(i) modeling one or more transactions, database queries and/or reports to be requested in advance of one or more said transactions, said database queries, and/or said reports being requested; and

(ii) combining said embedded information with information in said first audit log and/or said ascertained information from said modeling to construct a final audit record for at least one of said transactions, said database queries, and/or said requests for reports via a Business Intelligence (“

BI”

) tool by correlating and combining said first audit information and said embedded information, said BI tool operates to monitor said first audit information and said embedded information, wherein said step of combining comprises utilizing one or more identifiers added to at least one of;

said transactions, said database queries, said requests for reports, said first audit information, said comment information, said information ascertained and/or compiled by said modeling, and said embedded information, wherein;

said construction further comprises recording a user identification, a report name, and a universe/catalogue name associated with each of said transactions, said database queries, and/or said requests for reports, at least one of said user identification, said report name, and said universe/catalogue name being compiled from said comment information added to said transactions, said database queries, and/or said requests for reports after said transactions, said database queries, and/or said requests for reports are generated by one or more said plural users, and at least one of said user identification, said report name, and said universe/catalogue name being compiled from information not added to said transactions, said database queries, and/or said requests for reports after said transactions, said database queries, and/or said requests for reports are generated by one or more said plural users.

15. The apparatus of claim 14 wherein said final audit record is used to update rules governing acceptable queries to be made in said database.

16. The apparatus of claim 15 wherein said embedded information includes a user ID to identify one of said plural users.