4 Replies Latest reply on Feb 11, 2008 11:17 AM by metalhead

    Move machines to new System Tree group based on Query?

      All-

      I have one gigantic problem with ePO 4.0; and it makes my job as the VScan Administrator a massive headache. As far as I know what I am trying to is completely impossible to do in the current iteration of ePO 4.0.

      Here is what I want to do: I want to have an automated query that runs everyday which finds all machines with 10 or more infections in the last 24 hours; and then based on the results of that query move the machines into a System Tree group I dub 'Quarantine'. This mode forces a bit more restrictive settings on the machine and forces a full scan.

      I can easily get a query output correct, however the automated task to take that output and move machines fails because the output has more then one entry for each machine. It sees duplicate names and just fails. I thought maybe I could get around this by using a Summary Table, but the automated task will not accept a summary table output to move machines.

      Shouldn't this be an easy task? Instead I have to run the query and move each machine by hand; which in an environment of over 2500 machines takes way too much of my daily time. Not to mention if there was a large outbreak between 50 or so machines.


      I'm really frustrated by this... does anyone have any solution?

      -tempie23
        • 1. RE: Move machines to new System Tree group based on Query?
          metalhead
          Can you edit the SQL expression for your query directly and put an "GROUP BY systemname" at the end ?
          Another try would be to put "DISTINCT" after the "SELECT" command.
          • 2. How would you do this?
            Tom-

            I have to say, I'm pretty ignorant when it comes down to the SQL query level... is there a document on proper syntax?

            Also how exactly do you go about editing the actual SQL query? I assume I would have to export the query in XML, modify it, and then reinsert it?

            Here is my exported query which produces a table of Host Names that have been infected with Qhosts.apd (a Trojan hijack to modify the hosts file) within the last 5 days:

            <queries>
            <query>
            <name language="en">VSE: Hosts detected with specific infections within the last 5 days (Test)</name>
            <description language="en">Query produces table of Computer Names for use with the Automated Task</description>
            <property name="target">EPOEvents</property>
            <property name="tableURI">query:table?orion.table.columns=EPOEvents.TargetHostName&amp;or ion.table.order=az&amp;orion.table.order.by=EPOEvents.TargetHostName</property>
            <property name="conditionURI">query:condition?orion.condition.sexp=%28+where+%28+and+%28+ eq+EPOEvents.ThreatName+%22Qhosts.apd%22+%29+%28+newerThan+EPOEvents.ReceivedUTC +432000000++%29+%29+%29</property>
            <property name="summaryURI">query:summary?orion.chart.type=table&amp;orion.sum.query=fals e</property>
            </query>
            </queries>

            Thanks,
            tempie23
            • 3. RE: How would you do this?
              metalhead
              As I reviewed this you cannot do it this way because the DISTINCT command has to be placed before the column in the SELECT statement. I have not found out by now how this is generated by ePO.

              Perhaps someone else ?
              • 4. RE: How would you do this?
                metalhead
                Perhaps try editing this and reimport the query:

                <property name="tableURI">query:table?orion.table.columns=EP OEvents.TargetHostName&amp;

                changed to

                <property name="tableURI">query:table?orion.table.columns=DISTINCT EPOEvents.TargetHostName&amp;

                No guarantee happy