There’s a number of approaches you could take to solve this problem. One solution is to use Table Valued Parameters which I’ve previously blogged about to push a batch of rows to SQL Server and add only new rows. This does require creating both a table type and stored procedure on the SQL Server and only works for SQL Server 2008 and higher:

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
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71

<#
/* FROM SSMS */
USE tempdb
GO
/* Create a Request table for testing purposes*/
CREATE TABLE Request
( PKID INT,
 MessageText varchar(max));
GO
/* Create a RequestList table type */
CREATE TYPE RequestList AS TABLE
( PKID INT,
 MessageText varchar(max));
GO
 
/* Create a procedure to use insert only new rows  */
CREATE PROCEDURE uspSetRequest
    @TVP RequestList READONLY
    AS
    SET NOCOUNT ON
    INSERT Request
    SELECT tvp.PKID, tvp.MessageText
    FROM @TVP tvp
    LEFT JOIN Request r ON
    tvp.PKID = r.PKID
    WHERE r.PKID IS NULL;
 GO
 #>
 
#FROM Powershell
#Create an ADO.NET DataTable matching the RequestList Table Type:
$dt = new-object Data.datatable
$col =  new-object Data.DataColumn
$col.ColumnName = 'PKID'
$col.DataType = [Int32]
$dt.Columns.Add($Col)
$col =  new-object Data.DataColumn
$col.ColumnName = 'MessageText'
$col.DataType = [String]
$dt.Columns.Add($Col)
 
#BEGIN INSERT foreach Loops to add records to DataTable
#Example below inserts only one record
#Add a Row to the DataTable
$dr = $dt.NewRow()
$dr.Item('PKID') = 1
$dr.Item('MessageText') = 'It worked!'
$dt.Rows.Add($dr)
#END INSERT foreach Loops to add records to DataTable 
 
#Connection and Query Info
$serverName="$env:computername\sql1"
$databaseName='tempdb'
$query='uspSetRequest' 
 
#Connect
$connString = "Server=$serverName;Database=$databaseName;Integrated Security=SSPI;"
$conn = new-object System.Data.SqlClient.SqlConnection $connString
$conn.Open()
 
#Create Sqlcommand type and params
$cmd = new-object System.Data.SqlClient.SqlCommand
$cmd.Connection = $conn
$cmd.CommandType = [System.Data.CommandType]"StoredProcedure"
$cmd.CommandText= $query
$null = $cmd.Parameters.Add("@TVP", [System.Data.SqlDbType]::Structured)
$cmd.Parameters["@TVP"].Value = $dt
 
#Execute Query and close connection
$cmd.ExecuteNonQuery() | out-null
$conn.Close()

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
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78

add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
add-type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
add-type -AssemblyName "Microsoft.SqlServer.SMOExtended, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
add-type -AssemblyName "Microsoft.SqlServer.SqlEnum, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
add-type -AssemblyName "Microsoft.SqlServer.Management.Sdk.Sfc, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
 
