Playing with PowerShell Studio 2015 Windows Form 1/2

Yes! SAPIEN TechnologiesPowerShell Studio 2015” product allow you to create Windows form and at the same time you can compile it creating an executable application.  So, just for playing around I’m going to create a Windows application that will allow me to execute a PowerShell command and display all of its properties values in a datagrid form.

PSStudio_00_4-7-2015

Creating a PowerShell Studio Windows form

This application will have the following Windows components:

1. A textbox to type the PowerShell command.
2. Two checkboxes to allow display all or selected properties.
3. A datagrid to display the results.
4. And the button to execute the PowerShell command typed in the textbox.

DataGrid_01_4-8-2015

Also, I created a PowerShell function use to build the data to be sent to the datagrid component.

To create a new Windows form click on the “File” menu and select “New” then from the dropdown list pick “New Form“. This option will create a “*.psf” file.  I’ve named it “TestDataGrid1.psf“.

PSStudio_01_4-7-2015

PSStudio_02_4-7-2015

One important thing to keep in mind, when working building a Windows-based solution, this editor is Visual Studio-like. So, if you have work with any version of Visual Studio then your learning curved is minimal.  Just drag-and-drop the object into the form then later add the script code afterward.

This Windows form will contain the following controls:
1. TextBox
2. Label
3. Checkbox
4. Button
5. DataGrid

DataGrid_02_4-8-2015

As you add object components to the form their properties can change too. Mainly, look into changing the “Text” property of some of the objects, such as: Form, Label, and the Button.

DataGrid_03_4-9-2015

At this point there’s no code added to this Windows application but, from the “Home” menu, you can click on the “Run” option to see it running.  And, ‘No!’, you can’t execute the application outside of this editor.  Unless, you create an executable program out of this solution which you can do with this editor.

DataGrid_04_4-10-2015

Next upcoming blog will be adding the PowerShell code and some functions to our solution.

 

PowerShell SQL Server SMO Simplicity Series – 1

This series is a learn by sample blog which I’m hoping any DBA and/or SQL Developer will take advantage to re-use these code snippets.  Due to the lack of SQL Server cmdlets, I prefer to use SMO which gives me flexibility to build my own PowerShell commands to automate most of my routine tasks.

1 – Connecting to your SQL Server engine

This take at least three lines of code: (excluding the comment lines)

## - Setting variables with Server and Database name:
$SQLInstanceName = 'YourSQLServerInstancename';

## - Loading SMO .NET Assembly: (Required)
[system.reflection.assembly]::LoadWithPartialName("Microsoft.SQLServer.Smo") | Out-Null;

## - Connecting to SQL Server (Windows Authentication) and building you table object:
$MySQL = new-object('Microsoft.SqlServer.Management.Smo.Server') SQLInstanceName;

The first line you create a variable holding the SQL Server instance name, then loads the SQL Server assembly “Microsoft.SQLServer.SMO“, and then creating the variable holding the SQL Server objects.

The third line it’s what connect to the server using Windows Authentication.  Please be advice that, even you don’t have a Windows Authentication, it will not returned an error and it will return a Null object.

So, if you need to use SQL Server Authentication, you will need to add the following code after creating the “$MySQL” object:

## - Uncomment code block within [#region - #endregion] to enable use of SQL Authentication:
#region - Changing from default Windows to SQL Authentication:

#   ## - Variables required to SQL UserID and SQLPassword:
$SQLUserName = "YourSQLUserID"; $sqlPwd = "YourSQLUSerPassword";

## - Block of Code required to build SQL Authentication:
$MySQL.ConnectionContext.LoginSecure = $false;
$MySQL.ConnectionContext.set_Login($SQLUserName);
$SqlUserPwd = ConvertTo-SecureString $sqlPwd -AsPlainText -Force;
$MySQL.ConnectionContext.set_SecurePassword($SqlUserPwd);

Now, that you’re connected to the SQL Server instance, you can check the content in the variable $MySQL.  To do this the following one-liner help in exploring your object members (Methods and Properties);

$MySQL | Get-member | Out-GridView;

Get-MemberSMO01_3-30-2015

For starters, you might be looking to display some information so you need to look for $MySQL members “Properties” which hold values you can display using the PowerShell “Select-Object” cmdlet.  For example, the following oneliner will display the following database properties: Name, Owner, CreateDate, FileGroup, and PrimaryFilePath.

