Understanding The Audit Database In SAP BI 4

Transcription

Understanding the AuditDatabase in SAP BI 4.xadam@alangeconsulting.com

RESOURCEShttp://help.sap.com/bobip42See: Business Intelligence PlatformAdministrator Guide (Chapters 23 – 24)ADAM@ALANGECONSULTING.COM

SAP WEBINARhttps://youtu.be/ll9G2JEWb9QSAP Business Intelligence 4.2: New AuditingUniverse and ReportsADAM@ALANGECONSULTING.COM

WHAT IS AUDITING?The SAP BI platform can record activity on thesystem, such as:– Which users are logging in and when?– Which reports are being run and by whom?– Who deleted an object?– Who is creating or modifying reports?– And so on Does not record system content, security,metadataADAM@ALANGECONSULTING.COM

AUDIT CMSThe audit database records activity.The CMS database records content andconfigurations.ADAM@ALANGECONSULTING.COM

ODBCConfigure 32-bit and 64-bit ODBCconnections to the audit databaseADAM@ALANGECONSULTING.COM

AUDIT UNIVERSEDownload auditing universe and reports (LCMBIARfiles) and install via Promotion Management in ence-documents-for-bi41/ADAM@ALANGECONSULTING.COM

AUDIT UNIVERSERetrieve a list of Web Intelligence reports viewed(i.e. opened) since February 1, 2018.ADAM@ALANGECONSULTING.COM

AUDIT UNIVERSEList of users logged in since January 1, 2018.ADAM@ALANGECONSULTING.COM

AUDIT UNIVERSEList of users from the Finance Universe Group whohave logged in since January 1, 2018.ADAM@ALANGECONSULTING.COM

AUDIT DATABASE TABLES *ADS Event: Main log tableADS Event Type Str: View, Delete, Logon, etc.ADS Object Type Str: User, Web Intelligence, etc.ADS User: Table of users and user namesADS Event Detail: Supplemental information abouteach transaction, e.g. universe name, user group* This is a partial list of tables in the Audit (ADS) database

AUDIT DATABASE TABLES *ADS EVENT* This is a partial list of tables in the Audit (ADS) database

AUDIT DATABASE TABLES *ADS EVENT TYPE STRADS EVENT* This is a partial list of tables in the Audit (ADS) database

AUDIT DATABASE TABLES *ADS EVENT TYPE STRADS OBJECT TYPE STRADS EVENT* This is a partial list of tables in the Audit (ADS) database

AUDIT DATABASE TABLES *ADS EVENT TYPE STRADS OBJECT TYPE STRADS EVENTADS USER* This is a partial list of tables in the Audit (ADS) database

AUDIT DATABASE TABLES *ADS EVENT TYPE STRADS OBJECT TYPE STRADS EVENTADS USERADS EVENT DETAIL* This is a partial list of tables in the Audit (ADS) database

AUDIT DATABASE TABLES *ADS EVENT TYPE STRADS EVENTADS USERADS OBJECT TYPE STRADS EVENT DETAILTYPE STRADS EVENT DETAIL* This is a partial list of tables in the Audit (ADS) database

AUDIT SQL EXAMPLES *SELECTE.User Name,MAX(E.Start Time) Most Recent Logon,COUNT(*) LoginsFROMADS EVENT E INNER JOINADS EVENT TYPE STR T ON E.Event Type ID T.Event Type ID AND T.Language 'EN'WHERET.Event Type Name 'Logon'AND E.User Name ''AND E.User Name IS NOT NULLAND E.Start Time CONVERT(DATETIME,'11/01/2017',101)GROUP BYE.User Name* Syntax applicable to SQL Anywhere

AUDIT SQL EXAMPLES *SELECTE.Object ID,E.Object Name Report Name,FOLDER1.FOLDER NAME ISNULL(' ' FOLDER2.FOLDER NAME, '') Folder,COUNT(*) Runs,COUNT(DISTINCT E.User Name) Users,MAX(CAST(FLOOR(CAST(E.Start Time AS FLOAT)) AS DATETIME)) Last OpenedFROMADS EVENT E INNER JOINADS EVENT TYPE STR T ON E.Event Type ID T.Event Type ID AND T.Language 'EN' INNER JOINADS OBJECT TYPE STR O ON E.Object Type ID O.Object Type ID AND O.Language 'EN' LEFT JOIN(SELECT F1.Object ID, MAX(F1.Object Name) FOLDER NAMEFROM ADS EVENT F1 INNER JOIN ADS OBJECT TYPE STR O1 ON F1.Object Type ID O1.Object Type ID AND O1.Language 'EN'WHERE O1.Object Type Name LIKE '%Folder%'GROUP BY F1.Object ID) FOLDER1 ON E.Top Folder ID FOLDER1.Object ID LEFT JOIN(SELECT F1.Object ID, MAX(F1.Object Name) FOLDER NAMEFROM ADS EVENT F1 INNER JOIN ADS OBJECT TYPE STR O1 ON F1.Object Type ID O1.Object Type ID AND O1.Language 'EN'WHERE O1.Object Type Name LIKE '%Folder%'GROUP BY F1.Object ID) FOLDER2 ON E.Folder ID FOLDER2.Object IDWHEREO.Object Type Name 'Web Intelligence'AND T.Event Type Name 'View'GROUP BYE.Object ID,E.Object Name,FOLDER1.FOLDER NAME ISNULL(' ' FOLDER2.FOLDER NAME, '')ORDER BYCOUNT(*) DESC* Syntax applicable to SQL Anywhere

REPORT COMMENTARYComments posted on Web Intelligence reports arestored in the Commentary Master tableADAM@ALANGECONSULTING.COM

NEXT STEPS1) Download and review BI PlatformAdmin Guide (Chapters 23 – 24)2) Create 32-bit and 64-bit ODBC3) Set auditing parameters in CMC4) Install SAP auditing universe andsample reportsADAM@ALANGECONSULTING.COM

Q&AADAM@ALANGECONSULTING.COM

ADAM@ALANGECONSULTING.COM. AUDIT DATABASE TABLES *. ADS_Event:Main log table. ADS_Event_Type_Str: View, Delete, Logon, etc. ADS_Object_Type_Str: User, Web Intelligence, etc. ADS_User:Table of users and user names. ADS_Event_Detail: Supplemental information about each transaction, e.g. universe name, user group.File Size: 1MBPage Count: 24