WSUS–PowerShell Reporting

Posted on February 26, 2016 by Luka

Hi,

So, since it took me a while to understand that you cannot get scheduled reports from WSUS 3.0, I had a quest to create my own. I searched through SQL database, and WSUS public Views and procedures, but it was time expensive.

Then I stumbled upon PoshWSUS scripts (here). Boeprox created PowerShell scripts, which help to administer WSUS from PowerShell. You can read all about it on his page.

So I needed to create an scheduled report which would periodically send me the status of servers updated by WSUS. And to look nice and shiny, the report will be HTML.

 1 #Define HTML Body,table, cells
 2 $a = "<style>"
 3 $a = $a + "BODY{background-color:white;font-family: Arial; font-size: 8pt;}"
 4 $a = $a + "TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;table-layout: fixed; width: 100%;}"
 5 $a = $a + "TH{border-width: 1px;padding: 0px;border-style: solid;border-color: black;background-color:white;}"
 6 $a = $a + "TD{border-width: 1px;padding: 0px;border-style: solid;border-color: black;background-color:white;}"
 7 $a = $a + "</style>"
 8 $a = $a + "<p style="+'"font-family: Arial;font-size: 10pt;"'+">Table Summary WSUS</p><p style="+'"font-family: Arial; font-size: 10pt;"'+">Admin</p>"
 9 
10 
11 #Sent to admin
12 $to = "admin@company.com"
13 
14 #Now Import Posh modul and connect to server
15 Import-module poshwsus
16 Connect-PoshWSUSServer -WsusServer wsusserver.company.com -port 8530 -verbose
17 
18 
19 <#
20 So, PoshWSUS has couple of scripts, and I foud out I need two, and I need them to combined
21 Get-PoshWSUSUpdateSummaryPerClient
22 and
23 Get-PoshWSUSClient
24 
25 First I get status by computer, then mor information about client itself
26 
27 #>
28 
29 
30 $Output = @() #define array to fill
31 $Computers = Get-PoshWSUSUpdateSummaryPerClient
32 
33 foreach ($Computer in $Computers)
34     {
35     $computersOstalo = Get-PoshWSUSClient $Computer.Computer
36     $grupaOstalo = Get-PoshWSUSGroup
37 
38 #save fields in array
39         $Props = @{
40       "ComputerName" = $Computer.Computer
41             "IPAddress" = $computersOstalo.IPAddress
42             "OS" = $computersOstalo.OSDescription
43             "Group" = $computersOstalo.ComputerGroup 
44       "LastUpdated" = $Computer.LastUpdated
45       "LastSyncTime" = $computersOstalo.LastSyncTime
46       "LastReportedStatusTime" = $computersOstalo.LastReportedStatusTime
47             "NeededCount" = $Computer.NeededCount
48             "FailedCount" = $Computer.FailedCount
49             "InstalledCount"=$Computer.InstalledCount
50             "PendingReboot" = $Computer.InstalledPendingRebootCount
51                         }
52    #Save every record to @output array
53     $Output += New-Object PSObject -Property $Props 
54     }
55 
56   # convert records to HTML
57 $body = $output |Select-Object ComputerName,IPAdresa,OperativniSustav,Grupa,ZadnjeUpdateirano,SinhroniziranoSaWSUS,SinhroniziranoStatusSaWSUS,PotrebnoZakrpa,NeuspjesnihZakrpa,InstaliranoZakrpaZadnjiUpdate,TrebaRestart |Sort-Object Grupa,ComputerName
58 
59 $body = $body |ConvertTo-HTML -head $a |Out-String
60 
61 #and finayl send mail to admin
62 send-mailmessage -from "WSUS Servers <wsusreports@company.com>" -to $to -subject "Report Server Summary" -bodyAsHtml -body $Body  -priority Normal -smtpServer "mailServer"

So just task scheduler this script, and you’ll have a nice report.

Good Luck

SelfService Active Directory Powershell script

Posted on October 24, 2015 by Luka

Hi everybody,

We have a need for self service portal in our IT department. We arranged after work shifts, and if I’m not near my VPN connected PC, I needed to unlock or reset user passwords, or give users the ability to have their mangers do that for them.

