SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Split escaped characters


Split escaped characters

Author
Message
schleep
schleep
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1076 Visits: 1328
My network admins, in their wisdom, created admin accounts for us developers for those time when we simply must have some privileges.

As you can see, my CN appears to be based on my DisplayName (Admin-LastName, Paul) rather than a concatenation of my FirstName and sn (Paul and Admin-LastName, respectively).

CN=Admin-Lastame\, Paul,OU=Users,OU=Data_Admins,OU=Admins,DC=....

My problem is that I need to split the CN based on comma, and don't know how to get -split to ignore the escaped comma after Admin-LastName.

I was not able to find, among the myriad examples in the split docs on Technet, this kind of case.

How do I do this? Do I need to replace the escape?

For that matter, is there a better way of getting to the endpoint, which is, I need a list of all AD groups and their members in a FirstName.LastName format, not using QAD cmdlets?


TIA.



Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15525 Visits: 14396
I do not do much AD work these days, let alone from PowerShell, but I do know of the AD cmdlets MS published. http://technet.microsoft.com/en-us/library/ee617195

Here is what I got (using replacement) with some basic PS in case you proceed down the current path. I could not find a slick split option or similar built into PS:

$str = 'CN=Admin-Lastame\, Paul,OU=Users,OU=Data_Admins,OU=Admins'
#$str
[string[]]$a = $str.Replace('\,', '~~~').Split(',')
#$a
#$a.Count

for ($i=0; $i -lt $a.Count; $i++)
{
$a[$i] = $a[$i].Replace('~~~',',')
}

$a



__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
bruce 1565
bruce 1565
SSC-Enthusiastic
SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)

Group: General Forum Members
Points: 197 Visits: 1022
Here's a regex that might work for you.
$cn = 'CN=Admin-Lastame\, Paul,OU=Users,OU=Data_Admins,OU=Admins,DC=....'
$pat = 'CN=\w+\-(\w+)\\,\s+(\w+),.*$'
[regex]::Replace($cn, $pat, '$2 $1')


It returns
Paul Lastame



Edit: just reread your op. rewritten for just firstname lastname

Edit2: I knew that original regex wouldn't handle two word last names like St. James or hyphenated names which was why I said "might work". Anyway while playing golf with the regex I modified it to handle hyphens and two word last names. If you don't have those kind of names the original works. Replace with the pattern below if you do.
$pat = 'CN=Admin-(\w.+)\\, (\w+),.*$'


Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90417 Visits: 41147
Never mind...

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
schleep
schleep
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1076 Visits: 1328
@opc.three: thanks. I can't wait for the time when we move on from XP :-), and I can begin using those AD cmdlets.

@Bruce: thanks for that. I can see I''m finally going to have to learn regex.



Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90417 Visits: 41147
With that thought in mind, let me ask... do you really need to do this in PowerShell or is the ultimate target of your efforts to have something stored in T-SQL???

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
schleep
schleep
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1076 Visits: 1328
We grant SQL Server / DB access to Windows groups.

We determine which version of the app (read "Server"), which projects (read "databases") users can see, as well as what functionality is enabled, based on group membership.

Ultimate goal is to have a table containing AD Group Name and samaccountname.

Currently doing this in PS because the custom system stored proc I'd been using in SQL2K is not allowed in SQL 2K8.

The table needs to be refreshed every 15 minutes or so, as users are added/shuffled around.
We have ~800 groups.

Takes 19 seconds in PS + 3 to import to SQL Server, 50 seconds in T-SQL using xp_logininfo and xp_enumgroups and a cursor.

Also, xp_logininfo only returns results for groups granted server access.



Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15525 Visits: 14396
schleep (7/10/2012)
@opc.three: thanks. I can't wait for the time when we move on from XP :-), and I can begin using those AD cmdlets.

Bummer. I am in the same boat at the current shop on the desktop (I wanted to give them a quick spin). Seeing your setup however I am surprised you do not have an app server you could use for this. Granted, you would need one to develop on before releasing it into the wild, but sometimes with these types of system admin tasks that's a good thing.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
schleep
schleep
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1076 Visits: 1328
The edict came from on high a couple of years ago: NO NEW SERVERS! (except when a prod box dies).



Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15525 Visits: 14396
Booo :-P

You could explore the possibility of using the command line tool dsquery which is available on XP. I have used it before, but never for automation. However with PS it may be a cinch to use stdout coming from it.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search