Tag Archives: Reporting

SCSM 2012: Asset Management Part 5 – Reports

This is the fifth part of the blog series about my Asset Management solution for SCSM 2012 R2.
Part 1: General overview
Part 2: Authoring – Classes and Relationships
Part 3: Folders and Views
Part 4: Authoring – Forms
Part 6: Runbook/Automation details

This part covers SSRS reports to export the data out of the SCSM database.

The reports are connecting to the ServiceManager database and not the DataWarehouse, so that also environments without a DW can use this.

First create your SCSM database DataSource and enter the SQL server instance where your ServiceManager database is installed.

scsmds

You can import my published reports or you create your own based on this example.

Create a new report and use the table wizard or create a blank report. You need to define a DataSet to retrieve the data.

The shown details of the DataSet belong to the All Computer Assets report.

The DataSet has a lot of joins because of the class structure and that some properties are not in the base class but in the sub classes. That is also the reason why the report takes a while to run.

with tab1 as
(
select
AssetID_DFC39F85_F244_B890_ABE1_434092CE4CCF as AssitID,
DS2.DisplayName as Region,
DS.DisplayName as Country, DS1.DisplayName as Site,
DS3.DisplayName as Manufacturer,
A.WarrantySLA_2436F00C_8311_69F9_51BC_4A117865E691 as WarrantySLA,
A.WarrantyExpirationDate_9F77D31A_5E17_203A_9A1C_3053CA0CB5BD as WarrantyExpire,
A.EquipmentCost_B0AD0B19_CE43_82BD_21BD_4AF08B32F0E5 as EquipmentCost,
A.MaintenanceContractReferenceNumber_7E79B4BD_24FE_2FCA_5B99_AD07D07EF49C as MaintNumber,
A.MaintenanceExpirationDate_8D4D2459_1179_1448_7725_01378A3A9ADE as MaintExpire,
DS4.DisplayName as AssetStatus,
A.AssetTag_323C1952_001D_7A22_769C_75E87571AC00 as AssetTag,
A.SerialNumber_B6972540_76A0_783B_4D52_50550ACBB70D as SerialNumber,
A.GoodsReceivedDate_EA1CE6FE_E807_7443_DBE6_30668AC97EEF as GoodsReceived,
DS5.DisplayName as Model,
DS6.DisplayName as Type,
DS7.DisplayName as Category,
usr.DisplayName as Custodian,
usr.UserName_6AF77E23_669B_123F_B392_323C17097BBD as CustodianID
from
MT_AssetManagementBaseClass as A with (NOLOCK)
inner join MT_ComputerAsset as C on A.BaseManagedEntityId = C.BaseManagedEntityId
left join dbo.DisplayStringView as DS2 with (NOLOCK) on A.Region_CC2529A0_A250_14EA_545F_64690C2641AD = DS2.MPElementId
left join dbo.DisplayStringView as DS with (NOLOCK) on A.Country_E7A6DA1E_DAA6_CEFF_0359_71D21E300887 = DS.MPElementID
left join dbo.DisplayStringView as DS1 with (NOLOCK) on A.Site_EF2272FC_B5F0_4FF4_19DB_873308FD9E21 = DS1.MPElementID
left join dbo.DisplayStringView as DS3 with (NOLOCK) on A.AssetManufacturer_C56091BA_FDCE_AA2A_F1BD_C6DE0483F1E5 = DS3. MPElementId
left join dbo.DisplayStringView as DS4 with (NOLOCK) on A.AssetStatus_B6E7674B_684A_040D_30B8_D1B42CCB3BC6 = DS4.MPElementId
left join dbo.DisplayStringView as DS5 with (NOLOCK) on C.AssetModel_9044D038_1D8D_F1C3_62D0_689A4FB335B6 = DS5.MPElementId
left join dbo.DisplayStringView as DS6 with (NOLOCK) on C.Type_91F17DA4_E885_2295_6A8F_99454001961A = DS6.MPElementId
left join dbo.DisplayStringView as DS7 with (NOLOCK) on C.Category_7A7B2210_04E4_DCB3_D669_401D6E5652FD = DS7.MPElementId
left outer join relationship rel with (nolock) on rel.SourceEntityId = A.BaseManagedEntityId and rel.RelationshipTypeId=’27C08AC8-422E-B3DB-DE9E-1AC2C3D351D7′ — AssetToCustodian Relationship
and rel.IsDeleted=0
left outer join MTV_System$Domain$User usr with (nolock) on rel.TargetEntityId = usr.BaseManagedEntityId
where DS4.LanguageCode = ‘ENU’
)
select distinct * from tabsq