I needed unlock or password reset solution for users, that will trigger a script on mail recieve. So I searched for simillar powershell script, and I found it DeployHappiness AD Self Service Portal.
This blog show us how to send SMS to mail, but our provider does not have this kind of service.

Still, this gave me a logic of things, and how to connect ot Exhange via API on specific mailbox, and query that mailbox for specific mails.

Then I needed to make sure that only users from our Organization can request, and that the person requesting is direct or indirect manager of the user.

Example:

IT Admins
- John
  - Dave
  - Julia
    - Peter
    - Sandra
  - Steve
- Angela
  - David
  - Jeremy
    - Marco

IT admins can request Unlock or Reset for everybody
John can request Unlock or Reset for his group (Dave,Julia,Peter,Sandra), but not for himself or Angela’s group.
Angela can request Unlock or Reset for hers group (David,Jeremy,Marco), but not for herself or John’s group.
Julia can request Unlock or Reset for hers group (Peter,Sandra), but not for herself or Dave and Steve.
Jeremy can request Unlock or Reset for Marco, but not for himself or David.

(Hope you got it) Smiješak

Next on Prerequisites:

Create a user with mail adress
Create Recieve connector on Exchange for server that will host the script
On server that will host a script install Feature: Active Directory module for Window Powershell
Setup Active Directory Users Manager on Users Organization Tab (example)
Define Mail Subject and Body field (For me Subject = User for Unlock or rest, Body = what to do Unlock user or Reset password)
Create taks scheduler (run as user to which you send mail requests), triggers set to 5 minutes (more or less is up to you)

Script:
Explanations are in my script commented.

<#
Script for SelService AD
This script enables IT to unlock or reset passwords to AD users. Mangers can also request for theirs employees.
        Author: Luka Gros
        Website: blog.lukagros.com
        Email: luka@lukagros.com
        Date created: 20.Octobar.2015
        Last modified: 20.Octobar.2015
        Version: 1.1
    .LINK
        http://blog.lukagros.com
        https://twitter.com/lukagros
#>
####Parts of Script from http://deployhappiness.com/reset-user-passwords-with-ad-self-service-portal/#######
##############################################################Config##############################################################
$SmtpServer = "10.10.10.1"
$ResetEmail = "Reset Notification <reset.password@domain.com>"
$Username = "DOMAIN\user"
$Password = "Passw0rd"
$MailServer = "https://mail.domain.com/ews/exchange.asmx"
$ExchangeVersion = "Exchange2013" ##"Exchange2010_sp1"
####ALERT MAIL####################
$LoggingUser = "InformationMail@domain.com"
####NEW USER RESET PASSWORD######
$NewPassword = "Passw0rd" #You can also find function that creates random password, but we use ours default. And be careful of character length in GPO
#######Download for API assembley file is here: http://www.microsoft.com/en-us/download/details.aspx?id=35371 (There is 2.2 that works better with Exchange 2013)
[Reflection.Assembly]::LoadFile("C:\Program Files\Microsoft\Exchange\Web Services\2.2\Microsoft.Exchange.WebServices.dll")
##############################################################Config##############################################################
###############################Function that checks if requester is manager to user###############################
#######http://www.lazywinadmin.com/2014/10/powershell-who-reports-to-whom-active.html########
function Get-ADdirectReports
{
    PARAM ($SamAccountName)
    Get-Aduser -identity $SamAccountName -Properties directreports | %{
        $_.directreports | ForEach-Object -Process {
            # Output the current Object information
            Get-ADUser -identity $Psitem -Properties mail,manager | Select-Object -Property Name, SamAccountName, Mail, @{ L = "Manager"; E = { (Get-Aduser -iden $psitem.manager).samaccountname } }
            # Find the DirectReports of the current item ($PSItem / $_)
            Get-ADdirectReports -SamAccountName $PSItem
        }
    }
}#CLOSE SECTION function Get-ADdirectReports
###############################Connect to Exchange mailbox###############################
 $email = New-Object Microsoft.Exchange.WebServices.Data.ExchangeService([Microsoft.Exchange.WebServices.Data.ExchangeVersion]::Exchange2013)
 $email.Credentials = New-Object Net.NetworkCredential($Username, $Password)
 $uri=[system.URI] $MailServer
 $email.Url = $uri
 $inbox = [Microsoft.Exchange.WebServices.Data.Folder]::Bind($email,[Microsoft.Exchange.WebServices.Data.WellKnownFolderName]::Inbox)
