Max Trinidad – The PowerShell Front
A little of everything about PowerShell!!!
MS Office
Back in October 2014, I presented and recorded on my topic about working with Excel and PowerShell. I thought this video was lost but I founded it on a backup drive. Well its loaded with good samples using my favorite tool: SAPIEN’s PowerShell Studio. These tools are an essential for building .NET scripting and applications with PowerShell.
Check out the video at:
I recently helped someone with providing a solution using PowerShell to extract data from SQL Server into an Excel file. We all know that we could use SSIS to provide the means to do this but there are some situations you may want to use scripting instead. So, found one script I did back in 2009 that will do such a thing. The funny thing is, when I looked at it, I realized that for the Excel part I had a lot of unnecessary extra code and it could be improved greatly. So, here’s the updated version.
This *script will do the following steps:
1. Connect to SQL Server and get the SQL Server data.
2. Build the Excel file with columns heading and data.
3. Save the Excel file and Close/Terminate Excel process.
*Note: This script is PowerShell Version 2.0 compatible.
Getting you SQL Data
This script uses the ‘System.Data.SqlClient.SqlConnection’ which you can run on any machines without SQL Server installed. In this example the connection string is a trusted ‘Windows Authentication’. Then, I’m using the Here-String @”..”@ to insert the T-SQL script I want to execute against SQL Server. Keep in mind, this connection string can be change to use SQL Server Authentication.
[sourcecode language=”powershell”]
## ———- Working with SQL Server ———- ##
## – Get SQL Server Table data:
$SQLServer = ‘SQLServer01\MSSQLInstance01’;
$Database = ‘Database1′;
$SqlQuery = @’
Select top 10
[Field1]
,[Field2]
,[Field3]
,[Field4]
,[Field5]
from Database1.dbo.Table1
where [Field4] = ‘X001’
‘@;
## – Connect to SQL Server using non-SMO class ‘System.Data’:
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection;
$SqlConnection.ConnectionString = `
"Server = $SQLServer; Database = $Database; Integrated Security = True";
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand;
$SqlCmd.CommandText = $SqlQuery;
$SqlCmd.Connection = $SqlConnection;
## – Extract and build the SQL data object ‘$DataSetTable’:
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter;
$SqlAdapter.SelectCommand = $SqlCmd;
$DataSet = New-Object System.Data.DataSet;
$SqlAdapter.Fill($DataSet);
$DataSetTable = $DataSet.Tables["Table"];
[/sourcecode]
Buidling the Excel file
This block of code will generate the Excel file consuming the SQL data object ‘$DataSetTable’. The result of the SQL DataSet will be use to automatically create the columns heading and data rows. This is the heart of the script where the magic happen.
[sourcecode language=”powershell”]
## ———- Working with Excel ———- ##
## – Create an Excel Application instance:
$xlsObj = New-Object -ComObject Excel.Application;
## – Create new Workbook and Sheet (Visible = 1 / 0 not visible)
$xlsObj.Visible = 0;
$xlsWb = $xlsobj.Workbooks.Add();
$xlsSh = $xlsWb.Worksheets.item(1);
## – Build the Excel column heading:
[Array] $getColumnNames = $DataSetTable.Columns | Select ColumnName;
## – Build column header:
[Int] $RowHeader = 1;
foreach ($ColH in $getColumnNames)
{
$xlsSh.Cells.item(1, $RowHeader).font.bold = $true;
$xlsSh.Cells.item(1, $RowHeader) = $ColH.ColumnName;
$RowHeader++;
};
## – Adding the data start in row 2 column 1:
[Int] $rowData = 2;
[Int] $colData = 1;
foreach ($rec in $DataSetTable.Rows)
{
foreach ($Coln in $getColumnNames)
{
## – Next line convert cell to be text only:
$xlsSh.Cells.NumberFormat = "@";
## – Populating columns:
$xlsSh.Cells.Item($rowData, $colData) = `
$rec.$($Coln.ColumnName).ToString();
$ColData++;
};
$rowData++; $ColData = 1;
};
## – Adjusting columns in the Excel sheet:
$xlsRng = $xlsSH.usedRange;
$xlsRng.EntireColumn.AutoFit();
[/sourcecode]
Saving and Terminating Excel
Now that I’ve build the Excel sheet, I need to save the file, quit and terminate Excel. And, Yes! It’s needed to terminate/kill the Excel process because this process will remain active even if when exiting/closing the PowerShell session.
[sourcecode language=”powershell”]
## ———- Saving file and Terminating Excel Application ———- ##
## – Saving Excel file – if the file exist do delete then save
$xlsFile = `
"C:\Temp\NewExceldbResults_$((Get-Date).ToString("yyyyMMdd_hhmmss")).xls";
if (Test-Path $xlsFile)
{
Remove-Item $xlsFile
$xlsObj.ActiveWorkbook.SaveAs($xlsFile);
}
else
{
$xlsObj.ActiveWorkbook.SaveAs($xlsFile);
};
## Quit Excel and Terminate Excel Application process:
$xlsObj.Quit(); (Get-Process Excel*) | foreach ($_) { $_.kill() };
## – End of Script – ##
[/sourcecode]
Additional Note
One thing to understand, this process will work with small datasets. So, the more data you extract the slower it may take to build the Excel file. This is why is important to test the script(s) and look at other best possible solution. Maybe it’s better to use SSIS (SQL
Server Integration Services) but it doesn’t hurt try other technologies.
Here’s a quick way to start getting a list of SQL Server users having “SysAdmin” Role. Basically, I’m using SQLPS module (now available with SQL Server 2012) which loads all the SMO needed to help you script against your SQL engine.
This script does the following:
Here’s the code:
[sourcecode language=”powershell”]
Import-Module SQLPS -disablenamechecking
$SQLSvr = "SQLServername\Instancename";
$MySQL = new-object Microsoft.SqlServer.Management.Smo.Server $SQLSvr;
$SQLLogins = $MySQL.Logins;
$SysAdmins = $null;
$SysAdmins = foreach($SQLUser in $SQLLogins)
{
foreach($role in $SQLUser.ListMembers())
{
if($role -match ‘sysadmin’)
{
Write-Host "SysAdmins found: $($SQLUser.Name)" -ForegroundColor Yellow;
$SQLUser | Select-Object `
@{label = "SQLServer"; Expression = {$SQLSvr}}, `
@{label = "CurrentDate"; Expression = {(Get-Date).ToString("yyyy-MM-dd")}}, `
Name, LoginType, CreateDate, DateLastModified;
};
};
};
$SysAdmins | Export-Csv -Path ‘C:\temp\SQLSysAdminList.csv’ -Force -NoTypeInformation;
ii ‘C:\temp\SQLSysAdminList.csv’;
[/sourcecode]
Eventually, you could make changes to this scritp to be capable to access a list of SQL Servers and build your custom report.
Bonus:
To add the functionallity to connect to multiple servers, we can add a list of Servers and then using the “Foreach” statement to loop through the list, and with little changes to the previous code.
Here’s how it will look with just adding a few more line of code:
[sourcecode language=”powershell”]
## – Loads SQL Powerhell SMO and commands:
Import-Module SQLPS -disablenamechecking
## – BUild list of Servers manually (this builds an array list):
$SQLServers = "Server01","Server01\InstanceNameA","Server03";
$SysAdmins = $null;
foreach($SQLSvr in $SQLServers)
{
## – Add Code block:
$MySQL = new-object Microsoft.SqlServer.Management.Smo.Server $SQLSvr;
$SQLLogins = $MySQL.Logins;
$SysAdmins += foreach($SQLUser in $SQLLogins)
{
foreach($role in $SQLUser.ListMembers())
{
if($role -match ‘sysadmin’)
{
Write-Host "SysAdmins found: $($SQLUser.Name)" -ForegroundColor Yellow;
$SQLUser | Select-Object `
@{label = "SQLServer"; Expression = {$SQLSvr}}, `
@{label = "CurrentDate"; Expression = {(Get-Date).ToString("yyyy-MM-dd")}}, `
Name, LoginType, CreateDate, DateLastModified;
};
};
};
## – End of Code block
}
## – BUild and open report:
$SysAdmins | Export-Csv -Path ‘C:\temp\SQLSysAdminList.csv’ -Force -NoTypeInformation;
ii ‘C:\temp\SQLSysAdminList.csv’;
[/sourcecode]
That’s it!
I have to admit that having setup Office 365 was a breeze. Just make sure you have a reliable fast internet connection, and you are good to go. By the way… no servers equipment is needed.
Well, since I got Office 365 Beta setup in my computer I’ve been looking for information about how to use PowerShell. Now, thanks to one of my PowerShell MVP
college (Sean Kearney) mention about this information been well documented in
Office 365 Beta. I found it, and I couldn’t believe it was that SIMPLE!!! YES,
SIMPLE!!
My computer environment is very basic. I have no Exchange, Linc, or Sharepoint. So now I’m part of the cloud experience with Office 365, and my answer of using PowerShell was at my fingertip.
If you want to try it, then check out the links below, and copy/paste the code. Make sure you meet all the requirements needed so you can experience using PowerShell with Office 365. By the way, It’s highly recommended by our PowerShell MVP’s colleges to set your Execution Policy to “ RemoteSigned” and not “unrestrincted”. Using “unrestricted” will still give you some security warning.
Office 365 – PowerShell – Resource Information Currently
available under: “Outlook Web App Help”
Install and Configure Windows PowerShell: http://help.outlook.com/en-us/140/cc952756.aspx
Reference to Available PowerShell Cmdlets: http://help.outlook.com/en-us/140/dd575549.aspx
Use Windows PowerShell in Exchange Online: http://help.outlook.com/en-us/140/cc546278.aspx
**Control Users’ Access to Windows Remote
Management: http://help.outlook.com/en-us/140/Dd256962.aspx
Connect Windows PowerShell to the Service: http://help.outlook.com/en-us/140/cc952755.aspx
**You need to understand one thing, and quote this from the
help “… By default, all new user accounts are allowed to use Windows
Remote Management (WinRM) to access the cloud-based organization with Windows
PowerShell …”. But, the help documentation gives you the
code to prevent users to access remoting with Office 365. (read the help documentation)
By the way, there’s a recent change to the connection URI:
… -ConnectionUri “https://pod51011psh.outlook.com/PowerShell-LiveID?PSVersion=2.0”
I love our PowerShell MVP Community. Here’s an observation from Aleksandar Nikolic: “… i think you should use generic Connectionuri(https://ps.outlook.com/powershell) and not specific server. you’ll be redirected … office365 will redirect you to closest, the least busy server …”. Thanks Alek.:)
This is totally true, and the only reason I included the change was to avoid the redirection warning message. So you can still use what’s documented:
-ConnectionUri https://ps.outlook.com/powershell/
Sample Script:
[sourcecode language=”powershell”]
##—- Getting Started with Office 365 PowerShell —-##
## – Get your Office 365 Credential
$LiveCred = Get-Credential
## – Prepare your Office 365 PS Session
$Session = New-PSSession -ConfigurationName Microsoft.Exchange `
-ConnectionUri "https://pod51011psh.outlook.com/PowerShell-LiveID?PSVersion=2.0" `
-Credential $LiveCred -Authentication Basic -AllowRedirection
## – Load Office 365 PowerShell module into your current PS Console session.
$Office365PS = Import-PSSession $Session
## – Using the exported functions get a list of your mailboxes
Get-User
## – Get command information, count and list names.
$Office365PS.ExportedFunctions.count
get-command -Module $Office365PS | more
[/sourcecode]
In my case, I got a total of 216 more PowerShell commands to manage and/or automate my Office 365 cloud application: (listed below)
Add-DistributionGroupMember
Add-MailboxFolderPermission
Add-MailboxPermission
Add-RecipientPermission
Add-RoleGroupMember
Clear-ActiveSyncDevice
Clear-TextMessagingAccount
Compare-TextMessagingVerificationC
Complete-Migration
Disable-InboxRule
Disable-Mailbox
Enable-InboxRule
Enable-Mailbox
Enable-OrganizationCustomization
Export-MigrationReport
Get-AcceptedDomain
Get-ActiveSyncDevice
Get-ActiveSyncDeviceAccessRule
Get-ActiveSyncDeviceClass
Get-ActiveSyncDeviceStatistics
Get-ActiveSyncMailboxPolicy
Get-ActiveSyncOrganizationSettings
Get-AdminAuditLogConfig
Get-CalendarNotification
Get-CalendarProcessing
Get-CASMailbox
Get-CASMailboxPlan
Get-Contact
Get-DistributionGroup
Get-DistributionGroupMember
Get-DynamicDistributionGroup
Get-FailedContentIndexDocuments
Get-FederatedOrganizationIdentifie
Get-FederationInformation
Get-FederationTrust
Get-Group
Get-HotmailSubscription
Get-ImapSubscription
Get-InboxRule
Get-IRMConfiguration
Get-LinkedUser
Get-LogonStatistics
Get-Mailbox
Get-MailboxAuditBypassAssociation
Get-MailboxAutoReplyConfiguration
Get-MailboxCalendarConfiguration
Get-MailboxCalendarFolder
Get-MailboxFolder
Get-MailboxFolderPermission
Get-MailboxFolderStatistics
Get-MailboxJunkEmailConfiguration
Get-MailboxMessageConfiguration
Get-MailboxPermission
Get-MailboxPlan
Get-MailboxRegionalConfiguration
Get-MailboxSpellingConfiguration
Get-MailboxStatistics
Get-MailContact
Get-MailUser
Get-ManagementRole
Get-ManagementRoleAssignment
Get-ManagementRoleEntry
Get-ManagementScope
Get-MessageCategory
Get-MessageClassification
Get-MessageTrackingReport
Get-MigrationBatch
Get-MigrationStatus
Get-MigrationUser
Get-MoveRequest
Get-MoveRequestStatistics
Get-OrganizationalUnit
Get-OrganizationRelationship
Get-OwaMailboxPolicy
Get-PerimeterConfig
Get-PopSubscription
Get-ProvisioningRequest
Get-Recipient
Get-RecipientPermission
Get-RecipientStatisticsReport
Get-RemoteDomain
Get-RemovedMailbox
Get-RetentionPolicy
Get-RetentionPolicyTag
Get-RMSTemplate
Get-RMSTrustedPublishingDomain
Get-RoleAssignmentPolicy
Get-RoleGroup
Get-RoleGroupMember
Get-SecurityPrincipal
Get-SendAddress
Get-ServiceStatus
Get-SharingPolicy
Get-Subscription
Get-SyncConfig
Get-TextMessagingAccount
Get-ThrottlingPolicy
Get-ThrottlingPolicyAssociation
Get-ToolInformation
Get-TransportConfig
Get-User
Import-ContactList
New-ActiveSyncDeviceAccessRule
New-ActiveSyncMailboxPolicy
New-AdminAuditLogSearch
New-DistributionGroup
New-DynamicDistributionGroup
New-HotmailSubscription
New-ImapSubscription
New-InboxRule
New-Mailbox
New-MailboxAuditLogSearch
New-MailboxFolder
New-MailContact
New-MailMessage
New-MailUser
New-MessageClassification
New-MigrationBatch
New-MoveRequest
New-OrganizationRelationship
New-OwaMailboxPolicy
New-PopSubscription
New-ProvisioningRequest
New-RemoteDomain
New-RetentionPolicy
New-RetentionPolicyTag
New-SharingPolicy
New-Subscription
Remove-ActiveSyncDevice
Remove-ActiveSyncDeviceAccessRule
Remove-ActiveSyncMailboxPolicy
Remove-DistributionGroup
Remove-DistributionGroupMember
Remove-DynamicDistributionGroup
Remove-InboxRule
Remove-Mailbox
Remove-MailboxFolderPermission
Remove-MailboxPermission
Remove-MailContact
Remove-MailUser
Remove-MessageClassification
Remove-MoveRequest
Remove-OrganizationRelationship
Remove-OwaMailboxPolicy
Remove-ProvisioningRequest
Remove-RecipientPermission
Remove-RemoteDomain
Remove-RetentionPolicy
Remove-RetentionPolicyTag
Remove-RoleGroupMember
Remove-SharingPolicy
Remove-Subscription
Resume-MoveRequest
Search-AdminAuditLog
Search-Mailbox
Search-MailboxAuditLog
Search-MessageTrackingReport
Send-TextMessagingVerificationCode
Set-AcceptedDomain
Set-ActiveSyncDeviceAccessRule
Set-ActiveSyncMailboxPolicy
Set-ActiveSyncOrganizationSettings
Set-CalendarNotification
Set-CalendarProcessing
Set-CASMailbox
Set-Contact
Set-DistributionGroup
Set-DynamicDistributionGroup
Set-FederatedOrganizationIdentifie
Set-Group
Set-HotmailSubscription
Set-ImapSubscription
Set-InboxRule
Set-LinkedUser
Set-Mailbox
Set-MailboxAuditBypassAssociation
Set-MailboxAutoReplyConfiguration
Set-MailboxCalendarConfiguration
Set-MailboxCalendarFolder
Set-MailboxFolderPermission
Set-MailboxJunkEmailConfiguration
Set-MailboxMessageConfiguration
Set-MailboxPlan
Set-MailboxRegionalConfiguration
Set-MailboxSpellingConfiguration
Set-MailContact
Set-MailUser
Set-MessageClassification
Set-MoveRequest
Set-OrganizationConfig
Set-OrganizationRelationship
Set-OwaMailboxPolicy
Set-PerimeterConfig
Set-PopSubscription
Set-RemoteDomain
Set-RetentionPolicy
Set-RetentionPolicyTag
Set-RoleGroup
Set-SharingPolicy
Set-TextMessagingAccount
Set-TransportConfig
Set-UMMailbox
Set-UMMailboxPIN
Set-User
Start-ManagedFolderAssistant
Start-MigrationBatch
Start-ProvisioningRequest
Start-RetentionAutoTagLearning
Stop-MigrationBatch
Suspend-MoveRequest
Test-MAPIConnectivity
Test-MigrationServerAvailability
Test-OrganizationRelationship
Update-DistributionGroupMember
Update-RoleGroupMember
Write-AdminAuditLog
As you can see, there’s plenty of commands to learn. So, let’s get busy.
Happy PowerShelling!!
We are pleased to announce the first South Florida IT Camp on Saturday, July 23rd 2011 from 8:00 am to 5:00 pm at Nova Southeastern in Fort Lauderdale.
Parking and admittance to this event is free. Lunch will provided. Please Register now. Let your friends and colleagues know about this event.
Join system administrators IT professionals and database professionals in addition to managers at all levels in Florida that work with Microsoft technologies for a “IT Camp Saturday”. IT Camps are a free, one day learning event for anyone seeking professional development. This event serves IT professionals and students with a focus on IT Pro related technologies. IT Camps offer a conference style learning environment free of charge to attendees and is open to presenters of
all backgrounds and expertise.
Have questions? Comment or Contact Us.
The call for speakers is open until 6/11. Topics can be anything applicable to System admins, and/or IT Pros including SharePoint, System Center, SQL Server, PowerShell, Active Directory, or Windows 2008 R2 and Windows 7.
To submit your session, please Click Here, or if you have questions? Comment or contact us.