#######################
<#
.SYNOPSIS
Backs up a database object definition.
.DESCRIPTION
The Backup-DatabaseObject function  backs up a database object definition by scripting out the object to a .sql text file.
.EXAMPLE
Backup-DatabaseObject -ServerInstance Z002 -Database AdventureWorks -Schema HumanResources -Name vEmployee -Path "C:\Users\Public"
This command backups up the vEmployee view to a .sql file.
.NOTES
Version History
v1.0   - Chad Miller - Initial release
#>
function Backup-DatabaseObject
{
    [CmdletBinding()]
    param(
    [Parameter(Mandatory=$true)]
    [ValidateNotNullorEmpty()]
    [string]$ServerInstance,
    [Parameter(Mandatory=$true)]
    [ValidateNotNullorEmpty()]
    [string]$Database,
    [Parameter(Mandatory=$true)]
    [ValidateNotNullorEmpty()]
    [string]$Schema,
    #Database Object Name
    [Parameter(Mandatory=$true)]
    [ValidateNotNullorEmpty()]
    [string]$Name,
    [Parameter(Mandatory=$true)]
    [ValidateNotNullorEmpty()]
    [string]$Path
    )
 
    $server = new-object Microsoft.SqlServer.Management.Smo.Server($ServerInstance)
    $db = $server.Databases[$Database]
 
    #Create a UrnCollection. URNs are used by SMO as unique identifiers of objects. You can think of URN like primary keys
    #The URN format is similar to XPath
    $urns = new-object Microsoft.SqlServer.Management.Smo.UrnCollection
 
    #Get a list of database object which match the schema and object name specified
    #New up an URN object and add the URN to the urns collection
    $db.enumobjects() | where {$_.schema -eq $Schema -and  $_.name -eq $Name } |
        foreach {$urn = new-object Microsoft.SqlServer.Management.Sdk.Sfc.Urn($_.Urn);
                 $urns.Add($urn) }
 
    if ($urns.Count -gt 0) {
 
        #Create a scripter object with a connection to the server object created above
        $scripter = new-object Microsoft.SqlServer.Management.Smo.Scripter($server)
 
        #Set some scripting option properties
        $scripter.options.ScriptBatchTerminator = $true
        $scripter.options.FileName = "$Path\BEFORE_$Schema.$Name.sql"
        $scripter.options.ToFileOnly = $true
        $scripter.options.Permissions = $true
        $scripter.options.DriAll = $true
        $scripter.options.Triggers = $true
        $scripter.options.Indexes = $true
        $scripter.Options.IncludeHeaders = $true
 
        #Script the collection of URNs
        $scripter.Script($urns)
 
    }
    else {
        write-warning "Object $Schema.$Name Not Found!"
    }
 
} #Backup-DatabaseObject

And here’s example of sourcing and calling the function:

1
2

. ./Backup-DatabaseObject.ps1
Backup-DatabaseObject -ServerInstance Z002 -Database AdventureWorks -Schema HumanResources -Name vEmployee -Path "C:\Users\Public"

I’ve posted the code on PoshCode also.

Writing to the Event Log

First a quick review of how  SqlProxy module writes messages to the event log. This is important because as we’ll see in a moment, how the message is constructed helps in extracting Event log data. In the SqlProxy module I use a standard template in each function for logging messages to the Event log:

$PSUserName = $PSSenderInfo.UserInfo.Identity.Name
$logmessage =  "PSUserName=$PSUserName" + $($psBoundParameters.GetEnumerator() | %{"`n$($_.Key)=$($_.Value)"})
write-sqlproxylog -eventID $eventID."$($myinvocation.mycommand.name)" -message $logmessage

The message is constructed using several built-in variables written as key/value pairs.

1. The $PSSenderInfo variable is available inside of remote session and returns information about the user who started the PSSession. Since I’m using runas credentials I’ll grab the name of the person who is connected.
2. $psBoundParameters contains a hashtable of the parameters and their values for the current function.
3. This code may look a little odd, $eventID.”$($myinvocation.mycommand.name)”. I created hashtable called $eventid in the SqlProxy module to translate a function name from a an EventId. Since $myinvocation.mycommand.name returns the function name I’ll use this as the hashtable key as shown below:

$EventID = @{
"Add-SqlDatabaseRoleMember"=0
"Add-SqlLogin"=1
"Add-SqlServerRoleMember"=2
"Add-SqlUser"=3
"Remove-SqlDatabaseRoleMember"=4
"Remove-SqlLogin"=5
"Remove-SqlServerRoleMember"=6
"Remove-SqlUser"=7
"Rename-SqlLogin"=8
"Set-SqlLogin"=9
"Set-SqlLoginDefaultDatabase"=10
}

The write-sqlproxlog function is just a wrapper around write-eventlog as follows:

function Write-SqlProxyLog
{
    param(
    [Parameter(Position=0, Mandatory=$true)] $EventID,
    [Parameter(Position=1, Mandatory=$true)] $Message,
    [Parameter(Position=2, Mandatory=$false)] $EntryType='SuccessAudit'
    )
 
    write-eventlog -logname SqlProxy -source SqlProxy -eventID $eventID -message $message -EntryType $EntryType
 
} #Write-SqlProxyLog

A typical event log entry will look like this:

Extracting Data from the Eventlog

In order to load the Eventlog data into a SQL Server table I created a module called SqlTools which is collection of functions I use frequently for querying and loading data. I’ve posted the module here.

The initial load script as shown below makes use of SqlTools module:

import-module SqlTools
 
$ComputerName = 'Z001'
$ServerInstance = 'Z002\sql1'
$Database = 'SqlProxy'
 
$dt = Get-EventLog -LogName SqlProxy -ComputerName $ComputerName -EntryType 'SuccessAudit' | % { $ht = ($_.Message -replace "\\","/") |
    ConvertFrom-StringData; $xml = new-object psobject -Property $ht | ConvertTo-Xml -NoTypeInformation -As String;
    new-object psobject -Property @{'Index' = $_.Index; 'TimeGenerated'=$_.TimeGenerated;
    'EventId'=$_.EventId; 'MessageXml'=$xml} } | Out-DataTable
 
Add-SqlTable -ServerInstance $ServerInstance -Database $Database -TableName 'SqlProxyLog' -DataTable $dt -AsScript | clip

At this point I’ll paste the T-SQL script into SSMS, modify column data types, null/not null, add primary key and finally create the table:

CREATE TABLE [dbo].[SqlProxyLog](
	[EventId] [int] NOT NULL,
	[TimeGenerated] [datetime] NOT NULL,
	[MessageXml] [xml] NOT NULL,
	[Index] [int] NOT NULL,
 CONSTRAINT [PK_SqlProxyLog] PRIMARY KEY CLUSTERED
(
	[TimeGenerated] ASC,
	[Index] ASC
)
)

Then I’ll return to Powershell to execute the write-datatable function:

Write-DataTable -ServerInstance $ServerInstance -Database $Database -TableName 'SqlProxyLog' -Data $dt

In order to incrementally load only new events, I’ll modify the get-sqlproxylog.ps1 to first grab the max timegenerated or 1900-01-01 if its null and then use value for the -After param of the Get-Eventlog cmdlet:

import-module SqlTools
 
$ComputerName = 'Z001'
$ServerInstance = 'Z002\sql1'
$Database = 'SqlProxy'
 
$query = "SELECT ISNULL(MAX(TimeGenerated),'1900-01-01') AS TimeGenerated FROM dbo.SqlProxyLog"
$maxDtm = invoke-sqlcmd2 -ServerInstance $ServerInstance -Database $Database -Query $query | select -ExpandProperty TimeGenerated
 
$dt = Get-EventLog -LogName SqlProxy -ComputerName $ComputerName -EntryType 'SuccessAudit' -After $maxDtm | % { $ht = ($_.Message -replace "\\","/") |
    ConvertFrom-StringData; $xml = new-object psobject -Property $ht | ConvertTo-Xml -NoTypeInformation -As String;
    new-object psobject -Property @{'Index' = $_.Index; 'TimeGenerated'=$_.TimeGenerated;
    'EventId'=$_.EventId; 'MessageXml'=$xml} } | Out-DataTable
 
if ($dt) {
    Write-DataTable -ServerInstance $ServerInstance -Database $Database -TableName 'SqlProxyLog' -Data $dt
}

Some interesting points about this script:

• Since the message data is stored as key/value pairs, the built-in ConvertFrom-StringData cmdlet is used to create the hashtable $ht
• The hashtable is then used to create a psobject
• The psobject is converted into XML using ConverTo-Xml.

One minor issue I ran into is with unenclosed backslashes. If backslashes are enclosed in quotes its fine, if not it causes an error with ConvertFrom-StringData, so I replace them with forward slashes.

Data Is Loaded, Now What?

After I’ve loaded the data I’ll use XQuery to shred the message XML column into a relational data set. I created a function and view in SQL Server for this purpose:

