Joining system and user resource queries (Full Version)

All Forums >> [Management Products] >> Microsoft Systems Management Server >> SMS 2003



Message

rastagusta -> Joining system and user resource queries (6/5/2008 1:53:35 PM)

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



skissinger -> RE: Joining system and user resource queries (6/5/2008 4:49:26 PM)

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)



jnelson993 -> RE: Joining system and user resource queries (6/5/2008 4:59:20 PM)

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>



jnelson993 -> RE: Joining system and user resource queries (6/5/2008 5:01:10 PM)

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>")



skissinger -> RE: Joining system and user resource queries (6/5/2008 5:41:28 PM)

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



jnelson993 -> RE: Joining system and user resource queries (6/5/2008 11:22:29 PM)

Interesting...I do the same thing and I get invalid query...I move that to a subselect and it's fine.



jnelson993 -> RE: Joining system and user resource queries (6/5/2008 11:28:58 PM)

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.



rastagusta -> RE: Joining system and user resource queries (6/9/2008 12:34:35 AM)

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
 



jnelson993 -> RE: Joining system and user resource queries (6/9/2008 10:40:08 AM)

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")





Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.1875