myITforum.com Community Forum myITforum.com Community Forum

Home  Forums  Blogs  Live Support chat  Search Articles  Wiki  FAQ  Email Lists  Register  Login  My Profile  Inbox  Address Book  My Subscription  My Forums 

Photo Gallery  Member List  Search  Calendars  FAQ  Ticket List  Log Out

All Forums RSS Feed Subscription:


  


Create computer collection based on AD user group membership

 
View related threads: (in this forum | in all forums)

Logged in as: Guest
  Printable Version
All Forums >> [Management Products] >> Microsoft Systems Management Server >> SMS 2003 >> Create computer collection based on AD user group membership Page: [1]
Login
Message << Older Topic   Newer Topic >>
Create computer collection based on AD user group membe... - 6/19/2008 11:55:48 AM   
lt

 

Posts: 13
Score: 0
Joined: 6/19/2008
Status: offline 		Hello Everyone,

I made this SQL query that will show me users computers from a specific AD user group:

Select sys.Name0,sys.User_Name0,grp.User_Group_Name0
from dbo.v_R_System sys
join dbo.v_R_User usr on sys.User_Name0 = usr.User_Name0
join dbo.v_RA_User_UserGroupName grp on usr.ResourceID = grp.ResourceID
Where grp.User_Group_Name0 like 'domain\group'

It works fine in SMS reports or query analyzer but I cannot make a collection with it because I need to turn it into a WQL query. I really wish M$ stuck with SQL all the way through this WQL adds a huge degree of confusion.

Basically I want collections of computers that are based on users group AD memberships.

I have tried everything I can but it just won't work.

Any ideas?

Thanks

Lt
Post #: 1
RE: Create computer collection based on AD user group m... - 6/19/2008 11:57:05 AM   
lt

 

Posts: 13
Score: 0
Joined: 6/19/2008
Status: offline 		I should point out that I don't want collections based of System AD groups.. I want a computer collection based on the last logged in users AD group.

(in reply to lt)
Post #: 2
RE: Create computer collection based on AD user group m... - 6/19/2008 12:37:14 PM   
pwstrain

 

Posts: 102
Score: 5
Joined: 3/11/2007
From: Danville, Illinois
Status: offline 		In taking a stab at it, the closest I can come is:

select distinct SMS_R_System.Name from  SMS_R_System inner join SMS_G_System_SYSTEM_CONSOLE_USAGE on SMS_G_System_SYSTEM_CONSOLE_USAGE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SYSTEM_CONSOLE_USAGE.TopConsoleUser in (select distinct UserName from  SMS_R_User where UserGroupName = "<domain>\\<group>")

The problem is that the TopConsoleUser returns domain\user, while the SMS_R_User returns only username. Anyone have a way to parse off the domain name from TopConsoleUser, or just a better way to do this?


_____________________________

My Personal Blog
My Technical Blog

(in reply to lt)
Post #: 3
RE: Create computer collection based on AD user group m... - 6/19/2008 12:58:02 PM   
skissinger


Posts: 1856
Score: 123
Joined: 9/13/2001
From: Sherry Kissinger
Status: offline 		I use SLAT from systemcentertools.com, not top console user; but this might give you an idea:

select SMS_R_SYSTEM.ResourceID
from SMS_R_System
inner join
SMS_G_System_MCS_USERLOGONINFO_1_0 on SMS_G_System_MCS_USERLOGONINFO_1_0.ResourceID = SMS_R_System.ResourceId
where SMS_G_System_MCS_USERLOGONINFO_1_0.UserName
in (select UserName from  SMS_R_User where UserGroupName = "DOMAIN\\GroupName") and SMS_G_System_MCS_USERLOGONINFO_1_0.UserRank = 1

_____________________________

mofmaster@smsexpert.com (version 2007) | http://www.smsexpert.com | http://www.sccmexpert.com
Microsoft MVP - ConfigMgr