$MySQL.Databases | Select name, Owner, CreateDate, Filegroups, PrimaryFilePath | Format-Table -autosize;

So, Get-Member is you best command to explore your PowerShell objects and understand its content.  Another very useful command is the “Out-Gridview” which will use more heavily in the next blog series.

Don’t forget for any of these PowerShell cmdlet you can use the “Get-Help” display the command documentation:

Get-Help Get-Member -ShowWindow

Have fun and Keep learning PowerShell!

 

 

 

 

Post on PowerShell SQLServer SMO simplicity

Stay tuned for a series of post and learn how to start using SQL Server SMO with PowerShell V4 (or greater). Collect information from your SQL Server with PowerShell starting with a few lines of code.  See shortcuts and string formatting working in your favor while manipulating your data.  The scripts will evolved as you learn to apply new techniques.

Yes! PowerShell is about evolution of your automation skills for productivity.

How far can we take this? From this code:

($MySQL.Databases) `
| Select-object Name, Owner, RecoveryModel, Size, DataSpaceUsage, SpaceAvailable `
| Ft -AutoSize;

to an HTML solution.

SqlHTML_01_3-17-2015

At the end, it’s all about reusable code.

We glad to welcome our new sponsor Devart with their product “dbForge” which is an excellent tool next to SQL Server Management Studio.  I’ll be briefly showcasing this product.

dbForge_01_3-17-2015

 

FLPSUG February 26th meeting “Azure Edition”

Join us tomorrow in this special edition of our Florida PowerShell User Group meeting.

Our topic: “PowerShell Building Azure Quick VMs” – This is a PowerShell Azure via to quickly create Virtual Machines in it’s basic form. It will give you a quick way to get you started with Microsoft Azure.  Live Sample demo will be shown.

This is the same topic already given at the SQL PASS Virtual PowerShell meeting last Thursday February 19th.

I added a few more content and code.  All audience are welcome but mainly oriented to new Azure DevOps.

To register: https://www.eventbrite.com/e/florida-powershell-user-group-monthly-meeting-february-2015-tickets-15921743363

 

 

Florida PowerShell User Group – New Year 2015 Resolutions

Lets Do THIS!!  Come and join us.  It’s free essential training for a couple of hours.

  • IT Skills
    Any IT Pro/DevOps resolution for this year should be to improve your skills.  You need to start getting into PowerShell because is the the main technology for automating your On-Premise, Cloud and/or Hybrid infrastructure.  If you don’t care about it then you need to pick to a different career.
  • More Azure Sessions
    This new year I’m going to extend my PowerShell session to include more Azure presentations.  If you are a newbie you’re welcome to join us.  Or, if you’re already working with Azure PowerShell, then join us and share your experience.
  • Get to know Microsoft Cloud
    This year, get to know Microsoft Azure and all its component because they are all manageable with PowerShell.  With PowerShell you’ll need to building your knowledge of working with DSC (Desired State Configurations), OneGet, and Runbooks for Azure Task Automation.  There’s plenty of information out there and still you need to connect the dots to make it work.
  • Learn about tools
    One thing for sure is that Windows ISE editor and PowerShell version 4.0 (or greater) plays an important roll when buidling scripts.  Also, when combining the free Visual Studio Community Edition will be a most when integrating PowerShell and providing source code management with Team Foundation Online or Github. This is a MOST to learn to use!
  • Extending meetings
    Here’s where our new coming Azure PowerShell meeting will start diving into the essential of using DSC as a foundation to improve our understanding of Desired State Configuration.  At the end, of this journey we’ll end up taking a look at CHEF tool.

So, our normal monthly FLPSUG meeting (3rd Thursday) will be inviting MVP’s speakers to showcase their expertise.  Then, on our new second FLPSUG meeting (starting in February) we’ll be strictly concentrating on PowerShell Azure for Cloud Task Automation and having MVP Adnan cartwright (co-hosting) in these series.

Let us Welcome New Year 2015!!

Join Us next meeting on January 22nd 6:30PM (EST): https://www.eventbrite.com/e/florida-powershell-user-group-monthly-meeting-january-2015-tickets-15120220988

 

Getting started with Windows 10 PowerShell v5.0 Preview