CREATE FUNCTION [dbo].[ufn_GetEventMessage] (@MessageXml XML)
RETURNS @Message TABLE
(
	 ChangeOrder VARCHAR(128) NULL
	,dbname  VARCHAR(128) NULL
	,PSUserName   VARCHAR(255) NULL
	,name   VARCHAR(128) NULL
	,rolename   VARCHAR(128) NULL
	,[Description]   VARCHAR(128) NULL
	,sqlserver   VARCHAR(128) NULL
	,loginame   VARCHAR(128) NULL
	,[login]   VARCHAR(128) NULL
	,DefaultDatabase VARCHAR(128) NULL
)
AS
BEGIN
	INSERT @Message (ChangeOrder,dbname,PSUserName,name,rolename,Description,sqlserver,loginame,[login],DefaultDatabase)
	SELECT
	 Objects.Object.query('Property[@Name="ChangeOrder"]').value('.', 'varchar(128)') AS ChangeOrder
	,Objects.Object.query('Property[@Name="dbname"]').value('.', 'varchar(128)') AS dbname
	,Objects.Object.query('Property[@Name="PSUserName"]').value('.', 'varchar(128)') AS PSUserName
	,Objects.Object.query('Property[@Name="name"]').value('.', 'varchar(128)') AS name
	,Objects.Object.query('Property[@Name="rolename"]').value('.', 'varchar(128)') AS rolename
	,Objects.Object.query('Property[@Name="Description"]').value('.', 'varchar(128)') AS [Description]
	,Objects.Object.query('Property[@Name="sqlserver"]').value('.', 'varchar(128)') AS sqlserver
	,Objects.Object.query('Property[@Name="loginame"]').value('.', 'varchar(128)') AS loginame
	,Objects.Object.query('Property[@Name="login"]').value('.', 'varchar(128)') AS [login]
	,Objects.Object.query('Property[@Name="DefaultDatabase"]').value('.', 'varchar(128)') AS DefaultDatabase
	FROM @MessageXml.nodes('/Objects/Object') AS Objects(Object)
RETURN
END;
GO
 
CREATE VIEW [dbo].[vw_SqlProxyLog]
AS
SELECT l.*, m.*
FROM dbo.SqlProxyLog l
CROSS APPLY dbo.ufn_GetEventMessage(l.MessageXml) m
GO

The function requires I define all the possible attributes as columns. Here’s an example of parsing a typical message:

DECLARE @MessageXml XML
SET @MessageXml =
'<Objects><Object><Property Name="DefaultDatabase">master</Property>
<Property Name="PasswordPolicyEnforced">False</Property>
<Property Name="Description" />
<Property Name="sqlserver">[Z002/SQL1]</Property>
<Property Name="password">System.Security.SecureString</Property>
<Property Name="name">test3</Property>
<Property Name="PasswordExpirationEnabled">False</Property>
<Property Name="ChangeOrder">1234</Property>
<Property Name="logintype">SqlLogin</Property>
<Property Name="PSUserName">Z002/u00</Property>
</Object></Objects>'
 
SELECT * FROM dbo.ufn_GetEventMessage(@MessageXMl)

Finally I’ll add reference table for the SqlProxyEventType:

CREATE TABLE [dbo].[SqlProxyEventType](
	[EventName] [varchar](50) NOT NULL,
	[EventId] [smallint] NOT NULL,
 CONSTRAINT [PK_SqlProxyEventType] PRIMARY KEY CLUSTERED
(
	[EventId] ASC
)
);
 
INSERT [dbo].[SqlProxyEventType] ([EventName], [EventId]) VALUES (N'Add-SqlDatabaseRoleMember', 0)
INSERT [dbo].[SqlProxyEventType] ([EventName], [EventId]) VALUES (N'Add-SqlLogin', 1)
INSERT [dbo].[SqlProxyEventType] ([EventName], [EventId]) VALUES (N'Add-SqlServerRoleMember', 2)
INSERT [dbo].[SqlProxyEventType] ([EventName], [EventId]) VALUES (N'Add-SqlUser', 3)
INSERT [dbo].[SqlProxyEventType] ([EventName], [EventId]) VALUES (N'Remove-SqlDatabaseRoleMember', 4)
INSERT [dbo].[SqlProxyEventType] ([EventName], [EventId]) VALUES (N'Remove-SqlLogin', 5)
INSERT [dbo].[SqlProxyEventType] ([EventName], [EventId]) VALUES (N'Remove-SqlServerRoleMember', 6)
INSERT [dbo].[SqlProxyEventType] ([EventName], [EventId]) VALUES (N'Remove-SqlUser', 7)
INSERT [dbo].[SqlProxyEventType] ([EventName], [EventId]) VALUES (N'Rename-SqlLogin', 8)
INSERT [dbo].[SqlProxyEventType] ([EventName], [EventId]) VALUES (N'Set-SqlLogin', 9)
INSERT [dbo].[SqlProxyEventType] ([EventName], [EventId]) VALUES (N'Set-SqlLoginDefaultDatabase', 10)