###############################Connect to Exchange mailbox###############################
###############################Check if there are unread mails###############################
if ($inbox.UnreadCount -gt 0)
 {
        $PropertySet = new-object Microsoft.Exchange.WebServices.Data.PropertySet([Microsoft.Exchange.WebServices.Data.BasePropertySet]::FirstClassProperties)
        $PropertySet.RequestedBodyType = [Microsoft.Exchange.WebServices.Data.BodyType]::Text;
        # Set search criteria - unread only
        $SearchForUnRead = New-object Microsoft.Exchange.WebServices.Data.SearchFilter+IsEqualTo([Microsoft.Exchange.WebServices.Data.EmailMessageSchema]::IsRead, $false)
        $items = $inbox.FindItems($SearchForUnRead,10)  #return only 10 unread mail items
        Import-Module -Name ActiveDirectory
 ###############################CHECK IF UNREAD ITEMS - IF NOT END###############################
 foreach ($item in $items.Items)
 {
  # load the property set to allow us to view the body
 $item.load($PropertySet)
    #######Get Subject and Body fields###
        $getsubjecttext = $item.subject
        $getsubjecttext = $getsubjecttext+"*" #add right wildcard
        $getbodytext = $item.body
        $getsubjectuser = Get-ADUser -Filter {DisplayName -like $getsubjecttext -and employeetype -eq'1'} -Properties UserPrincipalName,SamAccountName,cn,DisplayName,mail
        If ($getsubjectuser -eq $null) #If IT cannot be found by full name, try by SamAccountName
            {
            $getsubjectuser = Get-ADUser -Filter {SamAccountName -like $getsubjecttext -and employeetype -eq'1'} -Properties UserPrincipalName,SamAccountName,cn,DisplayName,mail
            }
        $managerEmployeOK = '0' ##Reset Manager Flag
        #######Get-requestere###
        $address = $item.From.address
        $user = Get-ADUser -Filter {UserPrincipalName -eq $address} -Properties UserPrincipalName,SamAccountName,cn
  ###Reset Unlock nad Reset flags for every mail####
  $UnlockAccount = '0'
  $ResetpwdAccount = '0'
#######IF Keywords in body
  if (($item.body.text -Like "Unlock account*") -and $getsubjectuser -ne $null) #THIS IS STRICT OR PEOPLE WILL WRITE RUBBISH
        {
            $UnlockAccount = '1'
        }
  if ($item.body.text -Like "Reset Password" -and $getsubjectuser -ne $null -and $UnlockAccount -eq '0') #THIS IS STRICT OR PEOPLE WILL WRITE RUBBISH
        {
            $ResetpwdAccount = '1'
        }
#################################If requester is found GO - IF NOT do nothing#######################################
if($user -ne $null)
{
<# If user is under requester set flag $managerEmployeOK=1, if not send mail NO RIGHTS
#>
    $usersToDo = Get-ADDirectReports $user.SamAccountName
    foreach ($Name in $usersToDo)
    {
        if ($Name.SamAccountName -eq $getsubjectuser.SamAccountName)
            {
                #Write-Output $Name.SamAccountName
                $managerEmployeOK = '1'
            }
    }
####Allow IT admins######
If ($user.SamAccountName -eq 'userAdmin1' -or $user.SamAccountName -eq 'userAdmin2') ##You can write if userAdmin is in some AdminGroup that can send requests
    {
    $managerEmployeOK = '1'
    }
##################################
IF ($managerEmployeOK -eq 1)
{
###UNLOCK SECTION################################################################################
 if($UnlockAccount -eq '1')
    {
            ##The line below is commented for e-mail testing, no command is issued
            ###Unlock-ADAccount -identity $getsubjectuser.samaccountname
            ##Send MAIL TO $LoggingUser = "InformationMail@domain.com"###################################
            $body="
                <p style='font-family:arial'>User Account " + $getsubjectuser.cn + " (" + $getsubjectuser.SamAccountName + ") has been unlocked. Request was from: $address</p>
                <p>&nbsp;
                <p style='font-family:arial;font-size:12px;color:red'>Please do not respond to this automatic e-mail!</p>
                "
            send-mailmessage -to $LoggingUser -from $ResetEmail -subject "Action: User unlock requested!" -body $Body  -SmtpServer $SmtpServer -BodyAsHtml -Encoding UTF8
            ##Send MAIL TO Requester###################################
            $body="
                <p style='font-family:arial'>HI,</p>
                <p style='font-family:arial'>User " + $getsubjectuser.cn + " (" + $getsubjectuser.SamAccountName + ") has been unlocked.</p>
                <p>&nbsp;
                 <p style='font-family:arial;font-size:12px;color:red'>Please do not respond to this automatic e-mail!</p>
                "
            send-mailmessage -to $item.From.address -from $ResetEmail -subject "User is unlocked!" -body $Body -SmtpServer $SmtpServer -BodyAsHtml -Encoding UTF8
            ##SEND MAIL TO unlocked user###################################
                $body="
                <p style='font-family:arial'>Hi " + $getsubjectuser.cn + ",</p>
                <p style='font-family:arial'>User " + $user.cn + " (" + $item.From.address + ") has requested unlocking of your account.</p>
                <p style='font-family:arial'>Your account <b>" + $getsubjectuser.SamAccountName +"</b> is now unlocked</p>
                <p>&nbsp;
                 <p style='font-family:arial;font-size:12px;color:red'>Please do not respond to this automatic e-mail!</p>
                "
            send-mailmessage -to $getsubjectuser.mail -from $ResetEmail -subject "Your account has been unlocked!" -body $body -SmtpServer $SmtpServer -BodyAsHtml -Encoding UTF8
            $Unlock = $True
    }#UNLOCK CLOSE SECTION
###RESET SECTION################################################################################
 if($ResetpwdAccount -eq '1')
    {
                <#The line below is commented for e-mail testing, no command is issued
                ###Set-ADAccountPassword -identity $user.samaccountname -Reset -NewPassword (ConvertTo-SecureString -AsPlainText $NewPassword -Force) #Set users new password
                ###Unlock-ADAccount -identity $user.samaccountname #Probably user locked, so unlock
###Set-ADUser -Identity $user.samaccountname -ChangePasswordAtLogon $true #Set must change password on next logon
                #>
                ##If the password was reseted in the last 10 minutes do nothing
                $PasswordAge = (Get-ADUser $getsubjectuser -Properties PasswordLastSet | Select PasswordLastSet)
                if ((Get-Date).AddMinutes(-10) -ge $PasswordAge.PasswordLastSet)
                 {
                ##Send MAIL TO $LoggingUser = "InformationMail@domain.com"###################################
                    $body="
                        <p style='font-family:arial'>Request for password reset for user " + $getsubjectuser.cn + " (" + $getsubjectuser.SamAccountName + "). Request was from: $address</p>
                        <p>&nbsp;
                        <p style='font-family:arial;font-size:12px;color:red'>Please do not respond to this automatic e-mail!</p>
                      "
                    send-mailmessage -to $LoggingUser -from $ResetEmail -subject "Action: User password reset requested!" -body $Body  -SmtpServer $SmtpServer -BodyAsHtml -Encoding UTF8
                    ##Send MAIL TO Requester###################################
                    $body="
                        <p style='font-family:arial'>Hi,</p>
                        <p style='font-family:arial'>Password reset for user" + $getsubjectuser.cn + " (" + $getsubjectuser.SamAccountName + ").</p>
                        <p style='font-family:arial'>New password is : <b>" + $NewPassword + "</b></p> ##If user cannot read their mail, then their manager can let them now
                        <p style='font-family:arial'>User can now try to logon with his/hers new password, but password must be changed on first logon!</p>
                        <p>&nbsp;
                        <p style='font-family:arial;font-size:12px;color:red'>Please do not respond to this automatic e-mail!</p>
                        "
                    send-mailmessage -to $item.From.address -from $ResetEmail -subject "Reset user password complete!" -body $Body -SmtpServer $SmtpServer -BodyAsHtml -Encoding UTF8
                    ##SEND MAIL TO user###################################
                    $body="
                        <p style='font-family:arial'>Hi " + $getsubjectuser.cn + ",</p>
                        <p style='font-family:arial'>User " + $user.cn + " (" + $item.From.address + ") requested your password to be changed. Your new password is: <b>" + $NewPassword +"</b></p>
                        <p>&nbsp;
                        <p style='font-family:arial;font-size:12px;color:red'>Please do not respond to this automatic e-mail!</p>
                           "
                    send-mailmessage -to $getsubjectuser.mail -from $ResetEmail -subject "Your password has been reseted!" -body $body -SmtpServer $SmtpServer -BodyAsHtml -Encoding UTF8
                } #CLOSE SECTION when was password last set
    }#CLOSE RESET SECITON
 ###Command or user unknown################################################################################
 if (($UnlockAccount -eq '0' -and $ResetpwdAccount -eq '0') -or ($getsubjectuser -eq $null))
    {
                        ##Send MAIL TO $LoggingUser = "InformationMail@domain.com"###################################
                        $body="
                        <p style='font-family:arial'>There was request " + $getsubjecttext +"; with body " + $getbodytext +"</p>
                        <p style='font-family:arial'>Request was  from: $address</p>
                        <p>&nbsp;
                        <p style='font-family:arial;font-size:12px;color:red'>Please do not respond to this automatic e-mail!</p>
                        "
                        send-mailmessage -to $LoggingUser -from $ResetEmail -subject "Action:request error" -body $Body  -SmtpServer $SmtpServer -BodyAsHtml -Encoding UTF8
                        ##Send MAIL TO Requester###################################
                        ##Don't forget here to write the instructions how the mail was supose to look
                        $body ="
                        <p style='font-family:arial'>Hi " + $user.cn + ",</p>
                        <p style='font-family:arial'>YOur mail, with title <b>" + $item.subject + "</b> is unknown.</p>
                        <p style='font-family:arial'>Please write correct user FirsName and Surname, or loginname.</p>
                        <p style='font-family:arial'>Keywords:</p>
                        <ul style='font-family:arial'>
                        <li><b>Unlock</b> - unlocks</li>
                        <li><b>Reset</b> - password reset</li>
                        </ul>
                        <p>&nbsp;
                        <p style='font-family:arial;font-size:12px;color:red'>Please do not respond to this automatic e-mail!</p>
                        "
                        send-mailmessage -to $item.From.address -from $ResetEmail -subject "Mail command unknown" -body $Body -SmtpServer $SmtpServer -BodyAsHtml -Encoding UTF8
      }#CLOSE SECTION Command or user unknown
} #CLOSE SECTION managerEmployeOK
else #If not manager
{
                        ##Send MAIL TO $LoggingUser = "InformationMail@domain.com"###################################
                        $body="
                        <p style='font-family:arial'>Stigao je mail sa naslovom <b>" + $getsubjecttext +"</b></p>
                        <p style='font-family:arial'>i tekstom <b>" + $getbodytext +"</b></p>
                        <p style='font-family:arial'>Zahtjev je došao sa adrese: $address</p>
                        <p>&nbsp;
                        <p style='font-family:arial;font-size:12px;color:red'>Please do not respond to this automatic e-mail!</p>
                        "
                        send-mailmessage -to $LoggingUser -from $ResetEmail -subject "Action:manager error" -body $Body  -SmtpServer $SmtpServer -BodyAsHtml -Encoding UTF8
                        ##Send MAIL TO Requester###################################
                        $body ="
                        <p style='font-family:arial'>Hi " + $user.cn + ",</p>
                        <p style='font-family:arial'>According to our specifications, you are not manager to user " + $getsubjectuser.cn + " (" + $getsubjectuser.SamAccountName + "). </p>
                        <p style='font-family:arial'>Request denied!</p>
                        <p>&nbsp;
                        <p style='font-family:arial;font-size:12px;color:red'>Please do not respond to this automatic e-mail!</p>
                        "
                        send-mailmessage -to $item.From.address -from $ResetEmail -subject "Manager request error" -body $Body -SmtpServer $SmtpServer -BodyAsHtml -Encoding UTF8
}#CLOSE SECTION if not manager
##Set mail read
$item.Isread = $true
$item.Update([Microsoft.Exchange.WebServices.Data.ConflictResolutionMode]::AlwaysOverwrite)
 }#CLOSE SECTION Requester OK
 } #CLOSE SECTION foreach ($item in $items.Items)
 } #CLOSE SECTION ($inbox.UnreadCount -gt 0)

So , this would be it.

Active Directory User Auditing – Simple

Posted on March 6, 2014 by Luka

For my Audit report, I had to create an audit list about creating,deleting,disabling,enabling,locking, unlocking my AD users.

Since we haven’t had a SCOM on our premises, I hade to come up with something that will trigger an e-mail to me with an event.
First of all you have to enable user auditing on Default GPO.

After that, I connected to my primary domain controller and created an Powershell script (with a little help from http://powershell.com/cs/) which sends an e-mail in HTML form to me, with some parameters.
First of all, it creates HTML file with a table, which then populates from Security Event under Event ID 4740. After it populates HTML file, this table sets as an body, and sends it to email addresses.

#$DC = "DCServerName"    2: $Report= "C:\Admin\lockedaccount\locked.html" 
$HTML=@" 
<title>Account locked out Report</title> 
<style> 
BODY{background-color :#FFFFF} 
TABLE{Border-width:thin;border-style: solid;border-color:Black;border-collapse: collapse;} 
TH{border-width: 1px;padding: 1px;border-style: solid;border-color: black;background-color: ThreeDShadow} 
TD{border-width: 1px;padding: 0px;border-style: solid;border-color: black;background-color: Transparent} 
H2{color: #457dcf;font-family: Arial, Helvetica, sans-serif;font-size: medium; margin-left: 40px; 
</style> 
"@ 
$Account_Name = @{n='Account name';e={$_.ReplacementStrings[-1]}} 
$Account_domain = @{n='Account Domain';e={$_.ReplacementStrings[-2]}} 
$Caller_Computer_Name = @{n='Caller Computer Name';e={$_.ReplacementStrings[-1]}} 
$event= Get-EventLog -LogName Security -ComputerName $DC -InstanceId 4740 -Newest 1 | 
   Select TimeGenerated,ReplacementStrings,"Account name","Account Domain","Caller Computer Name" | 
   % { 
     New-Object PSObject -Property @{ 
      "Account name" = $_.ReplacementStrings[-7] 
      "Account Domain" = $_.ReplacementStrings[5] 
      "Caller Computer Name" = $_.ReplacementStrings[1] 
      Date = $_.TimeGenerated 
    } 
   } 
  $event | ConvertTo-Html -Property "Account name","Account Domain","Caller Computer Name",Date -head $HTML -body  "<H2> User is locked in the Active Directory</H2>"| 
     Out-File $Report -Append 
$MailBody= Get-Content $Report 
$MailSubject= "User Account locked out" 
$SmtpClient = New-Object system.net.mail.smtpClient 
$SmtpClient.host = "mail.uniqa.hr" 
$MailMessage = New-Object system.net.mail.mailmessage 
$MailMessage.from = “AccountLockout@test.com” 
$MailMessage.To.add(“itsupport@test.com”) 
$MailMessage.Subject = $MailSubject 
$MailMessage.IsBodyHtml = 1 
$MailMessage.Body = $MailBody 
$SmtpClient.Send($MailMessage) 
del C:\Admin\lockedaccount\locked.html 

After creating this powershell script, the next step is to create an Event Trigger which will send this e-mail.
This is done through Task Scheduler.

This trigger works when Event with 4740 ID is generated in Security Event Viewer.

-command "& 'C:\Admin\lockedaccount\account_locked_out.ps1' "

The final result is this:

Now, you can do this with Unlock account 4767, or Disable account 4725 or deleted 4726etc.
I found out this site with lists of Event IDs : link

Good Luck

Luka Gros

Tips n' Tricks from SQL And Windows administrator

Category Archives: Task Scheduler

WSUS–PowerShell Reporting

SelfService Active Directory Powershell script

Active Directory User Auditing – Simple