SCCM – Software Update Groups Compliance Dashboard Revisited

So first, it’s been a long time since my last post. So I hope it will be an interesting one for you guys… and yes, it is about custom software update report again.

In this blog post, I will share with you a Dashboard for Software Updates Group and it’s based on the ones created by Gary Simmons, which are just incredible. So for those who aren’t using it yet, here’s a link that I recommended to read first, as it explains in detail most of the feature include in this Dashboard;  SUG Dashboard from Gary Simmons

First, let’s talk about the parameters, in this Dashboard I’ve change the parameters to match our needs, so for that I’ve create a filter on the software update group parameter to only show the SUG that contains compliance in the name (This is use as a template containing all the updates that are required in our company).   Other fields like Company and Entity match our Folder Structure in SCCM and Scope is the collections that are within the second level folder SCCM. Here’s an example of what we use:

SCCMHierarchy_1

And finally the OS Type parameters allows to scope this report by OS Type, Servers, Workstations, etc. it the case that you have different OS in the Collection.

Dashboard_Parameters

Ok, so in the first part of the Dashboard I’ve change the Asset table to include all devices and the ability to drill down to get the detailed information. Also, in this part I added 2 tables to first get the top 10 vulnerable systems and them the top 10 missing updates.

Dashboard_1

In the second part, I’ve added a custom Overall Systems Compliance part (again from a report from Gary Simmons) which contains all the OS Versions.

 

Dashboard_2

And lastly in the third part, I’ve added a chart to show the Compliance level per update severity, a chart about the updates scan status (% of success) and Errors details, a chart with the top 5 Windows Updates version and a table with the Software updates point status (last sync time, sync status).

Dashboard_3

here’s a link from where you can download this dashboard, in the case you are interested to try it;

SUG Compliance Dashboard

And in the next part, I will publish all the sub reports that I’ve create to drill down and get all the details information.

Part 2: Software Update Groups Compliance Dashboard Revisited, Part 2

 

 

SCORCH – Use Orchestrator to build a password expiration notifier tool

In this post, I’ll show you one of the possible ways on how we can use Orchestrator to build a password expiration notify tool. Based on this example, you will be able to customize how many days before the expiration that you would like to notify the users (you can use more than one policy) , use different template for the  password change reminder emails, set up different policies based on OU, etc… Of course, there are other ways to accomplish this task, such as PowerShell, but by using orchestrator, it will be very easy to extend the Runbook and add some features such as notifying the manager of the user, creating an incident in service manager when the password is expired, adding some logging/error handling, reused the same process for different tasks, such as account expiration and so on.

Ok, so before going further, here are some details on my environment;

  • Windows 2008 R2 domain and forest functional level.
  • Windows 2008 R2 DCs.
  • The service account use to invoke the child Runbook has the proper read permissions on AD users attributes.
  • The service account use to invoke the child Runbook has permissions to create table on a SQL Database.
  • Orchestrator Service account has Data Reader permissions the DB created by the PowerShell script.

Also, here are other things to consider;

  • If the orchestrator service account has the proper read permissions on AS users attributes, you do not need to invoke a child Runbook.
  • If you want to schedule the Runbook to run a custom schedule, you can use Orchestrator Schedule.
  • If needed, you can modify the PowerShell script to output some error and adding some condition in the Runbook to monitor those errors.

Ok, so here are the steps;

  • create the following variables in Orchestrator (and if defined, use your naming convention)
  1. SQLServerName =  “SQLServerName”
  2. SQLDBName = “SQLDataBaseName”
  3. SQLTableName = “SQLTableName”
  4. OUToMonitor = “OUDistinguishedName”
  • create new Runbook and use the following PowerShell script to the Import AD Data to SQL;

img1

—————————————————————————————-

You can download the PowerShell Script to import AD data into SQL from here,

https://drive.google.com/file/d/0ByVMhVXdDQn4TDNQNl9kZUpBQ3c/edit?usp=sharing

—————————————————————————————-

  • Configure the return value of the Runbook as;

img12

img2

  • Then create another Runbook with the following activites;

img3

img11

make sure to thick the check box, wait for completion and then configure it to invoke the first Runbook (make sure that the account use to invoke the Runbook have to proper AD and SQL permissions.)

img4

add these conditions on the link

img5

use this query to get the information from the database (in my example I will send  two reminder, so one 14 days before the expiration and the other 3 days.) And if needed, you can change the values in the where clause to match the your need. Also, add the SQLSERVERDBNameVariable from Orchestrator to the FROM clause.

img6

img8

img9

