myITforum and Windows IT Pro Forums

 Joining system and user resource queries

Author Message
rastagusta

  • Total Posts : 3
  • Scores: 0
  • Reward points : 0
  • Joined: 6/5/2008
  • Status: offline
Joining system and user resource queries Thursday, June 05, 2008 1:53 PM (permalink)
0
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
 
#1
    skissinger

    • Total Posts : 4922
    • Scores: 472
    • Reward points : 135220
    • Joined: 9/13/2001
    • Location: Sherry Kissinger
    • Status: offline
    RE: Joining system and user resource queries Thursday, June 05, 2008 4:49 PM (permalink)
    0
    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@myitforum.com
    My Blog
    Microsoft MVP - ConfigMgr
     
    #2
      jnelson993

      • Total Posts : 931
      • Scores: 159
      • Reward points : 8850
      • Joined: 2/18/2005
      • Location: Minneapolis, MN
      • Status: offline
      RE: Joining system and user resource queries Thursday, June 05, 2008 4:59 PM (permalink)
      0
      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 on Friday, June 06, 2008 10:16 AM>
       
      #3
        jnelson993

        • Total Posts : 931
        • Scores: 159
        • Reward points : 8850
        • Joined: 2/18/2005
        • Location: Minneapolis, MN
        • Status: offline
        RE: Joining system and user resource queries Thursday, June 05, 2008 5:01 PM (permalink)
        0
        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>")

         
        #4
          skissinger

          • Total Posts : 4922
          • Scores: 472
          • Reward points : 135220
          • Joined: 9/13/2001
          • Location: Sherry Kissinger
          • Status: offline
          RE: Joining system and user resource queries Thursday, June 05, 2008 5:41 PM (permalink)
          0
          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@myitforum.com
          My Blog
          Microsoft MVP - ConfigMgr
           
          #5
            jnelson993

            • Total Posts : 931
            • Scores: 159
            • Reward points : 8850
            • Joined: 2/18/2005
            • Location: Minneapolis, MN
            • Status: offline
            RE: Joining system and user resource queries Thursday, June 05, 2008 11:22 PM (permalink)
            0
            Interesting...I do the same thing and I get invalid query...I move that to a subselect and it's fine.
             
            #6
              jnelson993

              • Total Posts : 931
              • Scores: 159
              • Reward points : 8850
              • Joined: 2/18/2005
              • Location: Minneapolis, MN
              • Status: offline
              RE: Joining system and user resource queries Thursday, June 05, 2008 11:28 PM (permalink)
              0
              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.
               
              #7
                rastagusta

                • Total Posts : 3
                • Scores: 0
                • Reward points : 0
                • Joined: 6/5/2008
                • Status: offline
                RE: Joining system and user resource queries Monday, June 09, 2008 12:34 AM (permalink)
                0
                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
                 
                 
                #8
                  jnelson993

                  • Total Posts : 931
                  • Scores: 159
                  • Reward points : 8850
                  • Joined: 2/18/2005
                  • Location: Minneapolis, MN
                  • Status: offline
                  RE: Joining system and user resource queries Monday, June 09, 2008 10:40 AM (permalink)
                  0
                  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")


                   
                  #9
                    Online Bookmarks Sharing: Share/Bookmark

                    Jump to:

                    Current active users

                    There are 0 members and 1 guests.

                    Icon Legend and Permission

                    • 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
                    • Read Message
                    • Post New Thread
                    • Reply to message
                    • Post New Poll
                    • Submit Vote
                    • Post reward post
                    • Delete my own posts
                    • Delete my own threads
                    • Rate post

                    2000-2014 ASPPlayground.NET Forum Version 3.9