SHIFT

--- Sjoerd Hooft's InFormation Technology ---

User Tools

Site Tools


Sidebar

Recently Changed Pages:

View All Pages


View All Tags


LinkedIn




WIKI Disclaimer: As with most other things on the Internet, the content on this wiki is not supported. It was contributed by me and is published “as is”. It has worked for me, and might work for you.
Also note that any view or statement expressed anywhere on this site are strictly mine and not the opinions or views of my employer.


Pages with comments

View All Comments

adcontrols

AD Controls

This page will show you the script for various checks you could do in your environment for compliancy or risk purposes. The following functionality is used:

  • Create reports in excel, and comparing results with previous reports
    • Specific accounts in OUs are identified
    • Local administrator accounts on all servers are checked
  • Sending html emails with a report on found findings
  • Upload the report to sharepoint online
  • Look for changes causing the findings in the TOPdesk SQL database
  • Use a default file with standard functions

Schedule the Script

Scripts can be scheduled from any Windows Server but it needs domain admin permissions to access all local servers. The scripts are located on a central Script Repository so you need to set the executionpolicy accordingly:

  • PowerShell:
    • Program: C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe
  • PowerShell Core (version 6 and higher):
    • Program: C:\Program Files\PowerShell\6\pwsh.exe
  • Arguments:
-Executionpolicy Bypass -nologo -noninteractive -command "& {\\filerepository\dfs\SCRIPT_REPOSITORY$\ICTControlFramework\ICT-CF-02.ps1}"

See Schedule Tasks on Windows Server for more information, and know that I tested the commands above on a Windows Server 2016 system.

Standard Functions

Include this script to include standard functionlaity for email, sharepoint and sql.

# Author: Sjoerd Hooft / https://www.linkedin.com/in/sjoerdhooft/
 
### Versioning and functionality ##################################################################
### 2019 01 31 ###
# Default Functions for sending emails and uploading files to SharePoint Online
###
### 2019 02 05 ###
# Changed email format to HTML
###  
### 2019 02 14 ###
# Added script to check for server availability
# Made some changes to the Send-Email Function for high priority emails and emails without attachments 
###################################################################################################
 
### Start Functions ###############################################################################
### Send email
### Needs two parameters:
#### $subject : Subject line for email
#### $info    : Plaintext body for email 
### All other variables need to be set in the script
Function Send-Email ($subject, $info){
   # Send high prio email without attachment
   if ($priority -eq "high" -and $monthreport -eq $null){
		Send-MailMessage -To $toemail -From $fromemail -cc $ccmail -SmtpServer $mailserver -Subject $subject -Body $info -BodyAsHtml -Priority High
   }
   # Send mail without attachment (some Controls do not need attachments) 
   elseif ($monthreport -eq $null){
		Send-MailMessage -To $toemail -From $fromemail -cc $ccmail -SmtpServer $mailserver -Subject $subject -Body $info -BodyAsHtml
	}
	else{
		Send-MailMessage -To $toemail -From $fromemail -cc $ccmail -SmtpServer $mailserver -Subject $subject -Body $info -BodyAsHtml -Attachments "$monthreport"
	}
}
### End 
###
### Check if server is available
### Needs one parameter 
#### $computername : servername in [string] format
Function CheckAvailability($computername){
	#$computername = $computer.Name
	if (Test-Connection $computername -quiet -count 1){
		# Computer answers to ping
		#powershell 6 does not support this anymore: https://docs.microsoft.com/en-us/powershell/scripting/whats-new/breaking-changes-ps6?view=powershell-6
		if ($PSVersionTable.PSVersion.Major -eq 6){
			Return "OK"}
		elseif (get-wmiobject -ErrorAction SilentlyContinue -computername $computername "win32_process"){
			#Computer reageert op wmi
			Return "OK"}
		else{Return "WMIError"}}
	else{Return "PingError"}
}
### End
### 
### Upload file to SharePoint
### Needs one parameter 
#### $fileToUpload : The file that needs to be uploaded to sharepoint
### All other variables need to be set in the script
Function Upload-Report ($fileToUpload) {
	#Retrieve list
	$Context.Load($Library)
	$Context.ExecuteQuery()
	#Retrieve folder
	$FolderToBindTo = $Library.RootFolder.Folders
	$Context.Load($FolderToBindTo)
	$Context.ExecuteQuery()
	$FolderToUpload = $FolderToBindTo | Where {$_.Name -eq $FolderName}
	#Upload File
	$FileStream = ([System.IO.FileInfo] (Get-Item $fileToUpload)).OpenRead()
	$SourceFileName = Split-path $fileToUpload -leaf
	$FileCreationInfo = New-Object Microsoft.SharePoint.Client.FileCreationInformation
	$FileCreationInfo.Overwrite = $true
	$FileCreationInfo.ContentStream = $FileStream
	$FileCreationInfo.URL = $SourceFileName
	# For rootfolder upload (and you donlt need the retrieve folder section
	#$FileUploaded = $Library.RootFolder.Files.Add($FileCreationInfo)
	$FileUpload = $FolderToUpload.Files.Add($FileCreationInfo)
	#Execute
	$Context.Load($FileUpload) 
	$Context.ExecuteQuery() 
}
### End
###
### Retrieve all TOPdesk info 
### Parameter $query needs to be a valid SQL statement
Function TOPdesk-Query ($query){
	# https://cmatskas.com/execute-sql-query-with-powershell/ - Using DataAdapter and DataSet
	$SqlConnection = New-Object System.Data.SqlClient.SqlConnection   
	$SqlConnection.ConnectionString = "Server = $SqlServer; Database = $SqlDbName; Integrated Security = false; User ID = $sqlusername; Password = $sqlpass;"    
	$SqlConnection.Open()	
	$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
	$SqlQuery = $query
	$SqlCmd.Connection = $SQlConnection
	$SqlCmd.CommandText = $SqlQuery
	$SqlDataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
	$SqlDataAdapter.SelectCommand = $SqlCmd
	$SqlDataSet = New-Object System.Data.DataSet
	$SqlDataAdapter.Fill($SqlDataSet) 
	$SqlConnection.Close()
	Return $SqlDataSet.Tables[0]
}
### End
### End Functions #################################################################################

Check Accounts

Use this script to check for accounts with elevated access (part of specific containers):

# Author: Sjoerd Hooft / https://www.linkedin.com/in/sjoerdhooft/
 
### Versioning and functionality ##################################################################
### 2019 01 31 - Sjoerd Hooft ###
# Collect Evidence for ICT Control 02
### Retrieve and list all user accounts with Elevated Access, sorted on Description  
### Retrieve and list all disabled user accounts
### List Added accounts that are enabled since previous time the script ran 
### List Removed accounts since previous time the script ran 
### List Accounts that are enabled but are never used
### List Accounts that are enabled but haven't been used in 90 days 
###
### 20190 02 05 - Sjoerd Hooft ###
# Added HTML email as output with all TOPdesk changes in email 
# Added ICT CF information to email 
# Added clearity for username and password for both SharePoint and SQL
###################################################################################################
 
### Bugs ##########################################################################################
### Be careful when opening the original Excel Report 
# When ran manually, if the excel file is opened on a worksheet other then the first one, some worksheets may be overwritten. To prevent this, never work with the source excel file but only with the excel file that is sent around
###
### Microsoft Office Excel cannot access the file
# Excel bug: These directories have to exist for this script to work with excel while ran as a scheduled task
# 32bit: * C:\Windows\System32\config\systemprofile\Desktop
# 64bit: * C:\Windows\SysWOW64\config\systemprofile\Desktop
# Expected Error: Exception calling "Open" with "1" argument(s): "Microsoft Office Excel cannot access the file 'xxxxxx'. There are several possible reasons: The file name or path does not exist. The file is being used by another program. The workbook you are trying to save has the same name as a currently open workbook.
###################################################################################################
 
### How-To ########################################################################################
# When adding a datarow to the header part of the report in the script, make sure to add an empty row to the report as well. If you do not the compares will fail as these are dependent on having the headerrow on the same row. 
###
# When changing and testing
## Disable the Upload-Report at the end
## Disable the toemail variable and replace it with one that points just to you 
## Make a backup of the XLSX file
## Make sure the report has something to report by either changing data in the last worksheet or removing the last (few) worksheet(s)
###################################################################################################
 
### Script Overview ###############################################################################
### Fase 1
# Set script variables
# Start Logging
# Check for required modules
# All other Variables 
# Import default functions 
### Fase 2
# Collect all raw data from Active Directory and sort on Description
### Fase 3
# Start Excel and make sense of the raw data 
# Find Accounts that are enabled but are never used
# Find Accounts that are enabled but haven't been used in 90 days 
### Fase 4
# Compare the current data with the data from the previous run to find added and removed accounts 
### Fase 5
# Add the data from fase 4 to the excel file
# Apply formatting to Excel file 
### Fase 6
# Find all matching TOPdesk CRs 
### Fase 7
# Send report as required 
# Cleanup temporary files
# Stop Logging 
###################################################################################################
 
########################################## Start Fase 1 ###########################################
 
# Script Variables
$scriptname = [System.IO.Path]::GetFilenameWithoutExtension($MyInvocation.MyCommand.Path.ToString())
$scriptlocation = Split-Path $myinvocation.mycommand.path
 
# Start transcript for full logging capabilities
start-transcript -path "$scriptlocation\logtranscript.txt"
 
# Check Required Modules
if (Get-Module -ListAvailable -Name ActiveDirectory) {
    Write-Host "Module ActiveDirectory exists"}
else {Write-Host "Module ActiveDirectory does not exist"}
if (Get-Module -ListAvailable -Name Microsoft.Online.SharePoint.PowerShell) {
    Write-Host "Module Microsoft.Online.SharePoint.PowerShell exists"
	# Module needs to be imported and Installed: #Install-Module -Name Microsoft.Online.SharePoint.PowerShell
	Import-Module Microsoft.Online.SharePoint.PowerShell}
else {Write-Host "Module Microsoft.Online.SharePoint.PowerShell does not exist"}
 