The last thing I’ll do is setup a schedule SQL Agent job to run the get-sqlproxylog.ps1 script:

–Setup SQL Job with CmdExec Job Step:

–C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -NoProfile -Command c:\scripts\get-sqlproxylog.ps1

Summary

This post demonstrated extracting information from a specially crafted message in an event log and loading the data into a SQL Server table. Its kind of interesting to think about how easy it is to transform data from one thing to the next using Powershell. In this case the data was converted six times:

1. To an Event log message data stored as key/value pairs using Write-Eventlog/Get-Eventlog
2. To a hashtable using ConvertFrom-StringData
3. To a psobject using New-Object -property hashtable
4. To a DataTable using Out-DataTable
5. Into a SQL Server table using Write-DataTable
6. Out of a SQL Server table XML column to relational data set using XQuery

That said, next time I should probably just write the damn thing directly to a database to begin with.

Get SQL Server SPNs from Active Directory

Before Powershell I would use the command-line utility setspn.exe to retrieve a list of SPNs for a given account. The syntax is:

setspn -L AccountName

Although this works reasonably well there are a couple of issues first the  output is text instead of objects which means I’d have to do a lot parsing and if you find you’re parsing text in Powershell too much there’s a high likelihood you’re doing it the hard way!. The second issue with setspn -L is that it expects an account and doesn’t retrieve ALL SPNs for a given service. For these reasons I created a script called Get-SqlSpn which will query Active Directory for all SQL Server SPNs. Querying AD for SPNs still requires a bit of parsing (I’ve taken care of this for you in the Get-SqlSpn function), but not near as much as starting from setspn. Let’s look at how I use Get-SqlSpn…

Using Get-SqlSpn

1. Download Get-SqlSpn
2. Source the function

. ./Get-Sqlspn.ps1

#Get the SQL Server SPNs

 $spns = Get-SqlSpn

If I’m interactively exploring data I like to use a tool for that specific purpose which for me is either loading the data into SQL Server or using Excel. For this particular I think Excel is a better fit. There is some tidying up and normalizing data that needs to be done which is really easy with Excel. Although you could mess with additional scripts to export data directly into Excel or or convert CSV files–a quick and dirty way to get data into Excel is simply copy and paste from out-gridview:
3. Open a new Excel document and rename a worksheet servers
4. Get Column Headers

$object = $spns | select -first 1
$ht = @{}
foreach($property in $object.PsObject.get_properties()) {
  $ht.add($property.Name.ToString(),$property.Name.ToString())
}

5. Copy/Paste heading row to Excel (Ctrl-A, Ctrl-C)

new-object psobject -Property $ht | out-gridview

6. Copy/Paste spns to Excel (Ctrl-A, Ctrl-C)

$spns | out-gridview

Now that you have a list of all SQL Server SPNs from AD, let’s grab some information from all of our SQL Servers to match against. For this purpose I created a script called Get-SqlWmi..

Using Get-SqlWmi

1. Download Get-SqlWmi and Invoke-Sqlcmd2
2. Source the functions

. .\invoke-sqlcmd2.ps1
. .\get-sqlwmi.ps1

I use a  query against System Center Configuration Manager database to pull in a list of SQL Servers. A second approach, if you maintain a SQL Server Center Management Server (CMS)  is to query sysmanagement tables. I’ll then pipe the list of servers to the get-sqlwmi function:

