This is a sample of powershell code I used today at add functionality from a text file to AD while crossing the data to an SQL Server.
This script does a few useful things that I like, Starting with refreshing the Debug Environment, then opening an SQL connection, Parsing a textfile that’s ‘|’ pipe delimited then comparing with row’s returned from an SQL Query.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
Remove-Variable * -ErrorAction SilentlyContinue; Remove-Module *; $error.Clear(); Clear-Host $ConnectionString = "Server=MYVM;Database=Mydatabase;Trusted_Connection=yes;" $sqlConnection = new-object System.Data.SqlClient.SqlConnection $ConnectionString $sqlConnection.Open() $ActiveFeed = @{} foreach($line in get-content "\\MYVM\d$\MyFeed.txt") { $recipients = $line -split [RegEx]::Escape("|") $samAccountName = [string] $samAccountName = $recipients[0].trim() $WWW = $recipients[0].trim() $ActiveFeed.Add($samAccountName,$WWW) } $sqlCommand = $sqlConnection.CreateCommand() $sqlCommand.CommandText = "select DISTINCT * FROM dbo.AppRights WHERE (AppName='ActiveDirectory' and RightsItem='DIR_SecurityGroup') and ((Dept = '0') and (Jobcode = '0') and (Entity = '0')) order by Dept, JobCode" $DirectorTags = $sqlCommand.ExecuteReader() try { while ($DirectorTags.Read()) { $ADTags.GetEnumerator() | ForEach-Object { if ($_.value -eq "DIR") { $message = '{0} is a {1}, it gets {2}!' -f $_.key, $_.value, $DirectorTags.GetValue(4) Write-Output $message } } } } catch { } $DirectorTags.Close() |