Reporting on logon scripts with SCCM 2012

Reporting on logon scripts with SCCM 2012

I had a use case to be able to report on the Current logon scripts in our environment, along with the status of the user accounts. This report is to be used to plan the consolidate all of our many logon scripts into a single script.

So by default we all look to running a powershell script or alike to return the logon script details from Active Directory and use that to plan the consolidation, as we all know this is a static list which we need to obtain on a regular bases to ensure that we keep track of the changes.

So I had the idea of what if we can capture this information in a SQL database and make it work for us, thankfully Microsoft has a really easy tool to handle this out of the box, it’s called System Center Configuration Manager (both 2007 & 2012 Supports this method.).

To enable the inventory of addition Active Directory fields into the SCCM database it’s a simple as updating the Active Directory User Discovery method to include the scriptpath attribute.

1

This is accessible on the last tab of the “Active Directory User Discovery Properties” (2012 has the nice search function to ensure correct spelling 🙂 )

2

Run a full discovery on the Active Directory Users. Once the scan has completed you can check in the adusrdis.log file to confirm the agent has completed, we can now run the SQL query of (make sure you change the domain name to match your domain), go ahead I’ll wait:

SELECT user_name0,

user_Account_control0,

CASE user_Account_control0

WHEN ‘512’ THEN ‘Active Account’

WHEN ‘514’ THEN ‘Account Disabled’

WHEN ‘66048’ THEN ‘Password Does Not Expire’

WHEN ‘544’ THEN ‘does not require Password’

WHEN ‘590336’ THEN ‘Trusted for Delegation and Password does not expire’

WHEN ‘66050’ THEN ‘account disabled and does not require password’

WHEN ‘66080’ THEN’Password does not expire and password not required’

ELSE CAST(user_account_control0 as VARCHAR(20)) end as ‘Account Status’,  scriptPath0

FROM v_r_user

WHERE Windows_NT_Domain0=’domain7′

AND NOT user_account_control0 in (

‘66176’/* don’t expire password, emailed, encrypted text password allowed*/,

‘546’/* disabled, Password not required*/,

‘2080’/* Interdomain trust account, Password not required*/,

‘4260352’/* don’t require preauth, don’t expired password, enabled */)

ORDER BY [Account Status]

 Which returns something like this:

3

So we can capture a good amount of information by querying the database directly. The next step is to present it in a fashion that is useful for other staff, so let’s step through the process of putting this into a report.

Browse to your reporting site and if you don’t already have a folder for internal reports create one (keeps it neat and tidy) browse to the folder and select report builder. Once report builder opens select new report, table or matrix

4

And we are wanting to create a dataset

5

Browse for the system generate Data Source Connector, this is located under the configmgr_ folder on the SSRS server.

6

Enter your username & password that has access to the SQL database.

7

Select the edit as text option and paste the above query

8

We can now select how we present the information, if we want to put in a simple table it is a matter of adding all of the options into the values box like this:

9

Which will return a result something like below, which will give us a decent amount of information:

10

But we want to create a report that will allow for a glance to provide a count so we can select the options like this:

11

Which presents a result like this:

12

Which give us a place to start on how to clean up the logon scripts.

One thing to note is with SCCM 2012 the Active Directory User Discovery ignores disabled user objects, whereas SCCM 2007 would bring this information into the database to allow you to report on the information.

Obviously you can replace the scriptpath with almost any active directory user attribute to bring into the SCCM database, the common ones that I bring through include the phone numbers, address fields, title, and manager.

I hope this helps.

Good luck

Steve

Leave a Reply