param(
[string]$Database,
[string]$Server="localhost",
[string]$Username,
[string]$Password,
[int]$Days = 30,
[string]$FilterAD = "(&(objectClass=computer)(operatingSystem=Windows*))",
[string]$SaveCSV,
[switch]$UseEventLog=$False,
[switch]$Verbose=$False,
[switch]$WhatIf=$False
)
$Usage = @"
Function: to create a list of computers active in AD but not active in Symantec SEPM.
Connect to Symantec MS SQL DB to obtain a list of defined clients that are not active.
Connect to AD to obtain a list of compter that are active in the last Days
Options:
-Database SEPM MS SQL Database (no default - if not specified the program will query for options)
-Server SEPM MS SQL Database Server (default: localhost)
-Username DB Username (default: system credentials)
-Password DB Password (no default)
-SaveCSV The file to save to, with postfix -<month nr><day nr>.csv (default: print to standard out)
-Days The number of days after which a device is "not active" in AD (default: $Days days)
-FilterAD The filter used to query AD (default: $FilterAD)
-UseEventLog Send program events to Windows Application Log (default: False)
-WhatIf This "usage"
"@
if ($WhatIf.IsPresent){
write-host $Usage
exit
}
$DISABLED = 2; # Used to check if account disabled
$CsvPost = Get-Date -UFormat "%d%m"
$CsvPost = "-" + $CsvPost + ".csv"
# Exception handler
function trapped ( [string]$Info, [string]$Exception ){
Event-Log "TRAP: $Info `n $Exception" "Error" 666
exit
}
# Used to decrypt $Password in Read-Host
Function ConvertTo-PlainText( [security.securestring]$secure ) {
$marshal = [Runtime.InteropServices.Marshal]
$marshal::PtrToStringAuto( $marshal::SecureStringToBSTR($secure) )
}
function send-mail
{
param(
[string]$server = $(throw "server must be set")
,[string]$toAddress = $(throw "toAddress must be set")
,[string]$toName = ""
,[string]$fromAddress = $(throw "fromAddress must be set")
,[string]$fromName = ""
,[string]$subject = $(throw "subject must be set")
,[string]$body = ""
,[string]$replyTo)
# Init Mail address objects
$emailFrom = New-Object system.net.Mail.MailAddress $fromAddress , $fromName
$emailTo = New-Object system.net.Mail.MailAddress $toAddress , $toName
$smtp = new-object Net.Mail.SmtpClient($server)
$MailMessage = new-object Net.Mail.MailMessage($emailFrom, $emailTo, $subject, $body)
$MailMessage.IsBodyHtml = $true
if ($replyToAddress)
{
$MailMessage.ReplyTo = $replyTo
}
$smtp.Send($MailMessage)
}
function Event-Log([string]$Entry, [string]$Type="Information", [int]$Ident="0"){
if ( !$UseEventLog ){ Write-Host "$Type $Ident $Entry"; return }
$Entry = $Entry + "`n`n`n`n" # Add space to useless default text from Microsoft
switch ($Type) {
"Information" { $EventLogEntryType = [System.Diagnostics.EventLogEntryType]::Information; break}
"Warning" { $EventLogEntryType = [System.Diagnostics.EventLogEntryType]::Warning; break}
"Error" { $EventLogEntryType = [System.Diagnostics.EventLogEntryType]::Error; break}
default { $EventLogEntryType = [System.Diagnostics.EventLogEntryType]::Error; break}
}
$EventLog = new-object System.Diagnostics.EventLog("Application", ".", "Symantec Audit")
$EventLog.WriteEntry($Entry, $EventLogEntryType, $Ident )
}
function Get-DomainComputerAccounts([string]$Filter = "(&(objectClass=computer))"){
trap [Exception] {
trapped "AD:$Filter:" $($_.Exception.Message)
}
$searcher = new-object DirectoryServices.DirectorySearcher([ADSI]"") #Leaving the ADSI statement empty = attach to your root domain
$searcher.filter = $Filter
$searcher.CacheResults = $true
$searcher.SearchScope = “Subtree”
$searcher.PageSize = 1000
$accounts = $searcher.FindAll()
if($accounts.Count -gt 0){
foreach($account in $accounts){
$name = $account.Properties["name"][0];
#$useraccountcontrol = $account.Properties["userAccountControl"]; # didn't work
$fixme = [adsi]$account.Path
$useraccountcontrol = $fixme.userAccountControl.Item(0);
# Property that contains the last password change in long integer format
$pwdlastset = $account.Properties["pwdlastset"];
# Convert the long integer to normal DateTime format
$lastchange = [datetime]::FromFileTimeUTC($pwdlastset[0]);
# Determine the timespan between the two dates
$datediff = new-TimeSpan $lastchange $(Get-Date);
# Create an output object for table formatting
$obj = new-Object PSObject;
# Add member properties with their name and value pair
$obj | Add-Member NoteProperty ComputerName($name);
$obj | Add-Member NoteProperty UserAccountControl($useraccountcontrol);
$obj | Add-Member NoteProperty LastPasswordChange($lastchange);
$obj | Add-Member NoteProperty DaysSinceChange($datediff.Days);
Write-Output $obj;
}
}
}
function Get-Simple-Query([string]$Query,[string]$Database, [string]$Server="localhost", [string]$Credentials="Integrated Security=True"){
trap [Exception] {
trapped "DB Access $Server/$Database/$Username" $($_.Exception.Message)
}
# The technical Powertools access to MSSQL
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection("Server=$Server;Database=$Database;$Credentials")
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($Query, $SqlConnection)
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
Write-Output $DataSet.Tables[0]
}
function Get-SEPM-InactiveClients([string]$Server="localhost", [string]$Database="sem5", [string]$Username='', [string]$Password=''){
trap [Exception] {
trapped "Symantec DB Access $Server/$Database/$Username" $($_.Exception.Message)
}
if ( $Username -eq ''){
$Credentials="Integrated Security=True"
}else{
$Credentials="user=$Username;password=$Password"
}
# The Query that is used
$DefinedClients="$Database.SEM_CLIENT"
$ActiveClients="$Database.SEM_COMPUTER"
$Columns = "$DefinedClients.COMPUTER_NAME, $DefinedClients.COMPUTER_DOMAIN_NAME, $DefinedClients.DESCRIPTION"
$LeftJoin = "left join $ActiveClients on $DefinedClients.COMPUTER_NAME=$ActiveClients.COMPUTER_NAME"
$Where = "where $ActiveClients.COMPUTER_NAME is NULL"
$Where = $Where + " and $DefinedClients.POLICY_MODE='1'and $DefinedClients.GROUP_IS_OU='1'"
$Query = "select $Columns from $DefinedClients $LeftJoin $Where"
Get-Simple-Query $Query $Database $Server $Credentials
}
function Get-No-SEP($ActiveComputerS, $InactiveClientS){
# Create hash of AD ActiveComputers : better way?
$ACH = @{}
foreach ($ActiveComputer in $ActiveComputerS){
if ( $ActiveComputer.ComputerName -ne $null){
$ACH.add($ActiveComputer.ComputerName, $ActiveComputer.ComputerName)
}
}
foreach ( $Computer in $InactiveClientS){
if ( $Computer.COMPUTER_NAME -ne $null){
if ( $ACH.Contains($Computer.COMPUTER_NAME) ){
Write-Output $Computer
}
}
}
}
function Get-IntrestingComputerAccounts([string]$FilterAD, [int]$Days ){
$ActiveComputerS = Get-DomainComputerAccounts $FilterAD `
| Where-Object {$_.DaysSinceChange -le $Days} `
| Where-Object {($_.UserAccountControl -band $DISABLED) -ne $DISABLED }
Write-Output $ActiveComputerS
}
# Start of program ...
if ( $Database -eq ''){
# No parameters, so get some basic parameters...
Write-Host "Use -WhatIf for help ..."
$Database = Read-host "SEPM MSSQL -Database"
$Server = Read-host "SEPM MSSQL -Server"
$Username = Read-host "SEPM MSSQL Database -Username (empty for system credentials)"
if ( $Username -ne ''){
$Password = ConvertTo-PlainText(Read-host "SEPM MSSQL Database -Password" -assecurestring)
}
}
if ( $Verbose ){ Event-Log "Collecting from SEPM $Server/$Database/$Username (InactiveClients)" }
$InactiveClientS = Get-SEPM-InactiveClients $Server $Database $Username $Password
$CountInactive = $InactiveClientS.Count
if ( $Verbose ){ $InactiveClientS | ft -Autosize }
if ( $Verbose ){ Event-Log "Collecting from AD with filter $FilterAD and active within $Days days (ActiveComputers)" }
$ActiveComputerS = Get-IntrestingComputerAccounts $FilterAD $Days
$CountActive = $ActiveComputerS.Count
if ( $Verbose ){ $ActiveComputerS | ft -Autosize }
if ( $Verbose ){ Event-Log "Finding No SEP computers (NoSEP)" }
$NoSEP = Get-No-SEP $ActiveComputerS $InactiveClientS
$CountNoSEP = $NoSEP.Count
if ( $Verbose ){ Event-Log "Result (InactiveClients)" }
if ( $SaveCSV ){
$FileName = $SaveCSV + $CsvPost
$NoSEP | Export-Csv $FileName
}else{
$NoSEP | ft -AutoSize
}
Event-Log "NoSEP: $CountNoSEP ActiveComputers: $CountActive InactiveClients: $CountInactive"
# Bye