$servers = invoke-sqlcmd2 -ServerInstance 'smsserver\sql10' -Database 'dbautility' -query "select server_name AS 'ComputerName' from sms_sql_server_name_vw" | get-sqlwmi

3. Rename Excel worksheet Sheet2 to servers
4. Get Column Headers

$object = $servers | select -first 1
$ht = @{}
foreach($property in $object.PsObject.get_properties()) {
   $ht.add($property.Name.ToString(),$property.Name.ToString())
}

5. Copy/Paste heading row into Excel

new-object psobject -Property $ht | out-gridview

6. Copy/Paste servers to Excel

$servers | out-gridview

Now that I collected the data, I start analyzing…

Analyzing SPNs

Note: The steps which follow  involve using some basic data analysis functions in Excel. If you’re not sure how to filter, define named ranges or use Excel functions you may brush up on Excel. I’ve also included an Excel document with the appropriate fields and formulas for download here.

First I’ll look for invalid SPNs. I’ve seen issues where people will manually create SPNs incorrectly. The correct format is:

MSSQLSvc/<SQL Server computer name>:1433 AccountName

MSSQLSvc/<SQL Server FQDN>:1433  AccountName

Strangely enough I’ve seen semi-colons or commas instead of colons used which simply doesn’t work. The easiest way to find these SPNs is to use the Text Filter using contains ; or ,

The next thing I’ll do is normalize the data. Get-SqlSpn lists the accounts without the domain prefix, while Get-SqlWmi does so I’ll do a global replace to remove the domain slash.

In order to define a unique key, I’ll create a column which combines the SPN and service account name:

= B6 & ” ”  &C6

I’ll name the column searchSPN, this will allow me to do matching against the server list as we’ll see in a moment.

On the servers worksheet I’ll create two new columns of spn and combined as follows:

= “MSSQLSvc/” & D2 & “.contoso.com:” &A2

= E2 & ” ” &C2

I’ll then name the combine column (unique key) searchServer

Next I’ll add a column called matched to each worksheet and use the MATCH function to look for well, matches between SPN and Server lists:

=MATCH(E2,searchServer,0)

=MATCH(F2,searchSpn,0)

If there’s a match the match column will list the row number of corresponding match and if not you’ll see #NA. You can then filter on #NA and try to figure out why there are mismatches. Did I miss a server in inventory? Are SPNs defined for servers no longer on the network, etc.

And finally I’ll add one more column of a setspn command to delete or add SPNs using a Excel formula:

=”setspn -D ” & E2

=”setspn -A ” & F2

It may seem a little odd to build up commands in this way rather than using Powershell script, but I’m really paranoid about SPNs and want to verify each action. If you’re not careful you can cause some authentication problems by deleting needed SPNs or adding duplicates.

Note: a quick way to find duplicate SPNs is to use the command-line setspn -T * -X. You can also use  Excel’s conditional formatting to quickly identify duplicate values as described in here.  I’ve seen duplicate SPNs cause SQL Server to fallback to NTLM instead of using Kerberos. Searching for duplicate SPNs should be part of  your reconciliation. To fix simply remove the duplicate SPN.

Summary

This post demonstrated how to reconcile SQL Server SPNs between Active Directory and the SQL Server services. I think its  important to point out that you don’t need to take a 100% Powershell approach to a problem. In this example I could have used various Powershell cmdlets (compare-object, where-object, group-object, etc.), however for moderate to complex data analysis I prefer to use tools suited to task of data analysis either Excel or SQL Server. I’ll still use Powershell to gather the data and for simple data analysis tasks.

Well, there’s documented way of sharing profiles on the same machine where you create an All Users profile. As stated in help about_Profiles Powershell will look in the following locations for profiles:

        Name                               Description
        -----------                        -----------
        $Profile                           Current User,Current Host
        $Profile.CurrentUserCurrentHost    Current User,Current Host
        $Profile.CurrentUserAllHosts       Current User,All Hosts
        $Profile.AllUsersCurrentHost       All Users, Current Host
        $Profile.AllUsersAllHosts          All Users, All Hosts

