Browsed by
Month: February 2014

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

SCCM 2012 Report Builder Certificate chain error

SCCM 2012 Report Builder Certificate chain error

So you have just installed SCCM 2012 R2 and found the awesome power of SQL Server Reporting Services, and you have given your managers access to the web site to allow them to access the information that they keep asking you about.

After a while you get complaints that not all of the information that they need is available via the website and they want the ability to be able to create their own reports on the website. So you delegate them access to create reports from the console and you go there desk to explain how SQL report builder works and then you get the following error: “The Certificate chain was issued by an authority that is not trusted” or looks like this.

1

You probably received the same error on your computer but you lived with it because we can just make the change on the server and it works.

So the course of this issue is this little option inside the saved connection

2

Ok cool we have identified the why, can’t we just change the TrustServerCertificate to equal true, this does work, for about 5-10 minutes before the SCCM system checks run and revert the setting back.

So to resolve this we just need to export the “ConfigMgr SQL Server Identification Certificate” from the SCCM server and import it onto each of the computers that you plan to have access to update/modify reports.

To do this first we need to export the Certificate from the SCCM server which is a matter of running the MMC console on the SCCM server

3

And adding the local computer certificate snapin

4

And browsing to the Personal Certificate Store

5

And exporting the highlighted certificate as DER

6

Now on the report builder machine we need to import the certificate to the Current User

7

Into the Trusted Root Certificate Authorities

8

Hit yes on the Security Warning

9

And go back to Report Builder and everything will be fixed.

10

Now you can create reports to you hearts content.

Good luck

Steve