So this is the first post in a series about how to be proactive with ConfigMgr.  I’ve started with something that is relatively straightforward, disk space.  Now, you might say that this is too simple because we already have reports in ConfigMgr that tell us this information.  What I am aiming to due is get a report that can be used to contact end-users, rather than just report ConfigMgr data about machines.

Prerequisite AD Information

Before we can get started we need to know some information about our users.  Where is the best information about users (hopefully)? Active Directory, so we turn to Active Directory User Discovery.  For this example, I’m going to extend AD User Discovery to collect the following additional attributes:

  • telephoneNumber
  • mail
  • givenName
  • displayName
  • sn
  • distinguishedName

sn in this can is surname. You may also want distinguishedName because that is useful in linking people to their managers and linking computer objects to their owners.

Enable Asset Intelligence

Additionally, before we begin we want to enable Asset Intelligence (AI).  Enabling AI gets us the primary user of the computer we are looking at.  Some people have concerns about the accuracy of this data, and while I agree it’s not perfect it’s a lot better than last logged on user.  So go ahead and turn on the following AI classes, if you don’t have them on:

  • SMS_SystemConsoleUsage
  • SMS_SystemConsoleUser

Views Used

We are going to use four views in this report, as follows:

  1. v_GS_LOGICAL_DISK: This is our main view that gives us information on logical drive utilization on each machine.  We will probably limit this to only fixed disks.  It is linked it using the ResourceID column.
  2. v_R_System_Valid: This views gives us the computer name we are working with.  It is linked using the ResourceID column.
  3. v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP: This views gives us the primary console user for a given computer. It is linked using the ResourceID column.
  4. v_R_User: This view gives us the AD attributes we want for a given user.  While this view does have a ResourceID column, this maps to User resource and not Computer resources like our other views.  This means we will have to link it to our other views using the user’s domain\accountname format instead.  The Windows_NT_Domain0 and User_Name0 columns give us this (with a \ in between of course).  This can then be linked to v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0.

SQL Query

The following SQL query can be used to create your own reports.  You may need to customize this for your own environment (perhaps changing the 0.15 to a parameter for the report or getting it to target a specific collection instead of all machines in the environment). This join format will be reused thought the remainder of this series to show to link computer, inventory and users together.

SELECT  Systems.Netbios_Name0 AS 'Computer Name',
        Disks.Name0 AS 'Drive Letter',
        ROUND(Disks.FreeSpace0*1.00/Disks.Size0*1.00,2) AS '% Free',
        Disks.FreeSpace0 AS 'Free Space (MB)',
        Disks.Size0 AS 'Disk Size (MB)',
        MaxUsage.TopConsoleUser0 AS 'Primary User',
        Users.displayName0 AS 'User''s Name',
        Users.mail0 AS 'E-mail Address',
        Users.telephoneNumber0 AS 'Telephone Number',
        Users.givenName0 AS 'Given Name',
        Users.sn0 AS 'Surname'
FROM v_GS_LOGICAL_DISK AS Disks
JOIN v_R_System_Valid Systems
    ON Disks.ResourceID = Systems.ResourceID
JOIN v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP MaxUsage
    ON Disks.ResourceID = MaxUsage.ResourceID
JOIN v_R_User Users
    ON MaxUsage.TopConsoleUser0 =
        Users.Windows_NT_Domain0 + '\' + Users.User_Name0
WHERE DriveType0=3
AND FreeSpace0 IS NOT NULL
AND ROUND(Disks.FreeSpace0*1.00/Disks.Size0*1.00,2) < 0.15

Sample Output

Computer Name Drive Letter % Free Free Space (MB) Disk Size (MB) Primary User User’s Name E-mail Address Telephone # Given Name Surname
Client01 C: 10 100 1000 LAB\Bob Bob Smith bob@contoso 4567 Bob Smith
Client02 C: 0.2 2 1000 LAB\Kate Kate Smith kate@contoso 5678 KAte Smith

Conclusion

The outputted list will be easy to Word’s Mail Merge feature to draft messages to end users to let them know that their machines are running out of disk space, that this may cause performance problems and IT can help.  Alternatively, use this list to call end-users to schedule a visit (perhaps 15% isn’t good for calling but under 100MB or 50MB my justify a call).

This post was contributed by Saud Al-Mishari, a Premier Field Engineer with Microsoft Premier Field Engineering, UK.