monitoring the audit database using event...

Tip or Technique

Monitoring the Audit Database using Event Studio

Product(s): Event Studio, Framework Manager

Area of Interest: Reporting

Monitoring the Audit Database using Event Studio 2

IBM Cognos Proprietary Information

Copyright

Copyright © 2008 Cognos ULC (formerly Cognos Incorporated). Cognos ULC is an IBM Company. While every attempt has been made to ensure that the information in this document is accurate and complete, some typographical errors or technical inaccuracies may exist. Cognos does not accept responsibility for any kind of loss resulting from the use of information contained in this document. This document shows the publication date. The information contained in this document is subject to change without notice. Any improvements or changes to the information contained in this document will be documented in subsequent editions. This document contains proprietary information of Cognos. All rights are reserved. No part of this document may be copied, photocopied, reproduced, stored in a retrieval system, transmitted in any form or by any means, or translated into another language without the prior written consent of Cognos. Cognos and the Cognos logo are trademarks of Cognos ULC (formerly Cognos Incorporated) in the United States and/or other countries. IBM and the IBM logo are trademarks of International Business Machines Corporation in the United States, or other countries, or both. All other names are trademarks or registered trademarks of their respective companies. Information about Cognos products can be found at www.cognos.com

This document is maintained by the Best Practices, Product and Technology team. You can send comments, suggestions, and additions to [email protected] .



Contents

1 INTRODUCTION ............................................................................................ 4

1.1 PURPOSE ............................................................................................................4 1.2 WHERE THIS PROCEDURE IS USEFUL? ..........................................................................4

2 MODIFY THE AUDIT MODEL.......................................................................... 4

3 CREATE THE EVENT STEP BY STEP PART 2 ............................................... 13



1 Introduction

1.1 Purpose

The purpose of this workshop is to get familiar a technique which can be used to extract information from the audit logs and integrate the information with warning emails generated from Event Studio. It can monitor for a

specific error X number of times within the last Y hours and notify the administrator by email should this occur.

1.2 Applicability

This document applies to IBM Cognos 8.2.

1.3 Where this procedure is useful?

This technique would be useful to any administrator.

With some minor modification when specific error patterns are seen some proactive actions can be taken.

2 Modify the Audit Model

Open the Audit package in Framework Manager. Right click on the Audit view and select Create and then Query Subject.

You can locate the sample Audit package at … Install_location\webcontent\samples\Models\Audit\Audit.cpf

1) Right click on the Audit namespace and Create a new Query Subject.



2) Call this Query Subject Get CM-REQ-4159 Errors for last 2 Hours

3) Click OK

This will bring up the Subject Query Definition Dialog

4) Add from the Available Model Objects from under the database view



- [Audit].[COGIPF_ACTION].[ERROR DETAILS] - [Audit].[COGIPF_ACTION].[TIME STAMP]

5) Click on the Add Query Item link near the bottom of the Query Items

and Calculations: window.

This will bring up the Calculation Definition Dialog

6) Name this calculation ‘Error Count’

7) Set the expression to …

count([Audit].[Get CM-REQ-4159 Errors for last 1 Hours].[ERROR DETAILS])



8) Click OK

9) Select the Filter tab



10) Click on the Add Filter link near the bottom of the Filters window.

This will bring up the Calculation Definition Dialog



11) Name this filter “Has CM-REQ-4159 Error” and


[Audit].[COGIPF_ACTION].[ERROR DETAILS] contains 'CM-REQ-4159 '

13) Click OK

14) Click the Add Filter link again

15) Name this filter “Last 2 hours”


cast(substring(cast(getdate ( ) - [Audit].[Get CM-REQ-4159 Errors for last 2 Hours].[TIME STAMP] ,varchar(100)),1,11),integer) = 0

and

cast(substring(cast(getdate ( ) - [Audit].[Get CM-REQ-4159 Errors for last 2 Hours].[TIME STAMP] ,varchar(100)),11,3),integer) < 2



17) Click OK



18) Select the Test tab and then Test Sample

If any CM-REQ-4159 error is thrown in the last 2 hours they will appear in this query.

Note: this error is thrown when a report is run but the database connection cannot be found.

You may not receive any rows.

19) Click OK



20) Drag the new query above the Database View.

21) Publish the package to the portal and proceed to Event Studio to create an event which will check the Error Count query item to see if we have exceeded

the threshold. In this case the Event will be [Error Count] > 4 and if this happens we will send an email notification.



3 Create the Event Step by Step

1) Open Event Studio against the Audit package.

2) From the ‘Insurable Objects’ panel drag in the [Error Count] field and add > 4.

3) Click on the small arrow next to the ‘Add a task’ near the top of the ‘I want to…’ panel and select email.

This will bring up the ‘Specify the email to Send’ window.

4) Enter the recipient information for the people who should be notified.



5) Save the event to the portal. As “Check CM-REQ-4159 Workshop”.

This event can be then scheduled to run on a specified interval.

Each time the event is run, if there are more that 4 CM-REQ-4159 errors within the past 2 hours then the email will be sent to the indicated person and the necessary actions can be taken.



Notes:

The Time filter

cast(getdate ( ) - [Audit].[Get CM-REQ-4159 Errors for last 2 Hours].[TIME

STAMP] ,varchar(100)) will generate a string of the following format

000000000 01:55:55.467000000

This represents 0days 1hour 55 minutes 55 seconds …

substring(000000000 01:55:55.467000000, 1 , 11) would give us ‘000000000’

and casted to an integer we have the number of days, 0 in this case

substring(000000000 01:55:55.467000000, 11 , 3) would give us ‘ 01’ and

casted to an integer we have the number of hours, 1 in this case

substring(000000000 01:55:55.467000000, 15 , 2) would give us ‘’55’ and

casted to an integer we have the number of minutes, 55 in this case

Using this technique the filter can be constructed as desired.

CM-REQ-4159 Error

This error will be thrown when a report is run but the database connection cannot be found.

This is easy to setup for testing.

Rename a data source and schedule a report to run which requires this data source.

monitoring the audit database using event...

Documents