# Date & Time Variables
$timestamp = Get-Date -format "yyyyMMdd-HH.mm"
$readdate = Get-Date -format "d MMM yyyy"
$weekdate = Get-Date -uformat %V
$monthdate = Get-Date -format "MMMM-yyyy"
 
# Reporting Variables
### Email Variables
$report = "$scriptlocation\$scriptname.xlsx"
$monthreport = "$scriptlocation\$monthdate-$scriptname.xlsx"
$mailserver = "mailserver1"
#$toemail = "sjoerd_getshifting.com"
$toemail = "customer_getshifting.com", "customer2_getshifting.com"
$ccmail = "sjoerd_getshifting.com", "customer3_getshifting.com", "customer4_getshifting.com", "customer5_getshifting.com", "customer6_getshifting.com"
$fromemail = "$scriptname_getshifting.com"
# Make the email start specific for this control
$info = "Dear $toemail , <br>"
$info += "<br>"
$info += "This is the automatic report as run for ICT Control Framework 02<br>"
$info += "<i>What do you need to do:</i><br>"
$info += "<i>What:</i> For each supplier, the registered users in the central identity system (AD) are confirmed with the supplier (identity, employment status, last logon). Inconsistencies are followed up by registering an ICT incident/change.<br>"
$info += "<i>Who:</i> Managers of the ICT teams Application, Infrastructure, Servicedesk & FS and manager of the Finance team BI (are the stakeholders).<br>"
$info += "<i>When:</i> Monthly, to be executed before the 16th of each month. <br>"
$info += "<br>"
$info += "Check the inconsistencies below and add the TOPdesk references that are missing. When done, print as pdf and upload the pdf to SharePoint - Online<br>"
$info += "<b>The following inconsistencies were found:</b> <br>"
$info += "<br>"
### SharePoint Variables
$spusername = "sa_automation_getshifting.com"
# NOTE: Run once per server per user: read-host -assecurestring | convertfrom-securestring | out-file "$scriptlocation\spcreds.txt"
$sppassword = get-content "$scriptlocation\spcreds.txt" | convertto-securestring
$cred = new-object -typename System.Management.Automation.PSCredential -argumentlist $spusername,$sppassword
$Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($cred.UserName,$cred.Password)
# SharePoint Online Site
$WebUrl = "https://getshifting.sharepoint.com/teams/ICT/ICT Control Framework Evidence"
$Context = New-Object Microsoft.SharePoint.Client.ClientContext($WebUrl)
$Context.Credentials = $Credentials
# SharePoint Library
$LibraryName = "Documents"
#Foldername can only be one level deep
$FolderName = "02 Third Party Access"
$Library =  $Context.Web.Lists.GetByTitle($LibraryName)
 
# TOPdesk (SQL) Variables
$SqlServer = "WIN-SQL01\SQL01"
$SqlDbName = "TopDeskPrd8"
$SqlUser = "TOPdesk_user"
# NOTE: Run once per server per user: read-host -assecurestring | convertfrom-securestring | out-file "sqlcreds.txt"
$SqlPassWord = get-content "$scriptlocation\sqlcreds.txt" | convertto-securestring
$credentials = new-object -typename System.Management.Automation.PSCredential -argumentlist $SqlUser,$SqlPassWord
$sqlusername = $credentials.UserName
$sqlpass = $credentials.GetNetworkCredential().Password
 
### Start Functions ###
# Import functions from global function file
. "$scriptlocation\Functions.ps1"
 
########################################## Start Fase 2 ###########################################
 
# Create a raw list of AD accounts
$servermanagement = Get-ADUser -Filter * -Properties name, description, SamAccountName, LastLogonDate, Enabled -SearchBase "OU=Server management,OU=DELFT,DC=ad,DC=getshifting,DC=com"
$outsourcingict = Get-ADUser -Filter * -Properties name, description, SamAccountName, LastLogonDate, Enabled -SearchBase "OU=Outsourcing ICT,OU=Users,OU=DELFT,DC=ad,DC=getshifting,DC=com"
# WHY DISABLED USERS - hier zouden alle disabled accounts moeten staan maar dat is niet zo 
$disabledusers = Get-ADUser -Filter * -Properties name, description, SamAccountName, LastLogonDate, Enabled -SearchBase "OU=Disabled,OU=Users,OU=DELFT,DC=ad,DC=getshifting,DC=com"
$adusersunsorted = $servermanagement + $outsourcingict + $disabledusers
$adusers = $adusersunsorted | sort description
$countusers = $adusers.Count
 
# Create a raw list of AD accounts that have never logged on and are enabled
$neverusedservermanagement = Get-ADUser -Filter {(lastlogondate -notlike "*" -AND enabled -eq "True")} -SearchBase "OU=Server management,OU=DELFT,DC=ad,DC=getshifting,DC=com" 
$neverusedoutsourcingict = Get-ADUser -Filter {(lastlogondate -notlike "*" -AND enabled -eq "True")} -SearchBase "OU=Outsourcing ICT,OU=Users,OU=DELFT,DC=ad,DC=getshifting,DC=com" 
$neveruseddisabledusers = Get-ADUser -Filter {(lastlogondate -notlike "*" -AND enabled -eq "True")} -SearchBase "OU=Disabled,OU=Users,OU=DELFT,DC=ad,DC=getshifting,DC=com" 
$neverusedadusers = $neverusedservermanagement + $neverusedoutsourcingict + $neveruseddisabledusers
$countneverusedadusers = $neverusedadusers.Count
 
# Create a raw list of AD accounts that have last logged on over 90 days ago and are enabled
$90Days = (get-date).adddays(-90)
$90daysservermanagement = Get-ADUser -Filter {(lastlogondate -le $90days -AND enabled -eq "True")} -SearchBase "OU=Server management,OU=DELFT,DC=ad,DC=getshifting,DC=com" 
$90daysoutsourcingict = Get-ADUser -Filter {(lastlogondate -le $90days -AND enabled -eq "True")} -SearchBase "OU=Outsourcing ICT,OU=Users,OU=DELFT,DC=ad,DC=getshifting,DC=com" 
$90daysdisabledusers = Get-ADUser -Filter {(lastlogondate -le $90days -AND enabled -eq "True")} -SearchBase "OU=Disabled,OU=Users,OU=DELFT,DC=ad,DC=getshifting,DC=com" 
$90daysadusers = $90daysservermanagement + $90daysoutsourcingict + $90daysdisabledusers
$count90daysadusers = $90daysadusers.Count
 
### Test Mode ###
###$adusers = Get-ADUser sht1901 -Properties name, description, SamAccountName, LastLogonDate, Enabled
###$countusers = 1
 
########################################## Start Fase 3 ###########################################
 
# Set Excel Report Properties
$Excel = New-Object -ComObject Excel.Application
# Set to true for monitoring progress, to false for scheduled creation
$Excel.visible = $false
# Set to False to save existing files without warning
$Excel.DisplayAlerts = $False
$workfile = $Excel.Workbooks.open($report)
$worksheet = $workfile.Worksheets.Add()
$Sheet = $workfile.Worksheets.Item(1)
$Sheet.Name = "$timestamp"
 
# Remove old worksheets after two years and one month (25) if run each month - worksheet 26 is deleted
$workfile.Worksheets.Item(26).Delete()
 
### Set Report Header and Information ###
# Report Header
$Sheet.Cells.Item(1,1) = "ICT Control Framework: 02 Third Party Access"
$range = $Sheet.Range("a1","e2")
$range.Style = 'Title'
$range.Merge() | Out-Null
$range.VerticalAlignment = -4108
$range.HorizontalAlignment = -4108
# Date Information
$Sheet.Cells.Item(3,1) = "Month: $monthdate"
$Sheet.Cells.Item(3,3) = "Date: $readdate" 
$Sheet.Cells.Item(5,1) = "Added Accounts:" 
$Sheet.Cells.Item(6,1) = "Removed Accounts:" 
$Sheet.Cells.Item(7,1) = "Never Used Accounts ($countneverusedadusers):" 
$Sheet.Cells.Item(8,1) = "Accounts used > 90 days ago ($count90daysadusers):" 
$range = $Sheet.Range("a3","e4")
$range.Style = 'Title'
$range.VerticalAlignment = -4108
$range.HorizontalAlignment = -4108
$range = $Sheet.Range("a3","b4")
$range.Merge() | Out-Null
$range = $Sheet.Range("c3","e4")
$range.Merge() | Out-Null
$range = $Sheet.Range("b5","e5")
$range.Merge() | Out-Null
$range = $Sheet.Range("b5","e5")
$range.Merge() | Out-Null
$range = $Sheet.Range("b6","e6")
$range.Merge() | Out-Null
$range = $Sheet.Range("b7","e7")
$range.Merge() | Out-Null
$range = $Sheet.Range("b8","e8")
$range.Merge() | Out-Null
$range = $Sheet.Range("a9","e9")
$range.Merge() | Out-Null
# Set Custom Data Headers
# Use variable for data header row, depending on amount of information above the actual data
$headerrow = 10
$Sheet.Cells.Item($headerrow,1) = "Name"
$Sheet.Cells.Item($headerrow,2) = "Description"
$Sheet.Cells.Item($headerrow,3) = "Account Name"
$Sheet.Cells.Item($headerrow,4) = "Last Logon Date"
$Sheet.Cells.Item($headerrow,5) = "Account Enabled"
$range = $Sheet.Range("a$headerrow","e$headerrow")
$range.Font.Bold = $True
$range.Font.ColorIndex = 2
$range.Interior.ColorIndex = 1
# Automatically provide a filter on the headerrow
$range.AutoFilter() | Out-Null
 
# List AD Accounts
# Start below the data header row
$dataRow = $headerrow + 1
 
