0 Replies Latest reply on Jan 24, 2015 10:56 PM by theglot

    Custom XLM query or View to add more filter options not available in the Query Builder (Users on Hosts >=2)

    theglot

      So I have a requirement to show a dashboard query where a user is logged onto more than one host at a time.  Got the query buildt to so every user and what hosts they are logged into, but I'm having trouble on how to get the query to only show where the results at 2 or more hosts.

       

      I see two options:

      1- manually modify the XLM and then import it back into the ePO

      2-create a custom view and then write the XLM to call that view and import the XLM into the epo

       

      All I need is a "where count (users) =>2  but stumped on the best way to do it.  I need to change out the Where in the example below, but stumped.

       

      anyone know of a good XLM to SQL translator?

       

       

      SQL is easy, but epo doen'st execp  SLQ

      select count(*) as 'count', [EPOComputerProperties].[UserName], [EPOComputerProperties].[ComputerName], [EPOComputerProperties].[UserName], [EPOComputerProperties].[ComputerName] from [EPOLeafNode] left join [EPOComputerProperties] on [EPOLeafNode].[AutoID] = [EPOComputerProperties].[ParentID] where ( [EPOComputerProperties].[UserName] is null or ( [EPOComputerProperties].[UserName] <> N'g' )) group by [EPOComputerProperties].[UserName], [EPOComputerProperties].[ComputerName] order by [EPOComputerProperties].[UserName] asc, [EPOComputerProperties].[ComputerName] asc

       


      XLM

      <?xml version="1.0"?>

      -<list id="1">

      -<query id="2">

      <dictionary id="3"/>

      <name>Logged in Users</name>

      <description>show the logged in users by computer. MAG 231914JAN15</description>

      <target>EPOLeafNode</target>

      <table-uri>query:table?orion.table.columns=EPOLeafNode.LastUpdate%3AEPOComputerP roperties.UserName%3AEPOComputerProperties.ComputerName&orion.table.order=az&ori on.table.order.by=EPOLeafNode.LastUpdate%3AEPOComputerProperties.UserName%3AEPOC omputerProperties.ComputerName</table-uri>

      <condition-uri>query:condition?orion.condition.sexp=%28+where+%28+ne+EPOComputer Properties.UserName+%22g%22+%29+%29</condition-uri>

      <summary-uri>query:summary?orion.sum.query=true&orion.query.type=summary.multigr oup&orion.sum.group.by=EPOComputerProperties.UserName%3AEPOComputerProperties.Co mputerName&orion.sum.order=az%3Aaz&orion.sum.aggregation=count&orion.sum.aggrega tion.showTotal=true</summary-uri>

      </query>

      </list>