As we already know with the new version of Windows 10 comes PowerShell Version 5.0 PREVIEW loaded with more enhancements and new Cmdlets.  It also come with the PowerShell ISE (Integrated Scripting Environment) which has been enhanced.  But, how can we get started?

The same procedure needed to get started in previous PowerShell versions still applies:

1. Add PowerShell on your “Start Menu” or just run it from the Windows System menu group.

ExecutePowerShellasAdiministrator

2. Open either of the PowerShell Console or the shell within the ISE then set the “ExecutionPolicy“.  Preferably executing “as Administrator“:

Set-ExecutionPolicy -ExecutionPoilicy RemoteSigned

ExecutePowerShellExecutionPolicy

3. To start the Windows PowerShell ISE editor you will need to execute the “ISE” command from the PowerShell console.
Then when is open, you can do a right-click on the ISE task bar and do “Pin to Taskbar“.  ISE can’t be pinned to the “Start Menu” yet.

ExecutePowerShellISE

Although, without setting the ExecutionPolicy, you can still execute single command lines. But, failure to do so will cause a script file not to execute.

Next, If you don’t have the time to buy a book then take a look at the PowerShell Help “About_*” topics.   There are a additional step that update your PowerShell documentation on your machine.  Of course, Internet connection is required.

4. Open the PowerShell Console or the shell in ISE to run the following command “Update-Help”, and again preferably executing “as Administrator”:

Update-Help

ExecutePowerShellISE_02

ExecutePowerShellISE_01

After you have updated all your PowerShell Version 5.0 documentation try usign the following command lines to list and select any of the existing help “About_*” topics:

[Array] $s = (Get-Help About_* | Select Name) | out-gridview -PassThru;
foreach($i in $s){ Get-Help $i.Name -ShowWindow;};

PowerShellColectingHelpTopic_01

PowerShellColectingHelpTopic_02

Basically, these two commands will help in listing all help About_* topics and using ‘Out-Gridview’ cmdlet with the  -Passthru’ parameter you can select multiple topics to read.  Just press the Ctrl key when selecting the topics.

I’ve create a help module if you want to give it a try to assist in your PowerShell journey.  Check this link:
https://gallery.technet.microsoft.com/DiscoverPowerShell-Module-1fbe9a5e

http://www.maxtblog.com/2014/05/more-discover-powershell-how-about-help-with-powershell-variables/

4. When loading PowerShell modules the following folder “C:\Users\…\Documents\WindowsPowerShell\Modules” doesn’t exist.  So you will need to go to the User document folder create this folder.

This folder is where you install all you customs and/or add any existing on PowerShell modules (including the ones for ISE).  Below is a list of suggested PowerShell Add-on modules you may want to look at:

Make sure to read the ReadMe files and verify that the module(s) can run on this version of PowerShell.

Now, at this point, you’ll be ready to start using PowerShell v5.0.

FLPSUG October 16th Meeting PowerShell Excel Deep Dive…

Thanks to everyone who attend last Thursday meeting on “Quick Deep Dive to Excel with PowerShell” .   Here’s the demo sample scripts containing the following:

1. 0_TipsTrickResourceWithExcel.ps1 – Tips, Trick, and web resource information.
2. 1_TrapSystemLogData.ps1 - Sample script to build an PSObject with both Application/System errors and warning logs use to print the Excel report.
3. 2_CreateSystemLogReport.ps1 - Procedure that will generate the Eventlog results show grouped and count of errors in Excel format.
4. 3_Function_Convert-psObjectToExcel_01.ps1 - Another variation of creating a basic formatted Excel file from a PSObject.
5. 4_SMOGetdatabaseSize.ps1 - Sample script use to build a PSObject for the previous function.

Download Demo Scripts:

PowerShell using SMO to get SQL Database Table information

Using SQL Server Management Object .NET class can assist in extract information about your SQL Server engine.  This is a sample PowerShell script code using SMO to extract database tables information and display it in the PowerShell console;

## - SMO query for Tables information within the database:
## --

## - Loading SMO .NET Assembly: (Required)
[system.reflection.assembly]::LoadWithPartialName("Microsoft.SQLServer.Smo") | Out-Null;

## - Setting variables with Server and Database name:
$SQLInstanceName = 'WIN81ENT01'; $Global:SourcedbName = 'AdventureWorks2014';

## - Connecting to SQL Server (Windows Authentication) and building you table object:
$MySQL = new-object('Microsoft.SqlServer.Management.Smo.Server') $SQLInstanceName

## - Uncomment code block within #region - #endregion to enable use of SQL Authentication:
#region - Changing from default Windows to SQL Authentication:

#   ## - Variables required to SQL UserID and SQLPassword:
# $SQLUserName = "YourSQLUserID"; $sqlPwd = "YourSQLUSerPassword";
#
#   ## - Block of Code required to build SQL Authentication:
# $MySQL.ConnectionContext.LoginSecure = $false;
# $MySQL.ConnectionContext.set_Login($SQLUserName);
# $SqlUserPwd = ConvertTo-SecureString $sqlPwd -AsPlainText -Force;
# $MySQL.ConnectionContext.set_SecurePassword($SqlUserPwd);

#endregion

## - Create PSObject with all database table information:
$dbtables = $MySQL.Databases[$Global:SourcedbName].tables;

## - End of Script

Notice that in the code for connecting to SQL Server using SQL Authentication is included.  So, to enabled it, just uncomment the block of code, then provide the SQL UserName and Password.

After you have created the psObject with the database table information, we can proceed to display it on console using the Out-Gridview cmdlet.


## 1 - Display Table Information on a GridView:
$dbtables | Select-Object `
@{ Label = 'SQLServerName'; Expression = { ($SQLInstanceName) }; }, `
@{ Label = 'DatabaseName'; Expression = { ($_.Parent) }; }, `
@{ Label = 'TableName'; Expression = { ($_.Name) }; },
@{ Label = 'DataSpaceUsed(KB)'; Expression = { ($_.dataspaceused) }; }, `
@{ Label = 'IndexSpaceUsed(KB)'; Expression = { ($_.IndexSpaceUsed) }; }, RowCount, `
@{
Label = "LastTableUpdate"; Expression = { `
$sqlupdtbl = @"
Select
--object_name(object_Id) as TableName,
last_user_update as [Last_Table_Update]
from sys.dm_db_index_usage_stats
where database_id = db_ID('$($Global:SourcedbName)')
and Object_ID = Object_id('$($_.Name)')
"@; `
$x = $MySQL.Databases[$Global:SourcedbName].ExecuteWithResults($sqlupdtbl); `
(((($x.tables).getenumerator()) | Select $_.Last_Table_Update) `
| Select -first 1 Last_Table_Update).Last_Table_Update;
}} | Out-GridView `
-Title "Display Database: $SourcedbName Tables Information";

Output to a gridview

Also, it’s possible to export the psobject information to a *.csv file format for later viewing using the Export-csv cmdlet.


## 2 - Create csv file:
$dbtables | Select-Object `
@{ Label = 'SQLServerName'; Expression = { ($SQLInstanceName) }; }, `
@{ Label = 'DatabaseName'; Expression = { ($_.Parent) }; }, `
@{ Label = 'TableName'; Expression = { ($_.Name) }; },
@{ Label = 'DataSpaceUsed(KB)'; Expression = { ($_.dataspaceused) }; }, `
@{ Label = 'IndexSpaceUsed(KB)'; Expression = { ($_.IndexSpaceUsed) }; }, RowCount, `
@{ Label = "LastTableUpdate"; Expression = { `
$sqlupdtbl = @"
Select
--object_name(object_Id) as TableName,
last_user_update as [Last_Table_Update]
from sys.dm_db_index_usage_stats
where database_id = db_ID('$($Global:SourcedbName)')
and Object_ID = Object_id('$($_.Name)')
"@; `
$x = $MySQL.Databases[$Global:SourcedbName].ExecuteWithResults($sqlupdtbl); `
(((($x.tables).getenumerator()) | Select $_.Last_Table_Update) `
| Select -first 1 Last_Table_Update).Last_Table_Update; }} `
| Export-Csv -NoClobber -NoTypeInformation `
-Path "C:\Temp\$SQLInstanceName_ListDatabase_$SourcedbName_TableInfo.csv";

## 3 - Open file:
Invoke-Item "C:\Temp\$SQLInstanceName_ListDatabase_$SourcedbName_TableInfo.csv";

Export to *.CSV file