The All Users profile creation is a little odd in that you would need to create your profiles in the $pshome directory instead of the $home directory. The $pshome directory is the location where powershell.exe is installed i.e. C:\Windows\System32\WindowsPowerShell\v1.0 while $home is under <user>\Documents\WindowsPowershell.

I don’t like messing with storing my shared profiles in a system directory, so instead I use a different technique…

Enter Symlinks

I’ve previously blogged about Symlinks, but I didn’t mention I use them for sharing profiles. Note: This  requires Vista, Windows 7  or 2008 or higher OS. So here’s the steps to share profiles using symlinks:

1. Create A WindowsPowershell folder or copy your existing WindowsPowershell to a shared location. I use C:\Users\Public\Documents.
2. Make sure your accounts don’t have a WindowsPowershell folder under <user>\Documents already.
3. Start a classic Window command prompt as Administrator (that’s right don’t use Powershell to create symlinks)
4. Change directories to the Documents folder for account you want to share profiles
5. Run the following command

1
2

c:\Users\u00\Documents>mklink /D WindowsPowerShell C:\users\Public\Documents\WindowsPowerShell
symbolic link created for WindowsPowerShell <<===>> C:\users\Public\Documents\WindowsPowerShell

Repeat steps 4 and 5 for each account.

You now can now share profiles and modules across accounts on the same computer.

IT Pro Camps are focused on serving the needs of  IT Pro’s (Windows system administrators). IT Pro’s haven’t had many events like our developer counterpart’s code camps and this is why I’m excited to be a part of IT Pro Camp. There’s a definite need for events which serve the IT Pro community. We’ve done Tampa, South Florida, Orlando and in two weeks we’ll add Sarasota to our growing list of cities. We’re also planning on Jacksonville, Pensacola and for the second year we’ll be returning to Tampa and South Florida. We hope to have dates for the remainder of 2012 finalized by the end of March. I’ll be sure to post an update once we work out scheduling and venues.

The Sarasota IT Pro Camp will feature 24 one-hour sessions on topics covering Powershell, BI, SQL Server, Cloud, Active Directory, System Center and Data Security. I’ll be presenting on Storing Powershell Output. Although there are many good sessions to choose from, I’d like to highlight a few sessions which peaked my interest:

We”ll have three sessions, on a topic we haven’t had at previous camps, Data Security.

Adam Malone – Cyber Crime and the FBI

Joseph Schorr – Rule 1: Cardio

Jeff Wolach - Introduction to Next Generation Firewalls

I think this this is one of the interesting things about having IT Pro Camps in different cities, there tends to be a strong technical community around particular disciplines and for Sarasota I’d say its Data Security.

Jose Chinchilla  (blog|twitter) is doing a two-part session on Business Intelligence.

Jose Chinchilla - Introduction to Microsoft Business Intelligence

Jose Chinchilla - Taking Business Intelligence to the next level with SharePoint 2010!

This is a good opportunity for attendees to get both an introduction and more advanced overview on business intelligence. Two-part series are kind of unique to community events and you tend not to see a two-part series at paid conferences.

Be sure to register to attend the free Sarasota IT Pro Camp event. A continental breakfast and lunch will be provided. Please tell your colleagues about IT Pro Camps. I look forward to seeing you there!

–Chad Miller

• Intro to SQL Server for IT Professionals by Michael Wells (Blog|Twitter)—An excellent overview of SQL Server for the system admin SQL noob.
• Version Control for IT Professionals by Jason Hofferle (Blog|Twitter) – A good overview of using Mercurial source control for sys admin scripts.
• Introduction to PowerShell Remoting also by Jason Hofferle (Blog|Twitter)  —An eye opening look at the benefits of using Powershell remoting in a large enterprise based on real-world benchmarks.

If you can’t make Orlando, the Sarasota IT Pro Camp will be held on Saturday, February 18th. 2012. See the IT Pro Camp site for more information.

• T-SQL BULK INSERT command
• LogParser command-line
• LogParser COM-based scripting
• A Windows Powershell-based approach using several functions