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