Instantly share code, notes, and snippets.

Embed
What would you like to do?
#----------------------------------------------------------------------##
# Module Name : DEPMSSQLActions.psm1
# Created : 27.10.2016
# Created by : Gisli Gudmundsson
# LinkedIN : https://is.linkedin.com/in/gisli-gudmundsson-11a77639# License Usage :
# This code can be used for private use only# You may modify this code and distribute
##----------------------------------------------------------------------#
#Import the SQL Server Powershell Module
Import-Module SqlServer
#Import custom made modules
Import-Module C:\DynamicDocumentation\Settings\GlobalVariables.psm1 -Force
#Usage "RunQuery -Query $Query"
function RunQuery($Query, $Environment){
$ServerInstance = ServerInstance
$DatabaseName = DatabaseEnvironment -Environment $Environment
#Runs specific query that is added to the parameter
Invoke-Sqlcmd -Query $Query -ServerInstance $ServerInstance -Database $DatabaseName
}
function RunSelectQuery($Query, $Environment){
$ServerInstance = ServerInstance
$DatabaseName = DatabaseEnvironment -Environment $Environment
#Runs specific query that is added to the parameter
return Invoke-Sqlcmd -Query $Query -ServerInstance $ServerInstance -Database $DatabaseName
}
#Usage "AddMSSQLUserCount -UserCount $Integer -UserType $StringMax50Chars -CostCenter $StringMax50Chars
function AddMSSQLUserCount($UserCount, $UserType, $CostCenter, $Environment){
#Adds new row into the database
$Insert_Query = "
INSERT INTO ADUsersCount (UserCount, UserType, UserCostCenter) VALUES ('$UserCount','$UserType','$CostCenter')
"
#Calls the Run-Query function
RunQuery -Query $Insert_Query -Environment $Environment
}
function addMSSQLADAlerts($UserName, $Alert, $Environment){
#Adds new row into the database
$Insert_Query = "
INSERT INTO ADAlerts (UserName, Alert) VALUES ('$UserName','$Alert')
"
#Calls the Run-Query function
RunQuery -Query $Insert_Query -Environment $Environment
}
function getMSQLGlobalAdmins($Environment){
$Select_Query = "
SELECT * FROM ADGlobalAdmins
"
$GlobalAdmins = RunSelectQuery -Query $Select_Query -Environment $Environment
return $GlobalAdmins
}
#Usage "DebugShowMSSQLTable -TableName"
function DebugShowMSSQLTable($TableName, $ColumnName, $Environment){
$Show_Query = "
SELECT * FROM $TableName WHERE $ColumnName = 'DEBUG'
"
RunQuery -Query $Show_Query -Environment $Environment
}
#Usage "DebugCleanUpMSSQLUserCount -TableName $TableName -ColumnName $ColumnName -Environment $Environment"
function DebugCleanUpMSSQLUserCount($TableName, $ColumnName, $Environment){
#Removes all data where debug row inserts has been added
$Clean_Query = "
DELETE FROM $TableName
WHERE $ColumnName = 'DEBUG'
"
RunQuery -Query $Clean_Query -Environment $Environment
}
#----------------------------------------------------------------------##
# Module Name : GetADStatus.ps1
# Created : 06.11.2016
# Created by : Gisli Gudmundsson
# LinkedIN : https://is.linkedin.com/in/gisli-gudmundsson-11a77639
# License Usage : # This code can be used for private use only
# You may modify this code and distribute
##----------------------------------------------------------------------##
Import-Module ActiveDirectory
Import-Module C:\DynamicDocumentation\Dependencies\DEPMSSQLActions.psm1 -Force -Verbose
$OU = "OU=Departments,DC=tstdomain,DC=com"
$Environment = "production"
function getUserCount(){
#Will get users in ad
$OrganizationalUnits = Get-ADOrganizationalUnit -Filter * -SearchScope OneLevel -SearchBase $OU -Properties adminDescription | where { $_.adminDescription -ne $null } | select adminDescription, DistinguishedName | Sort-Object adminDescription
foreach($OrganizationalUnit in $OrganizationalUnits){
$OUUsers = "OU=Users," + $OrganizationalUnit.DistinguishedName
$CostCenter = $OrganizationalUnit.adminDescription
$TotalEnabledUsers = Get-ADUser -SearchBase $OUUsers -Filter * | where { $_.Enabled -eq $true}
$TotalDisabledUsers = Get-ADUser -SearchBase $OUUsers -Filter * | where { $_.Enabled -eq $false}
AddMSSQLUserCount -UserCount $TotalEnabledUsers.Count -UserType "Enabled Users" -CostCenter $CostCenter -Environment $Environment
AddMSSQLUserCount -UserCount $TotalDisabledUsers.Count -UserType "Disabled Users" -CostCenter $CostCenter -Environment $Environment
}
}
function getADAlerts(){
#Will figure out who is member of domain admin group
$DomainAdmins = Get-ADGroupMember -Identity "Domain Admins" | Get-ADUser | select SamAccountName
$GlobalAdmins = getMSQLGlobalAdmins -Environment $Environment
$Compare = Compare-Object -ReferenceObject $GlobalAdmins.SamAccountName -DifferenceObject $DomainAdmins.SamAccountName
foreach($C in $Compare){
$SamAccountName_Alert = $C.InputObject
addMSSQLADAlerts -UserName $SamAccountName_Alert -Alert "USER IS ASSIGNED TO DOMAIN ADMINS" -Environment $Environment
}
}
#Main Run the application
getADAlerts
getUserCount
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment