Avatar of jahhan
jahhan

asked on 

Parsing text from a Powershell script

I have created a powershell script that displays a column (ServerDN field) that has the following text:
/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

I would like to minimize this column by only displaying LAXMBGroup01, LAXMBGroup01, LAXMBGroup01.
I am thinking the key is to use a parsing command; however, I am not good with reg expressions.  I would really appreciate if someone could help me resolve this issue.
PowershellExchange

Avatar of undefined
Last Comment
jahhan
SOLUTION
Avatar of soostibi
soostibi
Flag of Hungary image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Chris Dent
Chris Dent
Flag of United Kingdom of Great Britain and Northern Ireland image

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] }

Open 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]*$' }

Open 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] }

Open in new window

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

Chris
Avatar of Chris Dent
Chris Dent
Flag of United Kingdom of Great Britain and Northern Ireland image

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

Chris
Avatar of GusGallows
GusGallows
Flag of United States of America image

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.
Avatar of Chris Dent
Chris Dent
Flag of United Kingdom of Great Britain and Northern Ireland image

. = 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
Avatar of jahhan
jahhan

ASKER

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?
Avatar of soostibi
soostibi
Flag of Hungary image

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'
}
Avatar of jahhan
jahhan

ASKER

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.
Avatar of Chris Dent
Chris Dent
Flag of United Kingdom of Great Britain and Northern Ireland image

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
ASKER CERTIFIED SOLUTION
Avatar of Chris Dent
Chris Dent
Flag of United Kingdom of Great Britain and Northern Ireland image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Avatar of jahhan
jahhan

ASKER

Thanks for the quick response Chris.  Yes, it's the exchangeserver field I am trying to manipulate.
Avatar of Chris Dent
Chris Dent
Flag of United Kingdom of Great Britain and Northern Ireland image

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
Avatar of GusGallows
GusGallows
Flag of United States of America image

Thanks for the explanation and link Chris.
Avatar of jahhan
jahhan

ASKER

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

Soostibi,
Thanks for helping out giving an alternative cmdlet.
Exchange
Exchange

Exchange is the server side of a collaborative application product that is part of the Microsoft Server infrastructure. Exchange's major features include email, calendaring, contacts and tasks, support for mobile and web-based access to information, and support for data storage.

213K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo