Share via

Creating a Custom Report for System Center 2012 R2 Configuration Manager – Part 3

  • Article

In Part 1 it was mentioned that the dashboard will accept
certain parameters. By default, SRS will list 2 parameters per line.

For the Collection parameter, the dashboard will display the collections name but will pass its Unique ID to the datasets in a variable called ColID. 

To create this parameter, a dataset needs to be created. To create the dataset, a data source is required.

Creating the Data Source

  1. In the Report Data pane right click Data Sources--> Click Add Data Source
    1. The Data Source Properties window will appear
  2. Change the Name to CM
  3. Select Use a connection embedded in my report
  4. Select Microsoft SQL Server as the connection type
  5. Click the Build… button
  6. Enter the Server name to the SQL server
  7. Select the ConfigMgr database
  8. Click Test Connection to ensure that a connection could be established
  9. Click OK
  10. Click OK

Creating the All_Collections Data Set

  1. In the Report Data pane right click Datasets --> Click Add Dataset
    1. The Dataset Properties window will appear
  2. Change the Name to All_Collections
  3. Select Use a dataset embedded in my report
  4. Select the CM Data source
  5. In the Query box enter the following:
    select distinct c.CollectionID, c.Name from v_Collection c order by c.Name
  6. Click OK

Creating the ColID Parameter 

  1. In the Report Data pane right click the Parameters folder --> Click Add Parameter.
    1. The Report Parameter Properties window will appear
  2. Change the Name to ColID (this is spelled colid)
  3. Change the prompt to Select a collection
  4. Click Available Values
  5. Select Get values from a query from the dataset
  6. Select CollectionID for the Value field
  7. Select Name for the Label field
  8. Click Default Values
  9. Select Specify values
  10. Click the Add button
  11. Enter SMS00001 for the Value
    1. This is the Collection ID for the All Systems collection.
  12. Click OK

Your Report data pane should look like this:

Creating the other parameters

Month Parameters

Although it is possible to create datasets for the other parameters, this demonstration will use static entries.

  1. In the Report Data pane right click the
    Parameters folder --> Click Add Parameter.
  1. The Report Parameter Properties window will appear
  • Change the Name to StartMonth
  • Change the prompt to Select a month that the report will start from
  • Change the Data type to Integer
  • Click Available Values
  • Select Specify values
  • Click the Add button 12 times
  • Enter January for the Label and 1 for the Value
  • Repeat this until you have December for the Label and 12 for the Value
  • Click Default Values
  • Select Specify values
  • Click the Add button
  • Click the fx button
  1. The Expression window will appear
  • Enter =Month(today()) for the expression value
  1. This will get the number value of the month for day the report is run
  • Click OK
  • Click OK 

Repeat these steps for the EndMonth parameter with the prompt of Select a month the report will end with

Year Parameters

Similar to the Month parameter the dashboard will create static year parameters with the starting year having a slight twist to its default value.

  1. In the Report Data pane right click the Parameters folder --> Click Add Parameter.
  1. The Report Parameter Properties window will appear
  • Change the Name to StartYear
  • Change the prompt to Select a year that the report will start from
  • Change the Data type to Integer
  • Click Available Values
  • Select Specify values
  • Click the Add button 10 times
  • Enter 2004 for both the Label and Value
  • Repeat this until you have 2014for both the Label and Value
  1. You may add or remove in more entries if you want to report update older or newer than 2004
  • Click Default Values
  • Select Specify values
  • Click the Add button
  • Click the fx button
  1. The Expression window will appear
  • Enter =Year(today()) -1 to default the report to the year previous to when it is run
  • Click OK
  • Click OK

Repeat these steps for the EndYear parameter with the prompt of Select a year that the report will end with and a default value of =Year(today())

Your Report Data pane should now look like this:

