Creating Custom SCCM Reports Introduction Michael Underwood Email

Creating Custom SCCM Reports

Introduction • Michael Underwood • E-mail: mpunderw@ncsu. edu

Why Do We Need Custom Reports? • Lots of default reports – https: //docs. microsoft. com/enus/sccm/core/servers/manage/list-of-reports – Reports can be difficult to find • Default reports are not always useful – Targeted more towards organizations with central IT – Reports don’t always have all of the information needed • Reports are not targeted for management – Reports more technical focused – Managers want summaries with charts and graphs

Tools Needed • SQL Server Management Studio • Report Builder vs. Visual Studio vs. Power BI – Report Builder • Free • Easy to understand – Visual Studio • Report Designer feature • Favored by more experienced report builders • Allows for creation of projects and templates – Power BI • Stand alone version free, Pro version included with A 5 license • More suited for bash boards • Preconfigured dash boards available for download

Never Write Queries Against The Production Database • Poorly written queries can bring down a site • Do you back up your site? Do you test your backups? – Site Maintenance Task – SQL backup plans • Ola Hallengren • https: //ola. hallengren. com/ – SQL Server Developer – Should be testing restores – Automate restores

Only Write Queries Against SQL Views • • Database consist of tables and views SCCM database is very normalized Views are summarizations of tables View naming convention – GS – Current – HS – Historical

Where to Find Information? • Look at default reports for inspiration – Most queries use a custom function to RBAC • List of views per version – https: //gallery. technet. microsoft. com/SCCMConfigmgr-2012 -R 2 -SQL-5 fefdd 3 b • Explanation of SQL Views – https: //technet. microsoft. com/enus/library/dd 334611. aspx – 2007, old but still useful information • Sample Queries – https: //technet. microsoft. com/enus/library/dd 334590. aspx

Create a Style Guide • • • Doesn’t have to very formal Naming Conventions – For reports and columns Things to include – Format of the queries • Makes queries easier to read • Do formatting in query or report – Aliases • Make consistent across queries • Don’t use aliases – Use Consistent Views • Installed Software vs. Add/Remove Programs • Provides SIMILAR data but not the same, and can lead to inconsistencies in reports – Date formats • 10/01/2018 02: 00 PM – Convert to human readable • 1’s and 0’s – Common columns • Computer name, operating system, OU, etc. – Other • Column width • Sortable • Alternate line highlighting

Always Print Reports • People, especially managers like to print reports • Verify information placement – All columns should fit on one sheet • Always have the date it was printed on in the footer – Says when the report was run • Always customize reports – Logos or other branding

Where to Publish • If you publish to your current reporting point organize them in a way that make them easier to find – NCSU • General • Malware • MBAM • Make sure to backup custom reports • Create custom SQL Server Reporting Service Server – Don’t do this, WAY too complicated

How to Make Report • Review Reports • Microsoft training class – Querying Data with Transact-SQL

Questions, Comments, or Concerns?