(in reply to pwstrain)
Post #: 4
RE: Create computer collection based on AD user group m... - 6/19/2008 1:06:51 PM   
lt

 

Posts: 13
Score: 0
Joined: 6/19/2008
Status: offline 		Thanks for the great suggestions. I tried both of them the first one didn't return any results for me and the second suggestion just gave me a invalid query error.

(in reply to skissinger)
Post #: 5
RE: Create computer collection based on AD user group m... - 6/19/2008 1:21:19 PM   
lt

 

Posts: 13
Score: 0
Joined: 6/19/2008
Status: offline 		In taking your suggestions I tried this and also got errors:

select distinct SMS_R_System.Name from  SMS_R_System inner join SMS_R_User on SMS_R_User.User_Name = SMS_R_System.User_name inner join SMS_RA_User_GroupName on SMS_RA_User_GroupName.ResourceID = SMS_R_User.ResourceID where SMS_R_User in (select distinct ResourceID from SMS_RA_User_GroupName where User_Group_Name = "domain\name") 

I guess I am confused about how this WQL works as apposed to SQL.

(in reply to lt)
Post #: 6
RE: Create computer collection based on AD user group m... - 6/19/2008 1:27:15 PM   
pwstrain

 

Posts: 102
Score: 5
Joined: 3/11/2007
From: Danville, Illinois
Status: offline 		Aha! This one works for me:

select distinct SMS_R_System.Name from  SMS_R_System inner join SMS_G_System_SYSTEM_CONSOLE_USAGE on SMS_G_System_SYSTEM_CONSOLE_USAGE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SYSTEM_CONSOLE_USAGE.TopConsoleUser in (select distinct UniqueUserName from  SMS_R_User where UserGroupName = "<domain>\\<group>")

If you don't have SP3 I don't think that "Top Console User" is available.

_____________________________

My Personal Blog
My Technical Blog

(in reply to lt)
Post #: 7
RE: Create computer collection based on AD user group m... - 6/19/2008 1:29:56 PM   
jnelson993


Posts: 731
Score: 91
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline 		WQL is really only like SQL in that they both end in QL.

