Tag Archives: Report

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

SCCM 2012: Diskspace Report sorted by Freespace Percentage

System Center admins often get asked for disk space reports.
Depending on the discovery settings the data can be more current in SCOM or SCCM.
So you need to decide which datasource you use.

I have created a report for System Center Configuration Manager 2012, which lists Total Disk Space (MB), Total Free Space (MB), Total Used Space (MB), Total Free Space Percent and Total Used Space Percent.

It sorts by Total Free Space Percent and colour codes the output with this rule:

< 20 %: red
< 40 %: orange
Rest: green

You can select any device collection.

diskspacereport

The report can be downloaded here.