ForEach ($aduser in $adusers){
  # Change enabled state in list to number for icon replacement later on
  if ($aduser.enabled -eq "True"){
	$enabled = 1}
  else{
	$enabled = 0}
  # Find users that have never logged on
  if (($aduser.lastlogondate -eq $null) -and ($aduser.enabled -eq "True")){
	# Convert object to string
	$tempnever = [string]$aduser.Name
	$neverusedaccounts = $neverusedaccounts + "$tempnever; "}
  # Find users that have not logged on for 90 days or more
  if (($aduser.lastlogondate -ne $null) -and ($aduser.lastlogondate -le $90days) -and ($aduser.enabled -eq "True")){
	# Convert object to string
	$temp90days = [string]$aduser.Name
	$90daysaccounts = $90daysaccounts + "$temp90days; "}
 
  $Sheet.Cells.Item($dataRow, 1) = [string]$aduser.Name
  $Sheet.Cells.Item($dataRow, 2) = [string]$aduser.Description
  $Sheet.Cells.Item($dataRow, 3) = [string]$aduser.SamAccountName
  $Sheet.Cells.Item($dataRow, 4) = [string]$aduser.LastLogonDate
  $Sheet.Cells.Item($dataRow, 5) = [string]$enabled
 
  $datarow++
}
 
# Use a symbol to indicate a user is enabled or not
# Define enabled range
$startrow = $headerrow + 1
$endrow = $startrow + $countusers
$enabledcol = "e"
$r1 = $enabledcol + $startrow
$r2 = $enabledcol + $endrow
$range = $Sheet.Range("$r1","$r2")
$range.VerticalAlignment = -4108
$range.HorizontalAlignment = -4108
# Change number into icon
$xlConditionValueNumber = 0
$xlIconSet = 6
$xl3Symbols = 7
$range.FormatConditions.Add($xlIconSet)
$range.FormatConditions.AddIconSetCondition
$range.FormatConditions.Item(1).ShowIconOnly = $true
$range.FormatConditions.Item(1).IconSet = $workfile.IconSets.Item($xl3Symbols)
$range.FormatConditions.Item(1).IconCriteria.Item(2).Value = 0
$range.FormatConditions.Item(1).IconCriteria.Item(2).Operator = 5
$range.FormatConditions.Item(1).IconCriteria.Item(3).Value = 1
$range.FormatConditions.Item(1).IconCriteria.Item(3).Operator = 7
 
# Save, close and quit Excel because otherwise the excel files gets saved as csv file
$Sheet.SaveAs($report)
$workfile.Close()
# Wat for the filesystem to close the file
sleep 5
$Excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
 
########################################## Start Fase 4 ###########################################
 
# Find Changes Compared To Last Time
# Comparing is easy with csv files
$csvfile1 = "$scriptlocation\$scriptname-01.csv"
$csvfile2 = "$scriptlocation\$scriptname-02.csv"
$csvfile3 = "$scriptlocation\$scriptname-03.csv"
$csvfile4 = "$scriptlocation\$scriptname-04.csv"
# Open Excel Report (again)
$Excel = New-Object -ComObject Excel.Application
# Set to true for monitoring progress, to false for scheduled creation
$Excel.visible = $false
# Set to False to save existing files without warning
$Excel.DisplayAlerts = $False
$workfile = $Excel.Workbooks.open($report)
$sheet = $workfile.Worksheets.Item(1)
$sheet.SaveAs("$csvfile1", 6)
sleep 3
$lastWeekSheet = $workfile.Worksheets.Item(2)
$lastWeekSheet.SaveAs("$csvfile2", 6)
sleep 3
# Create new csv files without the header info
$skip = $headerrow - 1
Get-Content $csvfile1 | Select -skip $skip | Set-Content $csvfile3
sleep 3
Get-Content $csvfile2 | Select -skip $skip | Set-Content $csvfile4
sleep 3
# Load csv files - Find Removed Accounts
$thisweek = Import-CSV $csvfile3
$lastweek = Import-CSV $csvfile4
$removedaccounts = Compare $thisweek $lastweek -Property Name | where {$_.SideIndicator -eq '=>'} | Select Name | ForEach {$_.Name}
$countremovedaccounts = $removedaccounts.Count
$allremovedaccounts = $removedaccounts -join '; '
# Load csv files - Find Added Accounts. Added accounts are only relevant when enabled, we don't wanna know about users that have been disabled
$thisweek = Import-CSV $csvfile3 | where {$_."Account Enabled" -eq 1}
$lastweek = Import-CSV $csvfile4 | where {$_."Account Enabled" -eq 1}
$addedaccounts = Compare $thisweek $lastweek -Property Name | where {$_.SideIndicator -eq '<='} | Select Name | ForEach {$_.Name}
$countaddedaccounts = $addedaccounts.Count
$alladdedaccounts = $addedaccounts -join '; '
#Quit Excel without saving
sleep 5
$Excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null
 
########################################## Start Fase 5 ###########################################
 
# Open Excel Report (again)
$Excel = New-Object -ComObject Excel.Application
# Set to true for monitoring progress, to false for scheduled creation
$Excel.visible = $false
# Set to False to save existing files without warning
$Excel.DisplayAlerts = $False
$workfile = $Excel.Workbooks.open($report)
$sheet = $workfile.Worksheets.Item(1)
# Overwrite text
$Sheet.Cells.Item(5,1) = "Added Accounts ($countaddedaccounts):" 
$Sheet.Cells.Item(6,1) = "Removed Accounts ($countremovedaccounts):" 
# Add account changes to sheet
$Sheet.Cells.Item(5,2) = "$alladdedaccounts" 
$Sheet.Cells.Item(6,2) = "$allremovedaccounts" 
$Sheet.Cells.Item(7,2) = "$neverusedaccounts"
$Sheet.Cells.Item(8,2) = "$90daysaccounts"
 
### Final Report Modifications ###
# Set Excel formatting
$formatting = $Sheet.UsedRange
$formatting.EntireColumn.AutoFit()
# Wrap Text but it won't change the autofit because cells are merged. 
# Only way around this is setting the data below the header and not doing a merge 
$formatting.WrapText = $true
 
# Save, close and quit Excel
$Sheet.SaveAs($report)
$workfile.Close()
# Wat for the filesystem to close the file
sleep 5
$Excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
 
########################################## Start Fase 6 ###########################################
 
### Make the list of anomalies that have to be checked for corresponding changes in TOPdesk
# Re-use the csv files to check for the removed accounts
$thisweek = Import-CSV $csvfile3
$lastweek = Import-CSV $csvfile4
$removedfortopdesk = Compare $thisweek $lastweek -Property Name,"Account Name" | where {$_.SideIndicator -eq '=>'} | Select Name,"Account Name"
# Re-use the csv files to check for the added accounts
$thisweek = Import-CSV $csvfile3 | where {$_."Account Enabled" -eq 1}
$lastweek = Import-CSV $csvfile4 | where {$_."Account Enabled" -eq 1}
$addedfortopdesk = Compare $thisweek $lastweek -Property Name,"Account Name" | where {$_.SideIndicator -eq '<='} | Select Name,"Account Name"
# Now combine the added and removed accounts, and sort and remove doubles (there shouldn't be any, but just in case)
$anomaliestemp = $addedfortopdesk + $removedfortopdesk
$anomalies  = $anomaliestemp | sort -Property name -unique
 
### Create TOPdesk data set with all changes for the last 6 months
$180days = (Get-Date).adddays(-180).ToString('MM/dd/yyyy HH:mm:ss')
$TOPdeskSQLQuery = $("SELECT number,status,calldate,briefdescription,description,unid FROM change WHERE status > 0 and calldate > '$180days'")
$TOPdeskDataSet = TOPdesk-Query $TOPdeskSQLQuery
 
### Match the anomalies and TOPdesk results with each other and add the info to the email body
ForEach ($anomaly in $anomalies){
	$anomalyname = $anomaly.Name
	$anomalysam = $anomaly."Account Name"
	$crsdescription = $TOPdeskDataSet | where {$_.description -like "*$anomalysam*"} | select number, briefdescription, unid
	#Count won't work with 1 result, use measure object
	$crsdescriptioncount = ($crsdescription | Measure-Object).Count
	$crsbriefdescription = $TOPdeskDataSet | where {$_.briefdescription -like "*$anomalyname*"} | select number, briefdescription, unid 
	$crsbriefdescriptioncount = ($crsbriefdescription | Measure-Object).Count
	write-host "Searched for TOPdesk CRs with $anomaly in the briefdescription ($crsbriefdescriptioncount results) or description (request) ($crsdescriptioncount results) fields. "
	# We don't trust the results if the count is higher than 5. The search was probably on a too generic search term. 
	# Example MBE is a username, but is also matches on november or december
	if ($crsdescriptioncount -gt 5){
		Clear-Variable crsdescription
		write-host "Description had too much results. This should be empty now: $crsdescription"
	}
	if ($crsbriefdescriptioncount -gt 5){
		Clear-Variable crsbriefdescription
		write-host "Brief description had too much results. This should be empty now: $crsbriefdescription"
	}
	# Now we combine the results
	$allTOPdeskCRsTemp=@()
	ForEach ($cr in $crsdescription){
		$allTOPdeskCRsTemp += $cr
	}
	ForEach ($cr in $crsbriefdescription){
		$allTOPdeskCRsTemp += $cr
	}
	# And sort the results and remove doubles
	$allTOPdeskCRs = $allTOPdeskCRsTemp | sort -Property number -unique
	# Let's check the results
	#write-host "All Topdesk CRs for $anomaly : "
	#$allTOPdeskCRs
 
	### Add the information to the email - because inline styling is hard without css we give each anomaly a table of it's own
	$info += '<table style="width:100%; border: 1px solid #dddddd; text-align: left;">'
	$info += "<tr>"
	$info += '<th style="width: 200px; text-align: left;">'
	$info += "$anomalyname"
	$info += '</th>'
	$info += '<td style="width: 100px; text-align: left;">'
	$info += "$anomalysam"
	$info += '</td>'
	$info += '<td style="text-align: left;">'
	# Now add the TOPdesk links to the CRs
	if ($allTOPdeskCRs.count -ne 0){
		ForEach ($cr in $allTOPdeskCRs){
			$TDCR = $cr.number
			$TDD = $cr.briefdescription
			$TCUNID = $cr.unid
			$info += "<a href=http://topdesk/tas/secure/newchange?unid=$TCUNID>$TDCR</a> - $TDD <br>"
		}
	}else{
		$info += "None or too many matching TOPdesk changes found in the last half year <br>"
		$info += "Fill in TOPdesk reference: <br>"
	}
	$info += '</td>'
	$info += "</tr>"
	$info += "</table>"
}
 