The result looks like this: (All Computer Assets)
allcomputerassetsreport

The solution has a report for all four asset types like (Computer Asset, Peripheral, Server Infrastructure Asset and Network Infrastructure Asset). The DataSet differs mainly in the selected table, in this example it is the MT_ComputerAsset.

There is also one additional report to list all Computer Assets owned by a Custodian:
computerassetsbycustodian

You can find all reports on github.

reports

Advertisements

Microsoft System Center Reporting Cookbook available soon

A new System Center book is on the horizon which covers the very important reporting topic. It will be published Friday 27th. You can find the link to the book and more information about it on the blog of Steve Buchanan, MVP and technical reviewer of the book.

Why is this book special?

Reporting is essential in the System Center world. What is for example Sccm without patch compliance reports? But where can you find good information about how to design System Center reports besides searching the web? This book gives you guidance with easy to follow recipes and a lot of useful information about setup, report design and other options besides SSRS like PowerPivot.

A big thank from me goes to Sam Erskine, one of the authors, who had the idea for the book. He managed the publication from the beginning to the end and it is really his baby. He made it possible that I was a technical reviewer of this book, that I saw how it grew and I am proud as a nurse which helped to bring a baby to live, that I had a small part in it.

So buy it, read it and share it ;-).

SCOM Tipp: Adding a Relative Date Time Picker to a SCOM report

I would like to whish you all a happy new year as this is my first post in 2014!

One of the topics a SCOM administrator has to deal with is reporting. You probably already have created one or more custom reports. Most of the documentation about this is still from SCOM 2007 R2 – which also works for 2012 – and references to basic date/time fields, where you can enter fixed dates.

Standard date/time picker:
standard picker

Here are some authoring examples:
http://technet.microsoft.com/en-us/library/hh528528.aspx
http://blogs.technet.com/b/jonathanalmquist/archive/2011/01/03/custom-report-authoring-for-beginners.aspx
http://thoughtsonopsmgr.blogspot.de/2011/08/my-first-little-report-part-i-lets-make.html

If you have to run a report on a monthly basis you perhaps also want to be able to schedule the report and use relative dates, but that is not possible with standard startdate and enddate parameters.

Relative date/time picker:
relative picker

You need to follow this Technet blog to add a relative date time picker: http://technet.microsoft.com/en-us/library/gg697751.aspx. Specially check the section “Example: Adding a Relative Date Time Picker to the Alert Report“.
The article describes well what needs to be done to change a standard report into a report with relative date time.

Some things I was struggeling with:

  • General: You can add all necessary parts through direct XML editing, you only need to know where to add them. => see Sample.Reports.Relative.xml which will help you with that.
  • Replace StartDate/Enddate with relative Parameters:
    Replace the @Startdate and @Enddate values in the queries with CONVERT(DATETIME, CONVERT(VARCHAR, @StartDate, 101)) and CONVERT(DATETIME, CONVERT(VARCHAR, @EndDate, 101)).
    After replacing your startdate and enddate with the new relative parameters, search for your old parameter name and check that you have not used it somewhere, where it was not mentioned in the article!
  • Controls in the Parameter block: The article only describes the parameters, which are needed for the relative date time picker, but you also will need controls if you have additional fields, otherwise you only see the date time fields.  Here is the list of common report controls.
    The Parameter Block is always direct before the report in the XML => see Sample.Reports.Relative.xml

I have also created two sample management packs with one sample report in it which will show the differences.

You can download the sample report management packs here.