Posts RSS Comments RSS 253 Posts and 411 Comments till now

Archive for the 'SQL' Category

Find-CitrixUser.ps1 (Citrix Top 10)

This is another script that I can use quite often. It is a simple script that queries all the sessions and returns the ones where the User matches. I made the User a RegEx search so you could do multiple users.

Name: Find-CitrixUser.ps1
Purpose: Finds where the user(s) are and outputs session info

# Find-CitrixUser.ps1
# Brandon Shell [MVP]
# www.bsonposh.com
# Finds where the user(s) are and out puts session info
Param($user=".*",[switch]$help)
function HelpMe{
    Write-Host
    Write-Host " Find-CitrixUser.ps1:" -fore Green
    Write-Host "   Finds where the user(s) are and out puts session info"
    Write-Host
    Write-Host " Parameters:" -fore Green
    Write-Host "   -User                  : Optional. Name of the User or RegEx (Default is all users)"
    Write-Host "   -Help                  : Optional. Displays This"
    Write-Host
    Write-Host " Examples:" -fore Green
    Write-Host "   Finds User TestMe and outputs and returns ServerName,ClientAddress, and SessionID" -fore White
    Write-Host "     .\Find-CitrixUser.ps1 | ft ServerName,ClientAddress,SessionID " -fore Yellow
    Write-Host
    Write-Host "   Finds all Users who start with ‘Sales’ and returns UserName,ServerName, and SessionID"  -fore White
    Write-Host "     .\Find-CitrixUser.ps1 `"^sales`" | ft UserName,ServerName,SessionID " -fore Yellow
    Write-Host
    Write-Host "   To View All properties availiable." -fore White
    Write-Host "     .\Find-CitrixUser.ps1 <username> | Get-Member" -fore Yellow
    Write-Host
}

# Check for the Help or if
if($help){helpme;Write-Host;return}

# Code to Get the Farm and Initialize
$farm = New-Object -com "MetaframeCOM.MetaFrameFarm"
$farm.Initialize(1)

# Get the Sessions and Parse for Users who match
$farm.Sessions | ?{$_.UserName -match $user}

C# to PowerShell Translation Thought Process

A gentleman on the powershell news group the other day was asking about executing SQL stored procedures and I provided him with example that I had posted earlier in my blog (Click Here) While I’m not sure my answer was exactly what he was looking for he was a little curious as to how I went about translating the original C# code. To be clear I am not a coder nor do I truly know C#, but I know enough to translate. Anyway, I decided this would be a good idea for a post

So… Here we GO!

First… I used this as the C# example
http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson07.aspx

I will show you my thought process by section I will include my comments and after I will post Both code Sections

This was fairly simple. In C# you have the ability to take namespace shortcuts by using ‘using ;’ In PowerShell we dont as of yet have that ability so I had to figure out what class SqlConnection was. A MSDN query returned System.Data.SqlClient.SqlConnection. The resulting PowerShell code is

The C# Code:

// Setup
conn = new SqlConnection("Server=(local);DataBase=Northwind;Integrated Security=SSPI");
conn.Open();

The Powershell Code:

$srv = "srv1"
$db = "Northwind"
$conn = new-Object System.Data.SqlClient.SqlConnection("Server=$srv;DataBase=$db;IntegratedSecurity=SSPI")
$conn.Open() | out-null # The out-null is because the method returns a value and I dont want that output

Again… I had to find out what SqlCommand was referencing so… back to MSDN… System.Data.SqlClient.SqlCommand. BTW… I think I should take time now to tell you it is a REALLY GOOD idea to get use to the idea of constructors (how the object should be created) and how to use MSDN to determine the correct way to create an instance of the class/object. It really helps to know what a class is expecting. In this example its good to know the constructor is wanting a string of the SP and A connection OBJECT to use.

The C# Code:

// 1.  create a command object identifying
//     the stored procedure
SqlCommand cmd  = new SqlCommand("CustOrderHist", conn);

Here is the PowerShell Code:

$cmd = new-Object System.Data.SqlClient.SqlCommand("CustOrderHist", $conn)

Here was the tricky part (at least sorta.) From the C# code its not clear if the CommandType.StoredProcedure is a property and it turns out its not. It is an enumeration. It took me a few clicks to figure it out. The first clue was when looking for CommandType… I got an enum and it turns out the valid options was StoredProcedure, TableDirect, or Text (MSDN LINK) Clearly this was an enum, but I was a little unsure how do to enums in powershell. I found this blog by /\/\0\/\/ that helped a lot (/\/\o\/\/ Link)

The C# Code:

// 2. set the command object so it knows
//    to execute a stored procedure
cmd.CommandType = CommandType.StoredProcedure;

I ended up with this Powershell Line:

$cmd.CommandType = [System.Data.CommandType]‘StoredProcedure’

This was fairly simple as well… Just had to drop the new sqlparameter because strongly typing was not required. Again the out-null was because the method returns data I did not want as well as set the parameters.

The C# Code:

// 3. add parameter to command, which
//    will be passed to the stored procedure
cmd.Parameters.Add(new SqlParameter("@CustomerID", custId));

The PowerShell Code:

$cmd.Parameters.Add("@CustomerID","ANATR") | out-Null

These two parts are really just the excution of the the previous code. I think the only difference is the way PowerShell Writes output

The C# Code:

// execute the command
rdr = cmd.ExecuteReader();

// iterate through results, printing each to console
while (rdr.Read())
{
 Console.WriteLine(
 "Product: {0,-35} Total: {1,2}",
 rdr["ProductName"],
 rdr["Total"]);
}

The Powershell Code:

$rdr = $cmd.ExecuteReader()
While($rdr.Read()){
    Write-Host "Product Name: " $rdr[‘ProductName’]
    Write-Host "Total: " $rdr[‘Total’]
}

Here is the Complete PowerShell Code.

$srv = "srv1"
$db = "Northwind"
$conn = new-Object System.Data.SqlClient.SqlConnection("Server=$srv1;DataBase=$db;Integrated Security=SSPI")
$conn.Open() | out-null
$cmd = new-Object System.Data.SqlClient.SqlCommand("CustOrderHist", $conn)
$cmd.CommandType = [System.Data.CommandType]‘StoredProcedure’
$cmd.Parameters.Add("@CustomerID","ANATR") | out-Null
$rdr = $cmd.ExecuteReader()
While($rdr.Read()){
    Write-Host "Product Name: " $rdr[‘ProductName’]
    Write-Host "Total: " $rdr[‘Total’]
}
$conn.Close()
$rdr.Close()

A Powershell Adventure: Chpt2 ‘Using .NET in Powershell’

Using .NET in Powershell (admins view)
————————————————————
One of the best features of POSH is the direct access to the .NET Object model. Unfortunately… this was one of my biggest obstacles. I’m not a developer and most of the concepts of programming are somewhat foreign to me.

When I started learning POSH I didn’t know the difference between Classes, Interfaces, constructs, or members and I didn’t truly understand the concept of objects (from a programmers point of view.) I think these concepts are critical to learning and using .NET with POSH. I also found that knowing a little c# (at least be able to read it) has become incredible helpful.

I would like to try to explain these concepts without getting to developery. As in all the things I post.. I like to explain by examples so I will be providing some code to help me through the process. I will also try (with my limited ability) to give some basic guidance in C# > POSH translation (this is critically helpful as most .NET examples are written in c#.)

Ok… Lets start with Definitions. Again.. if you’re a Developer… Please feel free to comment on anything I may not be exactly right on. These definitions are my opinion on what these concepts entail.

Definitions
===========
.NET: Blackboxed code that accepts specific input and returns either a value or object. Basically, Microsoft did all the coding for you. You just have to call on it correctly… MSDN is invaluable resource for this.

Wiki – http://en.wikipedia.org/wiki/Microsoft_.NET_Framework

Class: Almost everything I reference in .NET is a class. I like to think of a class as a template for an Object. A class is definition of what an object should look like. What properties/methods it should have. For Example… a Microsoft.Win32.RegistryKey object should have Name Property and a GetValue method.

Wiki – http://en.wikipedia.org/wiki/Class_%28computer_science%29

Members: Every class has members. Members are the properties and methods combined. It is a good place to look if you just want to see and overview of what a class has to offer. Check this out.
http://msdn2.microsoft.com/en-us/library/microsoft.win32.registrykey_members.aspx

Properties: Properties are define by a class as attributes of an object. Microsoft.Win32.RegistryKey class has properties of Name, SubkeyCount, and Value Count. So every Microsoft.Win32.RegistryKey object can have those properties.
Wiki- http://en.wikipedia.org/wiki/Property_%28programming%29

Methods: Methods are also defined by the class but instead of an attribute its more like a function of the class. Microsoft.Win32.RegistryKey class has CreateSubKey, DeleteSubkey, SetValue, and so on. So… just like properties every Microsoft.Win32.RegistryKey object can have those methods.

Wiki – http://en.wikipedia.org/wiki/Method_%28computer_science%29

Constructor: I define Contructs as the information or objects required to create an instance of a class. Lets use System.Data.SqlClient.SqlCommand as an example. It has 4 different ways you can create an object from the class. Each way creates and object with slightly different data.
Ref: http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.sqlcommand.aspx

Wiki – http://en.wikipedia.org/wiki/Constructor_%28computer_science%29

Static Methods: These are just like methods, but are availible without having to create an instance of the object. The reason I single these out is because in Powershell… accessing static functions is really simple. All you have to do is to [.NET Class]::StaticMethods(“arguments”). Here is an example [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey.

Now that you know that…

Lets look at some examples of .NET use in POSH.
Lets look at Eventlog access using .NET

[code]PS C:\> $evtLog = new-object system.diagnostics.eventlog
PS C:\> $evtLog.Log = "Application"
PS C:\> $evtLog.Entries
Index Time Type Source EventID Message
----- ---- ---- ------ ------- -------
1350 Nov 19 15:54 Warn Alert Manager Eve... 257 VirusScan Enterprise: Would be blocked by behaviour blocking
1351 Nov 19 15:54 Warn Alert Manager Eve... 257 VirusScan Enterprise: Would be blocked by behaviour blocking[/code]

Lets look at a couple of .NET static method Calls

Now Method of System.DataTime
[code]PS C:\> [system.datetime]::now
Monday, January 08, 2007 7:31:31 PM[/code]

GetLogicalDrives method of System.Environment
[code]PS C:\> [System.Environment]::GetLogicalDrives()
A:\
C:\
D:\
E:\
F:\[/code]

One last one… System.Math. We will use the static method pow. What is 83 to the second power anyway?
[code]PS C:\> [system.math]::pow(83,2)
6889[/code]

C# to Powershell translation
C# Code taking from:
http://www.csharp-station.com:80/Tutorials/AdoDotNet/Lesson07.aspx

[code]
using System;
using System.Data;
using System.Data.SqlClient;
// create and open a connection
objectconn = new SqlConnection("Server=(local);DataBase=Northwind;Integrated Security=SSPI");
conn.Open();
// 1.  create a command object identifying
//     the stored procedure
SqlCommand cmd  = new SqlCommand("CustOrderHist", conn);
// 2. set the command object so it knows
//    to execute a stored procedure
cmd.CommandType = CommandType.StoredProcedure;
// 3. add parameter to command, which
//    will be passed to the stored procedure
cmd.Parameters.Add(new SqlParameter("@CustomerID", custId));
// execute the command
rdr = cmd.ExecuteReader();
// iterate through results, printing each to console
while (rdr.Read()){
   Console.WriteLine(
   "Product: {0,-35} Total: {1,2}",
   rdr["ProductName"],
   rdr["Total"]);
}[/code]

Now in PowerShell:

[code]
$srv = "srv1"
$db = "Northwind"
$conn = new-ObjectSystem.Data.SqlClient.SqlConnection("Server=$srv1;DataBase=$db;IntegratedSecurity=SSPI")
$conn.Open() | out-null
$cmd = new-Object System.Data.SqlClient.SqlCommand("CustOrderHist", $conn)
$cmd.CommandType = [System.Data.CommandType]'StoredProcedure'
$cmd.Parameters.Add("@CustomerID","ANATR") | out-Null
$rdr = $cmd.ExecuteReader()While($rdr.Read()){
    Write-Host "Product Name: " $rdr['ProductName']
}
$conn.Close()
$rdr.Close()[/code]

Ok… Now to explain a couple of differences.

I think the key part of C# to Powershell translation is understanding the Namespace. In Powershell (at least as far as I know) you are unable to include namespaces. Therefore you have Fully Qualify any .Net classes you want to us. This is not the case in C#. In C# you are able to include namespaces. i.e. System.Data.SqlClient. This makes the translation a little more complicated because you have to figure out what namespace the class is from. Lets look at This example.
[code]conn = new SqlConnection(“Server=(local);DataBase=Northwind;Integrated Security=SSPI”);[/code]
How do I know what SqlConnection is? You best option is to google/MSDN it. If that is unavailble you can look that the using statements and see which make sense. In this case it is using System.Data.SqlClient.
So that in PowerShell is
[code]$conn = new-Object
System.Data.SqlClient.SqlConnection(“Server=$srv1;DataBase=$db;Integrated
Security=SSPI”)[/code]

Another important issue is strongly typing. While you can strongly type in Powershell you don’t have to. In my understanding of C# you have to strongly type variables. Not only that but you have instantiate them as well. This illustrated in this line
[code]SqlCommand cmd = new SqlCommand(“CustOrderHist”, conn);[/code]
This line in Powershell is (notice the fully qualified class)
[code]$cmd = new-Object System.Data.SqlClient.SqlCommand(“CustOrderHist”, $conn)[/code]

Summary: As I hope you can see. Using .NET in Powershell is pretty strait forward and actually if you use PowerShell at all… you use .NET regularly, but maybe not as directly. Please feel free to leave comments. Also… please let me know if anything is unclear.