########################################## Start Fase 7 ###########################################
 
# Send Report
#Copy file with monthdate so it will be unique on SharePoint
Copy-Item $report $monthreport
$subject = "ICT Control Framework 02 for month: $monthdate"
#$info = "Find the report attached to this email. Kind regards, Sjoerd Hooft."
Send-Email $subject $info
Upload-Report $monthreport
 
#Cleanup files
Remove-Item $csvfile1
Remove-Item $csvfile2
Remove-Item $csvfile3
Remove-Item $csvfile4
Remove-Item $monthreport
 
# Stop transcript 
stop-transcript

Check Local Admin Access on Servers

Check this script to check all servers in AD for local admin access, check for changes compared to last time.

# Author: Sjoerd Hooft / https://www.linkedin.com/in/sjoerdhooft/
 
### Versioning and functionality ##################################################################
### 2019 01 31 - Sjoerd Hooft ###
# Collect Evidence for ICT Control 06
### Retrieve and list all servers in Active Directory 
### Retrieve and list all users and groups with local admin permissions on these servers
### List all servers that are not available either by ping or wmi 
### List added and removed servers since the last time the script ran  
### List servers that do not hold local admins (applicable for a.o. cluster objects)
### List accounts that are not service accounts (sa_ or scom) but are direct member of the local administrators group 
### List regular (non SYS) accounts that have local admin access (skips memvers of the "Citrix XenApp 7 ICT" group)
####### If this changed since last time the script ran it does not show the current accounts but the changes
### List added and removed groups since the last time the script ran 
### List empty groups
### List added and removed users to and from groups since the last time the script ran 
### List members of the Domain Admin Group
####### If this changed since last time the script ran it does not show the current accounts but the changes
### List members of the Citrix XenApp 7 ICT group . This group grants local admin permissions to regular accounts on WIN-CTX7-D1 and D2 servers
####### If this changed since last time the script ran it does not show the current accounts but the changes
###
### 2019 02 05 - Sjoerd Hooft ###
# Added HTML email as output with all TOPdesk changes in email 
# Added ICT CF information to email 
# Added clearity for username and password for both SharePoint and SQL
### 2019 02 14 - Sjoerd Hooft ###
# Moved CheckAvailability Function to common functions script 
###################################################################################################
 
### Bugs ##########################################################################################
### Be careful when opening the original Excel Report 
# When ran manually, if the excel file is opened on a worksheet other then the first one, some worksheets may be overwritten. To prevent this, never work with the source excel file but only with the excel file that is sent around
### Microsoft Office Excel cannot access the file
# Excel bug: These directories have to exist for this script to work with excel while ran as a scheduled task
# 32bit: * C:\Windows\System32\config\systemprofile\Desktop
# 64bit: * C:\Windows\SysWOW64\config\systemprofile\Desktop
# Expected Error: Exception calling "Open" with "1" argument(s): "Microsoft Office Excel cannot access the file 'xxxxxx'. There are several possible reasons: The file name or path does not exist. The file is being used by another program. The workbook you are trying to save has the same name as a currently open workbook.
###################################################################################################
 
### How-To ########################################################################################
# When adding a datarow to the header part of the report in the script, make sure to add an empty row to the report as well. If you do not the compares will fail as these are dependent on having the headerrow on the same row. 
###
# When changing and testing
## Disable the Upload-Report at the end
## Disable the toemail variable and replace it with one that points just to you 
## Make a backup of the XLSX file
## Make sure the report has something to report by either changing data in the last worksheet or removing the last (few) worksheet(s)
###################################################################################################
 
### Script Overview ###############################################################################
### Fase 1
# Set script variables
# Start Logging
# Check for required modules
# All other Variables 
# Import default functions 
# Script specific functions
### Fase 2
# Start Excel 
# Collect all raw data (servers, groups and groupmembers) from Active Directory and from the local servers through WMI
# Find servers that are not available either by ping or wmi
### Fase 3
# Compare the current data with the data from the previous run to find 
## added and removed servers
## added and removed groups
## added and removed groupmemberships
## membership and changes to domain admins, Citrix XenApp 7 ICT and regular accounts
## accounts that are member of the local administrator groups directly
### Fase 4
# Add the data from fase 3 to the excel file
# Apply formatting to Excel file 
### Fase 5
# Find all matching TOPdesk CRs 
### Fase 6
# Send report as required 
# Cleanup temporary files
# Stop Logging 
###################################################################################################
 
########################################## Start Fase 1 ###########################################
 
# Script Variables
$scriptname = [System.IO.Path]::GetFilenameWithoutExtension($MyInvocation.MyCommand.Path.ToString())
$scriptlocation = Split-Path $myinvocation.mycommand.path
 
# Start transcript for full logging capabilities
start-transcript -path "$scriptlocation\logtranscript.txt"
 
# Check Required Modules ### No exit to try to complete as mich of the script as possible
if (Get-Module -ListAvailable -Name ActiveDirectory) {
    Write-Host "Module ActiveDirectory exists"}
else {Write-Host "Module ActiveDirectory does not exist"}
if (Get-Module -ListAvailable -Name Microsoft.Online.SharePoint.PowerShell) {
    Write-Host "Module Microsoft.Online.SharePoint.PowerShell exists"
	# Module needs to be imported and Installed: #Install-Module -Name Microsoft.Online.SharePoint.PowerShell
	Import-Module Microsoft.Online.SharePoint.PowerShell}
else {Write-Host "Module Microsoft.Online.SharePoint.PowerShell does not exist"}
 
# Date & Time Variables
$timestamp = Get-Date -format "yyyyMMdd-HH.mm"
$readdate = Get-Date -format "d MMM yyyy"
$weekdate = Get-Date -uformat %V
$monthdate = Get-Date -format "MMMM-yyyy"
 
# Reporting Variables
### Email Variables
$report = "$scriptlocation\$scriptname.xlsx"
$monthreport = "$scriptlocation\$monthdate-$scriptname.xlsx"
$mailserver = "mailserver1"
#$toemail = "sjoerd_getshifting.com"
$toemail = "customer_getshifting.com", "customer2_getshifting.com"
$ccmail = "sjoerd_getshifting.com", "customer3_getshifting.com", "customer4_getshifting.com", "customer5_getshifting.com"
$fromemail = "$scriptname_getshifting.com"
# Make the email start specific for this control
$info = "Dear $toemail , <br>"
$info += "<br>"
$info += "This is the automatic report as run for ICT Control Framework 06<br>"
$info += "<i>What do you need to do:</i><br>"
$info += "<i>What:</i> The following checks are performed: <br><b>a)</b>	The Domain Admin group in Active Directory contains only the SYS accounts of employees from the ICT Infrastructure team (by ICT Infrastructure team); <br><b>b)</b>	SYS accounts have only access to servers via local admin groups (by ICT Infrastructure team); <br><b>c)</b>	Normal user accounts have no access at all to server management (by ICT Infrastructure team); <br><b>d)</b>	Access to the various database servers is in line with agreed authorization set-up and third party access (by ICT Infrastructure team); <br><b>e)</b>	Access to the various local admin groups per application is in line with the system documentation for each application (by ICT Applications team); <br><b>f)</b>	Access of service accounts to servers is in line with the system documentation for the core applications (by ICT Applications team); <br><b>g)</b>	The existing SYS accounts in AD are in line with agreed access for both employees, as well as employees of third parties (see monitoring control 02 Third party access control). <br>Inconsistencies are investigated and when needed explained or followed-up by registering an ICT incident/change. <br>"
$info += "<i>Who:</i> Manager ICT Infrastructure team: <b>a)</b>, <b>b)</b>, <b>c)</b> and <b>d)</b> <br>Manager ICT Applications team: <b>e)</b> and <b>f)</b> <br>The <b>g)</b> is part of monitoring control 02 Third party access. <br>"
$info += "<i>When:</i> Monthly, to be executed before the 16th of each month. <br>"
$info += "<br>"
$info += "Check the inconsistencies below and add the TOPdesk references that are missing. When done, print as pdf and upload the pdf to SharePoint Online<br>"
$info += "<b>The following inconsistencies were found:</b> <br>"
$info += "<br>"
### SharePoint Variables
$spusername = "sa_automation_getshifting.com"
# NOTE: Run once per server per user: read-host -assecurestring | convertfrom-securestring | out-file "$scriptlocation\spcreds.txt"
$sppassword = get-content "$scriptlocation\spcreds.txt" | convertto-securestring
$cred = new-object -typename System.Management.Automation.PSCredential -argumentlist $spusername,$sppassword
$Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($cred.UserName,$cred.Password)
# SharePoint Online Site
$WebUrl = "https://getshifting.sharepoint.com/teams/ICT/ICT Control Framework Evidence"
$Context = New-Object Microsoft.SharePoint.Client.ClientContext($WebUrl)
$Context.Credentials = $Credentials
# SharePoint Library
$LibraryName = "Documents"
#Foldername can only be one level deep
$FolderName = "06 Privileged Access (local admin)"
$Library =  $Context.Web.Lists.GetByTitle($LibraryName)
 
# TOPdesk (SQL) Variables
$SqlServer = "WIN-SQL01\SQL01"
$SqlDbName = "TopDeskPrd8"
$SqlUser = "TOPdesk_user"
# NOTE: Run once per server per user: read-host -assecurestring | convertfrom-securestring | out-file "sqlcreds.txt"
$SqlPassWord = get-content "$scriptlocation\sqlcreds.txt" | convertto-securestring
$credentials = new-object -typename System.Management.Automation.PSCredential -argumentlist $SqlUser,$SqlPassWord
$sqlusername = $credentials.UserName
$sqlpass = $credentials.GetNetworkCredential().Password
 
