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:


  


Joining system and user resource queries

 
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 >> Joining system and user resource queries Page: [1]
Login
Message << Older Topic   Newer Topic >>
Joining system and user resource queries - 6/5/2008 1:53:35 PM   
rastagusta

 

Posts: 3
Score: 0
Joined: 6/5/2008
Status: offline 		Hi,
Im pretty much a newbie here. Anyway, id just need to know if its possible to join 2 queries. 1 for system resource whereby i need the info for Computer Name, SMS Advanced Client status etc. I also need the user OU name in my same query but this can only be done using User Resource. Is there a way to merge both these queries into 1 so that i can retrieve the relevant data for roll-outs. Currently if i choose User Resource, there is no option to add system resource and thus cant complete a relevant query ?.....Can this be done ?....If yes, pls help urgently !...Thanks
Post #: 1
RE: Joining system and user resource queries - 6/5/2008 4:49:26 PM   
skissinger


Posts: 1857
Score: 123
Joined: 9/13/2001
From: Sherry Kissinger
Status: offline 		Sort of.  You could try for join statements the following.  Keeping in mind that sometimes the data in "Last Known user" in the v_r_system table may not be accurate 100% of the time. 

select sys.netbios_name0, USR.User_Name0, USR.Directory_Location0
from v_r_system as SYS
left join v_r_user as USR on sys.User_Name0 = USR.User_Name0

If you're on SP3, and if you've enabled ConsoleUser stuff; you might also be able to try

select sys.netbios_name0, USR.User_Name0, USR.Directory_Location0
from v_r_system as SYS
join v_gs_system_console_user as SUSR on sys.resourceID = SUSR.ResourceID
join v_r_user as USR on SUSR.TopConsoleUser0 = USR.User_Name0

NOTE: I didn't actually test either of these... they might be junk. (probably are)

_____________________________

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

(in reply to rastagusta)
Post #: 2
RE: Joining system and user resource queries - 6/5/2008 4:59:20 PM   
jnelson993


Posts: 731
Score: 91
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline 		Yeah, I don't think those are going to work.  I believe you can't join user resources with system resources in the FROM, but you can use subselects to include system and user resources in the same query.

In other words, instead of joining SYS to USR, leave SYS in the main query and put a subselect in the WHERE clause to find only the users for a particular OU

<snip>

< Message edited by jnelson993 -- 6/6/2008 10:16:06 AM >

_____________________________

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

(in reply to skissinger)
Post #: 3
RE: Joining system and user resource queries - 6/5/2008 5:01:10 PM   
jnelson993


Posts: 731
Score: 91
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline 		Sorry, posted the wrong version
SELECT
  sys.ResourceID,
  sys.ResourceType,
  sys.Name,
  sys.SMSUniqueIdentifier,
  sys.ResourceDomainORWorkgroup,
  sys.Client
FROM  
  SMS_R_System AS sys
WHERE 
  (sys.Obsolete = 0 AND sys.Decommissioned = 0 AND sys.Client = 1)
  AND sys.LastLogonUserName IN (SELECT UniqueUserName FROM SMS_R_User WHERE UserOUName = "<YOUR_OU_HERE>")


_____________________________

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

(in reply to jnelson993)
Post #: 4
RE: Joining system and user resource queries - 6/5/2008 5:41:28 PM   
skissinger


Posts: 1857
Score: 123
Joined: 9/13/2001
From: Sherry Kissinger
Status: offline 		Well, maybe it's not supposed to work; but this worked for me (sort of)--as a report, not a collection query.  I got a LOT of empty usernames & Dir Locations, because there wasn't a value in last logged in user in v_gs_system; but it might be better than nothin' (The system console usage view didn't work out for me; but then again I'm using SLAT). 

select distinct sys.netbios_name0, USR.User_Name0, USR.Directory_Location0
from v_r_system as SYS
left join v_r_user as USR on sys.User_Name0 = USR.User_Name0

_____________________________

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

(in reply to jnelson993)
Post #: 5
RE: Joining system and user resource queries - 6/5/2008 11:22:29 PM   
jnelson993


Posts: 731
Score: 91
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline 		Interesting...I do the same thing and I get invalid query...I move that to a subselect and it's fine.

_____________________________

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

(in reply to skissinger)
Post #: 6
RE: Joining system and user resource queries - 6/5/2008 11:28:58 PM   
jnelson993


Posts: 731
Score: 91
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline 		Wait, so Sherry, you're talking about web report SQL aren't you...OK, that makes sense.  Yes, that's just fine joining all kinds of data together.  Rastagusta was doing it in SMS Queries so I was responding to that.

So, rastagusta, does it have to be an SMS Query (WQL) or can it be a web report (SQL)?  Cuz if it's web report we're golden and you can use Sherry's code, if you need to create an SMS Query or collection, you'll have to use the WQL I posted.


_____________________________

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

(in reply to jnelson993)
Post #: 7
RE: Joining system and user resource queries - 6/9/2008 12:34:35 AM   
rastagusta

 

Posts: 3
Score: 0
Joined: 6/5/2008
Status: offline 		Hi jnelson,
I had managed to set the sms query as per your advice. However, when i do a run query, nothing seems to appear.Would appreciate if you could advice me as to what i could do to diagnose or something ?..I have pasted the script as per below. Thanks alot.

select Name, ResourceId, ResourceType, SMSUniqueIdentifier, ResourceDomainORWorkgroup, Client from  SMS_R_System as sys where Obsolete = 0 and Decommissioned = 0 and Client = 1 and LastLogonUserName in (SELECT UniqueUserName FROM SMS_R_User WHERE UserOUName = "ENO-PROD.SG/ENO/INFOD/IIO")
 
Regards
 

(in reply to jnelson993)
Post #: 8
RE: Joining system and user resource queries - 6/9/2008 10:40:08 AM   
jnelson993


Posts: 731
Score: 91
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline 		Dunno, that works for me when I put my OUs in there...Could be that you don't have the user tables populated with AD user discovery, or the way your last logon user shows up in SMS_R_System may be differently than ours does or the UniqueUserName shows up differently (we do our own custom user discovery using VBScript and the domain comes in front of the user name so we need to go to UniqueUserName to join SMS_R_System to SMS_R_User)

Try using UserName instead of UniqueUserName in that subquery:

select Name, ResourceId, ResourceType, SMSUniqueIdentifier, ResourceDomainORWorkgroup, Client from  SMS_R_System as sys where Obsolete = 0 and Decommissioned = 0 and Client = 1 and LastLogonUserName in (SELECT UserName FROM SMS_R_User WHERE UserOUName = "ENO-PROD.SG/ENO/INFOD/IIO")




_____________________________

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

(in reply to rastagusta)
Post #: 9
Page:   [1]
All Forums >> [Management Products] >> Microsoft Systems Management Server >> SMS 2003 >> Joining system and user resource queries 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.719