Query problems - Group By and Latest date

Hi all,

hopefully someone can suggest the best way of implementing the problem i am trying to resolve. We have a table which contains rows relating to tests run on our product. This table is populated from an SSIS job which parses CSV files.

There are multiple rows per serial number relating to multiple tests. The only tests i am interested in are the ones with an ID of T120. Here is the query i have so far which should make it a little easier to explain:

SELECT [SerialNumber]
,Param1
,[TimeStamp]
FROM [Build Efficiency System].[dbo].[SSIS_SCANNERDATA_TBL]
WHERE Test = 'T120'
GROUP BY SerialNumber, Param1, [TimeStamp]
ORDER BY SerialNumber

What i have above is fine to a point. The problem i am encountering is that in test T120 it specifies a part which can be be one of about 6 in field Param1. If during testing there is a problem with the part then it is replaced and the test run a second time up until the whole product passes the test. The query above returns all instances of replacements so i may have the out put as follows:

SerialNumber Param1 TimeStamp
0 Part1 15/03/07
0 Part2 15/03/07
0 Part2 16/03/07
0 Part3 15/03/03

What i really need is to only list the last part that is installed, hence the one with the latest timestamp:

SerialNumber Param1 TimeStamp

0 Part1 15/03/07

0 Part2 16/03/07

0 Part3 15/03/03

Can someone please help me to alter the above query so that it will show only those Param1 fields that have the latest date for each part.

Many thanks in advance,

Grant

This should do the trick:

SELECT [SerialNumber]
,Param1
,MAX([TimeStamp])
FROM [Build Efficiency System].[dbo].[SSIS_SCANNERDATA_TBL]
WHERE Test = 'T120'
GROUP BY SerialNumber, Param1
ORDER BY SerialNumber

You only need to take the max of your timestamp field (and remove it from the group by). The group by all fields is a bit extreme in the previous query (you could use the distinct keyword instead if you had apparent duplicate rows (i.e. replaced the part 3 times in a day).

|||Thats sorted it.
I wasn't as far of the mark in the first place as i'd thought. Thank's very much for the assistance, its much appreciated.

Cheers,

Grant|||Hi, apologies but i need one more piece of advice on this subject.

If i want to include a column with a serial number of the part that has been replaced, how would i do that. As soon as i add it, it needs to be part of an aggregate function or the group by clause. When it becomes part of the group by clause it then duplicates the part again.

Any ideas?

Thanks,

Grant|||

Is the serial number of the part in the same table - if so presumably it is different for each time that part is replaced. You can use a nested query to get that - however it will run into a problem if there are multiple records with the same date. As it stands at the moment you could not distinuish between them.

If you had records:

SerialNumber Param1 TimeStamp Part_SN
0 Part1 15/03/07 1234
0 Part2 15/03/07 1235
0 Part2 16/03/07 1236
0 Part2 16/03/07 1237
0 Part3 15/03/03 1238

How would you know which of the two Part2 items fitted on 16 Mar to give the serial number of? If there are additional fields to determine this then we need to use them

If this does not arise then the query below should serve (substitute correct fieldname for Part_SN):

SELECT B.[SerialNumber]
,B.Param1
,B.[TimeStamp]
,B.Part_SN
FROM (
SELECT [SerialNumber]
,Param1
,MAX([TimeStamp]) AS TimeStamp
FROM [Build Efficiency System].[dbo].[SSIS_SCANNERDATA_TBL]
WHERE Test = 'T120'
GROUP BY SerialNumber, Param1
) A
INNER JOIN [Build Efficiency System].[dbo].[SSIS_SCANNERDATA_TBL] B
ON (A.[SerialNumber] = B.[SerialNumber]) AND
(A.Param1 = B.Param1) AND
(A.[TimeStamp] = B.[TimeStamp]) AND
(B.Test = 'T120')
ORDER BY B.[SerialNumber]

If this is a problem then you will get multiple records in that case - one for each serial number. If the TimeStamp is a datetime which includes the time of the replacement then this will not be an issue (as long as the required serial number is the last record.

|||Thanks,

That is exactly what i wanted to do. Works like a charm.

Grantsql

Query Problem

I am trying retrieve all records in a table by the max presciption by group of territory.

In my table I have columns-- IMS_num, last_name, terriorty_num, pres_num

I want to see all the data by territory and max pres_num
I am lost.

I got this far..

select max(pres_num) from table group by territory_num.

I do not know how to get the last_name and territory_num displayed.Perhaps it'll help ...

SELECT territory_num, last_name, max(pres_num)
FROM your_table
GROUP BY territory_num, last_name;

Query Performance

Hi,
I have one query for two groups of tables(3 tables). These two groups of
tables have the same data and schema but second group has been partitioned
based on OrderDate Column. By setting "Statistics IO On" I see very less IO
for second(partitioned) group and I expect it to run faster, but the query
for both groups finish in 10 seconds!
This is my query for non-partitioned tables:
select c.*,o.*,od.* from
sales.SalesOrderHeader o join Sales.SalesOrderDetail od
on o.salesorderid=od.salesorderid
join sales.customer c
on c.customerid=o.customerid
where (o.orderdate between '2003-01-01 00:00:00:000' and '2003-12-31
23:59:59:997')
and o.customerid>117
-- IO STATS:
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
Table 'SalesOrderDetail'. Scan count 1, logical reads 1618, physical reads
3, read-ahead reads 2383, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
Table 'SalesOrderHeader'. Scan count 1, logical reads 703, physical reads 1,
read-ahead reads 699, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
Table 'Customer'. Scan count 1, logical reads 105, physical reads 1,
read-ahead reads 103, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
--
And for partitioned tables:
select c.*,o.*,od.* from
orders2 o join orderdetails od
on o.salesorderid=od.salesorderid and o.orderdate=od.orderdate
join sales.customer c
on c.customerid=o.customerid
where (o.orderdate between '2003-01-01 00:00:00:000' and '2003-12-31
23:59:59:997')
and o.customerid>117
-- IO STATS:
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
Table 'OrderDetails'. Scan count 1, logical reads 748, physical reads 1,
read-ahead reads 743, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
Table 'Orders2'. Scan count 1, logical reads 318, physical reads 1,
read-ahead reads 316, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
Table 'Customer'. Scan count 1, logical reads 105, physical reads 1,
read-ahead reads 103, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
--
Is this what I must expect or the performance should be different?
Thanks in advance,
Leilayou'd be better off ot get rid of the paritions and create decent
indexes.
as a start,
customer.customerid
orders2.salesorderid
salesordersdetail.salesorderid
using * to describe your table name is a recipe for long term
catastrophes when a new guy adds a new table someday.|||I agree! Sometimes a non-partitioned covering index performs much better.
But I'm curious about the reason of same query performance with different
IOs!
"Doug" <drmiller100@.hotmail.com> wrote in message
news:1147020901.724076.61860@.e56g2000cwe.googlegroups.com...
> you'd be better off ot get rid of the paritions and create decent
> indexes.
> as a start,
> customer.customerid
> orders2.salesorderid
> salesordersdetail.salesorderid
> using * to describe your table name is a recipe for long term
> catastrophes when a new guy adds a new table someday.
>|||ok, is the data IDENTICAL for each group of data?
external factors such as caching, how many times you have run the
query, and disk utilization can have an impact on results you are
seeing.
I would also question that salesorderdetails and orderdetails don't
have the same data.|||Actually one group has been created using Make Table Query (Select
...into...) from another group. Therefore the tables in the left of JOIN
keyword are the same, and the right tables are identical to each other in
each query.
I cleaned the cache each time before executing the query to force the query
to reference the disk. I'm sure that the conditions are exactly the same for
the queries!
"Doug" <drmiller100@.hotmail.com> wrote in message
news:1147101576.517569.325820@.i40g2000cwc.googlegroups.com...
> ok, is the data IDENTICAL for each group of data?
> external factors such as caching, how many times you have run the
> query, and disk utilization can have an impact on results you are
> seeing.
> I would also question that salesorderdetails and orderdetails don't
> have the same data.
>

Query Performance

Hi,
I have one query for two groups of tables(3 tables). These two groups of
tables have the same data and schema but second group has been partitioned
based on OrderDate Column. By setting "Statistics IO On" I see very less IO
for second(partitioned) group and I expect it to run faster, but the query
for both groups finish in 10 seconds!
This is my query for non-partitioned tables:
select c.*,o.*,od.* from
sales.SalesOrderHeader o join Sales.SalesOrderDetail od
on o.salesorderid=od.salesorderid
join sales.customer c
on c.customerid=o.customerid
where (o.orderdate between '2003-01-01 00:00:00:000' and '2003-12-31
23:59:59:997')
and o.customerid>117
-- IO STATS:
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
Table 'SalesOrderDetail'. Scan count 1, logical reads 1618, physical reads
3, read-ahead reads 2383, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
Table 'SalesOrderHeader'. Scan count 1, logical reads 703, physical reads 1,
read-ahead reads 699, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
Table 'Customer'. Scan count 1, logical reads 105, physical reads 1,
read-ahead reads 103, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
--
And for partitioned tables:
select c.*,o.*,od.* from
orders2 o join orderdetails od
on o.salesorderid=od.salesorderid and o.orderdate=od.orderdate
join sales.customer c
on c.customerid=o.customerid
where (o.orderdate between '2003-01-01 00:00:00:000' and '2003-12-31
23:59:59:997')
and o.customerid>117
-- IO STATS:
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
Table 'OrderDetails'. Scan count 1, logical reads 748, physical reads 1,
read-ahead reads 743, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
Table 'Orders2'. Scan count 1, logical reads 318, physical reads 1,
read-ahead reads 316, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
Table 'Customer'. Scan count 1, logical reads 105, physical reads 1,
read-ahead reads 103, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
--
Is this what I must expect or the performance should be different?
Thanks in advance,
Leilayou'd be better off ot get rid of the paritions and create decent
indexes.
as a start,
customer.customerid
orders2.salesorderid
salesordersdetail.salesorderid
using * to describe your table name is a recipe for long term
catastrophes when a new guy adds a new table someday.|||I agree! Sometimes a non-partitioned covering index performs much better.
But I'm curious about the reason of same query performance with different
IOs!
"Doug" <drmiller100@.hotmail.com> wrote in message
news:1147020901.724076.61860@.e56g2000cwe.googlegroups.com...
> you'd be better off ot get rid of the paritions and create decent
> indexes.
> as a start,
> customer.customerid
> orders2.salesorderid
> salesordersdetail.salesorderid
> using * to describe your table name is a recipe for long term
> catastrophes when a new guy adds a new table someday.
>|||ok, is the data IDENTICAL for each group of data?
external factors such as caching, how many times you have run the
query, and disk utilization can have an impact on results you are
seeing.
I would also question that salesorderdetails and orderdetails don't
have the same data.|||Actually one group has been created using Make Table Query (Select
...into...) from another group. Therefore the tables in the left of JOIN
keyword are the same, and the right tables are identical to each other in
each query.
I cleaned the cache each time before executing the query to force the query
to reference the disk. I'm sure that the conditions are exactly the same for
the queries!
"Doug" <drmiller100@.hotmail.com> wrote in message
news:1147101576.517569.325820@.i40g2000cwc.googlegroups.com...
> ok, is the data IDENTICAL for each group of data?
> external factors such as caching, how many times you have run the
> query, and disk utilization can have an impact on results you are
> seeing.
> I would also question that salesorderdetails and orderdetails don't
> have the same data.
>

Query Performance

Hi,
I have one query for two groups of tables(3 tables). These two groups of
tables have the same data and schema but second group has been partitioned
based on OrderDate Column. By setting "Statistics IO On" I see very less IO
for second(partitioned) group and I expect it to run faster, but the query
for both groups finish in 10 seconds!
This is my query for non-partitioned tables:
select c.*,o.*,od.* from
sales.SalesOrderHeader o join Sales.SalesOrderDetail od
on o.salesorderid=od.salesorderid
join sales.customer c
on c.customerid=o.customerid
where (o.orderdate between '2003-01-01 00:00:00:000' and '2003-12-31
23:59:59:997')
and o.customerid>117
-- IO STATS:
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
Table 'SalesOrderDetail'. Scan count 1, logical reads 1618, physical reads
3, read-ahead reads 2383, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
Table 'SalesOrderHeader'. Scan count 1, logical reads 703, physical reads 1,
read-ahead reads 699, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
Table 'Customer'. Scan count 1, logical reads 105, physical reads 1,
read-ahead reads 103, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
--
And for partitioned tables:
select c.*,o.*,od.* from
orders2 o join orderdetails od
on o.salesorderid=od.salesorderid and o.orderdate=od.orderdate
join sales.customer c
on c.customerid=o.customerid
where (o.orderdate between '2003-01-01 00:00:00:000' and '2003-12-31
23:59:59:997')
and o.customerid>117
-- IO STATS:
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
Table 'OrderDetails'. Scan count 1, logical reads 748, physical reads 1,
read-ahead reads 743, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
Table 'Orders2'. Scan count 1, logical reads 318, physical reads 1,
read-ahead reads 316, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
Table 'Customer'. Scan count 1, logical reads 105, physical reads 1,
read-ahead reads 103, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
--
Is this what I must expect or the performance should be different?
Thanks in advance,
Leilayou'd be better off ot get rid of the paritions and create decent
indexes.
as a start,
customer.customerid
orders2.salesorderid
salesordersdetail.salesorderid
using * to describe your table name is a recipe for long term
catastrophes when a new guy adds a new table someday.|||I agree! Sometimes a non-partitioned covering index performs much better.
But I'm curious about the reason of same query performance with different
IOs!
"Doug" <drmiller100@.hotmail.com> wrote in message
news:1147020901.724076.61860@.e56g2000cwe.googlegroups.com...
> you'd be better off ot get rid of the paritions and create decent
> indexes.
> as a start,
> customer.customerid
> orders2.salesorderid
> salesordersdetail.salesorderid
> using * to describe your table name is a recipe for long term
> catastrophes when a new guy adds a new table someday.
>|||ok, is the data IDENTICAL for each group of data?
external factors such as caching, how many times you have run the
query, and disk utilization can have an impact on results you are
seeing.
I would also question that salesorderdetails and orderdetails don't
have the same data.|||Actually one group has been created using Make Table Query (Select
...into...) from another group. Therefore the tables in the left of JOIN
keyword are the same, and the right tables are identical to each other in
each query.
I cleaned the cache each time before executing the query to force the query
to reference the disk. I'm sure that the conditions are exactly the same for
the queries!
"Doug" <drmiller100@.hotmail.com> wrote in message
news:1147101576.517569.325820@.i40g2000cwc.googlegroups.com...
> ok, is the data IDENTICAL for each group of data?
> external factors such as caching, how many times you have run the
> query, and disk utilization can have an impact on results you are
> seeing.
> I would also question that salesorderdetails and orderdetails don't
> have the same data.
>

Query not producing correct results and Group By Error

This is what I need in my results:
Current Balance and Account Number should be retrieved where
Current Balance = Sum of rmstranamt - (sum of rmstranamt where rmstrancde is 10)

The Query below gives me a couple of problems

1) It's not producing the correct Current Balance

2) I keep getting these errors with Groupy by:

Column 'rf10.rmstranamt10' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

SELECT rm.rmsacctnum AS [Rms Acct Num],

(sum(rf.rmstranamt) - rf10.rmstranamt10) AS [Current Balance]

FROM RMASTER rm

INNER JOIN

(

SELECT RMSFILENUM, SUM(rmstranamt) AS rmstranamt10

FROM RFINANL

WHERE RMSTRANCDE = '10'

GROUP BY RMSFILENUM

) AS rf10 ON rf10.RMSFILENUM = rm.RMSFILENUM

INNER JOIN

(

SELECT RMSFILENUM, SUM(rmstranamt) AS rmstranamt

FROM RFINANL

GROUP BY RMSFILENUM

) AS rf ON rf.RMSFILENUM = rm.RMSFILENUM

GROUP BY rm.rmsacctnum

The error message is correct as you would have to enclose the rf10.rmstranamnt10 inside a sum() to allow for the aggregation. I always recheck the SQL with an english translation of what I am trying to do. Your descriptive phrase shows the secondary sum, but the T-SQL does not have it.

I hope this helps,

|||

I did as you said and added the sum. I am still getting a Current Balance of 0.00 for an account # I checked when I know for a fact there are a total of 2 positive rmstranamt records in RFINANL with a RMSTRANCDE of 10 so I should have the sume of those in my results for that account number but it's 0.00

This query shows why

SELECT rm.rmsacctnum AS [Rms Acct Num],

sum(rf.rmstranamt) as Sum_rstranamt,

sum(rf10.rmstranamt10) as Sum_rmstranamt_10,

(sum(rf.rmstranamt) - sum(rf10.rmstranamt10)) AS [Current Balance]

FROM RMASTER rm

INNER JOIN

(

SELECT RMSFILENUM, SUM(rmstranamt) AS rmstranamt10

FROM RFINANL

WHERE RMSTRANCDE = '10'

GROUP BY RMSFILENUM

) AS rf10 ON rf10.RMSFILENUM = rm.RMSFILENUM

INNER JOIN

(

SELECT RMSFILENUM, SUM(rmstranamt) AS rmstranamt

FROM RFINANL

GROUP BY RMSFILENUM

) AS rf ON rf.RMSFILENUM = rm.RMSFILENUM

AND rm.rmsacctnum = '4264287999172303'

GROUP BY rm.rmsacctnum

results:

4264287999172303 28789.50 28789.50 0.00

Sum_rstranamt should not be 28789.50 because there are 2 records for that account:

SELECT rm.rmsacctnum AS [Rms Acct Num],

rf.rmstranamt as [Rms rmstranamt],

rf.rmsbalance AS [Rms Balance]

FROM RMASTER rm

INNER JOIN RFINANL rf ON rf.RMSFILENUM = rm.RMSFILENUM

where rm.rmsacctnum = '4264287999172303'

4264287999172303 2878.95 0.00
4264287999172303 25910.55 28789.50

So Sum_rstranamt should be 2878.95 + 25910.55

|||

Here's a second look at what I've sruggled with:

I think we're almost there:
Let's work with account # '4264287999172303'
Query #1 - shows that there are 2 records in the RFINANL table for that account
SELECT rm.rmsacctnum AS [Rms Acct Num],
rf.rmstranamt as [Rms rmstranamt],
rf.rmsbalance AS [Rms Balance]
FROM RMASTER rm
INNER JOIN RFINANL rf ON rf.RMSFILENUM = rm.RMSFILENUM
where rm.rmsacctnum = '4264287999172303'
RESULTS:
4264287999172303 2878.95 0.00
4264287999172303 25910.55 28789.50

Query #2 - shows that there are 2 records in the RFINANL table for that account where RMSTRANCDE = '10'
--
select rm.rmsacctnum AS [Rms Acct Num], rf.rmstranamt
FROM RMASTER rm
INNER JOIN RFINANL rf ON rf.RMSFILENUM = rm.RMSFILENUM
where rf.RMSTRANCDE = '10'
and rm.rmsacctnum = '4264287999172303'
RESULTS:
4264287999172303 2878.95
4264287999172303 25910.55

Now this is what boggles my mind. If we were to take Query #1 and add a sum in it like below, you would get a result of 2878.95 + 25910.55 but I don't.
SELECT rm.rmsacctnum AS [Rms Acct Num],
sum(rf.rmstranamt) as [Rms rmstranamt], < added sum hoping to get 2878.95 + 25910.55
rf.rmsbalance AS [Rms Balance]
FROM RMASTER rm
INNER JOIN RFINANL rf ON rf.RMSFILENUM = rm.RMSFILENUM
where rm.rmsacctnum = '4264287999172303'
GROUP BY rm.rmsacctnum, rf.rmsbalance < but now I'm required to group by any values that do not contain an aggregate function
RESULTS:
4264287999172303 2878.95 0.00
4264287999172303 25910.55 28789.50
Take this a step further, now I add a sum to rf.rmsbalance since at this point I don't know what else do do. I then figure I should get 2878.95 + 25910.55 and 0.00 + 28.789.50 but again, I don't, I end up with both of them havin the same value. I have no idea why:
SELECT rm.rmsacctnum AS [Rms Acct Num],
sum(rf.rmstranamt) as [Rms rmstranamt],
Sum(rf.rmsbalance) AS [Rms Balance] <- Added sum to rf.rmsbalance
FROM RMASTER rm
INNER JOIN RFINANL rf ON rf.RMSFILENUM = rm.RMSFILENUM
where rm.rmsacctnum = '4264287999172303'
GROUP BY rm.rmsacctnum <- took out rf.rmsbalance because it's a sum now in the select
RESULTS:
4264287999172303 28789.50 28789.50

So at this point I'm still not getting this which I am ultimately wanting for each and every account if you we were to figure out the correct syntax:
(2878.95 + 25910.55) - (28789.50) as [Current Balance],
....

Query multiple cubes in a single MDX?

I am wondering if there is a way to query more than one cube in a single mdx statement. Here's the deal - I have a cube that has a measure group that is a count of invoices, and another cube that has additional measures / dimensions of interest. The 2nd cube satisfies everything we need, except for the count of invoices. Is there a way to query the invoice cube as a calculated measure in the mdx that is querying the 2nd cube? It would be easy to add the invoice measure group to the 2nd cube, but that would increase the processing time way too much (unfortunately we have to do a full process). Here's a stupid question - would adding the measure group to another cube in the database cause two physical reads of the table? I am guessing yes, but you never know.

I read about linked dimensions / measures, but it looks like they are for measures / dimsneions in a different AS database. There probably is a simple solution for this, but I am still fairly new to AS / mdx.

thank you in advance,

John Hennesey

Hello John! I think that the best solution is to have both measure groups in the same cube, even if processing time can increase.

I have not seen an MDX select with two cubes in the From clause. Subcube statements also refer to the same cube. I am not on the top 10 MDX expert list but I think that MDX will not help.

Can you not add the invoice number to the second measure group an aggregate it with count?

Kind Regards

Thomas Ivarsson

|||

Yeah, I did some tests this morning to see how long it takes to query the invoice dimension. Look like this is the way to go!

FYI - I did find a LookupCube function, but I think this is a last resort method - much slower than designing it properly from scratch.

Thanks for your quick response -

John

Query LDAP/ADSI for group members?

I am trying to get members of an Active Directory group by querying the AD server from Transact-SQL (SQL Server 2005). Although there does not seem to be any written list of LDAP attributes that can be queried in AD (or I am not finding it), I have gotten this far:

SELECT * FROM

OPENQUERY( MYSERVER,

'SELECT cn, msExchHomeServerName, userPrincipalName FROM ''LDAP://CN=Users,DC=MYSERVER,DC=COM'' WHERE userPrincipalName=''*'' ')

This gives me a user list. But I can't find the syntax or attribute name(s) to query in order to get the membership of a specific group - for example, the group "SQL_Developers".

Anybody out there familiar enough with LDAP, AD and OPENQUERY() to give me a hand?

Thanks....

Tom

SELECT Member FROM 'LDAP://CN=SqlDevelopers,OU=Container,DC=Myserver,DC=com'

returns an array.. use the distinguished name to reference your particular group.

|||Can't get that syntax to work - sorry. Not sure if it's a security problem (maybe) or if our servers are set up in a nonstandard way (possible) or if I'm just doing something wrong (likely). Can you elaborate a little bit more on your suggestion?

Query LDAP user group membership from SQL Server

this is driving me nuts!
all i want is a simple list of groups and members for each group in
Active Directory. I read numorous postings on this topic and no one
seems to know how to query it from sql server directly.
why can't Microsoft provide some kind of schema (views) for that?
i can issue a query like this
SELECT a.name, a.adspath, b.name, b.adspath
FROM OpenQuery(ADSI,
'SELECT name, ADsPath
FROM ''LDAP://server/ DC=mydomain,DC=com''
WHERE objectCategory = ''person''') a,
OpenQuery(ADSI,
'SELECT name, ADsPath
FROM ''LDAP://server/ DC=mydomain,DC=com''
WHERE objectCategory = ''group''') b
but there is no relationship i can join between the two to connect the
dots between groups and users.
the problems i have is that i'm not a VB programmer, and i am not a
network admin and don't knwo how to use some of the vb code samples
provided in the newsgroup (see below). unless someone has a more
comprehesive link for how to set those scripts up.
---
To the best of my knowledge, you can retrieve MemberOf in
your list of attributes, but you cannot query on it. You
must return a recordset with memberof among the
attributes, then enumerate the recordset and look for the
info you need. MemberOf will be an array. Use:
'<LDAP://myServer/cn=users,dc=myDomain,dc=com>;(&
(objectCategory=Person)(objectClass=user
))
;displayname, memberOf, objectCategory, cn,
adspath;subtree'
In your example, that means returning a recordset of all
users. If RS is the recordset, I code the following in
VBScript.
colMembers = RS.Fields("MemberOf")
For Each Item in colMembers
Wscript.Echo Item
---
either that, or does anyone knows how to script out that info from
Active Directory and output it to a text file for sql to pick up? I
just want a simple two column file to tell me all the groups and
members for each group. why would it be difficult?Hi
You need to look at the memberof attribute.
http://msdn.microsoft.com/library/d...ace_mapping.asp
This may also help
http://www.rlmueller.net/List%20User%20Groups.htm
John
"=== Steve L ===" <steve.lin@.powells.com> wrote in message
news:1108757700.331401.193420@.o13g2000cwo.googlegroups.com...
> this is driving me nuts!
> all i want is a simple list of groups and members for each group in
> Active Directory. I read numorous postings on this topic and no one
> seems to know how to query it from sql server directly.
> why can't Microsoft provide some kind of schema (views) for that?
> i can issue a query like this
> SELECT a.name, a.adspath, b.name, b.adspath
> FROM OpenQuery(ADSI,
> 'SELECT name, ADsPath
> FROM ''LDAP://server/ DC=mydomain,DC=com''
> WHERE objectCategory = ''person''') a,
> OpenQuery(ADSI,
> 'SELECT name, ADsPath
> FROM ''LDAP://server/ DC=mydomain,DC=com''
> WHERE objectCategory = ''group''') b
> but there is no relationship i can join between the two to connect the
> dots between groups and users.
> the problems i have is that i'm not a VB programmer, and i am not a
> network admin and don't knwo how to use some of the vb code samples
> provided in the newsgroup (see below). unless someone has a more
> comprehesive link for how to set those scripts up.
> ---
> To the best of my knowledge, you can retrieve MemberOf in
> your list of attributes, but you cannot query on it. You
> must return a recordset with memberof among the
> attributes, then enumerate the recordset and look for the
> info you need. MemberOf will be an array. Use:
> '<LDAP://myServer/cn=users,dc=myDomain,dc=com>;(&
> (objectCategory=Person)(objectClass=user
))
> ;displayname, memberOf, objectCategory, cn,
> adspath;subtree'
> In your example, that means returning a recordset of all
> users. If RS is the recordset, I code the following in
> VBScript.
> colMembers = RS.Fields("MemberOf")
> For Each Item in colMembers
> Wscript.Echo Item
> ---
> either that, or does anyone knows how to script out that info from
> Active Directory and output it to a text file for sql to pick up? I
> just want a simple two column file to tell me all the groups and
> members for each group. why would it be difficult?
>|||thanks for the reply. but i'm not trying to look up a window user
account's group info. i can see that in Active directory. what i need
is a way to script out all the groups and users info from AD and there
should a membership relationship like
user memberof
u1 grp1
u1 grp2
u1 grp3
u2 grp2...
and so on.
again, i'm not a vb programmer, so i need some intructions as to how to
run a script and so on. i have seen those links you posted before. i
couldnt' get them to work. something is missing from the instructions.
can anyone fill the gap?
thank you.|||Yeah John but have u been able to retrieve GROUPS from the Active
Diretory?
For example i want to get a USER and retrieve the GROUPS they belong to.
Any LUCK using OPEN QUERY?
*** Sent via Developersdex http://www.examnotes.net ***