# Other Variables
$offlinepingcount = 0
$offlinewmicount = 0
$nolocaladminscount = 0
# Rename the domain admins group if you changed the name
$domainadmins = "Domain Admins"
# The group below grants local admin access to normal accounts so it needs to be monitored
$ictlocaladmingroup = "Citrix XenApp 7 ICT"
# Use a script variable so the counter can be updated inside the function
$script:emptygroupscount = 0
 
### Start Functions ###
# Import functions from global function file
. "$scriptlocation\Functions.ps1"
 
### Other Functions
### Moved to Functions script
#Function CheckAvailability($computername){
#	#$computername = $computer.Name
#	if (Test-Connection $computername -quiet -count 1){
#		# Computer answers to ping
#		if (get-wmiobject -ErrorAction SilentlyContinue -computername $computername "win32_process"){
#			#Computer reageert op wmi
#			Return "OK"}
#		else{Return "WMIError"}}
#	else{Return "PingError"}
#}
 
Function GroupNesting($groupmembers){
	foreach ($groupmember in $groupmembers){
		if (Get-ADObject -Filter {objectClass -eq "Group" -and samAccountName -eq $groupmember}){
			$nestlevel++
			$script:datarow++
			#Prevent infinite nesting loops if someone made groups member of each other
			$maxnestinglevel = 5
			if ($nestlevel -le $maxnestinglevel){
				$groupname = $groupmember
				$nextlevelgroupmembers = Get-ADGroupMember $groupname | select -expandproperty SamAccountName
				# Make note of empty groups
				if ($nextlevelgroupmembers.Count -eq 0){
					Write-Host "This is an empty group: $groupmember "
					$script:emptygroups = $script:emptygroups + "$groupname; "
					$script:emptygroupscount++
				}
				$allnextlevelgroupmembers = $nextlevelgroupmembers -join '; '
				#write-host "Rij = $script:datarow - $groupmember is also a group in nestlevel $nestlevel with members $allnextlevelgroupmembers " 
				write-host "Rij = $script:datarow - $groupmember is also a group in nestlevel $nestlevel " 
				$Sheet.Cells.Item($script:datarow, 1) = $computername
				$Sheet.Cells.Item($script:datarow, 2) = [string]$groupname
				$Sheet.Cells.Item($script:datarow, 3) = "SHIFT Domain Group Nestlevel $nestlevel"
				$Sheet.Cells.Item($script:datarow, 4) = [string]$allnextlevelgroupmembers
				GroupNesting $nextlevelgroupmembers
			}
			else{
				write-host "Rij = $script:datarow - $groupmember is nested more than 5 levels deep. Please review group memberships."
				$Sheet.Cells.Item($script:datarow, 1) = $computername
				$Sheet.Cells.Item($script:datarow, 2) = [string]$groupmember
				$Sheet.Cells.Item($script:datarow, 3) = "SHIFT Domain Group Nested Beyond Level $maxnestinglevel"
			}
		}
	}
}
 
########################################## Start Fase 2 ###########################################
 
# Set Excel Report Properties
$Excel = New-Object -ComObject Excel.Application
# Set to true for monitoring progress, to false for scheduled creation
$Excel.visible = $false
# Set to False to save existing files without warning
$Excel.DisplayAlerts = $False
$workfile = $Excel.Workbooks.open($report)
$worksheet = $workfile.Worksheets.Add()
$Sheet = $workfile.Worksheets.Item(1)
$Sheet.Name = "$timestamp"
 
# Remove old worksheets after two years and one month (25) if run each month - worksheet 26 is deleted
$workfile.Worksheets.Item(26).Delete()
 
### Set Report Header and Information ###
# Report Header
$Sheet.Cells.Item(1,1) = "ICT Control Framework: 06 Privileged Access (local admin)"
$range = $Sheet.Range("a1","d2")
$range.Style = 'Title'
$range.Merge() | Out-Null
$range.VerticalAlignment = -4108
$range.HorizontalAlignment = -4108
# Date Information
$Sheet.Cells.Item(3,1) = "Month: $monthdate"
$Sheet.Cells.Item(3,3) = "Date: $readdate" 
$Sheet.Cells.Item(5,1) = "Total AD Servers:" 
#$Sheet.Cells.Item(6,1) = "Servers No Ping:" 
#$Sheet.Cells.Item(7,1) = "Servers No WMI:" 
#$Sheet.Cells.Item(8,1) = "Added Servers:" 
#$Sheet.Cells.Item(9,1) = "Removed Servers:" 
#$Sheet.Cells.Item(10,1) = "Servers without Local Admins:" 
#$Sheet.Cells.Item(11,1) = "Non-Service Local Admin Accounts without Group:" 
#$Sheet.Cells.Item(12,1) = "Servers with regular accounts as Local Admin Accounts:" 
#$Sheet.Cells.Item(13,1) = "Added Groups:" 
#$Sheet.Cells.Item(14,1) = "Removed Groups:" 
#$Sheet.Cells.Item(15,1) = "Empty Groups:" 
#$Sheet.Cells.Item(16,1) = "Added Users to Groups:" 
#$Sheet.Cells.Item(17,1) = "Removed Users from Groups:" 
#$Sheet.Cells.Item(18,1) = "Domain Admin Group Members:"
#$Sheet.Cells.Item(19,1) = "Citrix Local Admins:"
$range = $Sheet.Range("a3","e4")
$range.Style = 'Title'
$range.VerticalAlignment = -4108
$range.HorizontalAlignment = -4108
$range = $Sheet.Range("a3","b4")
$range.Merge() | Out-Null
$range = $Sheet.Range("c3","d4")
$range.Merge() | Out-Null
# Range voor total AD servers hoeft niet gemerged te worden 
#$range = $Sheet.Range("b5","d5")
#$range.Merge() | Out-Null
$range = $Sheet.Range("b6","d6")
$range.Merge() | Out-Null
$range = $Sheet.Range("b7","d7")
$range.Merge() | Out-Null
$range = $Sheet.Range("b8","d8")
$range.Merge() | Out-Null
$range = $Sheet.Range("b9","d9")
$range.Merge() | Out-Null
$range = $Sheet.Range("b10","d10")
$range.Merge() | Out-Null
$range = $Sheet.Range("b11","d11")
$range.Merge() | Out-Null
$range = $Sheet.Range("b12","d12")
$range.Merge() | Out-Null
$range = $Sheet.Range("b13","d13")
$range.Merge() | Out-Null
$range = $Sheet.Range("b14","d14")
$range.Merge() | Out-Null
$range = $Sheet.Range("b15","d15")
$range.Merge() | Out-Null
$range = $Sheet.Range("b16","d16")
$range.Merge() | Out-Null
$range = $Sheet.Range("b17","d17")
$range.Merge() | Out-Null
$range = $Sheet.Range("b18","d18")
$range.Merge() | Out-Null
$range = $Sheet.Range("b19","d19")
$range.Merge() | Out-Null
# Insert empty row
$range = $Sheet.Range("a20","d20")
$range.Merge() | Out-Null
# Set Custom Data Headers
# Use variable for data header row, depending on amount of information above the actual data
$headerrow = 21
$Sheet.Cells.Item($headerrow,1) = "Server Name"
$Sheet.Cells.Item($headerrow,2) = "Members of Administrators Group"
$Sheet.Cells.Item($headerrow,3) = "Object Type"
$Sheet.Cells.Item($headerrow,4) = "GroupMembers"
$range = $Sheet.Range("a$headerrow","d$headerrow")
$range.Font.Bold = $True
$range.Font.ColorIndex = 2
$range.Interior.ColorIndex = 1
# Automatically provide a filter on the headerrow
$range.AutoFilter() | Out-Null
 
# Create a raw list of AD servers (Note, won't work for Windows 2000)
$computers = Get-ADComputer -Filter {(operatingSystem -like "*windows*Server*")}
$computercount = [string]$computers.count
$Sheet.Cells.Item(5,2) = $computercount
 
# List AD Computers
# Start below the data header row, and use a script variable so I can modify it in the function
$script:dataRow = $headerrow + 1
 
ForEach ($computer in $computers){
	$computername = [string]$computer.Name
	$serverstate = CheckAvailability $computername
	if ($serverstate -eq "OK"){
		write-host "$computername = $serverstate"
		#Query WMI for administrators and select only the partcomponent without headers (expandproperty)
		$localadmins = get-wmiobject -computername $computername -query "select * from win32_groupuser where GroupComponent=""Win32_Group.Domain='$computername',Name='administrators'""" | select -ExpandProperty PartComponent		
		if ($localadmins.Count -ne 0){
			foreach ($admin in $localadmins){
				if($admin -match "Win32_UserAccount"){
					#$admin is a user
					#How to replace, you have to escape the \ characters!!!
					$admin = $admin -replace "\\\\$computername\\root\\cimv2:Win32_UserAccount.Domain=",""
					# Use a symbol combination never used in a computername to prevent accidental splitup
					$admin = $admin -replace '",Name="',"@"
					$admin = $admin -replace '"',""
					$domain = [string]$admin.split("@")[0]
					$username = [string]$admin.split("@")[1]
					$Sheet.Cells.Item($script:datarow, 1) = $computername
					$Sheet.Cells.Item($script:datarow, 2) = [string]$username
					if ($domain -eq "SHIFT"){
						$Sheet.Cells.Item($script:datarow, 3) = "SHIFT Domain User"}
					else{
						$Sheet.Cells.Item($script:datarow, 3) = "Local Computer User"}
					write-host "Rij = $script:datarow - Local admins on $computername is a user: $admin , on domain: $domain en username = $username "
					$script:datarow++
				}
				elseif($admin -match "Win32_Group"){
					#$admin is a group
					$admin = $admin -replace "\\\\$computername\\root\\cimv2:Win32_Group.Domain=",""
					# Use a symbol combination never used in a computername to prevent accidental splitup
					$admin = $admin -replace '",Name="',"@"
					$admin = $admin -replace '"',""
					$domain = [string]$admin.split("@")[0]
					$groupname = [string]$admin.split("@")[1]
					$Sheet.Cells.Item($script:datarow, 1) = $computername
					$Sheet.Cells.Item($script:datarow, 2) = [string]$groupname
					if ($domain -eq "SHIFT"){
						$groupmembers = Get-ADGroupMember $groupname | select -expandproperty SamAccountName
						if ($groupmembers.Count -eq 0){
							Write-Host "This is an empty group: $groupmember "
							$script:emptygroups = $script:emptygroups + "$groupname; "
							$script:emptygroupscount++
						}
						$allgroupmembers = $groupmembers -join '; '
						$Sheet.Cells.Item($script:datarow, 3) = "SHIFT Domain Group"
						$Sheet.Cells.Item($script:datarow, 4) = [string]$allgroupmembers
						#write-host "Rij = $script:datarow - Local admins on $computername is a group: $admin , on domain: $domain en groupname = $groupname with these members: $allgroupmembers"
						write-host "Rij = $script:datarow - Local admins on $computername is a group: $admin , on domain: $domain en groupname = $groupname "
						#Finding nested groups
						$nestlevel = 1
						GroupNesting $groupmembers
						$script:datarow++
					}
				}
				elseif($admin -match "Win32_SystemAccount"){
					#$admin is a systemaccount
					$admin = $admin -replace "\\\\$computername\\root\\cimv2:Win32_SystemAccount.Domain=",""
					# Use a symbol combination never used in a computername to prevent accidental splitup
					$admin = $admin -replace '",Name="',"@"
					$admin = $admin -replace '"',""
					$domain = [string]$admin.split("@")[0]
					$username = [string]$admin.split("@")[1]
					$Sheet.Cells.Item($script:datarow, 1) = $computername
					$Sheet.Cells.Item($script:datarow, 2) = [string]$username
					$Sheet.Cells.Item($script:datarow, 3) = "System Account"
					write-host "Rij = $script:datarow - Local admins on $computername is a systemaccount: $admin , on domain: $domain en username = $username "
					$script:datarow++
				}
				else{
					#$admin is not a user and not a group. Let's check
					write-host "Rij = $script:datarow - Local admins object on $computername is unknown: $admin "
					$Sheet.Cells.Item($script:datarow, 1) = $computername
					$Sheet.Cells.Item($script:datarow, 2) = [string]$admin
					$Sheet.Cells.Item($script:datarow, 3) = "Unknown member type"
					$script:datarow++
				}
			}
		}
		else{
			write-host "Rij = $script:datarow - server $computername does not have localadmins, it probably is a cluster object"
			$Sheet.Cells.Item($script:datarow, 1) = $computername
			$Sheet.Cells.Item($script:datarow, 2) = "No Local Admins"
			$script:datarow++
			$nolocaladmins = $nolocaladmins + "$computername; "
			$nolocaladminscount++
		}
	}
	elseif ($serverstate -eq "WMIError"){
		write-host "$computername niet ok maar $serverstate"
		$offlinewmi = $offlinewmi + "$computername; "
		$offlinewmicount++
	}
	elseif ($serverstate -eq "PingError"){
		write-host "$computername niet ok maar $serverstate"
		$offlineping = $offlineping + "$computername; "
		$offlinepingcount++
	}
 }
 
# Define servers that are not available over the network or by WMI
write-host "Ping: $offlineping"
write-host "WMI: $offlinewmi"
$Sheet.Cells.Item(6,1) = "Servers No Ping ($offlinepingcount):" 
$Sheet.Cells.Item(7,1) = "Servers No WMI ($offlinewmicount):" 
$Sheet.Cells.Item(6,2) = "$offlineping" 
$Sheet.Cells.Item(7,2) = "$offlinewmi" 
# Define servers that have no local admins. This is for example correct in case of cluster objects 
$Sheet.Cells.Item(10,1) = "Servers without Local Admins ($nolocaladminscount):"
$sheet.Cells.Item(10,2) = "$nolocaladmins"
#Define empty groups
$Sheet.Cells.Item(15,1) = "Empty Groups ($script:emptygroupscount):" 
$Sheet.Cells.Item(15,2) = "$script:emptygroups" 
 
# Save, close and quit Excel because otherwise the excel files gets saved as csv file
$Sheet.SaveAs($report)
$workfile.Close()
# Wat for the filesystem to close the file
sleep 5
$Excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
 
########################################## Start Fase 3 ###########################################
 
# Find Added or Removed Servers/Groups Compared To Last Time
# Comparing is easy with csv files
$csvfile1 = "$scriptlocation\$scriptname-01.csv"
$csvfile2 = "$scriptlocation\$scriptname-02.csv"
$csvfile3 = "$scriptlocation\$scriptname-03.csv"
$csvfile4 = "$scriptlocation\$scriptname-04.csv"
# Open Excel Report (again)
$Excel = New-Object -ComObject Excel.Application
# Set to true for monitoring progress, to false for scheduled creation
$Excel.visible = $false
# Set to False to save existing files without warning
$Excel.DisplayAlerts = $False
$workfile = $Excel.Workbooks.open($report)
$sheet = $workfile.Worksheets.Item(1)
$sheet.SaveAs("$csvfile1", 6)
sleep 3
$lastWeekSheet = $workfile.Worksheets.Item(2)
$lastWeekSheet.SaveAs("$csvfile2", 6)
sleep 3
# Create new csv files without not required header info 
$skip = $headerrow - 1
Get-Content $csvfile1 | Select -skip $skip | Set-Content $csvfile3
sleep 3
Get-Content $csvfile2 | Select -skip $skip | Set-Content $csvfile4
sleep 3
# Load servercolums from csv files and make sure every server only appears once (this also removes all headers)
$thisweekservers = (Import-CSV $csvfile3)."Server Name" | Sort | Get-Unique
$lastweekservers = (Import-CSV $csvfile4)."Server Name" | Sort | Get-Unique
# Without headers, compare put its info in the property InputObject
$removedservers = Compare $thisweekservers $lastweekservers | where {$_.SideIndicator -eq '=>'} | Select -ExpandProperty InputObject
$addedservers = Compare $thisweekservers $lastweekservers | where {$_.SideIndicator -eq '<='} | Select -ExpandProperty InputObject
$countremovedservers = $removedservers.Count
$allremovedservers = $removedservers -join '; '
$countaddedservers = $addedservers.Count
$alladdedservers = $addedservers -join '; '
# Do the same for groups # Note that one server (W2K12-CTX7-P3) list the domain admins in capitals. Therefore you need to use the unique parameter of sort to get truly case insensitive output (instead of Get-Unique)
$thisweekgroups = Import-CSV $csvfile3 | where {$_."Object Type" -like "SHIFT Domain Group*"} | Select -ExpandProperty "Members of Administrators Group" | Sort -unique
$lastweekgroups = Import-CSV $csvfile4 | where {$_."Object Type" -like "SHIFT Domain Group*"} | Select -ExpandProperty "Members of Administrators Group" | Sort -unique
# Without headers, compare put its info in the property InputObject
write-host "thisweek groups: $thisweekgroups ; last week: $lastweekgroups"
$removedgroups = Compare $thisweekgroups $lastweekgroups | where {$_.SideIndicator -eq '=>'} | Select -ExpandProperty InputObject
$addedgroups = Compare $thisweekgroups $lastweekgroups | where {$_.SideIndicator -eq '<='} | Select -ExpandProperty InputObject
$countremovedgroups = $removedgroups.Count
$allremovedgroups = $removedgroups -join '; '
$countaddedgroups = $addedgroups.Count
$alladdedgroups = $addedgroups -join '; '
# Do the same for users. Note that there are two types of users, through groups and directly. These are treated differently. This is the part for users within groups.
$thisweekusers = ((Import-Csv $csvfile3).GroupMembers).split('; ') | sort -unique
$lastweekusers = ((Import-Csv $csvfile4).GroupMembers).split('; ') | sort -unique
$removedusers = Compare $thisweekusers $lastweekusers | where {$_.SideIndicator -eq '=>'} | Select -ExpandProperty InputObject
$addedusers = Compare $thisweekusers $lastweekusers | where {$_.SideIndicator -eq '<='} | Select -ExpandProperty InputObject
$countremovedusers = $removedusers.Count
$allremovedusers = $removedusers -join '; '
$countaddedusers = $addedusers.Count
$alladdedusers = $addedusers -join '; '
# Now find the non-service account or scom users that have local admin on servers directly, without groups. 
$nogroupadmins = Import-CSV $csvfile3 | where {($_."Object Type" -match "SHIFT Domain User") -and ($_."Members of Administrators Group" -notlike "sa_*") -and ($_."Members of Administrators Group" -notlike "scom*")} | Select -ExpandProperty "Members of Administrators Group" | Sort -unique
$countnogroupadmins = $nogroupadmins.Count
$allnogroupadmins = $nogroupadmins -join '; '
 
# Define Domain Admin Members
$thisweekdomainadminmembers = Get-ADGroupMember $domainadmins | select -expandproperty SamAccountName
$lastweekdomainadminmembers = ((Import-csv $csvfile4 | where {$_."Members of Administrators Group" -match $domainadmins} | select -ExpandProperty GroupMembers | sort -unique) -replace "; ",";").split(';')
#$changeddomainadminmembers = Compare -IncludeEqual $lastweekdomainadminmembers $thisweekdomainadminmembers | where {$_.SideIndicator -ne '=='} | Select -ExpandProperty InputObject
$removeddomainadmins = Compare $thisweekdomainadminmembers $lastweekdomainadminmembers | where {$_.SideIndicator -eq '=>'} | Select -ExpandProperty InputObject
$addeddomainadmins = Compare $thisweekdomainadminmembers $lastweekdomainadminmembers | where {$_.SideIndicator -eq '<='} | Select -ExpandProperty InputObject
$domainadminmemberscount = $thisweekdomainadminmembers.Count
$alldomainadminmembers = $thisweekdomainadminmembers -join '; '
$removeddomainadminscount = $removeddomainadmins.Count
$addeddomainadminscount = $addeddomainadmins.Count
$allremoveddomainadmins = $removeddomainadmins -join '; '
$alladdeddomainadmins = $addeddomainadmins -join '; '
 
