4 Replies Latest reply on Nov 7, 2011 4:50 PM by pcrook

    Querying for product versions

    pcrook

      I'm trying to avoid querying for all systems and performing logic outside of the query to get some basic details out of ePO.  For example, if I wanted to return all managed systems that are reporting with a version of VSE < 8.8, I would want to be able to run a query that looked like the following:

      https://<server>:8443/remote/core.executeQuery?target=EPOLeafNode&where=(where(lt EPOProdPropsView_VIRUSCAN.productversion 8.8))

       

      However you cannot perform 'lt' (less than) on anything other than int & longs.  Is there a way to typecast in the query?  If not, is there a way that I can pull the product version such that it doesn't return 8.7.0.570.Srv, for example?

       

      Thanks,

      Parker

        • 1. Re: Querying for product versions
          jking

          Yeah you have to be careful.  Sorry for stepping through maybe TMI here but hopefully it'll help for others as well.

           

          When you look at a table type, you can show the table structure with core.listTable?table=<name> ie:

           

          https://localhost:8443/remote/core.listTables?table=EPOProdPropsView_SITEADVISOR

           

          OK:

          Name: SiteAdvisor Enterprise Plus Properties

          Target: EPOProdPropsView_SITEADVISOR

          Type: join

          Database Type:

          Description: null

          Columns:

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

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

              LeafNodeID          int            false   false      false    true   true  

              ProductPropertiesID int            false   false      false    true   true  

              ProductFamily       string         false   false      false    true   false 

              FamilyDispName      string         false   false      false    true   false 

              ProductCode         string         false   false      false    true   false 

              productversion      productVersion true    true       true     true   false 

              language            string_enum    true    true       true     true   false 

              hotfix              string         true    true       true     true   false 

              servicepack         string         true    true       true     true   false 

          Related Tables:

              Name

              -----------

              EPOLeafNode

          Foreign Keys:

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

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

              EPOProdPropsView_SITEADVISOR LeafNodeID     EPOLeafNode       AutoID              false           true        false 

           

           

          Notice the type of the columns, in this case the productversion column isn't an int type, it's a productVersion type.  What operations are available on that?

           

          https://localhost:8443/remote/core.listDatatypes?type=productVersion

           

          OK:

          Name: productVersion

          Operations:

          Name: version_lt

          Description: version_lt

           

           

          Name: version_eq

          Description: version_eq

           

           

          Name: version_ge

          Description: version_ge

           

           

          Name: version_neq

          Description: version_neq

           

          Since there's a single namespace for operators, the "less than" that works on productVersion type columns is version_lt.

           

          Give that one a shot.

           

          Jon

           

          Message was edited by: jking on 11/7/11 4:24:14 PM CST
          • 2. Re: Querying for product versions
            pcrook

            Jon,

             

            I appreciate the answer... thanks for pointing me to the core.listTables command, especially with an example.  I was actually wondering how to find out more about the data that can be addressed.  So, thanks a ton there.  However... when I try:

             

            https://<server>:8443/remote/core.executeQuery?target=EPOLeafNode&where=(where(version_l t EPOProdPropsView_VIRUSCAN.productversion '8.8'))

             

            I get:

             

            Error 0 :

            com.mcafee.orion.core.query.sexp.SexpProp cannot be cast to com.mcafee.orion.core.query.sexp.SexpString

             

            Message was edited by: pcrook on 11/7/11 4:37:14 PM CST
            • 3. Re: Querying for product versions
              jking

              I find it hard to figure out some of this stuff too, and I have access to the code.  =)  A trick I use is to create a query that does what I want, then export it and look at the xml.  I just did a quicky for this same thing, and got:

               

              <list id="1">

                <query id="2">

                  <name>asdf</name>

                  <description></description>

                  <target>EPOLeafNode</target>

                  <table-uri>query:table?orion.table.columns=EPOLeafNode.LastUpdate%3AEPOLeafNode .NodeName&amp;orion.table.order.by=EPOLeafNode.AgentGUID&amp;orion.table.order=a sc</table-uri>

                  <condition-uri>query:condition?orion.condition.sexp=%28+where+%28+version_lt+EP OProdPropsView_SITEADVISOR.productversion+%228.8%22+%29+%29</condition-uri>

                  <summary-uri>query:summary?orion.sum.query=false&amp;orion.query.type=table.tab le</summary-uri>

                </query>

              </list>

               

              What you care about here is the query:condition s-expression.  Extracted, that is:

               

              %28+where+%28+version_lt+EPOProdPropsView_SITEADVISOR.productversion+%228.8%22+% 29+%29

               

              Which if you unescape it it looks like:

               

              (where (version_lt EPOProdPropsView_SITEADVISOR.productversion "8.8"))

               

              That looks crazily close to what you've posted below.  Leaving the above in though to hopefully help others.

               

              Aha!  It's the single quotes vs. double quotes.  Double quotes works (cut & paste what I did above).  When I change the double quotes to single quotes I got your exact error message.

               

              Jon

               

              PS: Sorry I have SAE installed not VSE on my dev box at the moment so all my examples are SITEADVISOR not VIRUSCAN.

              • 4. Re: Querying for product versions
                pcrook

                Thanks for the quick turn-around Jon.  I was going off of the Scripting Guide, which uses single-quotes for all of the productversion operators: version_lt, version_ge, version_eq, version_neq.

                 

                Might want to get that updated.

                 

                -Parker