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

[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

[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

[nayan007]

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

[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

[jking · Accepted solution]

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

[mitch_reid]

Thanks!  That was it.

[jbrooks2]

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}]

>>>