# Define local ICT Citrix Accounts
# $ictlocaladmingroup = "Citrix XenApp 7 ICT"
$thisweekcitrixadmins = Get-ADGroupMember $ictlocaladmingroup | select -expandproperty SamAccountName
$lastweekcitrixadmins = ((Import-csv $csvfile4 | where {$_."Members of Administrators Group" -match $ictlocaladmingroup} | select -ExpandProperty GroupMembers | sort -unique) -replace "; ",";").split(';')
#$changeddomainadminmembers = Compare -IncludeEqual $lastweekdomainadminmembers $thisweekdomainadminmembers | where {$_.SideIndicator -ne '=='} | Select -ExpandProperty InputObject
$removedcitrixadmins = Compare $thisweekcitrixadmins $lastweekcitrixadmins | where {$_.SideIndicator -eq '=>'} | Select -ExpandProperty InputObject
$addedcitrixadmins = Compare $thisweekcitrixadmins $lastweekcitrixadmins | where {$_.SideIndicator -eq '<='} | Select -ExpandProperty InputObject
$citrixadminscount = $thisweekcitrixadmins.Count
$allcitrixadmins = $thisweekcitrixadmins -join '; '
$removedcitrixadminscount = $removedcitrixadmins.Count
$addedcitrixadminscount = $addedcitrixadmins.Count
$allremovedcitrixadmins = $removedcitrixadmins -join '; '
$alladdedcitrixadmins = $addedcitrixadmins -join '; '
 
#Define other local account
$thisweeknormalaccountadminsXXX = (((Import-csv $csvfile3 | where {$_."Members of Administrators Group" -notmatch $ictlocaladmingroup} | select -ExpandProperty GroupMembers | sort -unique) -replace "; ",";").split(';')) -match '^[a-z][a-z][a-z]$'
$thisweeknormalaccountadminsXXX0000 = (((Import-csv $csvfile3 | where {$_."Members of Administrators Group" -notmatch $ictlocaladmingroup} | select -ExpandProperty GroupMembers | sort -unique) -replace "; ",";").split(';')) -match '^[a-z][a-z][a-z][0-9][0-9][0-9][0-9]$'
$thisweeknormalaccountadmins = $thisweeknormalaccountadminsXXX + $thisweeknormalaccountadminsXXX0000
$lastweeknormalaccountadminsXXX = (((Import-csv $csvfile4 | where {$_."Members of Administrators Group" -notmatch $ictlocaladmingroup} | select -ExpandProperty GroupMembers | sort -unique) -replace "; ",";").split(';')) -match '^[a-z][a-z][a-z]$'
$lastweeknormalaccountadminsXXX0000 = (((Import-csv $csvfile4 | where {$_."Members of Administrators Group" -notmatch $ictlocaladmingroup} | select -ExpandProperty GroupMembers | sort -unique) -replace "; ",";").split(';')) -match '^[a-z][a-z][a-z][0-9][0-9][0-9][0-9]$'
$lastweeknormalaccountadmins = $lastweeknormalaccountadminsXXX + $lastweeknormalaccountadminsXXX0000
$removednormalaccountadmins = Compare $thisweeknormalaccountadmins $lastweeknormalaccountadmins | where {$_.SideIndicator -eq '=>'} | Select -ExpandProperty InputObject
$addednormalaccountadmins = Compare $thisweeknormalaccountadmins $lastweeknormalaccountadmins | where {$_.SideIndicator -eq '<='} | Select -ExpandProperty InputObject
$thisweeknormalaccountadminscount = $thisweeknormalaccountadmins.Count
$allnormalaccountadmins = $thisweeknormalaccountadmins -join '; '
$removednormalaccountadminscount = $removednormalaccountadmins.Count
$addednormalaccountadminscount = $addednormalaccountadmins.Count
$allremovednormalaccountadmins = $removednormalaccountadmins -join '; '
$alladdednormalaccountadmins = $addednormalaccountadmins -join '; '
 
#Quit Excel without saving
sleep 5
$Excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null
 
########################################## Start Fase 4 ###########################################
 
# Open Excel Report (again)
$Excel = New-Object -ComObject Excel.Application
# Set to true for monitoring progress, to false for scheduled creation
$Excel.visible = $false
# Set to False to save existing files without warning
$Excel.DisplayAlerts = $False
$workfile = $Excel.Workbooks.open($report)
$sheet = $workfile.Worksheets.Item(1)
# Add removed and added servers info 
$Sheet.Cells.Item(8,1) = "Added Servers ($countaddedservers):" 
$Sheet.Cells.Item(9,1) = "Removed Servers ($countremovedservers):" 
$Sheet.Cells.Item(8,2) = "$alladdedservers" 
$Sheet.Cells.Item(9,2) = "$allremovedservers" 
# Add removed and added groups info 
$Sheet.Cells.Item(13,1) = "Added Groups ($countaddedgroups):" 
$Sheet.Cells.Item(14,1) = "Removed Groups ($countremovedgroups):" 
$Sheet.Cells.Item(13,2) = "$alladdedgroups" 
$Sheet.Cells.Item(14,2) = "$allremovedgroups" 
# Add removed and added users info 
$Sheet.Cells.Item(16,1) = "Added Users to Groups ($countaddedusers):" 
$Sheet.Cells.Item(17,1) = "Removed Users from Groups ($countremovedusers):" 
$Sheet.Cells.Item(16,2) = "$alladdedusers" 
$Sheet.Cells.Item(17,2) = "$allremovedusers" 
# Add non-service account or scom users that have local admin on servers directly, without groups.
$Sheet.Cells.Item(11,1) = "Non-Service Local Admin Accounts without Group ($countnogroupadmins):" 
$Sheet.Cells.Item(11,2) = "$allnogroupadmins" 
# Add Domain Admin Members
$Sheet.Cells.Item(18,1) = "Domain Admin Group Members ($domainadminmemberscount):"
if(($removeddomainadmins.Count -eq 0) -and ($addeddomainadmins.Count -eq 0)){
	$Sheet.Cells.Item(18,2) = "No Changes. Current members: $alldomainadminmembers"}
else{
	$Sheet.Cells.Item(18,2) = "Added Domain Admins($addeddomainadminscount): $alladdeddomainadmins . Removed Domain Admins($removeddomainadminscount): $allremoveddomainadmins"}
# Add Citrix Admin Members for WIN-CTX7-D1 and D2
$Sheet.Cells.Item(19,1) = "Admin Accounts in $ictlocaladmingroup ($citrixadminscount):"
if(($removedcitrixadmins.Count -eq 0) -and ($addedcitrixadmins.Count -eq 0)){
	$Sheet.Cells.Item(19,2) = "No Changes. Current members: $allcitrixadmins"}
else{
	$Sheet.Cells.Item(19,2) = "Added Local Admins($addedcitrixadminscount): $alladdedcitrixadmins . Removed Local Admins($removedcitrixadminscount): $allremovedcitrixadmins"}
# Add Local Admin Members 
#$Sheet.Cells.Item(12,1) = "Servers with regular accounts as Local Admin Accounts:" 
$Sheet.Cells.Item(12,1) = "Regular accounts as Local Admin ($thisweeknormalaccountadminscount):"
if(($removednormalaccountadmins.Count -eq 0) -and ($addednormalaccountadmins.Count -eq 0)){
	$Sheet.Cells.Item(12,2) = "No Changes. Current accounts: $allnormalaccountadmins"}
else{
	$Sheet.Cells.Item(12,2) = "Added Local Admins($addednormalaccountadminscount): $alladdednormalaccountadmins . Removed Local Admins($removednormalaccountadminscount): $allremovednormalaccountadmins"}
 
### Final Report Modifications ###
# Set Excel formatting
# Set Autofit for column A through C and a fixed width for column D
$sheet.Columns("A:C").Autofit()
$sheet.Columns.Item("D").columnwidth = 50
$formatting = $Sheet.UsedRange
$formatting.WrapText = $true
 
# Save, close and quit Excel
$Sheet.SaveAs($report)
$workfile.Close()
# Wait for the filesystem to close the file
sleep 5
$Excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
 
########################################## Start Fase 5 ###########################################
 
### Make the list of anomalies that have to be checked for corresponding changes in TOPdesk - we face the challenge we need to check different kind of objects: Servers, Groups and Users
 
