AD Query - Multiple user groups

Hello,
I have the following OPENQUERY to fetch employee records from AD. However, I want to get the data from several of such groups which begins with "SG_GR%". Is there a way I can use contains or like syntax?

Please advice...!

select *  FROM OPENROWSET('ADSDSOObject',
 'adsdatasource;', 'SELECT  cn, mail, distinguishedName, displayName
 FROM ''LDAP://DomainController/ou=OrgUnit,DC=domain,DC=com'' 
 WHERE objectClass = ''user'' 
 AND memberof=''CN = "SG_GRGROUP1",OU=OrgUnit2,OU=OrgUnit3,DC=domain,dc=com'' 
 OR memberof=''CN = "SG_GRGROUP2",OU=OrgUnit2,OU=OrgUnit3,DC=domain,dc=com'' 
 OR memberof=''CN = "SG_GRGROUP3",OU=OrgUnit2,OU=OrgUnit3,DC=domain,dc=com'' 
 ')

Open in new window

Evan FiddlerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Will SzymkowskiSenior Solution ArchitectCommented:
Is it possible to use a powershell script?

Will.
Evan FiddlerAuthor Commented:
Unfortunately, I can't!
Deepak ChauhanSQL Server DBACommented:
Try this

select *  FROM OPENROWSET('ADSDSOObject',
 'adsdatasource;', 'SELECT  cn, mail, distinguishedName, displayName
 FROM ''LDAP://DomainController/ou=OrgUnit,DC=domain,DC=com''
 WHERE objectClass = ''user''
 AND memberof like ''SG_GR%''
 ')
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Evan FiddlerAuthor Commented:
Doesn't work.
Deepak ChauhanSQL Server DBACommented:
I can not test these queries because of no AD access. But can try to help you with this.

 SELECT * FROM OpenQuery(ADSI,
'SELECT sAMAccountName, cn, ADsPath FROM ''LDAP://my.domain.com/DC=domain,DC=com''  
where objectClass = ''user'' and CN=''SG_GR%'' ')

or

select * into #t FROM OPENROWSET('ADSDSOObject',
 'adsdatasource;', 'SELECT  cn, mail, distinguishedName, displayName
 FROM ''LDAP://DomainController/ou=OrgUnit,DC=domain,DC=com''
 WHERE objectClass = ''user''
 AND OU=OrgUnit2,OU=OrgUnit3,DC=domain,dc=com''
 ')
 
 select * from #t where cn like 'SG_GRP%'
Evan FiddlerAuthor Commented:
No luck.
Kash2nd Line EngineerCommented:
can't you create an array variable which holds the value of groups for you and then let the query search that variable.
Evan FiddlerAuthor Commented:
If it is doable.
However, my concern with that: there could be groups which will be added later and I do not want to maintain it manually. The group names will also precede SG_GRthenGroupName.

Thoughts?
AmitIT ArchitectCommented:
Chris DentPowerShell DeveloperCommented:
memberOf does not support wildcard queries (the same applies to any field which contains a distinguishedName).

I see you have two options:

Hard-code the list of groups and accept the burden of updating that list.
Perform an initial query which gets the list of groups using the wildcard (for example, filtering on group name). Then dynamically build the second query from the list returned by the first. No idea how to do that in SQL I'm afraid.

Chris
Evan FiddlerAuthor Commented:
Chris,

Thanks for your response. I was inclining towards the second method. Do you think the following query will fetch list of all user groups from AD?
select *  FROM OPENROWSET('ADSDSOObject',
 'adsdatasource;', 'SELECT  cn, mail, distinguishedName, displayName
 FROM ''LDAP://DomainController/ou=OrgUnit,DC=domain,DC=com'' 
 WHERE objectClass = ''groups'' 
 ')

Open in new window

Chris DentPowerShell DeveloperCommented:
Make that objectClass = ''group'' (singular instead of plural) and that should work. I suggest you implement your filter on the group name in there too.

Something along these lines:
select *  FROM OPENROWSET('ADSDSOObject',
 'adsdatasource;', 'SELECT  name, cn, mail, distinguishedName, displayName
 FROM ''LDAP://DomainController/ou=OrgUnit,DC=domain,DC=com'' 
 WHERE objectClass = ''group'' AND name LIKE ''SG_GR%''
 ')

Open in new window

I've added the name attribute into the property select too, that's the most likely thing you'll be filtering on. displayName may not be set at all depending on how the group was created (AD Users and Computers hides it to an extent, Exchange makes it more easily available).

If you can build the query in your very first post using the resultant list you should be fine. You only really need the distinguishedName property back from AD for the groups.

Chris

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Evan FiddlerAuthor Commented:
Chris,

I had to modify my query to pull group names, since the groups I'm looking for are in a different OU. Updated the OU and was able to return all the group names.

Now I can store these group names in a #temp table and do a join between the two results?
Chris DentPowerShell DeveloperCommented:
I would have thought so, yes.

I'm not anywhere near an SQL expert though so exact detail of implementing that side are a little unknown to me. Still, if the problem were mine I'd be walking down exactly that road right now, probably with a lot of google beside me :)

Chris
Evan FiddlerAuthor Commented:
Thank you for the resolution.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Active Directory

From novice to tech pro — start learning today.