December 14, 2011 at 1:40 pm
Goal:
To have a result set consisting of two columns
DepartmentName, Computer
Env:
SQL Server 2005, Win2003 Domain Controller.
AD has (n) number of groups with a naming convention of..
"<prefix>_<Department Name>_<Index>"
Each group has all computer objects for a particular department.
What I know:
I can query AD vial linked server and retrieve all group names I am looking for via ldap filter.
-------------------------------------------
from openquery
(ADSI, 'SELECT cn
FROM ''LDAP://DC=mydomina,DC=com''
WHERE objectCategory = ''group''
AND cn=''DEPT_*''')
------------------------------------------
I can manually retrieve all computers names from a particular group via ldap filter
------------------------------------------
from openquery
(ADSI, 'SELECT distinguishedName
FROM ''LDAP://DC=mydomina,DC=com''
WHERE objectCategory = ''Computer''
AND memberOf=''CN=DEPT_Technical Services Group_12,OU=... etc.
------------------------------------------
I can build a temp table consisting of all departments by extracting the departments name from the center of each group's name.
I can manually select contents of each group and union the results together to create the correct final result set.
What I want:
To dynamically...
Build a result set consisting of <DepartmentName><ComputerName>
by retrieving all "DEPT_" groups and the members of each group.
So if a group name changes (A departments name changes) or a new group is added / removed (A new department is created or an existing one is disbanded) then the underlying query will cope dynamically.
What I don't want:
Is to have to manually 'Hard Code' group names into linked server queries to build the result set. I know I can achieve the desired result doing this but it is not scalable or flexible.
Anyone out there able to help. I don't need spoon fed just a simple explanation or example to point me in the right direction.
Cheers all.
Jason
December 14, 2011 at 1:56 pm
To clarify:
You can get a list of group names.
From that, you need to query computer names, but need the group name in an OpenQuery string, which can't use variables or column values.
Is that a decent summary?
If so, then just use the first query to build the query strings for the second query. You can do that either with a cursor, or by concatenating the results together into a single query and running that. (I'd use the second method. Bit harder to debug, bit easier to write/refactor.)
E.g.:
USE master;
DECLARE @Cmd VARCHAR(8000);
SELECT @Cmd = (SELECT 'select * from [' + name + '].sys.tables;'
FROM sys.databases
FOR XML PATH(''), TYPE).value('.[1]','varchar(8000)');
PRINT @Cmd;
EXEC (@Cmd);
I'm sure, from this, you can construct the query you need.
If you need the data in a temp table, create it in the primary script, and just add the necessary Insert statement to the dynamic command.
Note that the use of a cursor instead of concatenation (sample is concatenation) will allow you to use bigger commands. The concat version cuts off at 8k characters, and that can be a problem if the initial dataset is long and the string is substantial. A cursor that builds the command for one record, then executes it, will allow more characters per command.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 14, 2011 at 2:07 pm
Yes - that is a very good summary. 🙂
I am relatively new to SQL so your example does not immediately make sense to me. In general it looks like what I am after and I can tell you I am very appreciative of that.
I will experiment with you're example and see what falls out.
Thanks for the reply.
Jason.
December 14, 2011 at 2:44 pm
The example builds a string from a query.
Just run this part:
SELECT 'select * from [' + name + '].sys.tables;'
FROM sys.databases
If will give you a list that looks something like:
select * from [master].sys.tables;
select * from [tempdb].sys.tables;
select * from [model].sys.tables;
select * from [msdb].sys.tables;
select * from [ReportServer].sys.tables;
select * from [ReportServerTempDB].sys.tables;
select * from [RedGateMonitor].sys.tables;
select * from [ProofOfConcept].sys.tables;
select * from [ProofOfConcept2000].sys.tables;
select * from [AdventureWorksDW2008R2].sys.tables;
select * from [AdventureWorksLT2008R2].sys.tables;
select * from [AdventureWorks].sys.tables;
select * from [AdventureWorksDW].sys.tables;
select * from [AdventureWorksLT].sys.tables;
select * from [Test1].sys.tables;
select * from [Test_DB].sys.tables;
(The a list from one of my desktop SQL instances. Your list will be different depending on the databased on your server.)
Obviously, those are each executable queries.
The "for XML path" bit is a string concatenation trick. It takes all the rows and turns them into a single string:
SELECT 'select * from [' + name + '].sys.tables; '
FROM sys.databases
FOR XML PATH(''), TYPE
Result:
select * from [master].sys.tables;select * from [tempdb].sys.tables;select * from [model].sys.tables;select * from [msdb].sys.tables;select * from [ReportServer].sys.tables;select * from [ReportServerTempDB].sys.tables;select * from [RedGateMonitor].sys.tables;select * from [ProofOfConcept].sys.tables;select * from [ProofOfConcept2000].sys.tables;select * from [VisitOrlando].sys.tables;select * from [AdventureWorksDW2008R2].sys.tables;select * from [AdventureWorksLT2008R2].sys.tables;select * from [AdventureWorks].sys.tables;select * from [AdventureWorksDW].sys.tables;select * from [AdventureWorksLT].sys.tables;select * from [CampaignMgmnt].sys.tables;select * from [Acxiom].sys.tables;select * from [Test1].sys.tables;select * from [Test_DB].sys.tables;
Same queries, just now it's a single string instead of multiple rows. The rest of that bit just cleans up any accidental XML artifacts. That's what the "value(...)" part is for, turning it into a regular string without XML rules.
Then I use the usual Print and Exec commands to review (Print) and to actually run it (Exec).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 14, 2011 at 3:37 pm
I am suitably impressed. Really.
Thank you very much for both replies. Absolutely magic. I suspect you have saved me a lot of time.
I feel this approach will work very nicely when the correct insert statement is added to populate the table var or temp table.
Regards,
Jason.
December 14, 2011 at 8:00 pm
Works like a charm.
Thanks again.
Jason.
December 15, 2011 at 6:24 am
You won't be able to use a table variable in this case. The dynamic scripts will be out of scope for it.
As a general note, table variables are really only useful where you can be absolutely certain they will contain very small numbers of rows (like not over 100), or when you need to persist the data in them past a rollback. They tend to kill database performance because of the way the query optimizer has to treat them.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 16, 2011 at 4:11 am
Yep, your right. After trying - I found that I can't. I looked at wrapping this up in a Table Valued UDF but something put me off the idea. Sorry it is late here and I cannot remember why?
At this stage I have a stored proc that returns the correct result set. The proc itself declares and instantiates a temporary table for the multiple insert statements to store data to. It then queries the temporary table (returns the result set) then drops the temp table.
So then when actually using the stored proc I can declare a temp table and run exec stored proc to insert into it. I then have a result set containing all computers in our domain and the Departments they are being used in. This is important to us...
This temp table can then be joined onto the other distributed parts of data that make up our logical CMDB to answer business questions if you will.
Sounds a little bit hairy I am sure but it is panning out really well. Especially after researching [LDAP_MATCHING_RULE_IN_CHAIN] for a search filter.
Note: If your ever using [LDAP_MATCHING_RULE_IN_CHAIN] functionality make sure the Base distinguished name is the root of the domain. Nothing else will work.
Unfortunately we have a number of constraints beyond our control that means we cannot directly connect to or influence the design of around half of the physical data sets we work with. So to build a workable configuration database we need to think a little outside the square so to speak. Leverage good naming conventions, clever use of delimited strings in description fields etc.
It is an interesting project and I am learning a lot. A good portion of that thanks to people like yourself.
Cheers,
Jason.
December 16, 2011 at 6:06 am
Probably that UDFs can't create temp tables, nor can they run dynamic SQL.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply