User Auditing Using the Business Objects NET SDK

User Auditing Using the Business. Objects. NET SDK Dell Stinnett, ZC Sterling COPYRIGHT © 2007 BUSINESS OBJECTS S. A.

AGENDA 1. 2. 3. 4. 5. 6. 7. SLIDE 3 Introduction User Auditing Issues In Memory Dataset Load Data View Data in Crystal Conclusion Q&A COPYRIGHT © 2007 BUSINESS OBJECTS S. A.

INTRODUCTION SLIDE 4 COPYRIGHT © 2007 BUSINESS OBJECTS S. A.

INTRODUCTION ZC Sterling A leading provider of mortgage outsourcing services Hazard Insurance Real estate tax servicing Voluntary products Customer care Best Shores Business. Objects XI R 2 Migrated from Seagate Info 7. 5 in 2006 Custom programs Named user licensing SLIDE 5 COPYRIGHT © 2007 BUSINESS OBJECTS S. A.

USER AUDITING ISSUES SLIDE 6 COPYRIGHT © 2007 BUSINESS OBJECTS S. A.

REQUIREMENTS Quarterly audit User Audit Group users by business line or department Identify last time a user logged on to Info. View Send report to department managers for review Group Audit Group by user group Users in a group Reports to which the group has access Report Audit Same report title can be in multiple folders Group by title and then by folder SLIDE 7 COPYRIGHT © 2007 BUSINESS OBJECTS S. A.

WHY NOT USE THE CMC? Limited access to CMC No hard-copy reports No ability to see “big picture” SLIDE 8 COPYRIGHT © 2007 BUSINESS OBJECTS S. A.

IN MEMORY DATASET SLIDE 9 COPYRIGHT © 2007 BUSINESS OBJECTS S. A.

IN MEMORY DATASET Four tables in an ADO. NET dataset SLIDE 10 COPYRIGHT © 2007 BUSINESS OBJECTS S. A.

LOAD DATA SLIDE 11 COPYRIGHT © 2007 BUSINESS OBJECTS S. A.

LOAD DATA Data comes from two places CMS Audit Database Three types of reports By User By Group By Report User data is always loaded. Report data is only loaded if required. SLIDE 12 COPYRIGHT © 2007 BUSINESS OBJECTS S. A.

LOAD DATA SLIDE 13 COPYRIGHT © 2007 BUSINESS OBJECTS S. A.

GET USER DATA FROM CMS string query = "Select SI_NAME, SI_USERFULLNAME, SI_ID " + "From CI_SYSTEMOBJECTS " + "Where SI_KIND='User' and SI_NAME != 'Administrator' " + " and SI_NAME != 'Guest'" + "Order by SI_NAME"; using (Info. Objects users = _common. BOEInfo. Store. Query(query)) { User user. Data = null; try { for (int i = 1; i <= users. Count; i++) { using (user. Data = (User)users[i]) { load. User. Info(user. Data); } if ((i % 10) == 0) Application. Do. Events(); } //users. count } finally { if (user. Data != null) user. Data. Dispose(); } get. Login. Info(); } //using users SLIDE 14 COPYRIGHT © 2007 BUSINESS OBJECTS S. A.

GET USER DATA FROM CMS (continued) private void load. User. Info(User user. Data) { Add. User. Row(user. Data. Title, user. Data. Full. Name, true); //get groups user is assigned to query = "Select SI_ID, SI_USERGROUPS " + "from CI_SYSTEMOBJECTS where SI_ID=" + user. Data. ID. To. String(); using (Info. Objects user. Info = _common. BOEInfo. Store. Query(query)) { if (user. Info. Count > 0) Add. Group. Row(user. Info[1]); } } SLIDE 15 COPYRIGHT © 2007 BUSINESS OBJECTS S. A.

GET LAST LOGIN FROM AUDIT DATABASE Query based on “Last Login for All Users” report that comes with the Auditing package. string query = "SELECT DISTINCT AUDIT_EVENT. User_Name, " + "max(AUDIT_EVENT. Start_Timestamp) " + "FROM AUDIT_EVENT " + "WHERE AUDIT_EVENT. Event_Type_ID In (65538)"+ "AND AUDIT_EVENT. User_Name Is Not Null " + "GROUP BY AUDIT_EVENT. User_Name"; SLIDE 16 COPYRIGHT © 2007 BUSINESS OBJECTS S. A.

GET REPORT INFO FROM CMS string query = "Select SI_ID, SI_NAME, SI_PARENTID, SI_KIND " + "From CI_INFOOBJECTS " + "Where SI_KIND in ('Crystal. Report', 'Webi', 'Excel', " + "'PDF', 'Rtf', 'Txt', 'Universe', 'Word') " + " and SI_INSTANCE_OBJECT<1 " + "order by SI_NAME, SI_ID"; SLIDE 17 COPYRIGHT © 2007 BUSINESS OBJECTS S. A.

GET REPORT INFO FROM CMS (continued) using (Info. Object rpt = rpts[i]) { ds. Audit. Info. report. Table. Addreport. Table. Row(rpt. ID, rpt. Title, rpt. Kind, _common. Get. Path. From. Folder. ID(rpt. Parent. ID)); string role; using (Security. Info si = rpt. Security. Info) { for (int j = 1; j <= si. Object. Principals. Count; j++) { if (si. Object. Principals[j]. Role != Ce. Role. ce. Role. No. Access) { switch (si. Object. Principals[j]. Role) { case Ce. Role. ce. Role. Advanced: role = "Advanced"; break; … SLIDE 18 COPYRIGHT © 2007 BUSINESS OBJECTS S. A.

VIEW DATA IN CRYSTAL SLIDE 19 COPYRIGHT © 2007 BUSINESS OBJECTS S. A.

Report Creation Select tables from Project DataADO. NET Data. Sets Six reports Users with Groups Users with Reports Users by Group Reports by Group Users by Report Groups by Report SLIDE 20 COPYRIGHT © 2007 BUSINESS OBJECTS S. A.

REPORT CREATION SLIDE 21 COPYRIGHT © 2007 BUSINESS OBJECTS S. A.

Report Viewer Single form with overloaded constructor. Pass ADO. NET Data. Set as parameter in constructor. Load report based on selections in GUI. SLIDE 22 COPYRIGHT © 2007 BUSINESS OBJECTS S. A.

BOEUser. Audit Application - Demo SLIDE 23 COPYRIGHT © 2007 BUSINESS OBJECTS S. A.

CONCLUSION The SDK provides the means to retrieve data for user auditing. It is not difficult to extract data from the CMS to use in reports. CMS data can be combined with data from other sources, such as the Auditing database, for reports “Project Data” in an ADO. NET Data. Set can be used as a data source for Crystal reports. SLIDE 24 COPYRIGHT © 2007 BUSINESS OBJECTS S. A.

Q&A Questions Dell Stinnett, Sr. Software Engineer, ZC Sterling I will repeat questions to ensure everyone can hear Contact information dell. stinnett@zcsterling. com http: //www. geocities. com/geekgurl 7/ Intro. html SLIDE 25 COPYRIGHT © 2007 BUSINESS OBJECTS S. A.