those values should match the ones set in the SQL query WHERE clause and you also have to configure them for each of the reminder you want to implement.  (In my examples, I use two reminder)

img10

again you have to configure this activity for all the reminders set in WHERE  clause of the SQL query.

And voila, at this point you have to configure the proper schedule then start the Runbook, and you should be good to test 🙂 … and please make sure to test this before implanting this on a production environment.

SCCM – Software Updates Compliance Reports (Updated)

Recently, I received some suggestions/comments about some of the reports that I published on my blog (and thanks to all of you guys)… and one of them was to add the Maintenance Windows information to the Software Updates Compliance Reports, which I think could be very helpful.

So, here’s an updated version of those reports, which now include the information on the next active maintenance window (if set), plus a new linked report, which gives the Maintenance Windows & Deployments details for the selected collection.

Here are some screenshots of these reports;

img3

Software_update_Collection_Status

img2

Software_update_Collection_Status

img4

Software_update_Computer_Detail

img1

Software_update_Collection_Detail

Links;

Software_update_Collection_Status; 

https://drive.google.com/file/d/0ByVMhVXdDQn4YUN1dTZMZEpEejA/edit?usp=sharing

Software_update_Computer_Detail;

https://drive.google.com/file/d/0ByVMhVXdDQn4UVhlRUxwZXFReHM/edit?usp=sharing

Software_update_Collection_Detail;

https://drive.google.com/file/d/0ByVMhVXdDQn4UHp6Ui0zLXpPcDA/edit?usp=sharing

PowerShell – Report Inactive AD Users

Recently I’ve start playing a bit with Powershell and here’s a script that I created to audit inactive user accounts in Active Directory. Quickly, the script is searching inactive users in a specific OU as well as all its child OUs.

Then, if there are inactive users, it going to send an email to each OU’s manager containing the following information as well as archiving all the html files into a folder.

Inactive User Report

Noted that in my case, all user’s OUs are within the same OU, such as;

USER
–>Finance
–>Administration
–>Marketing

So here’s the script;

—————————–

#Import AD Module if required
if(@(get-module | where-object {$_.Name -eq “ActiveDirectory”} ).count -eq 0) {import-module ActiveDirectory}

#Define variable
$fragments=@()
$lastDays=90

#Report archive folder
$folderPath=”$env:temp\”
$smtp = “ENTERSMTPSERVERNAME”
$from = “EMAILFROM@YOURDOMAIN”COM”

#Get Child OUs
$searhOU = Get-ADOrganizationalUnit -SearchBase “ENTER_ROOT_OU_DISTINGUISHEDNAME” -filter * -SearchScope 2

if($searhOU){

foreach ($OU in $searhOU){

$OUName = $OU.Name

#Define HTML format
$head = @”
<style>
body { background-color:#FAFAFA;font-family:Arial;font-size:12pt; }
td, th { border:1px solid black;border-collapse:collapse; }
th { color:white;background-color:black; }
table, tr, td, th { padding: 2px; margin: 0px }
tr:nth-child(odd) {background-color: lightgray}
table { margin-left:50px; }
</style>
<H2>This report list inactive users in $OUName OU<BR></H2>
“@

$users = Search-ADAccount -SearchBase $OU.DistinguishedName -accountinactive -TimeSpan $lastDays -usersonly -SearchScope 1 `
| Get-ADUser -Properties * `
| Sort-Object DisplayName `
| Select-Object @{n=’User Name’;e={$_.DisplayName}},@{n=’UserID’;e={$_.samAccountName}} `
,Description,@{n=’Last Logon Date’;e={$_.lastLogonDate}},@{n=’Created On’;e={$_.WhenCreated}} `
, @{n=’Enabled’;e={$_.Enabled}}

if ($users -ne $NULL){

$fragments += $users | ConvertTo-HTML -Fragment
$filePath = Get-Date -uformat ($folderPath + $OUName + “_%d%m%Y.htm”)

ConvertTo-HTML -Head $head -Body $fragments -PostContent “<br><br><i>report generated: $(Get-Date)</i>” `
| Out-File -FilePath $filePath -Encoding ascii

$to = $OU.ManagedBy | Get-ADObject -properties * | Select-Object -ExpandProperty mail
$subject = “Report – Inactive user”
$body = (get-content $filePath ) | out-string

send-MailMessage -SmtpServer $smtp -To $to -From $from -Subject $subject -Body $body -BodyAsHtml

$obj = $NULL
$fragments = $NULL
$users = $NULL

}
}
}

Remove-Variable * -Force -ErrorAction SilentlyContinue