Keep in mind, in order to use load and use the “Microsoft.SQLServer.Smo” assembly, you must had already install at least the SQL Server Management Studio or only using it’s individual components (http://www.maxtblog.com/2012/09/create-powershell-smo-scripts-without-installing-sql-server/).

For more information about SQL Server SMO check my article at:
http://sqlmag.com/powershell/using-sql-server-management-objects-powershell

 

 

Head start to PowerShell 5.0 Experimental Preview

Yes! Windows Management Framework 5.0 Preview Experimental – July 2014 has been available and full of goodies for IT Pro(s) and DevOp(s). I’m hoping you all realized PowerShell is your number ONE tool for automation productivity in your Infrastructure environment(s). Please, take the time to learn it and don’t be left behind.

You all heard Microsoft initiative to have everyone start migrating from Windows Server 2003 to Windows Server 2012 R2 for either your ‘On Premise’ and/or ‘MS Cloud’ environment(s). Definitely, PowerShell can provide the help in completing these tasks.

In order to use **PowerShell Version 5.0 Experimental, you are required to use Windows Server 2012 R2 and/or Windows 8.1. Also, prior to installing the preview, you will need to install a hotfix. This is all documented in the download link. This why is important to read the download instruction before proceeding with the installing the preview.

This new preview provide you with lots of enhancements:

  1. DCS (Desired State Configuration) – Bugs fixes and new modules.
  2. OneGet – simplify how you discover and install software packages.
  3. PowerShellGet – new way to discover, install, and update PowerShell Modules.
  4. Network Switched cmdlets.
  5. PowerShell ISE – DCS authoring improvements
  6. Language enhancements – Develop classes. (You need to check this one out!)

**NOTE:  This is a Preview and it doesn’t mean that all you see will be included in the RTM version. 

Read the Preview Documentation for more information.

 

Important Resources

Below are the necessary resource links to get you started with the latest materials about PowerShell Version 5.0: (Good reading material)

  1. PowerShell DSC Resource Kit Wave 5 Arrives: http://blogs.msdn.com/b/powershell/archive/2014/07/17/powershell-dsc-resource-kit-wave-5-arrives.aspx
  2. TechNet Gallery – resources for IT professionals – Gallery listing of DCS: http://gallery.technet.microsoft.com/site/search?f%5B0%5D.Type=Tag&f%5B0%5D.Value=DSC%20Resource%20Kit%20Wave-5&f%5B0%5D.Text=DSC%20Resource%20Kit%20Wave-5
  3. DSC Resource Kit (All Modules): http://gallery.technet.microsoft.com/DSC-Resource-Kit-All-c449312d
  4. Holiday Gift – Desired State Configuration (DSC) Resource Kit Wave-1: http://blogs.msdn.com/b/powershell/archive/2013/12/26/holiday-gift-desired-state-configuration-dsc-resource-kit-wave-1.aspx
  5. OneGet (v5.0 April Preview): http://blogs.msdn.com/b/powershell/archive/2014/04/04/windows-management-framework-v5-preview.aspx
  6. Getting Started with DSC: http://technet.microsoft.com/en-us/library/dn249912.aspx
  7. PowerShellGet (v5.0 May Preview): http://blogs.msdn.com/b/powershell/archive/2014/05/14/windows-management-framework-5-0-preview-may-2014-is-now-available.aspx
  8. Recent PowerShell Version 5.0 and DCS articles at the PowerShell Team Blog site: http://blogs.msdn.com/b/powershell/ \
  9. Also, recent DCS Articles at PowerShell.org site: http://www.powershellmagazine.com/tag/dsc/
  10. Check out the Microsoft Virtual Academy: http://www.microsoftvirtualacademy.com/Studies/SearchResult.aspx?q=PowerShell

 

Getting to know the MS Cloud technology

Yes! Start learning about MS Cloud, better known as Microsoft Azure. If you got a MSDN subscription then you got to use your free Azure credit which ranges between $150 to $200. There are no excuses. Just try it!

Now, you have the ability to build Windows Server(s) and/or Windows 8.1 desktop. YES!! A virtual machine Windows 8.1 Client. So, you won’t stay behind and keep up with today technologies.

Check out the Microsoft Cloud OS blog site: http://blogs.technet.com/b/privatecloud/

PowerShell is mention All Over the Cloud!!