Transcript Reports - Schd.ws

Got Reports?
Let me show you how in SCCM
Santos Martinez
@ConfigNinja
http://aka.ms/ConfigNinja
Brett Bennett
@texasmcse
http://aka.ms/breben
Santos Martinez
@ConfigNinja
5 SQL MVP
Senior Premier
Field Engineer at
Microsoft (five
years at MS)
#MMSMOA
11 Years of
Experience and
Fourth Degree
Black Belt
2 ConfigMgr MVP
co-author of
Mastering
series
Shihan Sensei
TaiFu Shoi KarateDo (Ninja)
Brett Bennett
@texasmcse
Senior Premier
Field Engineer at
Microsoft (10
years at MS)
#MMSMOA
Have worked with
SMS/SCCM in
various capacities
since SMS 1.0
Beta
co-author of
Mastering
series
We are giving away two copies
during this session
Get your book signed!
ground·work [ˈgroundˌwərk]
NOUN : : “preliminary or basic work”
"an SCCM admin should ensure that he/she has completed
the groundwork before writing awesome reports”
SQL Reporting Services
The CM2012 Reporting Services Point role uses SQL
Reporting Services (SRS)
SRS is part of Microsoft SQL Server
SRS has many benefits:
• SRS used in many products
• Utilizes standardized query language (T-SQL)
• SRS is scalable, features high performance
• Your SQL DBA’s probably already use SRS and understand it
• Reports can have subscriptions & can be exported to a variety of
file formats
SQL Reporting Services (continued)
A CM2012 hierarchy can have multiple reporting
services point
Reports are not replicated to other sites in the
hierarchy
Reports run against the local CM database
Optimizing SQL
Make sure SQL server is configured properly and operating at
peak performance
Watch for CM database fragmentation (scheduled reindex)
Check SQL minimum and maximum memory configuration
• Minimum: 8192MB
• Maximum: 50% of total RAM for co-located SQL, 80% of total for
remote SQL
Add additional tempdb files (one per CPU, up to 8 files)
Check CM database and transaction log autogrow settings
See our session “Talking Databases” at 3PM in Lakes D today for
more info on CM database tuning and configuration
frame·work [ˈfrāmˌwərk]
NOUN : : “an essential supporting structure of a building, vehicle, or
object (or report!)”
"an SCCM admin should ensure that he/she understands the SCCM
report framework before writing awesome reports”
WQL vs T-SQL
WMI Query Language (or WQL) is a Microsoft implementation
of a query language for the Common Information Model (CIM)
WQL is used in CM2012
Transact-SQL (or T-SQL) is a Microsoft extension to the Standard
Query Language originally developed by IBM
T-SQL is used in SQL server
They are both SQL queries so the commands are
interchangeable, right?
WQL vs T-SQL (continued)
Scenario: You created a query in CM2012 that you
want to use as the basis of a new report. How?
Run the query in CM2012 console
Use the SMS Provider log (smsprov.log) to see the
translation from WQL to T-SQL
Test the T-SQL query in Query Analyzer in SQL
Management Studio
Use the T-SQL query in the dataset in Report Builder
WQL vs T-SQL
WQL
select SMS_R_System.Name, SMS_R_System.OperatingSystemNameandVersion from
SMS_R_System where SMS_R_System.OperatingSystemNameandVersion like "%Windows
10%"
T-SQL
select all SMS_R_System.Name0,SMS_R_System.Operating_System_Name_and0 from
vSMS_R_System AS SMS_R_System where SMS_R_System.Operating_System_Name_and0
like N'%Windows 10%'
Tables vs Views
SQL tables are used to store data
SQL views are virtual tables, optimized for accessing
data via queries, reports
Always use views* when creating queries for reports,
never tables
• Microsoft does not support CM reports based on tables
• Database tables may be renamed during upgrades, views are
typically not
* (or RBAC functions, more on that soon)
What about RBAC?
Role-Based Access Control (RBAC) is key feature in
CM2012
RBAC uses security role, scope and collections to
determine which CM objects an admin can access
CM2012 RTM introduced RBAC in the console
CM2012 R2 introduced RBAC for reports
All native reports in CM2012 R2 are RBAC-enabled
What about RBAC? (continued)
When creating reports, you will need to decide:
Is report security a concern at your company?
Demo
RBAC vs non-RBAC Reports
User: Joe Admin
•
Joe’s security Role – Operations Administrator
•
Joe’s Scope – Default
•
Joe’s Collections – Windows 8
What about RBAC? (continued)
When creating reports, you will need to decide:
Is report security a concern at your company?
If YES, use RBAC functions when creating queries for
reports (example: fn_rbac_gs_operating_system)
If NO, use SQL views when creating queries for reports
(example: v_gs_operating_system)
What about RBAC? (continued)
Use View Dependencies in SQL Server Management Studio (SSMS)
to analyze the relationship between a function and a view
Creating the Query
Query tools
•
•
•
•
Query tool in SQL Server Management Studio (SSMS)
Query Designer in SSMS
Notepad (if you are a query expert
) and then copy/paste to Report Builder
FlySpeed SQL Queryhttp://www.activedbsoft.com/overview-querytool.html
Verify your query is optimized, efficient
• SET STATISTICS IO ON
• SET STATISTICS TIME ON
Use these tools that are included in the query tool in SSMS for additional
insight:
• Analyze query in Database Tuning Advisor
• Include Actual Execution Plan
• Include Client Statistics
SQL Sentry Plan Explorer is a third-party tool for viewing SQL performance (sqlsentry.com)
Report Creation Tools
What tools can I use to create reports?
• Report Builder 3.0
• Business Intelligence Development Studio (BIDS)
• SQL Server Data Tools
Which one should I use?
• Report Builder 3.0 is available as a standalone app and
ClickOnce
• Report Builder more intuitive, may be easier to understand
and use
Now that you understand the groundwork and the
framework we can start the real work of creating
reports in CM2012
real work [ˈrē(ə)l] [wərk]
ADJECTIVE (and a NOUN) : : “this is where heavy lifting may be
involved”
"an SCCM admin should ensure that everything else is in place
before starting the real work of creating awesome reports”
“I need you to create a report that shows….”
1. Does the data exist in the database? If not, can it/should it be added?
“Is it possible to get a list of all processes on clients?” “Uhm, yes, but….”
2. Does one of the 400+ reports included in CM2012 meet the requirement?
3. Is one of the included reports close and can be copied and modified to meet the
requirement?
4. BING search for a report that someone else (product team, MVP, myITforum.com
member, etc) created and shared. Likely you’ll find something close!
5. Create it!
Identify the views/RBAC functions that contain the data you will need
If you need help with the query, buy your SQL DBA a cup of coffee!
Microsoft Confidential
23
Demo
Overview of report structure
Creating the Report
Identify the views/functions that contain the data
you need and perfect the query (queries) first
Don’t expect a report to make a ‘bad’ query
better
Spend 90% of
your time here
Use parameters to make the report more user
friendly
Use the included expressions for page count,
date, report name, etc
Add images, color schemes, change fonts so it
Spend the rest here
matches company branding
T-SQL Query Basics
The available views for CM2012 are documented on
TechNet:
https://technet.microsoft.com/en-us/library/dn581978.aspx
A SQL query a colleague documented:
http://blogs.technet.com/b/configmgr_geek_speak/archive/2013/04/05/c
onfiguration-manager-2012-sp1-all-inclusive-sql-database-viewslisting.aspx
Most of the RBAC function/view names are fairly selfexplanatory
Creating T-SQL Queries
Q1
SELECT * FROM V_GS_OPERATING_SYSTEM
Q2
SELECT CAPTION0 FROM V_GS_OPERATING_SYSTEM
Q3
SELECT CAPTION0 AS OS FROM V_GS_OPERATING_SYSTEM
Q4
SELECT CAPTION0 AS OS FROM V_GS_OPERATING_SYSTEM ORDER BY OS
Q5
SELECT CAPTION0 FROM V_GS_OPERATING_SYSTEM WHERE CAPTION0 LIKE '%server%'
That’s great, but what about the computer name?!
Creating T-SQL Queries
Q6
SELECT * FROM V_R_SYSTEM
Q7
SELECT * FROM V_GS_OPERATING_SYSTEM
Q8
SELECT v_R_System.Name0, v_GS_OPERATING_SYSTEM.Caption0
FROM v_R_System INNER JOIN v_GS_OPERATING_SYSTEM ON v_R_System.ResourceID =
v_GS_OPERATING_SYSTEM.ResourceID
Demo
Some SQL query basics
Demo
Basic Report
List computer name with Installed Apps
Demo
Basic Report with parameters
List computer name with Installed Apps
Demo
Report Manager
Discuss…
Ask your questions-real world answers!
Plenty of time to engage, share knowledge.
Evaluations: Please provide session feedback by clicking the EVAL button in the scheduler app (also
download slides). One lucky winner will receive a free ticket to the next MMS!
SPONSORS
Session Title: