MOM Essentials 4: Extending Microsoft Operations Manager (MOM) 2005 - Part 2

1. MOM Essentials 4: Extending Microsoft Operations Manager (MOM) 2005 - Part 2 Paul Collins Microsoft UK

2. Agenda • Creating Custom Reports • Managing Server Security

3. Creating Custom Reports

4. Agenda • SQL Server 2000 Reporting Services • MOM data flow • Reporting wizard • Complex reports and large data sets • Tips and tricks • Reporting database schema • Packaging reports for distribution

5. Custom Application Browser Office Data Sources (SQL, OLE DB, XML/A, ODBC, Oracle, Custom) Security Services (NT, Passport, Custom) SQL Server 2000 Reporting Services XML Web Service Interface Report Server Report Processing Security Data Processing Delivery Rendering Output Formats (HTML, Excel, PDF, Custom) SQL Server Catalog Delivery Targets (E-mail, SharePoint, Custom)

6. SQL Server 2000 Reporting Services • Free add-on to SQLhttp://www.microsoft.com/sql/reporting/default.asp • Version for Standard and Enterprise • You can only use the version that matches the version of SQL Server you own • Designed by SQL Server team • Report creation is Visual Studio 2003 • Recently bought Active Views to make ad-hoc reporting easier • Very scalable • Very powerful • Can be used for all reporting needs – not just MOM

7. Database Reporting Database Performance Alerts/Events DTS Service Discovery Rules, etc. MOM Data Flow • Management Pack is installed Reports It contains Performance Collection Rules Event Collection Rules Alert Rules Service Discovery MOM Configuration Information • Data is collected in the MOM database • The DTS transfers the data using a Windows Scheduled Task on the Reporting Server • Reports are run on the data in the reporting database

8. Additive facts Rows are added every time by DTS Samplednumericdata=Performance Counter Alerts Events Periodic snapshot Rows are moved every time by DTS Attributes Computer Group Membership Computer to Rule Membership .. MOM Data Flow • The first run of the DTS creates the views in the reporting database- the Transaction log can grow to 2-3 times the amount of data being transferred during one run • Data is stored in the Reporting Server in two different ways MOM Reporting(SystemCenterReporting) MOM operational database(Onepoint) v

9. Reporting Overview • Based on SQL Reporting Service and the System Center Data Warehouse: • Long term data storage • Customization • Dynamic/Sophisticated reports • Per report security • Exporting data to other formats • Service specific reports out of box • Summary reports • Capacity and performance trend graphs • Operations reports • Resources • Availability and Reliability • Capabilities • View or print • Publish to Web site • Schedule generation offline

10. System Center Data Warehouse • Excellent Tool for Managing High Volumes of Data • Long term offline storage and analysis of data • Default is 13 months • In built data transformation and management functionality • Improved grooming for both data warehouse and DB • Star schema for better analytics System Center Data Warehouse Reporting

11. Reporting Improvements W Partial support R Full support

12. Pre-Requisites • Win2000 with SP4, all editions Windows 2003, all editions, XP • ASP.NET 1.1, IIS 5.0 or later installed and configured, MDAC 2.6 or higher. • SQL Server 2000 SP3a. For Windows 2003, the computer must be configured as an application server. For Windows 2003 to use the network service account to run the ReportServer service, you need SQL Server QFE 859. • Install Visual Studio 2003 • Install SQL 2000 Reporting Services according to your SQL Version • Install System Center Data Warehouse • Default website accessible through http://<servername>/Reportserver • System Center Data Warehouse Reports through http://<servername>/reports • SQL Server 2005 - Report Builder included

13. Reporting Services Walkthrough Tony Clarke Microsoft UK

14. Building a Simple Report • Building a simple report is easy using the reporting wizard • The reporting wizard allows you to select all of the required options for your report including: • Data source • SQL Query • Report look and feel • Fields to show on your report

15. A Simple Report Tony Clarke Microsoft UK

