Parsing text from a Powershell script

There are a lot of ways.

This regular expression match:

'\w*(?=/cn=(\w*\s?)*$)'

e.g.

"/o=ABC Group/ou=First Administrative Group/cn=Configuration/cn=Servers/cn=LAXMBGroup01/cn=Microsoft Private MDB",
"/o=ABC Group/ou=First Administrative Group/cn=Configuration/cn=Servers/cn=LAXMBGroup02/cn=Microsoft Private MDB",
"/o=ABC Group/ou=First Administrative Group/cn=Configuration/cn=Servers/cn=LAXMBGroup03/cn=Microsoft Private MDB" |
  ForEach-Object { $_ -Match '\w*(?=/cn=(\w*\s?)*$)' | Out-Null; $Matches[0] }

Select allOpen in new window

Or this replace:

'^(/(ou?|cn)=[\w\s]*){4}/cn=|/cn=[\w\s]*$'

e.g.

"/o=ABC Group/ou=First Administrative Group/cn=Configuration/cn=Servers/cn=LAXMBGroup01/cn=Microsoft Private MDB",
"/o=ABC Group/ou=First Administrative Group/cn=Configuration/cn=Servers/cn=LAXMBGroup02/cn=Microsoft Private MDB",
"/o=ABC Group/ou=First Administrative Group/cn=Configuration/cn=Servers/cn=LAXMBGroup03/cn=Microsoft Private MDB" |
  ForEach-Object { $_ -Replace '^(/(ou?|cn)=[\w\s]*){4}/cn=|/cn=[\w\s]*$' }

Select allOpen in new window

Or this split:

'/cn='

e.g.

"/o=ABC Group/ou=First Administrative Group/cn=Configuration/cn=Servers/cn=LAXMBGroup01/cn=Microsoft Private MDB",
"/o=ABC Group/ou=First Administrative Group/cn=Configuration/cn=Servers/cn=LAXMBGroup02/cn=Microsoft Private MDB",
"/o=ABC Group/ou=First Administrative Group/cn=Configuration/cn=Servers/cn=LAXMBGroup03/cn=Microsoft Private MDB" |
  ForEach-Object { ($_ -Split '/cn=')[-2] }

Select allOpen in new window

Or... well the list goes on, pick the one you find least upsetting?

Chris

*edit* soostibi's replace is better, I forgot about group matching :) Anyway, like I said, lots of different ways :)

Chris

Any way you guys can explain the parameters on that replace statement? In particular:
".+?/cn=(\w+)"

I have never used those before nad would like to understand them.

. = Matches any character
+ = Repeats the last character as many times as possible
? = Makes the previous expression optional (so .+ is optional here)
/cn= = Literal text (no special meaning in this context)
( ) = Defines a group
\w = Matches a Word character, a - z, 0 - 9 and _

For better, more comprehensive definitions I recommend you head off to http://www.regular-expressions.info/reference.html.

Chris

There are other servers that do not start with LAX.  Will these scripts work for those as well?  Do I have to apply these scripts to a where-object cmdlet?

Yes, it finds any servename without special characters (for example dash).
You just have to put in a ForEach-Object the expression:

yourexpressionthatpriducesthestrings | foreach-object {
$_ -replace ".+?/cn=(\w+)/cn=Microsoft\sPrivate\sMDB",'$1'
}

OK,
So here is my script.
$Date = get-date -uformat "%Y%m%d"
$outFile = "c:\scripts\get-BESAccounts_$date.csv"
$SQLCommand = "SELECT  
       [ServerConfig].[ServiceName]
      ,[UserConfig].[DisplayName]
      ,[userconfig].[MailboxSMTPAddr] as [SMTPAddress]
      ,[SyncDeviceMgmtSummary].[ModelName]
      ,[SyncDeviceMgmtSummary].[PhoneNumber]
      ,[SyncDeviceMgmtSummary].[PlatformVer]
      ,[SyncDeviceMgmtSummary].[IMEI]
        ,[SyncDeviceMgmtSummary].[ICCID]
      ,[SyncDeviceMgmtSummary].[HomeNetwork]
      ,[SyncDeviceMgmtSummary].[AppsVer]
        ,[UserConfig].[PIN]
      ,[ITPolicy2].[PolicyName]
      ,[UserConfig].[ServerDN] as [ExchangeServer]
      ,[UserConfig].[AgentId]
      ,[UserConfig].[RoutingInfo]
      ,[UserStats].[MsgsPending]
      ,[UserStats].[LastFwdTime]
      ,[UserStats].[LastSentTime]
        ,[UserConfig].[CreationTime]
          FROM [BESPOOL].[dbo].[UserConfig]
  INNER JOIN [BESPOOL].[dbo].[UserStats]
    ON [UserConfig].[Id]=[UserStats].[UserConfigId]
  INNER JOIN [BESPOOL].[dbo].[ITPolicy2]
    ON [UserConfig].[ITPolicy2Id]=[ITPolicy2].[Id]
  INNER JOIN [BESPOOL].[dbo].[ServerConfig]
    ON [UserConfig].[ServerConfigId]=[ServerConfig].[Id]
  INNER JOIN [BESPOOL].[dbo].[SyncDeviceMgmtSummary]
    ON [UserConfig].[Id]=[SyncDeviceMgmtSummary].[UserConfigId]"
 
 
$Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString ="server=BESSQL01;database=BESPOOL;trusted_connection=true;"
 
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.CommandType = [System.Data.CommandType]"Text"
$Command.Connection = $Connection
$Command.CommandText = $SQLCommand  
 
$dt_BESUsers = New-Object "System.Data.DataTable"
$Connection.Open()
$Reader = $Command.ExecuteReader()
$dt_BESUsers.Load($Reader)
$Connection.Close()
 
$dt_BESUsers `
|  foreach-object {
$_ -replace ".+?/cn=(\w+)/cn=Microsoft\sPrivate\sMDB",'$1'
}  `
|select displayname, servicename, exchangeserver, modelname, phonenumber,platformver,imei,iccid,appsver,pin `
|sort 'ModelName' #| Export-CSV $OUTFILE -NoTypeInformation

When the script finish running it does not display any text.

The first of mine needs extra treatment for special characters. The second two don't care about the server name, but may need adjustment if the full string contains other special characters. None of them are bothered about the content of the server name beyond that, they will pull any server, not just those beginning LAX.

Chris

Thanks for the quick response Chris.  Yes, it's the exchangeserver field I am trying to manipulate.

Cool, it should work. I used Soostibi's replace, yell if something doesn't work and one or both of us will endeavour to fix it :)

Chris

Thanks for the explanation and link Chris.

Chris,
Thanks for the script modifcation that was the fix.

Soostibi,
Thanks for helping out giving an alternative cmdlet.