Client Management Suite

Hi,

I need to create two more colums using the attached SQL query. I need one Colum titled School and the other titled OU (see attached sample xls which shows the current export). I've highlighted from the Active Directory OU colum where the information needs to come from and manually added two further colums (in red) with what i'd need it to look like..

I've really limited knowledge on SQL and have doctored the query which I have to suit what I need it for but can't work out how I can add this further query.

All help much appreciated.

Many thanks,

Gary

p.s i'm currently running the script straight from SQL Server Management Studio (2005).

Attachment(s)

Copy of SQL Export 080211.xls   1.87 MB 1 version

Sample xls output.xls   21 KB 1 version

Sorry attached is my SQL Query....

Thanks,

Gary

Attachment(s)

SQL Query GC 080211.txt   5 KB 1 version

So here's a query I keep around as a generic "tell me the group" query.  I am not a huge SQL person, so this could be messier or more resource intensive than it should be.  I don't know and I run against test databases which have few nodes.

Right-click on your AD Organizational View, choose Properties, and record the GUID.  Then insert it into this query as directed below.  No {} marks, just single quotes around the value.

Then right-click the 'Schools' OU and record this GUID, inserting it as directed below for the ParentFolderGuid value.  This will then return the GroupName for a computer's group if the computer's parent group is the OG synchronized with the AD OU 'Schools' and the OV is the AD synchronized view.

This only goes one level below, so this should tell you the numerical school value.  As an example, if you ran this against the Default view, and gave the GUID of the 'Network Resource' OG, you would only receive computers in the group 'Computer,' but not computers in the group 'Virtual Machines.'

SELECT

v1.[Name] as 'ComputerName',

Item.Name as 'GroupName',

Item.Description as 'GroupDescription',

v1.Guid as 'ComputerGuid',

ScopeMembership.ScopeCollectionGuid as 'GroupGuid',

Item.SecurityGuid,

ScopeCollection.ScopeSetGuid,

ScopeCollection.ScopeCollectionGuid,

ScopeMembership.ResourceGuid,

ScopeMembership.ScopeCollectionGuid

FROM

vComputer v1

JOIN ScopeMembership ON v1.Guid=ScopeMembership.ResourceGuid

JOIN ScopeCollection ON ScopeMembership.ScopeCollectionGuid=ScopeCollection.ScopeCollectionGuid

JOIN Item ON ScopeMembership.ScopeCollectionGuid=Item.SecurityGuid

JOIN vItemParentFolder ON vItemParentFolder.ChildItemGuid=ScopeMembership.ScopeCollectionGuid

--Return results only if the Organizational View is the Active Directory Organizational View

WHERE (ScopeCollection.ScopeSetGuid='GUID OF ACTIVE DIRECTORY VIEW' AND vItemParentFolder.ParentFolderGuid='GUID OF THE ORGANIZATIONAL GROUP CALLED SCHOOLS AND SYNCHRONIZED WITH ACTIVE DIRECTORY')

And here's the same query modified slightly by removing the vItemParentFolder reference/restriction.  It now returns 'GroupName' as the name of the group a computer resides in.

If you ran this against the Default view, you would now see computers that exist in Computer and in Virtual Machine.

Again, I am not a SQL guy, and I'm sure this isn't very efficient at all.  By itself it runs fine, but you may have performance considerations as you integrate it into your existing query.

In this case, this query would return the OU names like STUDENT LAPTOPS, STUDENT DESKTOPS, etc.

SELECT

v1.[Name] as 'ComputerName',

Item.Name as 'GroupName',

Item.Description as 'GroupDescription',

v1.Guid as 'ComputerGuid',

ScopeMembership.ScopeCollectionGuid as 'GroupGuid',

Item.SecurityGuid,

ScopeCollection.ScopeSetGuid,

ScopeCollection.ScopeCollectionGuid,

ScopeMembership.ResourceGuid,

ScopeMembership.ScopeCollectionGuid

FROM

vComputer v1

JOIN ScopeMembership ON v1.Guid=ScopeMembership.ResourceGuid

JOIN ScopeCollection ON ScopeMembership.ScopeCollectionGuid=ScopeCollection.ScopeCollectionGuid

JOIN Item ON ScopeMembership.ScopeCollectionGuid=Item.SecurityGuid

--Return results only if the Organizational View is the Active Directory Organizational View

WHERE ScopeCollection.ScopeSetGuid='GUID OF YOUR AD ORGANIZATIONAL VIEW HERE'

Since you're using v6, try looking into the SQL Substring command.  You can essentially do a subquery based on postion and length and return it as a row. I didn't look at your query yet, but you could do something like this.

SUBSTRING 14 (the starting position from the left), 36 (the length) as 'Schools'. I'm not sure if you can do this multiple times on the same table, but I'll take a look in a bit.

Thanks for your help guys, very much appreciated.

I'll have a crack at it shortly and hopefully should resolve my query. I'll keep you posted.

Regards,

Gary

Oops, I've posted for 7.  Make a note for when you migrate to 7 later!

You can still query against vItemParentFolder, however.  ParentFolderGuid is the GUID of the SCHOOLS OU, and a query against it will return the ChildItemGuid's, which are the numerical school values.

You'll still need to tie these back to membership through CollectionMembership, using the CollectionGuid to map to vItemParentFolder for the names (ChildFolderName and/or ParentFolderName).