1 / 24

User License Auditing Using the .NET SDK

User License Auditing Using the .NET SDK. Dell Stinnett, BOCP-BOE XIr2 Reporting Manager Ryla, Inc. Learning Points. It is fairly simple to access user license and security information through the SAP BusinessObjects SDK query language.

jody
Download Presentation

User License Auditing Using the .NET SDK

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. User License Auditing Using the .NET SDK Dell Stinnett, BOCP-BOE XIr2 Reporting Manager Ryla, Inc.

  2. Learning Points • It is fairly simple to access user license and security information through the SAP BusinessObjects SDK query language. • Data from multiple CMS queries can be combined to provide meaningful insight into user and security setup in SAP BusinessObjects. • There are easy ways to use in-memory data sets as a data source for Crystal Reports

  3. Agenda • Introduction • User Auditing Requirements • In Memory Dataset • Load Data • View Data in Crystal • Conclusion • Q&A

  4. Introduction • Ryla, Inc is a leading domestic provider of Customer Contact Solutions to Fortune 500 companies, government agencies and nonprofit organizations • Atlanta Business Chronicle's #1 Fastest Growing Firm in 2009 • On Inc. 5000’s Fastest Growing Private Companies list for four consecutive years, with a 2010 rank of 520. • SAP BusinessObjects Edge 3.1 • @240 total users • 20 Named User licenses • 20 Concurrent User Licenses • @700 active reports • Most reports delivered by e-mail • InfoView login required for on-demand reports and some on-demand drill-down reports.

  5. Auditing Requirements • Quarterly Audits • User Audit • Identify license type – Named or Concurrent • Get the timestamp of most recent activity in InfoView • Validate that all users are current Ryla Employees • Group Audit • Validate user groups against current project assignment • Validate the reports to which each group has access

  6. Why Not Use the CMC or Query Builder? • No hard-copy or historical views. • No ability to see the “big picture” • Can see all users assigned to a single group • Can see all groups assigned to a single user • Can see last logon for a single user • Can’t combine it all into a single view of the data

  7. BOECommon Class • Common functions for interacting with BusinessObjects • CMS Login • Translate Name to SI ID and vice versa • Build InfoView path to a report • Get all reports in a folder • And much more! • Reusable so that the same code doesn’t exist in multiple places • It made the upgrade from XI r2 to XI 3.1 much less complicated.

  8. In Memory DataSet • Four tables in an ADO.NET DataSet

  9. Load Data • Data comes from two places • CMS • Audit Database • Three types of reports • By User • By Group • By Report • User and Group data is always loaded • Report data is only loaded if required

  10. Load Data Logic

  11. Get User Data from CMS string query = "Select SI_NAME, SI_USERFULLNAME, SI_ID, SI_NAMEDUSER, " + "SI_LASTLOGONTIME, SI_DESCRIPTION " + "From CI_SYSTEMOBJECTS " + "Where SI_KIND='User' and SI_NAME!='Administrator' " + "and SI_NAME!='Guest' " + "and SI_NAME!='PMUser' and SI_NAME!='QaaWSServletPrincipal' " + "Order by SI_NAME"; using (InfoObjects users = _common.BOEInfoStore.Query(query)) { for (int i = 1; i <= users.Count; i++) { using (User userData = (User)users[i]) { loadUserInfo(userData); } } }

  12. Get User Data from CMS (continued) private void loadUserInfo(User userData) { bool namedUser = (userData.Connection == CeConnectionType.ceConnectionNamed); AddUserRow(userData.Title, userData.FullName, namedUser, userData.Description); //get groups user is assigned to string query = "Select SI_ID, SI_USERGROUPS " + "from CI_SYSTEMOBJECTS where SI_ID=" + userData.ID.ToString(); using (InfoObjects userInfo = _common.BOEInfoStore.Query(query)) { if (userInfo.Count > 0) AddGroupRow(userInfo[1]); } }

  13. Get Last Activty Date 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.User_Name Is Not Null " + " AND AUDIT_EVENT.User_Name != 'Administrator' " + " AND ltrim(rtrim(AUDIT_EVENT.User_Name)) != '' " + "GROUP BY AUDIT_EVENT.User_Name ORDER BY AUDIT_EVENT.User_Name";

  14. Get Report Info From CMS string query = "Select SI_ID, SI_NAME, SI_PARENTID, SI_KIND " + "From CI_INFOOBJECTS " + "Where SI_KIND in ('CrystalReport','Webi','Excel'," + "'PDF','Rtf','Txt','Universe','Word') " + " and SI_INSTANCE_OBJECT<1 " + "order by SI_NAME, SI_ID";

  15. Get Report Info From CMS (continued) using (InfoObject rpt = rpts[i]) { dsAuditInfo.reportTable.AddreportTableRow(rpt.ID, rpt.Title, rpt.Kind, _common.GetPathFromFolderID(rpt.ParentID)); string role; using (SecurityInfo si = rpt.SecurityInfo) { for (int j = 1; j <= si.ObjectPrincipals.Count; j++) { if (si.ObjectPrincipals[j].Role != CeRole.ceRoleNoAccess) { switch (si.ObjectPrincipals[j].Role) { case CeRole.ceRoleAdvanced: role = "Advanced"; break; …

  16. Report Creation • Select tables from Project Data\ADO.NET DataSets • Seven reports • User List • Users with Groups • Users with Reports • Users by Group • Reports by Group • Groups by Report • Users by Report

  17. Report Creation

  18. Report Viewer • Single form with overloaded constructor • Pass ADO.NET DataSet as a parameter to constructor • Load report based on selections in GUI.

  19. Report Viewer – Overridden Constructor public BOEAuditRptViewer(DataSet dsAuditInfo, AuditRptType rptType) { InitializeComponent(); switch (rptType) { case AuditRptType.UserList: ConfigureUserListReport(dsAuditInfo); break; case AuditRptType.UserGroups: ConfigureUserGroupsRpt(dsAuditInfo); break; case AuditRptType.UserReports: ConfigureUserRptsRpt(dsAuditInfo); break; case … } }

  20. BOEUserAudit Application - Demo

  21. Best Practices • If you’re going to be writing more than one utility, separate out “common” functions into their own assembly. • Use a single report viewer form to display different reports by overriding the constructor.

  22. Key Learnings • It is fairly simple to access user license and security information through the SAP BusinessObjects SDK query language. • Data from multiple CMS queries can be combined to provide meaningful insight into user and security setup in SAP BusinessObjects. • There are easy ways to use in-memory data sets as a data source for Crystal Reports

  23. Q&A Contact information: Dell Stinnett Email: dellstinnett@gmail.com Website: www.dellstinnett.com

  24. ] • Thank you for participating. Please remember to complete and return your evaluation form following this session. For ongoing education on this area of focus, visit the Year-Round Community page at www.asug.com/yrc [ SESSION CODE: 411

More Related