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:


  


AD computer groups for a specific computer

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

Logged in as: Guest
  Printable Version
All Forums >> [Management Products] >> System Center Products >> System Center Configuration Manager >> AD computer groups for a specific computer Page: [1]
Login
Message << Older Topic   Newer Topic >>
AD computer groups for a specific computer - 6/20/2008 9:56:42 AM   
sbremer

 

Posts: 13
Score: 0
Joined: 8/9/2007
Status: offline 		I can't figure out how to write a report that would list all system groups that a specific computer belongs to. Any takers please? Thanks.

Post #: 1
RE: AD computer groups for a specific computer - 6/20/2008 10:42:57 AM  1 votes
jnelson993


Posts: 793
Score: 109
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline 		This should get you started.  In your report, create a prompt for ComputerName, then paste this SQL in.

SELECT DISTINCT
  sys.netbios_name0 AS [Computer Name],
  grp.System_Group_Name0 AS [Group Name]
FROM
  dbo.v_R_System AS sys
  INNER JOIN dbo.v_RA_System_SystemGroupName AS grp
     ON sys.ResourceID = grp.ResourceID
WHERE
  sys.netbios_name0 = @COMPUTERNAME
  AND (sys.decommissioned0 = 0
       AND sys.obsolete0 = 0
       AND sys.client0 = 1)


_____________________________

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

(in reply to sbremer)
Post #: 2
RE: AD computer groups for a specific computer - 6/20/2008 10:51:17 AM   
sbremer

 

Posts: 13
Score: 0
Joined: 8/9/2007
Status: offline 		Perfect. Thanks.


(in reply to jnelson993)
Post #: 3
RE: AD computer groups for a specific computer - 6/20/2008 11:43:44 AM   
sbremer

 

Posts: 13
Score: 0
Joined: 8/9/2007
Status: offline 		So, could you give me code for this one too please:

AD user groups for a specific user (all user groups that the user is a member of)

Thanks


(in reply to sbremer)
Post #: 4
RE: AD computer groups for a specific computer - 6/20/2008 11:50:54 AM  1 votes
jnelson993


Posts: 793
Score: 109
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline 		Sure thing.  It's basically the same thing, but with the user views instead of the system views.  (You have to have user discovery and user group discovery running on the server that feeds the reporting point's DB in order to see this information)
SELECT DISTINCT
sys.netbios_name0 AS [Computer Name],
grp.User_Group_Name0 AS [Group Name]
FROM
dbo.v_R_User AS usr
INNER JOIN dbo.v_RA_User_UserGroupName AS grp
    ON usr.ResourceID = grp.ResourceID
WHERE
usr.Name0 = @USERNAME


_____________________________

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

(in reply to sbremer)
Post #: 5
RE: AD computer groups for a specific computer - 6/20/2008 11:56:16 AM   
sbremer

 

Posts: 13
Score: 0
Joined: 8/9/2007
Status: offline 		Ah. I was still using v_R_System for the user name. Thanks again.

(in reply to jnelson993)
Post #: 6
RE: AD computer groups for a specific computer - 6/20/2008 12:53:28 PM   
sbremer

 

Posts: 13
Score: 0
Joined: 8/9/2007
Status: offline 		I still cannot get this to return anything:

SELECT DISTINCT grp.User_Group_Name0
FROM v_R_User usr
JOIN v_RA_User_UserGroupName grp ON usr.ResourceID = grp.ResourceID
WHERE usr.Name0 like @username

Can you see anything wrong?
I'm on SCCM 2007.

Thanks


(in reply to sbremer)
Post #: 7
RE: AD computer groups for a specific computer - 6/20/2008 1:31:04 PM  1 votes
jnelson993


Posts: 793
Score: 109
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline 		The query looks fine, do you have user and group discovery turned on?  If the records aren't there, it won't show up.

And make sure the username you're entering is right...does it need a domain in front of it?

Run the query like this to get a feel for the data that's in there

SELECT DISTINCT TOP 1000
  usr.name0,
  grp.User_Group_Name0
FROM
  v_R_User usr
JOIN v_RA_User_UserGroupName grp ON usr.ResourceID = grp.ResourceID



_____________________________

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

(in reply to sbremer)
Post #: 8
RE: AD computer groups for a specific computer - 6/20/2008 1:41:56 PM   
sbremer

 

Posts: 13
Score: 0
Joined: 8/9/2007
Status: offline 		That helped. The names were like TASBINC\BremeSki (Skip Bremer) so I changed the last line to:

WHERE usr.Name0 like '%'+@username+'%'

and that fixed it. Probably should be a better way to do this but it works now at least.

Thanks again.


(in reply to jnelson993)
Post #: 9
RE: AD computer groups for a specific computer - 6/20/2008 1:47:12 PM   
sbremer

 

Posts: 13
Score: 0
Joined: 8/9/2007
Status: offline 		Using usr.User_Name0, instead of usr.Name0, got me the simple short name that I wanted.


(in reply to sbremer)
Post #: 10
RE: AD computer groups for a specific computer - 6/20/2008 1:50:05 PM   
jnelson993


Posts: 793
Score: 109
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline 		There are other name fields in that v_R_User view too.  Perhaps one of them would be more suitable?

SELECT TOP 100 *
FROM dbo.v_R_User. 

Look at all of the fields and see if any of them are better for this. 


_____________________________

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

(in reply to sbremer)
Post #: 11
Page:   [1]
All Forums >> [Management Products] >> System Center Products >> System Center Configuration Manager >> AD computer groups for a specific computer 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.234