6 Replies Latest reply on Oct 30, 2009 5:51 AM by valiman

    EPO 4.0 SQL Queries

      When I administered our EPO 3.x servers I was able to create my own SQL queries and have them display the data I wanted. I really don't like the query builder within EPO 4.0 as for most queries I try to perform the filter mechanism doesn't work. Is it possible to create your own queries and import them? There is an import query wizard which doesn't appear to like .sql files.

      Any help would be great.
        • 1. RE: EPO 4.0 SQL Queries
          It uses XML. Export an existing query and open it in a text editor to see how it does it.

          <queries>
          <query>
          <name language="en"></name>
          <description language="en"></description>
          <property name="target"></property>
          <property name="tableURI">q</property>
          <property name="conditionURI"></property>
          <property name="summaryURI"></property>
          </query>
          </queries>
          • 2. RE: EPO 4.0 SQL Queries
            Thanks for that Jim. Am I the only one who thinks that this is a hell of a messy implementation of XML? Is there any documentation anywhere that will make it easier to build custom queries?

            I've basically spent today trying to find some logic within this and i've not really got very far. Ran into alot of errors.

            To start off I built a basic query using the wizard then exported it to xml, opened the xml file into notepad++

            <queries>
            <query>
            <name language="en">List Current DAT Versions (VSE Enterprise)2</name>
            <description language="en"></description>
            <property name="target">EPOLeafNode</property>
            <property name="tableURI">query:table?orion.table.columns=EPOLeafNode.NodeName%3AEPOProdPropsView_VIRUSCAN.datver&amp;orion.table.order.by=EPOLeafNode.NodeName%3AEPOProdPropsView_VIRUSCAN.datver&amp;orion.table.order=az</property>
            <property name="conditionURI">query:condition?orion.condition.sexp=</property>
            <property name="summaryURI">query:summary?orion.chart.type=table&amp;orion.sum.query=false</property>
            </query>
            </queries>


            Here I have a simple query returning machine name, and DAT Version. I trying to introduce a Like statement in here for example,

            where [EPOLeafNode].[NodeName] Like 'NAM%'

            The full SQL for this query in EPO would be

            select [EPOLeafNode].[NodeName], [EPOProdPropsView_VIRUSCAN].[datver],
            [EPOLeafNode].[AutoID] from [EPOLeafNode] left join [EPOProdPropsView_VIRUSCAN]
            on [EPOLeafNode].[AutoID] = [EPOProdPropsView_VIRUSCAN].[LeafNodeID]
            where [EPOLeafNode].[NodeName] Like 'NAM%'
            order by [EPOLeafNode].[NodeName] asc



            When I try to include the Like statement in the XML I get errors, either when importing the file or when trying to run the query. I think it's tripping over the ' or the %.

            So far I've gathered that

            %28 = (
            %29 = )
            + = space
            %3A = , (maybe??)
            &amp = &

            Thanks

            Scott
            • 3. RE: EPO 4.0 SQL Queries
              I agree, it's a mess. It's a shame because custom queries would be even more useful now that they're actionable.

              If there are docs available I haven't found them.
              • 4. RE: EPO 4.0 SQL Queries
                It's clearly some sort of weird object query language. Anyway, is this anything like what you were after?

                <queries>
                <query>
                <name language="en">Test Query</name>
                <description language="en">This OQL is a bag o' shite</description>
                <property name="target">EPOLeafNode</property>
                <property name="tableURI">query:table?orion.table.columns=EPOLeafNode.NodeName%3AEPOProdPropsView_VIRUSCAN.datver&amp;orion.table.order=az&amp;orion.table.order.by=EPOLeafNode.NodeName%3AEPOProdPropsView_VIRUSCAN.datver</property>
                <property name="conditionURI">query:condition?orion.condition.sexp=%28+where+%28+startsWith+EPOLeafNode.NodeName+%22NAM%22+%29+%29</property>
                <property name="summaryURI">query:summary?orion.chart.type=table&amp;orion.sum.query=false</property>
                </query>
                </queries>
                • 5. RE: EPO 4.0 SQL Queries
                  That's awesome Jim, Thanks.

                  I've asked support if they can get a hold of any documentation for this so hopefully they'll come through with it and all will be well happy
                  • 6. RE: EPO 4.0 SQL Queries
                    Hi.
                    I need help to transform this SQl command to an xml file

                    select MAX([EPOProdPropsView_VIRUSCAN].[datver]) as 'Version' from [EPOProdPropsView_VIRUSCAN]

                    Can anyone to help me?

                    Thanks.