Skip to Content
Author's profile photo MARY MARGARET

Creating Custom Audit reports in WebIntelligence (WebI in BI Launchpad)

Introduction:

Just recently, I was tasked to develop a Business Objects Webi report that displays the list of all users within the system along with their last login dates. As simple as this project may sound, it involved some crucial steps and tweaks to the SAP BO Audit universe that is provided by SAP. In this blog, I will share information on how I waable to successfully accomplish my goals for creating this Webi report.

I was working with SAP BI 4.1 sp6 with a SQL server database back-end for storing the infostore and audit data.

Here is the list of steps that I had to go through in order to successfully complete the project:

Auditing services:

Please ensure that Auditing is turned on within CMC with the database connection pointing to the appropriate AUDIT database. If you skipped the AUDIT database creation during the install, you just need to create a new audit database and fill in the database details within the Audit configuration screen in CMC and the system will automatically create the necessary audit tables for storing audit information.

Downloading the AUDIT Universe:

As most of you may be already aware, the AUDIT universe and reports are not part of the standard business objects installation starting with version 4.x. In order to run audit reports, you will first need to download the AUDIT universe along with instructions to deploy the universe within your current system. Here is the link from where to install this: http://scn.sap.com/docs/DOC-53904

Develop the Webi Report:

Once you have deployed the universe to the repository. You are now ready to build audit reports. Below is an example of a query for an audit report that will “provide a monthly report for a given period that will report the following information for the prior requested period:

  • user name,

  • Date user last accessed BOBJ

  • Number of days in prior month user accessed BOBJ”

     

Step1Open Web Intelligence tool from BI Launchpad and select Universe as source: 

Step2: Select Auditing universe

Step3Select required fields along with Query filter to specify time period as shown below 

Step4Specify your time period

Step5Select “Start datetime” column and apply “MAX” function as shown in the picture

Step6Then you will get the user last accessed date as shown

Step7To convert date formats Select “User last Accessed” column->Select “Formatting” tab->Select “Numbers” sub tab and then select required Date format..

Step8Now create a new Variable to find out no of users logged in for the given period

Step9Give the formula as shown in the picture

Step10Drag the new variable into result pane as shown 

Step11In order to have the column heading as shown in the screen shot use the below formula at the heading cell

=”No of Times User Logged Between : “+ FormatDate(ToDate(UserResponse(“Events Started After”) ;””); “MM/dd/yyyy”) + ” -” + FormatDate(ToDate(UserResponse(“Events Started Before”) ;””); “MM/dd/yyyy”)

Hope it is useful…

 

Assigned Tags

      6 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Zahid Yener
      Zahid Yener

      Hi Mary,

       

      First of all, it is a nice blog. The thing is you cannot list all users and reports in the system using Auditing DB if the user is created before you create Audting DB and s/he has never been logged in to the system or nothing has done on the object. This is the case for all objects in the system if Auditing DB is created after the creation of the objects in the system.

      Instead, Admin Tools (Query Builder) or CMS database universe can  be used for this.

      https://wiki.scn.sap.com/wiki/display/BOBJ/Unlock+the+CMS+database+with+new+data+access+driver+for+BI+4.2+SP3

       

      Nice day.

      Author's profile photo Mohd Fahad
      Mohd Fahad

      Hi Zaheer,

       

      Thanks for the info.

      If i understand this correctly, is it possible to query our usecase using AdminTools, even if the AuditDatabase does not exists?

       

      Regards,

      Fahad

      Author's profile photo MARY MARGARET
      MARY MARGARET
      Blog Post Author

      Hi Zahid Yener !

       

      Thank you for your appreciation.

      Regarding the Users, what you explained is correct.

       

      Author's profile photo Dwarakanath Ramakrishnareddy
      Dwarakanath Ramakrishnareddy

      Hi Mary,

      It is very good.

      I have a small query, as per the your selection the report is not only showing the logon count, it includes the logout, run, refresh and other events
      Just you can add Event type in the report and validate.If you need only login count add the event type = Logon filter.

      Author's profile photo MARY MARGARET
      MARY MARGARET
      Blog Post Author

      Hi Dwarakanath Ramakrishnareddy,

      Thanks for the Correction....

      Author's profile photo MCGM BASIS
      MCGM BASIS

      Hi Mary,

      very Useful Blog. I have same requirement but I am getting error "specified rdbms is invalid sap Hana database". Auditing is Enabled and logs are also getting generated. but in webi report I am facing above error. I have found note how to correct the error for windows platform but my Environment is SUSE. I am not subject matter expert on this I am in basis.

      could you please help me out to resolve the same ?

      Regards,

      Jyotish