Archive for the ‘PowerShell’ Category
Taking Data from SQL [INTO] CSV [EHLO] SMTP-Email
Today I was looking at producing a report from a SQL2005 database and exporting the SQL data into a CSV file. Once completed I would be sending the CSV file to a member of my team via Email. As this is a common request I wanted to automate this with PowerShell.
I had posted previously on basic database connectivity with PowerShell here and using that code as a basis I wrote a PowerShell script that connects to the SQL database and extracts the data into an SQLDataAdapter. The required table is selected and imported into a hash table, and then exported to a CSV file. This file is then attached to an email using the .NET SMTP Mail object and sent on its way via SMTP.
Download Script at PoshCode.org
Code:
#Connection Strings $Database = "Database" $Server = "SQLServer" #SMTP Relay Server $SMTPServer = "smtp.domain.com" #Export File $AttachmentPath = "C:\SQLData.csv" # Connect to SQL and query data, extract data to SQL Adapter $SqlQuery = "SELECT * FROM dbo.Test_Table" $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = "Data Source=$Server;Initial Catalog=$Database;Integrated Security = True" $SqlCmd = New-Object System.Data.SqlClient.SqlCommand $SqlCmd.CommandText = $SqlQuery $SqlCmd.Connection = $SqlConnection $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlAdapter.SelectCommand = $SqlCmd $DataSet = New-Object System.Data.DataSet $nRecs = $SqlAdapter.Fill($DataSet) $nRecs | Out-Null #Populate Hash Table $objTable = $DataSet.Tables[0] #Export Hash Table to CSV File $objTable | Export-CSV $AttachmentPath #Send SMTP Message $Mailer = new-object Net.Mail.SMTPclient($SMTPServer) $From = "email1@domain.com" $To = "email2@domain.com" $Subject = "Test Subject" $Body = "Body Test" $Msg = new-object Net.Mail.MailMessage($From,$To,$Subject,$Body) $Msg.IsBodyHTML = $False $Attachment = new-object Net.Mail.Attachment($AttachmentPath) $Msg.attachments.add($Attachment) $Mailer.send($Msg)
Hope this helps.
All information is provided on an AS-IS basis, with no warranties and confers no rights.
Converting Excel to CSV using PowerShell
Quick Script: Converting Excel[.xls] to Comma Separated Value[.csv]
Many times my data is in .xls format and I really need it in .csv to leverage the accessibility of the Import-CSV cmdlet.
I found this code on the ‘PowerShellCommunity.org’ web forum: Link
Code:
$xlCSV=6 $Excelfilename = "file.xls" $CSVfilename = "file.csv" $Excel = New-Object -comobject Excel.Application $Excel.Visible = $False $Excel.displayalerts=$False $Workbook = $Excel.Workbooks.Open($ExcelFileName) $Workbook.SaveAs($CSVfilename,$xlCSV) $Excel.Quit() If(ps excel){kill -name excel}
I have only tested this with Office 2003 Sp2. I will try with 2007/2010 and post my findings.
Hope this helps.
All information is provided on an AS-IS basis, with no warranties and confers no rights.
Quest AD Management Shell – Moving AD User Objects
Code Snippet: Moving disable user objects in Active Directory using PowerShell and Quest Management cmdlets.Task:
Task: Moving all disabled users in the ‘/Users’ OU to the /Users/Disabled OU.
Code:
Add-PSSnapin -Name Quest.ActiveRoles.ADManagement -ErrorAction SilentlyContinue $Users = Get-QADUser -SearchRoot 'ad.domain.com/Users/' -Disabled Write-Host "Moving $Users.Count Users." ForEach($User In $Users){ Move-QADObject $User.DN -NewParentContainer 'ad.domain.com/Users/Disabled'}
Hope this helps
All information is provided on an AS-IS basis, with no warranties and confers no rights.
AD and Kerberos Token Bloat – Analyzing Group Nesting
How To: Analyze Nested AD Groups.
I am working on a Kerberos Token size issue (Token Bloat) and need to accurately depict a groups nested members for token size analysis and remediation.
Brief Token Bloat Explanation:
Token Bloat is where due to several factors including deep group nesting the Kerberos token utilized in AD for resource authentication bloats towards the set limit or even beyond. This can cause larger logon times to resources and even issues in applications like Exchange 2003 that can only exist on a 32Bit OS.
I came across this article on the “Microsoft Active Directory Group PowerShell” blog, it has a great script for analysing nested group memberships. Unfortunately to use the PowerShell script you need to be running Windows 2008 servers for the Active Directory cmdlets to be available. The environment that I required the script for was not W2008 so I converted the script to use the Quest cmdlets available with the Quest Active Roles Management PSSnapIn.
You will need to have these installed before using this script.
The script originally written by the MS AD PowerShell team, provides a visual tree view of the groups and their nested levels, a ‘MaxNestingLevel’ value and a ‘NestedGroupMembershipCount’.
Example:
PS C:\> Get-QADGroupNesting.ps1 group-name
Or
PS C:\> Get-QADGroupNesting.ps1 group-name -ShowTree
The above example not only displays group statistics it also plots the structure of the group in a tree format.
Download Script at PoshCode.org
Code:
##########Copy the below script into a new file called Get-ADGroupNesting.ps1 Param ( [Parameter(Mandatory=$true, Position=0, ValueFromPipeline=$true, HelpMessage="DN or ObjectGUID of the AD Group." )] [string]$groupIdentity, [switch]$showTree ) #Validate Quest PSSnapin is loaded Add-PSSnapin -Name Quest.ActiveRoles.ADManagement -ErrorAction SilentlyContinue $global:numberOfRecursiveGroupMemberships = 0 $lastGroupAtALevelFlags = @() function Get-GroupNesting ([string] $identity, [int] $level, [hashtable] $groupsVisitedBeforeThisOne, [bool] $lastGroupOfTheLevel) { $group = $null $group = Get-QADGroup -Identity $identity -SizeLimit 0 if($lastGroupAtALevelFlags.Count -le $level) { $lastGroupAtALevelFlags = $lastGroupAtALevelFlags + 0 } if($group -ne $null) { if($showTree) { for($i = 0; $i -lt $level - 1; $i++) { if($lastGroupAtALevelFlags[$i] -ne 0) { Write-Host -ForegroundColor Blue -NoNewline " " } else { Write-Host -ForegroundColor Blue -NoNewline "│ " } } if($level -ne 0) { if($lastGroupOfTheLevel) { Write-Host -ForegroundColor Blue -NoNewline "└─" } else { Write-Host -ForegroundColor Blue -NoNewline "├─" } } Write-Host -ForegroundColor Blue $group.Name } $groupsVisitedBeforeThisOne.Add($group.DN,$null) $global:numberOfRecursiveGroupMemberships ++ $groupMemberShipCount = $group.memberOf.Count if ($groupMemberShipCount -gt 0) { $maxMemberGroupLevel = 0 $count = 0 foreach($groupDN in $group.memberOf) { $count++ $lastGroupOfThisLevel = $false if($count -eq $groupMemberShipCount){$lastGroupOfThisLevel = $true; $lastGroupAtALevelFlags[$level] = 1} if(-not $groupsVisitedBeforeThisOne.Contains($groupDN)) #prevent cyclic dependancies { $memberGroupLevel = Get-GroupNesting -Identity $groupDN -Level $($level+1) -GroupsVisitedBeforeThisOne $groupsVisitedBeforeThisOne -lastGroupOfTheLevel $lastGroupOfThisLevel if ($memberGroupLevel -gt $maxMemberGroupLevel){$maxMemberGroupLevel = $memberGroupLevel} } } $level = $maxMemberGroupLevel } else #we've reached the top level group, return it's height { return $level } return $level } } $global:numberOfRecursiveGroupMemberships = 0 $groupObj = Get-QADGroup -Identity $groupIdentity -SizeLimit 0 if($groupObj) { [int]$maxNestingLevel = Get-GroupNesting -Identity $groupIdentity -Level 0 -GroupsVisitedBeforeThisOne @{} -lastGroupOfTheLevel $false Add-Member -InputObject $groupObj -MemberType NoteProperty -Name MaxNestingLevel -Value $maxNestingLevel -Force Add-Member -InputObject $groupObj -MemberType NoteProperty -Name NestedGroupMembershipCount -Value $($global:numberOfRecursiveGroupMemberships - 1) -Force $groupObj | Select-Object Name,DN,MaxNestingLevel,NestedGroupMembershipCount | Format-List }
Hope this helps
All information is provided on an AS-IS basis, with no warranties and confers no rights.
Scanning Active Directory for SMTP Addresses
How To: Scanning Active Directory for SMTP Addresses.
When an object in AD is mail enabled it is assigned an SMTP address. Sometimes these addresses are assigned incorrectly to objects or a requested address is already in use on a object and you need to find the object that it is assigned to. I come across this situation sometimes and the simplest way is to query AD for the SMTP address. The SMTP addresses are stored in an AD attribute called “proxyAddresses” this holds all addresses including SMTP. Using the QUEST PowerShell PSSnapIn, you can query AD for this attribute using an LDAP filter.
Querying AD using Quest cmdlets and LDAP query.
Get-QADObject -LdapFilter "(proxyAddresses=*$Address*)" -IncludedProperties "proxyAddresses"
Using the above command with perform the filter, however you will only pull back the default cached set of attributes for the object so I add the ‘-includeallproperties’ switch to pull all attributes available for the object back into the cache.
I also set the ‘-SizeLimit 0′ so the result set isn’t limited to the standard 1000 returned objects.
The below code turns the basic shell command into a ‘Function’ called ‘Get-ProxyAddress.ps1′.
Instructions:
- Copy the below code into a file called ‘Get-ProxyAddress.ps1′
- Open the PowerShell shell and traverse to the directory that hosts the above file
- Run the script using this syntax, “./Get-ProxyAddress.ps1 address@domain.com”
- address@domain.com being the SMTP address you are searching for.
The output details either that the SMTP address is not found in AD or the below attributes are listed for the object that own the SMTP address.
- Name
- DisplayName
- ObjectClass
- AccountisDisabled
- AccountisLockedOut
- MailNickName
- LegacyExchangeDN
Download Script from PoshCode.org
Code:
Param ( [Parameter(Mandatory=$true, Position=0, ValueFromPipeline=$true, HelpMessage="Enter SMTP address to search for in Active-Directory." )] [string]$objSMTP ) Function Get-ProxyAddresses ([string]$Address){ $objAD = $null $objAD = Get-QADObject -LdapFilter "(proxyAddresses=*$Address*)" -IncludeAllProperties -SizeLimit 0 -ErrorAction SilentlyContinue Write-Output $objAD }#Close Function #Validate Quest PSSnapin is loaded Add-PSSnapin -Name Quest.ActiveRoles.ADManagement -ErrorAction SilentlyContinue #Run Function to search AD for SMTP address $Results = $null $Results = Get-ProxyAddresses -Address $objSMTP | Select-Object Name,DisplayName,ObjectClass,Email,AccountisDisabled,AccountisLockedOut,MailNickName,LegacyExchangeDN -ErrorAction SilentlyContinue IF($Results -eq $null){ Write-Host "" Write-Host "No Object Found with .attribute[proxyAddress] containing $objSMTP."} Else{$Results | Format-List *} #End
Hope this helps.
All information is provided on an AS-IS basis, with no warranties and confers no rights.
Getting Virtual Machine CPU, Disk and Memory
I was asked to pull disk, memory and cpu statistics from VMware guests managed by multiple Virtual Center servers. Working with the VMWare Senior Administrator, we came up with this.
$CsvFile = "C:\VM_Hosts_Info.csv" $CsvData = @() $VCSrvs = "VCenter01","VCenter02","VCenter03" ForEach ($VCSrv in $VCSrvs){ Connect-VIServer -server $VCSrv Get-VM | % { $VM = $_ $Disk_T = "{0:N1}" -f(($VM | Get-HardDisk | measure-object -property CapacityKB -sum).Sum/1mb) $Row = "" | Select Name,Environment,MemoryMB,NumCPU,Total_DiskGB $Row.Name = $VM.Name $Row.Environment = $VCSrv $Row.MemoryMb = $VM.MemoryMb $Row.NumCpu = $VM.NumCpu $Row.Total_DiskGB = $Disk_T $CsvData += $Row }} $CsvData | Export-Csv $CsvFile -noTypeInformation
Hope this helps.
All information is provided on an AS-IS basis, with no warranties and confers no rights.
Getting Remote Registry WSUS Config
Using PowerShell to examine the registry is a simple process but to really get the power from PowerShell you need the data in an object.
Once in the object it is far easier to manipulate the data in a fashion that you are used to in the PowerShell command line.
I used the below PowerShell script to verify the “Automatic Update” configuration on a Windows 2003 Sp2 Server. The servers being analyzed are stored in a .txt file on the C:\ partition.
The path of the input list file is stored in the $File string. This method of using a custom object can be very usefull for non-object based data.
Code:
$WSUSREGALL = @() [String]$File = "C:\server.txt" $Servers = Get-Content $File ForEach($Server In $Servers) { $Registry = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey('LocalMachine', $Server) $RegKey0 = $Registry.OpenSubKey("Software\policies\Microsoft\Windows\WindowsUpdate\" ) $RegKey1 = $Registry.OpenSubKey("Software\policies\Microsoft\Windows\WindowsUpdate\AU\") $WSUSREG = New-Object System.Object $WSUSREG | Add-Member -MemberType NoteProperty -Name "Server" -Value $Server $WSUSREG | Add-Member -MemberType NoteProperty -Name WUServer -Value $RegKey0.GetValue("WUServer") $WSUSREG | Add-Member -MemberType NoteProperty -Name WUStatusServer -Value $RegKey0.GetValue("WUStatusServer") $WSUSREG | Add-Member -MemberType NoteProperty -Name TargetGroupEnabled -Value $RegKey0.GetValue("TargetGroupEnabled") $WSUSREG | Add-Member -MemberType NoteProperty -Name AcceptTrustedPublisherCerts -Value $RegKey0.GetValue("AcceptTrustedPublisherCerts") $WSUSREG | Add-Member -MemberType NoteProperty -Name AUOptions -Value $RegKey1.GetValue("AUOptions") $WSUSREG | Add-Member -MemberType NoteProperty -Name NoAutoUpdate -Value $RegKey1.GetValue("NoAutoUpdate") $WSUSREG | Add-Member -MemberType NoteProperty -Name NoAUShutdownOption -Value $RegKey1.GetValue("NoAUShutdownOption") $WSUSREG | Add-Member -MemberType NoteProperty -Name NoAUAsDefaultShutdownOption -Value $RegKey1.GetValue("NoAUAsDefaultShutdownOption") $WSUSREG | Add-Member -MemberType NoteProperty -Name ScheduledInstallDay -Value $RegKey1.GetValue("ScheduledInstallDay") $WSUSREG | Add-Member -MemberType NoteProperty -Name ScheduledInstallTime -Value $RegKey1.GetValue("ScheduledInstallTime") $WSUSREG | Add-Member -MemberType NoteProperty -Name UseWUServer -Value $RegKey1.GetValue("UseWUServer") $WSUSREG | Add-Member -MemberType NoteProperty -Name RescheduleWaitTimeEnabled -Value $RegKey1.GetValue("RescheduleWaitTimeEnabled") $WSUSREG | Add-Member -MemberType NoteProperty -Name RescheduleWaitTime -Value $RegKey1.GetValue("RescheduleWaitTime") $WSUSREG | Add-Member -MemberType NoteProperty -Name NoAutoRebootWithLoggedOnUsers -Value $RegKey1.GetValue("NoAutoRebootWithLoggedOnUsers") $WSUSREG | Add-Member -MemberType NoteProperty -Name DetectionFrequencyEnabled -Value $RegKey1.GetValue("DetectionFrequencyEnabled") $WSUSREG | Add-Member -MemberType NoteProperty -Name DetectionFrequency -Value $RegKey1.GetValue("DetectionFrequency") $WSUSREG | Add-Member -MemberType NoteProperty -Name AutoInstallMinorUpdates -Value $RegKey1.GetValue("AutoInstallMinorUpdates") $WSUSREG | Add-Member -MemberType NoteProperty -Name RebootWarningTimeoutEnabled -Value $RegKey1.GetValue("RebootWarningTimeoutEnabled") $WSUSREG | Add-Member -MemberType NoteProperty -Name RebootWarningTimeout -Value $RegKey1.GetValue("RebootWarningTimeout") $WSUSREG | Add-Member -MemberType NoteProperty -Name RebootRelaunchTimeoutEnabled -Value $RegKey1.GetValue("RebootRelaunchTimeoutEnabled") $WSUSREG | Add-Member -MemberType NoteProperty -Name RebootRelaunchTimeout -Value $RegKey1.GetValue("RebootRelaunchTimeout") $WSUSREG | Add-Member -MemberType NoteProperty -Name IncludeRecommendedUpdates -Value $RegKey1.GetValue("IncludeRecommendedUpdates") $WSUSREG | Add-Member -MemberType NoteProperty -Name AUPowerManagement -Value $RegKey1.GetValue("AUPowerManagement") $WSUSREGALL += $WSUSREG } $WSUSREGALL $WSUSREGALL | Export-Csv "C:\DataWSUS.csv" -NoTypeInformation
Hope this helps.
All information is provided on an AS-IS basis, with no warranties and confers no rights.
Exporting Data to Excel using PowerShell
I had to get a quick audit of systems and i needed to know OS Version, SP version, Memory and Disk Details.
One problem is that if you don’t know what disks you have exporting this to excel is difficult. I chose to dynamically create the headers for the excel file based on the drive letter names. This approach is by no means perfect as you rely on the fact that people have built the systems with sequential drive letters. Any drive letter out of sequence will disturb the excel header names.
Example: If you have 3 servers and two have letters C: and D: and the third has C: and E: this approach will not work for you. Luckily I know my servers have sequential drive letters.
Code:
$excel = New-Object -comobject Excel.Application $excel.visible = $True $wbook = $excel.Workbooks.Add() $wsheet = $wbook.Worksheets.Item(1) $wsheet.Cells.Item(1,1) = "Date" $wsheet.Cells.Item(1,2) = "Server" $wsheet.Cells.Item(1,3) = "Memory" $wsheet.Cells.Item(1,4) = "OSName" $wsheet.Cells.Item(1,5) = "SPVersion" $iRow = 2 $InputFile = "C:\Server.txt" #Input File Containing Servers to be Scanned $Servers = Get-Content $InputFile ForEach($Server in $Servers) { $wsheet.Cells.Item($iRow,1) = Get-Date $wsheet.Cells.Item($iRow,2) =$Server $AllCS = Get-WmiObject -Class Win32_ComputerSystem -ComputerName $Server | Select-Object TotalPhysicalMemory ForEach($CS In $AllCS){$wsheet.Cells.Item($iRow,3) = $CS.TotalPhysicalMemory/1GB} $AllOS = Get-WmiObject -Class Win32_OperatingSystem -ComputerName $Server ForEach($OS In $AllOS){$OSVersion = $OS.Caption;$SPVersion = $OS.CSDVersion} $wsheet.Cells.Item($iRow,4) = $OSVersion $wsheet.Cells.Item($iRow,5) = $SPVersion $logicalDisk = Get-WmiObject -Class Win32_LogicalDisk -ComputerName $Server -Filter "DriveType=3" | Select-Object Caption,Size $j = 6 For($i=0;$i -le $LogicalDisk.Length - 1; $i++){ $wsheet.Cells.Item(1,$j) = $LogicalDisk[$i].Caption $wsheet.Cells.Item($iRow,$j) = $LogicalDisk[$i].Size/1GB $j++} $iRow++ } $range = $wsheet.UsedRange $range.Interior.ColorIndex = 19 $range.Font.ColorIndex = 11 $range.Font.Bold = $True $range.EntireColumn.AutoFilter() $range.EntireColumn.AutoFit() $excel.ActiveWorkbook.SaveAs("C:\Server_Data.xls") $excel.ActiveWorkbook.Close $excel.Application.Quit If (ps excel) { kill -name excel}
Hope this helps.
All information is provided on an AS-IS basis, with no warranties and confers no rights.
Disconnected Mailboxes in Exchange 2003
I was looking into finding all disconnected/orphaned mailboxes in an Exchange2003 org. Normally I would use either an old VBScript I have or the Exchange 2003 ESM. I thought it would be good to experiment with PowerShell and try to simplify the VBScript that I have.
Below is a PowerShell script that utilizes ADSI to gather all Exchange 2003 servers in your configuration container. Then for each server it utilizes WMI to connect to the Root/MicrosoftExchangeV2 ‘NameSpace’ and the Exchange_Mailbox provider. From there we filter on only mailboxes that the “DateDiscoveredAbscentInDS” property is NOT NULL. That data is then passed through the pipeline to a Select-Object where a custom Hash table is built to display the Server Name of the filtered mailboxes. The filtered mailboxes and the selected data is then passed through the pipeline and exported to a .csv file on the root of C:\ named with the format C:\DisconnectedMailboxes_%Servername%.csv.
Code:
[String]$StrFilter = "(objectCategory=msExchExchangeServer)" $objRootDSE = [ADSI]"LDAP://RootDSE" [String]$strContainer = $objRootDSE.configurationNamingContext $objSearcher = New-Object System.DirectoryServices.DirectorySearcher $objSearcher.SearchRoot = New-object System.DirectoryServices.DirectoryEntry("LDAP://$strContainer") $objSearcher.PageSize = 1000 $objSearcher.Filter = $strFilter $objSearcher.SearchScope = "Subtree" $colResults = $objSearcher.FindAll() ForEach ($objResult in $colResults) { [String]$ServerDN = $objResult.Properties.distinguishedname $objServer = [ADSI]"LDAP://$ServerDN" [String]$Server = $objServer.cn Get-WmiObject -NameSpace Root\MicrosoftExchangeV2 -Class Exchange_Mailbox -Computer $Server | ` Where { $_.DateDiscoveredAbsentInDS -ne $null } | ` Select-Object @{N="Server";E={$_.__Server}},` StorageGroupName,StoreName,MailboxDisplayName,Size,DateDiscoveredAbsentInDS | ` Export-Csv "C:\DisconnectedMailboxes_$Server.csv" -NoTypeInformation }#End ForEach
Hope this helps.
All information is provided on an AS-IS basis, with no warranties and confers no rights.
Scheduling PowerShell Scripts
Just a quick Post…
Yesterday I had to schedule PowerShell scripts on a Windows 2003 Server, this following technique worked for me but I have not tested it on other OS platform.
Similar to a VBScript in the AT scheduled job you point to a .bat or .cmd file for the Program to run. In this file you have the below code to initialize the script in its shell.
In the .bat or .cmd file you will need the following code.
Powershell -NoLogo -Command "&{C:\Scripts\Posh\Script1.ps1}"
Hope this helps.
All information is provided on an AS-IS basis, with no warranties and confers no rights.
