Browsed by
Tag: logon script

Reporting on Shares with SCCM 2012

Reporting on Shares with SCCM 2012

To follow in the same vain as my last post for reporting on usage of logon scripts, we now need to find which shares have been removed since the last time the logon script was updated. To do this is reasonably easy to complete by connecting to each share and checking to see if they still exist, until you have a list of over 200 shares to check. So let’s take a look at what we can do with SCCM to handle this.

First task would be to identify if we can utilise an existing WMI class to query, thankfully there is the Win32_Share class which will return the information back to SCCM. In addition to this we can enable this class via selecting it in the Hardware Inventory for the client settings.

RSpic1

Once this has been enabled and some of the computers have reported back you can use the below query to your database. To present the data back in a usable form I have joined the share path.

RSpic2

 

Now we have the server names tied to the share paths, from this we can grab the shares from the logon script and do a query like this

 

select ‘\\’ + rsys.name0 + ‘\’ + sha.name0
from v_GS_SHARE as sha
Join v_r_system as rsys on sha.resourceID = rsys.resourceID
where ‘\\’ + rsys.name0 + ‘\’ + sha.name0 in (‘\\cm7\admin4’,
‘\\or7\c$’)

This will bring back a nice list we can then copy into excel and do a simple countif on the 2 columns of share paths.

The problem we have is when it comes to Clustered shares, as since Server 2000 Microsoft hasn’t represented the shares for Clusters with the Win32_Share class. But from Server 2008 R2 we have be able to query the Win32_Clustershare. The process to add this class to the hardware inventory in SCCM is a little bit different as you need to add the class to the list,

To do this open the “default client settings” policy and browse the hardware inventory, then select Classes to inventory. once this appears select Add and then on the screen that appears hit the connect button. Which will bring a prompt like this. (Yes Microsoft forgot to select the password field as hidden). Make sure you select Recursive otherwise most classes wont appear.

RSpic3

Once you select connect, search the win32_clustershare class and enable it.

RSpic4

Then we can select the Fields that we want to inventory either in the Default policy to apply to all computers in the environment, or apply to a new/existing client settings policy.

RSpic5

Once we have deployed the Client Settings to the cluster we can return the data with the below query, as you can see the default fields are structured a little bit differently to the win32_share class. The class has both the server name and the Share Path which saves creating a constructed string.

RSpic6

Much like the Win32_Share class we can do a where in query which would look something like this:

select ServerName0,
Path0,
Name0
from v_GS_CLUSTER_SHARE
where name0 in (‘\\svr354\ab’,
‘\\svr354\acc’)

From here you have a couple of options on how to move forward, for example you can create both of these queries as data sources in an Excel Workbook with the 3rd sheet containing a list of all your shares from your logon script, from which you can derive the exists of each of the shares from either the win32_share or the Win32_clustershare data sources.

Cheers

Steve

UPDATED: missing image.

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