5 Replies Latest reply on Aug 26, 2014 5:04 AM by sml1538

    Querying on a Group "Members" Field

    Apprentice

      I am trying to do some querying on the "Members" field (for example, Local Users and Groups > Local Groups > Administrators) but I am running into some trouble.  I'd like to construct a query that returns all locally-created accounts, not domain accounts.  If I have a system that has "hostname123\Administrator; domain.com\Domain Admins; hostname123\user1" I'd like this to be returned in the results.  However, if a system has "hostname456\Administrator; domain.com\Domain Admins; domain.com\user9" it should not be returned.

       

      I think I'd be able to figure it out if I could use the "Device Name" as a variable in a query.  Any thoughts or other guidance on how to achieve this would be welcome!  I am thinking this might be easier done in SQL but I'm still in the beginning stages of learning SQL.

        • 1. Re: Querying on a Group "Members" Field
          Gjstewart Specialist

          From what I can see the problem is splitting the 'members' up.

           

          If you just want a query for all local users - you choose

           

          Local Users and Groups > Local User Accounts > Name - Exists.

          Then add the Local Users and Groups > Local User Accounts > Name    column to the Query.

           

          if you want the members its

          Local Users and Groups > Local Groups > Members -

          but i tried putting Not Like - Domain\  -

          then adding the Local Users and Groups > Local Groups > Members - column to the Query

           

          This however as the members are returned in a string it wont show you any machines with any Domain accounts in which is not what you want.

           

          You need to do a SQL query to look at the string then split it by the delimiters ; and strip out all Domain\ before returning the results..

           

          I am trying something now.... if there are any advanced DBAs who can help great.

          • 2. Re: Querying on a Group "Members" Field
            Apprentice

            Thank you.

             

            I've had some amount of luck doing 'not like' on a string and putting an asterisk in front, like this: "*\Administrator; domain.com\Domain Admins".  I read somewhere that an asterisk is like a wildcard - and it works that way, partially.  The problem is that if I were to turn this into a report, LANDesk says there is bad code (or something like that).

             

            Yes, I was afraid that I would need to do this in SQL.  I can probably get exactly what I need that way... if I knew SQL.  :-)  If you have any guidance on that front, it would be hugely welcome.

             

            (Is there a piece in the community that deals with specifically SQL stuff, with regards to the LANDesk database?)

            • 3. Re: Querying on a Group "Members" Field
              Apprentice

              This is still an open question.  I guess I'm looking to find out possible ways to use SQL to query on the "Members" field (Local Users and Groups -> Local Groups -> <group name> -> Members).  The end result is to find all systems with locally-created accounts.  Thanks!

              • 4. Re: Querying on a Group "Members" Field
                saeidans Apprentice

                I think this should do it;

                 

                Computer > Local Users and Groups > Name = Administrators

                • 5. Re: Querying on a Group "Members" Field
                  Apprentice

                  The answers for this question can be found in a related thread:

                   

                  SQL Query on LocalGroups Members

                   

                  It all comes down to using SQL to parse the "Members" field of a group name (Administrators).