WQL is a query language for WMI.  So when you create an SMS/SCCM query or a collection query, you're really writing code to pull data from WMI.  In addition to all the SMS data being in SQL, it can also be gotten at using WMI.  The thought being there that there is a very limited number of things you can do with WQL compared to SQL so that the SMS/SCCM server is LESS likely to be tipped over by a poorly written query.  You can easily write bad SQL and SQL will run till it barfs trying to feed that data to you.  WQL will just look at you in disgust and tell you to go pound sand.  There are a lot of things that WQL as implemented in SMS/SCCM can't do, like string concatenation, substrings, directly joining system and user/usergroup data (although Sherry claims she can...I've had no luck).  There's no counting, no real aggregate functions like MAX(), basically, nothing that takes extra processing by SMS/SCCM.  It's a beach trying to do the same in WQL as you can in SQL.


_____________________________

Number2 (John Nelson)
MyITForum - Blog
MyITForum - Forum Posts

(in reply to lt)
Post #: 8
RE: Create computer collection based on AD user group m... - 6/19/2008 1:30:00 PM   
lt

 

Posts: 13
Score: 0
Joined: 6/19/2008
Status: offline 		Ok I see you are using the utility SLAT.

Not sure if this should be necessary. The User_Name in R_System is accurate enough for me for last login. The resource ids in RA_User_GroupName match the resource ids associated with user accounts in R_User.

I am surprised this isn't a built in query in sms it seems like it would be a pretty standard thing to do.

(in reply to lt)
Post #: 9
RE: Create computer collection based on AD user group m... - 6/19/2008 1:31:15 PM   
skissinger


Posts: 1856
Score: 123
Joined: 9/13/2001
From: Sherry Kissinger
Status: offline 		Correct, Top console User is available w/SP3 (or ConfigMgr), and the right GPOs in place.  I'm on ConfigMgr, but still using SLAT from SystemCenterTools because we have a service account on some workstations that shows up as the "Top user".  With SLAT, I could tell it to ignore entries from that Service Account, so the real highest ranked user is reported correctly.

Just a quick caution to LT:  Last logged on user isn't 100% reliable...

_____________________________

mofmaster@smsexpert.com (version 2007) | http://www.smsexpert.com | http://www.sccmexpert.com
Microsoft MVP - ConfigMgr

(in reply to pwstrain)
Post #: 10
RE: Create computer collection based on AD user group m... - 6/19/2008 1:36:16 PM   
lt

 

Posts: 13
Score: 0
Joined: 6/19/2008
Status: offline 		I have SMS 2003/wSP3

I just tried running this as pwstrain did

select distinct SMS_R_System.Name from  SMS_R_System inner join SMS_G_System_SYSTEM_CONSOLE_USAGE on SMS_G_System_SYSTEM_CONSOLE_USAGE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SYSTEM_CONSOLE_USAGE.TopConsoleUser in (select distinct UniqueUserName from  SMS_R_User where UserGroupName = "DOMAINNAME\\IT") 

and it just didn't return any results. I just pasted it in as an unspecified query (just to test before trying in a collection). Am I doing somethign wrong? 

(in reply to skissinger)
Post #: 11
RE: Create computer collection based on AD user group m... - 6/19/2008 1:45:03 PM   
lt

 

Posts: 13
Score: 0
Joined: 6/19/2008
Status: offline 		I am checking analyzer and I am not seeing anything that looks like G_System_SYSTEM_CONSOLE_USAGE what is its equivalent name in SQL views?

< Message edited by lt -- 6/19/2008 1:46:16 PM >

(in reply to lt)
Post #: 12
RE: Create computer collection based on AD user group m... - 6/19/2008 1:48:19 PM   
pwstrain

 

Posts: 102
Score: 5
Joined: 3/11/2007
From: Danville, Illinois
Status: offline 		I'm not sure. Assuming you're changing the domainname\group to match yours, it should work.
You could try creating a query that just does the second part :

select distinct UniqueUserName from  SMS_R_User where UserGroupName = "DOMAINNAME\\IT"

and make sure that works.

< Message edited by pwstrain -- 6/19/2008 1:57:53 PM >

_____________________________

My Personal Blog
My Technical Blog

(in reply to lt)
Post #: 13
RE: Create computer collection based on AD user group m... - 6/19/2008 2:03:50 PM   
lt

 

Posts: 13
Score: 0
Joined: 6/19/2008
Status: offline 		Yep that works fine

select distinct UniqueUserName from  SMS_R_User where UserGroupName = "DOMAINNAME\\IT"

it shows me the members.

I wonder if it has somethign to do with G_System_SYSTEM_CONSOLE_USAGE

(in reply to pwstrain)
Post #: 14
RE: Create computer collection based on AD user group m... - 6/19/2008 2:07:41 PM   
lt

 

Posts: 13
Score: 0
Joined: 6/19/2008
Status: offline 		Could it because I am missing a discovery method or something.

I have all the AD methods and Heat beat discovery enabled. All inventory methods enabled.

(in reply to lt)
Post #: 15
RE: Create computer collection based on AD user group m... - 6/19/2008 2:15:47 PM   
pwstrain

 

Posts: 102
Score: 5
Joined: 3/11/2007
From: Danville, Illinois
Status: offline 		I have a view under my SMS DB labeled dbo.b_GS_SYSTEM_CONSOLE_USAGE

_____________________________

My Personal Blog
My Technical Blog

(in reply to lt)
Post #: 16
RE: Create computer collection based on AD user group m... - 6/19/2008 2:17:10 PM   
lt

 

Posts: 13
Score: 0
Joined: 6/19/2008
Status: offline 		Ok I think I found what could be causing this

v_GS_SYSTEM_CONSOLE_USAGE I think is G_System_SYSTEM_CONSOLE_USAGE I did a select all from it and it only has 1 row of data? Why would it be like that? Shouldn't it have all of my users in it?


(in reply to lt)
Post #: 17
RE: Create computer collection based on AD user group m... - 6/19/2008 2:21:12 PM   
pwstrain

 

Posts: 102
Score: 5
Joined: 3/11/2007
From: Danville, Illinois
Status: offline 		There should be a row for each resource.id.
I do not know why this would not be populated. I assume you've updated all clients to SP3 as well?

(in reply to lt)
Post #: 18
RE: Create computer collection based on AD user group m... - 6/19/2008 2:23:07 PM   
lt

 

Posts: 13
Score: 0
Joined: 6/19/2008
Status: offline 		Are you referring XP SP3? or the sms agent version?

(in reply to pwstrain)
Post #: 19
RE: Create computer collection based on AD user group m... - 6/19/2008 2:26:39 PM   
pwstrain

 

Posts: 102
Score: 5
Joined: 3/11/2007
From: Danville, Illinois
Status: offline 		Sorry, the SMS Client version.

_____________________________

My Personal Blog
My Technical Blog

(in reply to lt)
Post #: 20
RE: Create computer collection based on AD user group m... - 6/19/2008 2:27:52 PM   
lt

 

Posts: 13
Score: 0
Joined: 6/19/2008
Status: offline 		If I check my sms client version under control panel on my system it shows 2.50.4160.2000.

(in reply to lt)
Post #: 21
RE: Create computer collection based on AD user group m... - 6/19/2008 2:49:49 PM   
skissinger


Posts: 1856
Score: 123
Joined: 9/13/2001
From: Sherry Kissinger
Status: offline 		In order for data to be reported, you need to have a group policy in place to record logins into the local EventVwr, Security log.  If you do not have that GPO, there is simply nothing for the TopConsoleUser routine to report from any individual client.  Alternatively, I think Top Console User may only be available if you chose to install SP3 with the Asset Intelligence.  So if you never installed Asset Intelligence, that may be missing as well (?)

And... I'm thinking you aren't on SP3.  That version looks like sms2003 sp2, so you won't have TopConsole User anyway.


< Message edited by skissinger -- 6/19/2008 2:51:55 PM >

_____________________________

mofmaster@smsexpert.com (version 2007) | http://www.smsexpert.com | http://www.sccmexpert.com
Microsoft MVP - ConfigMgr

(in reply to lt)
Post #: 22
RE: Create computer collection based on AD user group m... - 6/20/2008 9:07:59 AM   
lt

 

Posts: 13
Score: 0
Joined: 6/19/2008
Status: offline 		It appears that it may be working but only for computers that have the .3000 version of the client installed. For some reason my client push didn't send out the latest version of the client when I installed SP3 so I am doing a manual push. You also should make sure you have all the AD discovery types enabled when doing this. I will let you guys know how I make out. I need to wait for the inventory agents to collect the info before I can get any meaningful information from my users comps.

Thanks for the help!

(in reply to skissinger)
Post #: 23
RE: Create computer collection based on AD user group m... - 7/3/2008 6:49:35 PM   
turbokitty

 

Posts: 48
Score: 0
Joined: 6/12/2008
Status: offline 		<edit> figured this out.

< Message edited by turbokitty -- 7/4/2008 2:46:12 PM >

(in reply to lt)
Post #: 24
Page:   [1]
All Forums >> [Management Products] >> Microsoft Systems Management Server >> SMS 2003 >> Create computer collection based on AD user group membership Page: [1]
Jump to:





New Messages No New Messages
Hot Topic w/ New Messages Hot Topic w/o New Messages
Locked w/ New Messages Locked w/o New Messages
 Post New Thread
 Reply to Message
 Post New Poll
 Submit Vote
 Delete My Own Post
 Delete My Own Thread
 Rate Posts



  
Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI

0.359