16. Report Features Print the parameters selected This is a group header. Sorting works best on this level Logo can be replaced globally Underlined infromation is a link to a detailed report The next group header is only visible after expanding

17. Creating more Complex Reports • Use the Microsoft reports as a starting point when creating more complex reports • Save an existing report to RDL and open in SQL Reporting Services • Modify the look and feel as required • Edit the report to show the data you would like to see

18. A More Complex Report Tony Clarke Microsoft UK

19. Working With Large Volumes Of DataParameter Selection

20. Working With Large Volumes Of DataShow only relevant Information • Toggle field visibility by logical group headers

21. Working With Large Volumes Of Data Tony Clarke Microsoft UK

22. Report Planning • Data collectionData should be collected by MOM that you can Report on. If not already done you must create the rules to collect the data • Using Parameters to handle large data volumesReport header allows choice of parameters • FilteringA filter is a field where the whole results are impacted • SortingAllow to sort the columns in a Report • GroupingAllows to display data in groups and reduce the amount of data presented.

23. Things not to do • Don’t create a simple 100 pages long list- develop the Report based on a scenario e.g. IIS Server Reports were planned as:“give me all IIS Servers with the supported options and capacity to decide where I host that application”or “show me what Server has which Version of ASP .NET installed” • The faster you get to your result the better – details can be on a linked Report

24. Reporting Services Settings This is the setting in Visual Studio Reporting that controls where to put the Reports on the Reporting Website Set Data Source to SCDW Reason: If you deploy the Reports you want to have the Data Source installed on the Target System. As this is the MOM Data source you can re-use it.

25. Tips and Tricks • Don’t install SQL Server 2000 Reporting Services to be the default Website – it might break other Websites • Test export the Report first to PDF, then to Excel to see the results during your development • SQL Server 2000 Reporting Services does not support multi-select in Parameters. Use Computergroups instead • Reports execute automatically when opened – watch out what you put in as Parameter defaults • SQL Server 2000 Reporting Services does not support multiple queries for a single group – if a query is getting to complex use Stored Procedures and call the from the Report

26. Tips and Tricks – contd. • Convert all UTC date from DB to Local date using this function in the SQL Query dbo.fn_ToLocalDate(Date, GETUTCDATE(), GETDATE()) • display the Server/Agent Name with Domain name attached eg. COALESCE(CD.ComputerDomain_PK+'\'+CD.ComputerName_PK,CD.ComputerName_PK) • Have a no data text available in the description of the Report. The no data areas offered by Rosetta are too small. When you collect data explain which Rules need to be enabled for this Report in the no data text. • Use “Begin Date” and “End Date” as standard parameters • Print Sort By and Sort Order as standard parameter on the Report together with all filters entered • Use SCDW as datasource name • Have a runtime of no longer than 30 sec - Users don’t like to wait

27. Querying Data • The reporting database schema is documented in the SDK

28. Reporting Schema • Dimension Tables e.g. • SC_ComputerDimension • SC_AlertLevelDimentions • Fact Tables e.g. • SC_AlertFact_Table • SC_EventFact_Table • Periodic Snapshot Fact Tables e.g. • SC_ClassAttributeInstanceFact_Table • SC_ProcessRuleMembershipFact_Table

29. Deployment of Reports • Command line tool: RptUtil.execreates .xml file which can be imported using the MOM Admin consoleThe XML file can contain 1-n Reports /action: Action - import or export. /file: Full path to the import or export file. /url: Url of the report server. /reportpath: Path to the report or report folder to be exported. /fromdsref: Name of the "from" datasource reference to fixup. /todsref: Name of the "to" datasource reference to fixup. /datasource: Name of the datasource to fixup. /dwserver: Name of the datawarehouse server used to fixup the datasource. /dwdb: Name of the datawarehouse database used to fixup the datasource. Example for a Batch file to export a Report to xml: cd "E:\Program Files\Microsoft System Center Reporting\Reporting" rptutil.exe /file:e:\myalertlatency.xml /nowarn /reportpath:"/custom reports/alert logging latency"

30. Summary • Build your queries first • Use a Reports folder outside of “Microsoft Operations Manager Reporting” • Do not overload Reports with too much data – use linked reports if necessary • Use a template so your reports have a consistent look and feel • Test your output to Excel and PDF

31. Technical Resources • Developing Custom MOM Reports • http://www.microsoft.com/technet/prodtechnol/mom/mom2005/Library/cf1e57a0-ecb1-4f42-a8ef-4d43aa3e8d44.mspx?mfr=true • Download custom management pack • http://www.microsoft.com/downloads/details.aspx?familyid=c5b42e5b-68ed-45ea-8864-a9d4087d261d&displaylang=en • Information about SQL Server views • http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_06_9mlv.asp • MOM SDK 2.0 • http://www.microsoft.com/mom/downloads/sdk/default.asp • SQL Server Reporting Services • http://www.microsoft.com/sql/reporting

32. Managing Server Security

33. Three Main Factors For Effective Application Monitoring • Exception Monitoring • What errors are occurring in my application? • Performance Monitoring • How is my application performing? • Security • Is my application secure?

34. Collecting Security Information • Is my application fully patched? • SMS, MBSA • Who is accessing my application? • Security Auditing (event log\app log) • What is my configuration • Registry, DCM

35. Types of Security data • Pro-active alerting • i.e. Critical changes to groups, multiple logon failures, rogue processes etc. • Auditing • Workstation access, file access, who made changes and when • Configuration • What is my current configuration, do I meet best practices?

36. MBSA Management Pack • Vulnerability Assessment (VA) Check • Give our customers advance, proactive, and regular notification of any Vulnerabilities • Patch Alerting • Notify MOM administrators that their servers require patching • Integrates with SMS • DOES NOT deploy or install patches • VA checks in 4 Areas • Internet Explorer • IIS Server • SQL Server • Windows • Patch Scanning • Leverages mssecure.cab

37. Security Event Log • Focus on Domain Controller Logs • Turn on auditing for logon events • Create rules based on Event ID’s • e.g. 529 (logon failure) • Use filter rules to block system accounts • i.e. NTService$ • Consolidate rules to identify attacks • i.e. 15 x event id 529 in 5 minute period may indicate an attack

38. Security Event Collection Issues • Very large volume of events can impact database size and may effect performance • Consider second management group with separate database and multi-home agents • Lots of Security Alerts may take emphasis away from operational alerts • Only display critical alerts, use reports for analysis

39. System Controls MP for MOM 2005 Auditing & Reporting Services for Windows Server Security Events

40. Compliancy Quandrum • Security Best Practices and Governmental Regulations all require some level of auditing for Security Events. • Requires Controls, Segregation of Duties and Reporting • Event Collection & Control mapping add Clarity Above diagram is an example for reference only and does not represent actual mappings

41. Windows Server Security Auditing • System Controls Management Pack • Security Event collection (Parameter Extraction) • Cross-regulation applicability (FISMA, SOX, GLBA) • Predefined Control Rules (ie Domain Admin Member Added) • Knowledge Content supplemented by Randy Franklin Smith’s Online Event Encyclopedia available at www.UltimateWindowsSecurity.com • Expanded operational views (over 50 event views) • Security Auditing Tasks (SCA, Policy Editor, secedit /analyze) • Comprehensive reporting (Detail & Summary)

42. Sample Auditing Scenarios Scenarios are supported via collection, alerting and reporting features of the SCMP

43. SCMP Benefits • Leverages MOM Infrastructure • Rapid Deployment & Extensibility • Facilitates Internal Control Auditing • Authoritative Knowledge Content • Comprehensive Reporting Services • Centralized Security Event Collection

44. Collection Rules

45. Control Rules

46. Rule Knowledge

47. Operational Views

48. SCMP Security Reports

49. SCMP Reporting

50. SCMP Demo • System Controls Event Collection • Operational Views • Custom Control Auditing • System Controls Reporting Tony Clarke Microsoft UK