March 22, 2017 at 8:59 am
I want to create a view that takes two columns, Domain00, and Account00, from one table and add a 3rd column, distinguishedName. Account00 is the name of a group, and Domain00 is the domain to which that group belongs. Some of these groups are local groups, others are from 1 of 2 Active Directory domains. I want distinguishedName to be the distinguishedName of the AD groups, and NULL for the local groups.
This issues I'm running into are that in OPENQUERY, I cannot pass a variable (in this case Account00) as part of my query). I CAN pass the entire SELECT statement, but that means setting a variable in my CASE statement. As I've read, I cannot do that. I was going to change my CASE statement into an IF/THEN, but I've read that it's not a good idea...plus I haven't been able to make it work.
I had the bright idea to create another query that retrieved the distriguishedName for ALL groups, but I hit the ADSI limit on records returns. I have too many groups that begin with the letter G to even break them up into smaller chunks.
Can I do something like this with a procedure?
For giggles, this works...meaning it returns the distinguishedName for the group specified in the OPENQUERY SELECT for all groups from DOMAIN1:
SELECT Domain00
,Account00
,distinguishedName =
CASE
WHEN Domain00 = DOMAIN1
THEN
(
SELECT distinguishedName
FROM OPENQUERY
(
ADSI
,'SELECT distinguishedName
FROM ''LDAP://DC=DOMAIN1,DC=com''
WHERE objectCategory = ''Group''
AND cn=''G-Bothersome People'''
)
)
WHEN Domain00 = DOMAIN2
THEN 'OTHERDOMAIN'
ELSE NULL
END
FROM dbo.[v_Test_LocalGroupMembers-SubGroups]
March 24, 2017 at 2:06 pm
Why not create a Script Task in VB language within an SSIS package, that runs the LDAP query results either into a SQL Server table using ADO code, or into a text file, which can then be imported to SQL in the same package... That might get you away from the ADSI limit. Not sure if using VB to run the LDAP query will have the same limitations or not, though. You'll just have to try...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 11, 2017 at 9:17 pm
I'm not entirely sure why you want a view here, if this is a recordset that you want to use regularly and JOIN to it, then you probably want to pull the data into a table as suggested above, and reference that. Of course you have to keep that updated to be useful.
Alternatively, you are correct, you can do this in a stored procedure or table-valued function, if you build the entire OPENQUERY() statement within a string and then execute that. This would work if you just wanted one group, if you want everything then I would think this would be a terrible idea. But you could use this to update your local table maybe on some scheduled basis. YOU WILL WANT TO TEST THIS. If you have that many groups, a billion OPENQUERY() statements is insanity. But hey, you asked.
Sorry about lousy formatting, it's late and I don't feel like trying to figure it out
--You won't need this part, but since I didn't have your objects, I mocked them up
if object_id('Tempdb..[##v_Test_LocalGroupMembers-SubGroups]') IS NOT NULL BEGIN DROP TABLE [##v_Test_LocalGroupMembers-SubGroups] END;
CREATE TABLE [##v_Test_LocalGroupMembers-SubGroups](iRow int identity(1,1),Domain00 varchar(10),Account00 varchar(60));
INSERT INTO [##v_Test_LocalGroupMembers-SubGroups] (Domain00,Account00)
SELECT 'DOMAIN1','G-Bothersome People' UNION
SELECT 'DOMAIN2','G-Peoplesome Bothers' UNION
SELECT 'DOMAIN3','G-Never Can Tell With Bees'
--end my mockup, now down to tacks of brass, let's create you a procedure
CREATE PROCEDURE dbo.spUpdateDistinguishedNames
AS
BEGIN
DECLARE @SQL varchar(max);
SELECT @SQL = COALESCE(@SQL,'') + 'SELECT * FROM OPENQUERY(ADSI,''SELECT distinguishedName
FROM ''''LDAP://DC='+Domain00+',DC=com''''
WHERE objectCategory = ''''Group''''
AND cn='''''+Account00+''''''')
UNION
'
FROM [##v_Test_LocalGroupMembers-SubGroups]--dbo.[v_Test_LocalGroupMembers-SubGroups]--horrible name, by the way
WHERE --change this WHERE clause as necessary
Account00 LIKE 'G%'
SET @SQL = 'INSERT INTO someTableYouBuiltToHoldDistinguishedNames ' --see, I can make terrible names too
+ SUBSTRING(@SQL,1,LEN(@SQL)-12) --chop off the last UNION
PRINT @SQL --view what you've created, once happy, comment this out
--EXEC(@SQL)--once you're happy, uncomment and it will run it to return you a dataset
END
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
May 11, 2017 at 9:37 pm
I forgot you wanted to conditionally look for something or NULL. I think this is the CASE you wanted, replace the guts of the proc above with this:
SELECT @SQL = COALESCE(@SQL,'')
+
CASE WHEN Domain00 IN ('Domain1','Domain2') THEN
'SELECT * FROM OPENQUERY(ADSI,''SELECT '''''+Domain00+''''' AS [Domain00],'''''+Account00+''''' AS [Account00],distinguishedName
FROM ''''LDAP://DC='+Domain00+',DC=com''''
WHERE objectCategory = ''''Group''''
AND cn='''''+Account00+''''''')
UNION ALL
'
ELSE 'SELECT '''+Domain00+''' AS [Domain00],'''+Account00+''' AS [Account00],NULL AS distinguishedName
UNION ALL
'
END
FROM [##v_Test_LocalGroupMembers-SubGroups]--dbo.[v_Test_LocalGroupMembers-SubGroups]
WHERE --change this WHERE clause as necessary, maybe pass in a parameter to use in the LIKE
Account00 LIKE 'G%'
SET @SQL = SUBSTRING(@SQL,1,LEN(@SQL)-14) + 'ORDER BY Domain00, Account00' --chop off the last UNION and add an order by clause
PRINT @SQL --view what you've created, once happy, comment this out
--EXEC(@SQL)--once you're happy, uncomment and run it
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
May 12, 2017 at 9:25 am
Thank you, I'll give this a try. I only have a few groups I want to query, and understanding that there are no guaranties in life, I'm pretty sure it will always be that way.
Your note that my view had a horrible name. You were right. Between my original post and your reply, I discovered that the hard way. I tried applying the latest update to SCCM and it kept failing because of the hyphens. Lesson learned.
FYI - I have a table that SCCM created of all of the members of local groups on my client systems. Some of those members are other groups. I want to create a report that will show all of the USERS that are local admins. Phase 2 will be to add columns to show how they are granted that access; direct group membership or sub-group membership. This will hopefully get me a step closer to realizing this dream.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply