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.
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
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
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
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)
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:
You can find all reports on github.