# Re-use the csv files and code above to check for all anomalies 
#SERVERS
$thisweekservers = (Import-CSV $csvfile3)."Server Name" | Sort | Get-Unique
$lastweekservers = (Import-CSV $csvfile4)."Server Name" | Sort | Get-Unique
# Without headers, compare put its info in the property InputObject
$removedservers = Compare $thisweekservers $lastweekservers | where {$_.SideIndicator -eq '=>'} | Select -ExpandProperty InputObject
$addedservers = Compare $thisweekservers $lastweekservers | where {$_.SideIndicator -eq '<='} | Select -ExpandProperty InputObject
#GROUPS 
$thisweekgroups = Import-CSV $csvfile3 | where {$_."Object Type" -like "SHIFT Domain Group*"} | Select -ExpandProperty "Members of Administrators Group" | Sort -unique
$lastweekgroups = Import-CSV $csvfile4 | where {$_."Object Type" -like "SHIFT Domain Group*"} | Select -ExpandProperty "Members of Administrators Group" | Sort -unique
# Without headers, compare put its info in the property InputObject
write-host "thisweek groups: $thisweekgroups ; last week: $lastweekgroups"
$removedgroups = Compare $thisweekgroups $lastweekgroups | where {$_.SideIndicator -eq '=>'} | Select -ExpandProperty InputObject
$addedgroups = Compare $thisweekgroups $lastweekgroups | where {$_.SideIndicator -eq '<='} | Select -ExpandProperty InputObject
#USERS
$thisweekusers = ((Import-Csv $csvfile3).GroupMembers).split('; ') | sort -unique
$lastweekusers = ((Import-Csv $csvfile4).GroupMembers).split('; ') | sort -unique
$removedusers = Compare $thisweekusers $lastweekusers | where {$_.SideIndicator -eq '=>'} | Select -ExpandProperty InputObject
$addedusers = Compare $thisweekusers $lastweekusers | where {$_.SideIndicator -eq '<='} | Select -ExpandProperty InputObject
#DOMAIN ADMINS
$thisweekdomainadminmembers = Get-ADGroupMember $domainadmins | select -expandproperty SamAccountName
$lastweekdomainadminmembers = ((Import-csv $csvfile4 | where {$_."Members of Administrators Group" -match $domainadmins} | select -ExpandProperty GroupMembers | sort -unique) -replace "; ",";").split(';')
#$changeddomainadminmembers = Compare -IncludeEqual $lastweekdomainadminmembers $thisweekdomainadminmembers | where {$_.SideIndicator -ne '=='} | Select -ExpandProperty InputObject
$removeddomainadmins = Compare $thisweekdomainadminmembers $lastweekdomainadminmembers | where {$_.SideIndicator -eq '=>'} | Select -ExpandProperty InputObject
$addeddomainadmins = Compare $thisweekdomainadminmembers $lastweekdomainadminmembers | where {$_.SideIndicator -eq '<='} | Select -ExpandProperty InputObject
#ICT CITRIX ACCOUNTS
# $ictlocaladmingroup = "Citrix XenApp 7 ICT"
$thisweekcitrixadmins = Get-ADGroupMember $ictlocaladmingroup | select -expandproperty SamAccountName
$lastweekcitrixadmins = ((Import-csv $csvfile4 | where {$_."Members of Administrators Group" -match $ictlocaladmingroup} | select -ExpandProperty GroupMembers | sort -unique) -replace "; ",";").split(';')
#$changeddomainadminmembers = Compare -IncludeEqual $lastweekdomainadminmembers $thisweekdomainadminmembers | where {$_.SideIndicator -ne '=='} | Select -ExpandProperty InputObject
$removedcitrixadmins = Compare $thisweekcitrixadmins $lastweekcitrixadmins | where {$_.SideIndicator -eq '=>'} | Select -ExpandProperty InputObject
$addedcitrixadmins = Compare $thisweekcitrixadmins $lastweekcitrixadmins | where {$_.SideIndicator -eq '<='} | Select -ExpandProperty InputObject
#REGULAR LOCAL ADMIN ACCOUNTS
$thisweeknormalaccountadminsXXX = (((Import-csv $csvfile3 | where {$_."Members of Administrators Group" -notmatch $ictlocaladmingroup} | select -ExpandProperty GroupMembers | sort -unique) -replace "; ",";").split(';')) -match '^[a-z][a-z][a-z]$'
$thisweeknormalaccountadminsXXX0000 = (((Import-csv $csvfile3 | where {$_."Members of Administrators Group" -notmatch $ictlocaladmingroup} | select -ExpandProperty GroupMembers | sort -unique) -replace "; ",";").split(';')) -match '^[a-z][a-z][a-z][0-9][0-9][0-9][0-9]$'
$thisweeknormalaccountadmins = $thisweeknormalaccountadminsXXX + $thisweeknormalaccountadminsXXX0000
$lastweeknormalaccountadminsXXX = (((Import-csv $csvfile4 | where {$_."Members of Administrators Group" -notmatch $ictlocaladmingroup} | select -ExpandProperty GroupMembers | sort -unique) -replace "; ",";").split(';')) -match '^[a-z][a-z][a-z]$'
$lastweeknormalaccountadminsXXX0000 = (((Import-csv $csvfile4 | where {$_."Members of Administrators Group" -notmatch $ictlocaladmingroup} | select -ExpandProperty GroupMembers | sort -unique) -replace "; ",";").split(';')) -match '^[a-z][a-z][a-z][0-9][0-9][0-9][0-9]$'
$lastweeknormalaccountadmins = $lastweeknormalaccountadminsXXX + $lastweeknormalaccountadminsXXX0000
$removednormalaccountadmins = Compare $thisweeknormalaccountadmins $lastweeknormalaccountadmins | where {$_.SideIndicator -eq '=>'} | Select -ExpandProperty InputObject
$addednormalaccountadmins = Compare $thisweeknormalaccountadmins $lastweeknormalaccountadmins | where {$_.SideIndicator -eq '<='} | Select -ExpandProperty InputObject
 
#### all objects above are selected without header (through the expandproperty parameter). 
# this means we just have a list of names which can be added to each other
$allobjectstemp = $removedservers + $addedservers + $removedgroups + $addedgroups + $removedusers + $addedusers + $removeddomainadmins + $addeddomainadmins + $removedcitrixadmins + $addedcitrixadmins + $removednormalaccountadmins + $addednormalaccountadmins
# We remove doubles and make sure they are in alphabetical order
$allobjects = $allobjectstemp | sort -unique
 
# Make an array with the name and the samaccountname
$anomalies=@()
ForEach ($object in $allobjects){
	if (Get-ADObject -Filter {objectClass -eq "group" -and samAccountName -eq $object}){
		Write-Host "$object is a group!"
		$group = Get-ADGroup $object | select Name,SamAccountName
		$anomalies += $group 
	}elseif (Get-ADObject -Filter {objectClass -eq "computer" -and Name -eq $object}){ 
		#Server samaccountname has a $ at the end of the name so samaccountname does not match and it also makes for too many matches in TOPdesk (the $ it is), so we don't use this. 
		Write-Host "$object is a server!"
		#$server = Get-ADComputer $object | select Name,SamAccountName
		$server = Get-ADComputer $object | select Name
		$server | Add-Member -type NoteProperty -Name 'SamAccountName' -Value $object
		$anomalies += $server
	}elseif (Get-ADObject -Filter {objectClass -eq "user" -and samAccountName -eq $object}){
		Write-Host "$object is an user!"
		$user = Get-ADUser $object | select Name,SamAccountName
		$anomalies += $user
	}else{
		Write-Host "$object does not exist in AD anymore!"
		$unknownobject = New-Object psobject
		$unknownobject | Add-Member -type NoteProperty -Name 'Name' -Value $object
		$unknownobject | Add-Member -type NoteProperty -Name 'SamAccountName' -Value $object
		$anomalies += $unknownobject
	}
}
 
### Create TOPdesk data set with all changes for the last 6 months
$180days = (Get-Date).adddays(-180).ToString('MM/dd/yyyy HH:mm:ss')
$TOPdeskSQLQuery = $("SELECT number,status,calldate,briefdescription,description,unid FROM change WHERE status > 0 and calldate > '$180days'")
$TOPdeskDataSet = TOPdesk-Query $TOPdeskSQLQuery
 
### Match the anomalies and TOPdesk results with each other and add the info to the email body
ForEach ($anomaly in $anomalies){
	$anomalyname = $anomaly.Name
	$anomalysam = $anomaly.SamAccountName
	$crsdescription = $TOPdeskDataSet | where {$_.description -like "*$anomalysam*"} | select number, briefdescription, unid
	#Count won't work with 1 result, use measure object
	$crsdescriptioncount = ($crsdescription | Measure-Object).Count
	$crsbriefdescription = $TOPdeskDataSet | where {$_.briefdescription -like "*$anomalyname*"} | select number, briefdescription, unid 
	$crsbriefdescriptioncount = ($crsbriefdescription | Measure-Object).Count
	write-host "Searched for TOPdesk CRs with $anomaly in the briefdescription ($crsbriefdescriptioncount results) or description (request) ($crsdescriptioncount results) fields. "
	# We don't trust the results if the count is higher than 5. The search was probably on a too generic search term. 
	# Example MBE is a username, but is also matches on november or december
	if ($crsdescriptioncount -gt 5){
		Clear-Variable crsdescription
		write-host "Description had too much results. This should be empty now: $crsdescription"
	}
	if ($crsbriefdescriptioncount -gt 5){
		Clear-Variable crsbriefdescription
		write-host "Brief description had too much results. This should be empty now: $crsbriefdescription"
	}
	# Now we combine the results
	$allTOPdeskCRsTemp=@()
	ForEach ($cr in $crsdescription){
		$allTOPdeskCRsTemp += $cr
	}
	ForEach ($cr in $crsbriefdescription){
		$allTOPdeskCRsTemp += $cr
	}
	# And sort the results and remove doubles
	$allTOPdeskCRs = $allTOPdeskCRsTemp | sort -Property number -unique
	# Let's check the results
	#write-host "All Topdesk CRs for $anomaly : "
	#$allTOPdeskCRs
 
	### Add the information to the email - because inline styling is hard without css we give each anomaly a table of it's own
	$info += '<table style="width:100%; border: 1px solid #dddddd; text-align: left;">'
	$info += "<tr>"
	$info += '<th style="width: 200px; text-align: left;">'
	$info += "$anomalyname"
	$info += '</th>'
	$info += '<td style="width: 100px; text-align: left;">'
	$info += "$anomalysam"
	$info += '</td>'
	$info += '<td style="text-align: left;">'
	# Now add the TOPdesk links to the CRs
	if ($allTOPdeskCRs.count -ne 0){
		ForEach ($cr in $allTOPdeskCRs){
			$TDCR = $cr.number
			$TDD = $cr.briefdescription
			$TCUNID = $cr.unid
			$info += "<a href=http://topdesk/tas/secure/newchange?unid=$TCUNID>$TDCR</a> - $TDD <br>"
		}
	}else{
		$info += "None or too many matching TOPdesk changes found in the last half year <br>"
		$info += "Fill in TOPdesk reference: <br>"
	}
	$info += '</td>'
	$info += "</tr>"
	$info += "</table>"
}
 
 
########################################## Start Fase 6 ###########################################
 
# Send Report
#Copy file with monthdate so it will be unique on SharePoint
Copy-Item $report $monthreport
$subject = "ICT Control Framework 06 for month: $monthdate"
#$info = "Find the report attached to this email. Kind regards, Sjoerd Hooft."
Send-Email $subject $info
Upload-Report $monthreport
 
#Cleanup files
Remove-Item $csvfile1
Remove-Item $csvfile2
Remove-Item $csvfile3
Remove-Item $csvfile4
Remove-Item $monthreport
 
# Stop transcript 
stop-transcript
You could leave a comment if you were logged in.
adcontrols.txt · Last modified: 2021/09/24 00:24 (external edit)