Creating Other Datasets

  1. Similar to how the All_Collections dataset was created, create the All_OperatingSystems Dataset with the query of:
    select distinct c.Name [Collection Name], gsos.Caption0 [Operating System], count(gsos.Caption0) [Number of Systems]
    from v_R_System rs
    JOIN v_FullCollectionMembership fcm on rs.ResourceID = fcm.ResourceID
    JOIN v_Collection c on c.CollectionID = fcm.CollectionID
    JOIN v_GS_OPERATING_SYSTEM gsos on gsos.ResourceID = fcm.ResourceID
    WHERE c.CollectionID = @ColID
    group by c.Name, fcm.collectionID, gsos.Caption0
  2. Create another Dataset called Collection_Details with the query of:
    select distinct c.Name, count(rs.ResourceID) [Devices], (Select Count(LastScanTime) from v_UpdateScanStatus uss where datediff("d",LastScanTime, getdate()) <= 30) [Scanned last 30 Days],(Select Count(LastHWScan) from v_GS_WORKSTATION_STATUS gsws where datediff("d",LastHWScan, getdate()) <= 30) [Inventory last 30 Days]
    from v_FullCollectionMembership fcm
    JOIN v_Collection c on c.CollectionID = fcm.CollectionID
    JOIN v_R_System rs on rs.ResourceID = fcm.ResourceID
    LEFT JOIN v_GS_WORKSTATION_STATUS gsws on gsws.ResourceID = fcm.ResourceID
    where fcm.CollectionID = @ColID
    group by c.Name
  3. Create the final dataset called Software_Update_Details with the query of:
    DECLARE @StartDate datetime, @EndDate datetime
    Set @StartDate = DATEFROMPARTS(@StartYear,@StartMonth,1)
    Set @EndDate = DATEFROMPARTS(@EndYear,@EndMonth,1)
    Select ucs.CI_ID, count(ucs.CI_ID) [Count],
    CASE((status))
    When 3 Then 'Installed' When 2 Then 'Required' Else 'Not Applicable' End as 'Status', BulletinID, ArticleID,
    CASE((IsDeployed))
    When 0 Then 'No' Else 'Yes' End as 'Deployed',
    CASE((ui.IsSuperseded))
    When 0 Then 'No' Else 'Yes' End as 'Superseded',
    CASE((ui.IsExpired))
    When 0 Then 'No' Else 'Yes' End as 'Expired',
    ui.Title, ui.DatePosted, cica.CategoryInstanceName,
    CASE((ui.Severity))
    When 2 Then 'Low'
    When 6 Then 'Moderate'
    When 8 Then 'Important'
    When 10 Then 'Critical'
    Else 'NA' End as 'Severity'
    From v_Update_ComplianceStatusReported ucs
    JOIN v_UpdateInfo ui on ucs.CI_ID = ui.CI_ID
    JOIN v_CICategoryInfo_All cica JOIN v_CategoryInfo ci on cica.CategoryInstanceID = ci.CategoryInstanceID AND cica.CategoryTypeName = 'UpdateClassification' on ucs.CI_ID = cica.CI_ID
    Where resourceid IN(Select ResourceID from v_FullCollectionMembership where CollectionID = @ColID) AND ui.DatePosted BETWEEN @StartDate AND @EndDate AND ucs.status <> 1
    GROUP BY ucs.CI_ID, ucs.status, BulletinID, ArticleID, IsDeployed, ui.Title, DatePosted, cica.CategoryInstanceName, cica.CategoryInstanceID,ui.IsSuperseded, ui.Severity, ui.IsExpired
    ORDER BY ucs.status, BulletinID, ArticleID

Your Report Data pane should now look like this:

 

 

Creating a Custom Report for System Center 2012 R2 Configuration Manager – Part 1
Creating a Custom Report for System Center 2012 R2 Configuration Manager – Part 2
Creating a Custom Report for System Center 2012 R2 Configuration Manager – Part 3
Creating a Custom Report for System Center 2012 R2 Configuration Manager – Part 4
Creating a Custom Report for System Center 2012 R2 Configuration Manager – Part 5

