Writing SQL Query for an SCCM Custom Report to allow Dashboard Usage

-Partnered in this solution is Roberto Rodriguez with Microsoft Premier-

 

We had a customer request to provide a dashboard that would show Distribution Points that had less than 5% disk space on all drives. So using one of the standard reports, we customized the report, but found that we were unable to import this report into our dashboard. The SQL query from the standard report looked like this:

SELECT SYS.Name, SYS.SiteCode, LDISK.DeviceID0,

LDISK.Description0,

LDISK.FreeSpace0, LDISK.Size0,

LDISK.FreeSpace0*100/LDISK.Size0 as C074

FROM v_FullCollectionMembership SYS

join v_GS_LOGICAL_DISK LDISK on SYS.ResourceID =

LDISK.ResourceID

WHERE

     LDISK.DriveType0 =3 AND

     LDISK.Size0 > 0

     AND

LDISK.FreeSpace0*100/LDISK.Size0 < @variable

     AND SYS.CollectionID = @CollID

ORDER BY SYS.Name

 

After making changes, this is what the query looked like this:

SELECT SYS.Name, SYS.SiteCode, LDISK.DeviceID0,

LDISK.Description0,

LDISK.FreeSpace0, LDISK.Size0,

LDISK.FreeSpace0*100/LDISK.Size0 as C074

FROM v_FullCollectionMembership SYS

join v_GS_LOGICAL_DISK LDISK on SYS.ResourceID = LDISK.ResourceID

WHERE

     LDISK.DriveType0 =3 AND

     LDISK.Size0 > 5

     AND

LDISK.FreeSpace0*100/LDISK.Size0 < 5

     AND SYS.CollectionID =

'CollectionID'

ORDER BY SYS.Name

 

What the changes in the query do is pre-populate the fields that would otherwise be prompting you for information. Since this request requires the same static information, we are placing that inside the query itself. The final step that we had to figure out what that in order to use the report in the dashboard, the query cannot ask for prompts, so if you go into the SQL Query, you will see a button called 'Prompts' as shown below:

 

Once you click on the button, you will see the following:

Just remove the 2 items in the fields. Once this is complete, you can go into your Dashboard, and add the new custom report.