7 Replies Latest reply on Apr 30, 2012 5:57 PM by jbrooks2

    API Query question

    mitch_reid

      I have this Python query which works:

       

      systems=mc.core.executeQuery(target="EPOLeafNode",

      select="(select EPOComputerProperties.ComputerName EPOComputerProperties.UserProperty1 EPOComputerProperties.ParentID EPOBranchNode.NodeTextPath2)",

      where="(where(and(doesNotHaveTag EPOLeafNode.AppliedTags 17)(doesNotHaveTag EPOLeafNode.AppliedTags 18)))")

       

      I want to add another check to the query to only return results where EPOComputerProperties.UserProperty4 does not equal 'TEST'.

       

      Any help on adding that in?

       

      Thanks

        • 1. Re: API Query question
          Tristan

          No expert but possibly adding the bit in bold below might be what your looking for.

           

           

          systems=mc.core.executeQuery(target="EPOLeafNode",

          select="(select EPOComputerProperties.ComputerName EPOComputerProperties.UserProperty1 EPOComputerProperties.ParentID EPOBranchNode.NodeTextPath2)",

          where="(where(and(doesNotHaveTag EPOLeafNode.AppliedTags 17)(doesNotHaveTag EPOLeafNode.AppliedTags 18)(ne

          EPOComputerProperties.UserProperty4 "TEST" )))")

           

          I can't test exactly as i don't have the same data in my DB but my rough tests seem to work

           

          Message was edited by: Tristan on 24/02/12 17:35:26 GMT
          1 of 1 people found this helpful
          • 2. Re: API Query question
            jking

            Yep Tristan, that should do it.

             

            If you don't mind my adding a little color here ...

             

            You can check the type of a given column by looking at:

             

            https://<machine>:8443/remote/core.listTables?table=EPOComputerProperties

             

            OK:

            Name: Computer Properties

            Target: EPOComputerProperties

            Type: join

            Database Type:

            Description: null

            Columns:

                Name                Type          Select? Condition? GroupBy? Order? Number?

                ------------------- ------------- ------- ---------- -------- ------ -------

                ParentID            int           false   false      false    true   true  

                ComputerName        string        true    true       true     true   false 

                Description         string        true    true       true     true   false 

                SystemDescription   string        true    true       true     true   false 

                TimeZone            string_lookup true    true       true     true   false 

                DefaultLangID       string_enum   true    true       true     true   false 

                UserName            string        true    true       true     true   false 

                DomainName          string_lookup true    true       true     true   false 

                IPHostName          string        true    true       true     true   false 

                IPV6                ipv6          true    true       true     true   false 

                IPAddress           string        false   false      false    true   false 

                IPSubnet            ipv6          true    true       true     true   false 

                IPSubnetMask        ipv6          true    true       true     true   false 

                IPV4x               ipv4          true    true       false    true   false 

                IPXAddress          string        true    true       false    true   false 

                SubnetAddress       string        true    true       true     true   false 

                SubnetMask          string        true    true       true     true   false 

                NetAddress          string        true    true       true     true   false 

                OSType              string_lookup true    true       true     true   false 

                OSVersion           string_lookup true    true       true     true   false 

                OSServicePackVer    string_lookup true    true       true     true   false 

                OSBuildNum          int           true    true       true     true   false 

                OSPlatform          string_lookup true    true       true     true   false 

                OSOEMID             string_lookup true    true       true     true   false 

                CPUType             string_lookup true    true       true     true   false 

                CPUSpeed            int           true    true       false    true   false 

                ManagementType      string_enum   true    true       true     true   false 

                NumOfCPU            int           true    true       true     true   true  

                CPUSerialNum        string        true    true       true     true   false 

                TotalPhysicalMemory bytes         true    true       false    true   true  

                FreeMemory          bytes         true    true       false    true   true  

                FreeDiskSpace       megabytes     true    true       false    true   true  

                TotalDiskSpace      megabytes     true    true       false    true   true  

                IsPortable          enum          true    true       true     true   false 

                OSBitMode           enum          true    true       true     true   false 

                LastAgentHandler    enum          false   false      false    true   false 

                UserProperty1       string        true    true       true     true   false 

                UserProperty2       string        true    true       true     true   false 

                UserProperty3       string        true    true       true     true   false 

                UserProperty4       string        true    true       true     true   false 

                SysvolFreeSpace     megabytes     true    true       true     true   true  

                SysvolTotalSpace    megabytes     true    true       true     true   true  

            Related Tables:

                Name

                ----------------

                EPOAgentHandlers

            Foreign Keys:

                Source table          Source Columns   Destination table Destination columns Allows inverse? One-to-one? Many-to-one?

                --------------------- ---------------- ----------------- ------------------- --------------- ----------- ------------

                EPOComputerProperties LastAgentHandler EPOAgentHandlers  AutoID              true            false       true       

             

            And you can check what operators are available for a column by doing a:

             

            https://<machine>:8443/remote/core.listDatatypes?type=string

             

            OK:

            Name: string

            Operations:

            Name: startsWith

            Description: Starts with

             

             

            Name: ne

            Description: Does not equal

             

             

            Name: contains

            Description: Contains

             

             

            Name: isBlank

            Description: Value is blank

             

             

            Name: endsWith

            Description: Ends with

             

             

            Name: like

            Description: Contains pattern

             

             

            Name: not_isBlank

            Description: Value is not blank

             

             

            Name: eq

            Description: Equals

             

             

            Name: notContains

            Description: Does not contain

             

            Hope that's useful.

            Jon

             

            Message was edited by: jking on 2/24/12 11:49:01 AM CST
            • 3. Re: API Query question

              I will suggest you to get in touch with the technical support team for this issue

              • 4. Re: API Query question
                mitch_reid

                Thanks for helping,  When I run the command I'm getting:

                 

                    where="(where(and(doesNotHaveTag EPOLeafNode.AppliedTags 17)(doesNotHaveTag

                EPOLeafNode.AppliedTags 18)(ne EPOComputerProperties.UserProperty4 "TEST")))")

                 

                                                                                                                                                                    ^  (under TEST)

                SyntaxError: invalid syntax

                • 5. Re: API Query question
                  jking

                  You need to escape the quotes around TEST.  This worked for me ...

                   

                  >>> mc.core.executeQuery(target="EPOLeafNode",where="(where(and(doesNotHaveTag EPOLeafNode.AppliedTags 1)(ne EPOComputerProperties.UserProperty4 \"test\")))")

                  [{u'EPOLeafNode.AgentVersion': None, u'EPOLeafNode.ResortEnabled': False, u'EPOLeafNode.AgentGUID': None, u'EPOLeafNode.SequenceErrorCountLastUpdate': None, u'EPOLeafNode.LastCommSecure': u'0', u'EPOLeafNode.ServerKeyHash': None, u'EPOLeafNode.SequenceErrorCount': 0, u'EPOLeafNode.os': u'|||', u'EPOLeafNode.Tags': u'', u'EPOLeafNode.ManagedState': u'unmanaged', u'EPOLeafNode.NodeName': u'jk-test', u'EPOLeafNode.TransferSiteListsID': False, u'EPOLeafNode.ExcludedTags': u'', u'EPOLeafNode.LastUpdate': None}]

                  >>>

                   

                  Jon

                  • 6. Re: API Query question
                    mitch_reid

                    Thanks!  That was it.

                    • 7. Re: API Query question

                      For what it's worth, in Python you can use either single or double quotes to represent a literal string.  I find that when I have to nest a double quoted string with a hard coded string it helps to use a single quoted string and then I can use double quotes within the string w/o having to escape them.  For your example, you could avoid this by using single quotes and then you wouldn't have to escape the quotes within the where parameter:

                       

                      >>> mc.core.executeQuery(target='EPOLeafNode',where='(where(and(doesNotHaveTag EPOLeafNode.AppliedTags 1)(ne EPOComputerProperties.UserProperty4 "test")))')

                      [{u'EPOLeafNode.AgentVersion': None, u'EPOLeafNode.ResortEnabled': False, u'EPOLeafNode.AgentGUID': None, u'EPOLeafNode.SequenceErrorCountLastUpdate': None, u'EPOLeafNode.LastCommSecure': u'0', u'EPOLeafNode.ServerKeyHash': None, u'EPOLeafNode.SequenceErrorCount': 0, u'EPOLeafNode.os': u'|||', u'EPOLeafNode.Tags': u'', u'EPOLeafNode.ManagedState': u'unmanaged', u'EPOLeafNode.NodeName': u'jk-test', u'EPOLeafNode.TransferSiteListsID': False, u'EPOLeafNode.ExcludedTags': u'', u'EPOLeafNode.LastUpdate': None}]

                      >>>