Comments

  • Anonymous
    January 01, 2003
    Thanks Summet. I've been wanting to put a SQL 2008 compliant query but just don't seem to have the time.

    Also note that the end month is the first day of the month. Since updates come out on the 2nd Tuesday you would have to add a month to the end date dropdown.

    Once I get some time I'll post a feature to select the last day of that month..

  • Anonymous
    January 01, 2003
    My initial hunch is that you may be running this from SQL Server 2008. The DATEFROMPARTS is a function for SQL Server 2012. Are you running this on SQL 2012?

  • Anonymous
    December 10, 2013
    The comment has been removed

  • Anonymous
    December 11, 2013
    It looks like I missed the group by clause with the copy and paste and will update it. Add group by c.Name under the where clause so the query should look like this: select distinct c.Name, count(rs.ResourceID) [Devices], (Select Count(LastScanTime) from v_UpdateScanStatus uss where datediff("d",LastScanTime, getdate()) <= 30) [Scanned last 30 Days],(Select Count(LastHWScan) from v_GS_WORKSTATION_STATUS gsws where datediff("d",LastHWScan, getdate()) <= 30) [Inventory last 30 Days] from v_FullCollectionMembership fcm JOIN v_Collection c on c.CollectionID = fcm.CollectionID JOIN v_R_System rs on rs.ResourceID = fcm.ResourceID LEFT JOIN v_GS_WORKSTATION_STATUS gsws on gsws.ResourceID = fcm.ResourceID where fcm.CollectionID = '@ColID&#39; group by c.Name

  • Anonymous
    January 15, 2014
    I am getting an error when entering the Software_Update_Details dataset. After entering the query it prompts me to enter the query parameters with a list of the parameters we entered (ColID, start month and year, etc.). For all of them the parameter value is set to NULL and can be changed to blank. Both generate this error:"Could not create a list of fields for the query. Verify that you can connect to the data source and that your query syntax is correct. 'DATEFROMPARTS' is not a recognized built-in function name. 'DATEFROMPARTS' is not a recognized built-in function name.To continue without updating the fields, click OK."Any ideas?

  • Anonymous
    January 16, 2014
    Yup, that is it. Our SQL cluster is on 2008 R2.Thanks for clearing that up. Unfortunately I cannot get this whole (very cool) dashboard working. :(

  • Anonymous
    February 25, 2014
    You can use this query to work with SQL Server 2008

    DECLARE @StartDate datetime, @EndDate datetime
    Set @StartDate = ''+Cast(@StartYear as varchar)+'/'+cast(@StartMonth as varchar)+'/1';
    Set @EndDate = ''+Cast(@EndYear as varchar)+'/'+cast(@EndMonth as varchar)+'/1';
    Select ucs.CI_ID, count(ucs.CI_ID) [Count],
    CASE((status))
    When 3 Then 'Installed' When 2 Then 'Required' Else 'Not Applicable' End as 'Status', BulletinID, ArticleID,
    CASE((IsDeployed))
    When 0 Then 'No' Else 'Yes' End as 'Deployed',
    CASE((ui.IsSuperseded))
    When 0 Then 'No' Else 'Yes' End as 'Superseded',
    CASE((ui.IsExpired))
    When 0 Then 'No' Else 'Yes' End as 'Expired',
    ui.Title, ui.DatePosted, cica.CategoryInstanceName,
    CASE((ui.Severity))
    When 2 Then 'Low'
    When 6 Then 'Moderate'
    When 8 Then 'Important'
    When 10 Then 'Critical'
    Else 'NA' End as 'Severity'
    From v_Update_ComplianceStatusReported ucs
    JOIN v_UpdateInfo ui on ucs.CI_ID = ui.CI_ID
    JOIN v_CICategoryInfo_All cica JOIN v_CategoryInfo ci on cica.CategoryInstanceID = ci.CategoryInstanceID AND cica.CategoryTypeName = 'UpdateClassification' on ucs.CI_ID = cica.CI_ID
    Where resourceid IN(Select ResourceID from v_FullCollectionMembership where CollectionID = @ColID) AND ui.DatePosted BETWEEN @StartDate AND @EndDate AND ucs.status <> 1
    GROUP BY ucs.CI_ID, ucs.status, BulletinID, ArticleID, IsDeployed, ui.Title, DatePosted, cica.CategoryInstanceName, cica.CategoryInstanceID,ui.IsSuperseded, ui.Severity, ui.IsExpired
    ORDER BY ucs.status, BulletinID, ArticleID

  • Anonymous
    August 27, 2014
    fix the scanned sections...

    select distinct c.Name, count(rs.ResourceID) [Devices], (Select Count(LastScanTime) from v_UpdateScanStatus uss
    join v_r_system sys on uss.ResourceID = sys.ResourceID
    join V_Fullcollectionmembership as fcm on fcm.resourceid = sys.resourceid
    where datediff("d",LastScanTime, getdate()) <= 30 and fcm.collectionid = @ColID) [Scanned last 30 Days],
    (Select Count(LastHWScan) from v_GS_WORKSTATION_STATUS gsws
    join v_r_system sys on gsws.ResourceID = sys.ResourceID
    join V_Fullcollectionmembership as fcm on fcm.resourceid = sys.resourceid
    where datediff("d",LastHWScan, getdate()) <= 30 and fcm.collectionid = @ColID) [Inventory last 30 Days]
    from v_FullCollectionMembership fcm
    JOIN v_Collection c on c.CollectionID = fcm.CollectionID
    JOIN v_R_System rs on rs.ResourceID = fcm.ResourceID
    LEFT JOIN v_GS_WORKSTATION_STATUS gsws on gsws.ResourceID = fcm.ResourceID
    where fcm.CollectionID = @ColID
    group by c.Name