Can you give a little more detail about the contents of the query, might help to figure out a way to jam it all together? I would probably build a report/custom swql resource that includes the data set you want and instead of using a send email action I would use the email a web page action and point it at that resource.
Sure, here is the query:
select top 4 concat(ToLocal(f.timestamp) ,' | ',n.nodename, ' | ', f.sourceip, ' | ', f.destinationip, ' | ', round((sum(f.bytes) * 8) / 60 /1000000,2),' | ', max(port)) as lline
from orion.npm.interfaces i
--FROM Orion.Netflow.Flowsbyconversation f
inner join orion.netflow.flowsbyconversation f on f.interfaceidrx = i.interfaceid
inner join orion.nodes n on n.nodeid = i.nodeid
WHERE f.TimeStamp >= GetUtcDate() - 0.0021
and n.nodename='name of switch to evaluate'
group by ToLocal(f.timestamp), f.sourceip, f.destinationip, nodename
having bytes > 1000000
order by round((sum(f.bytes) * 8) / 60 /1000000,2) DESC
And here is the query result example:
05/17/2017 11:11:00 | NORTH | 10.12.2.107 | 10.101.1.20 | 5.17 | 23233
05/17/2017 11:12:00 | NORTH | 10.12.2.5 | 10.101.1.20 | 3.51 | 23233
05/17/2017 11:11:00 | NORTH | 10.12.136.111 | 22.214.171.124 | 2.97 | 443
05/17/2017 11:12:00 | NORTH | 10.12.2.121 | 10.101.1.20 | 2.73 | 23233
I'm wanting to not have to login to orion to get the information on consumers.
I did solve it a different way (would prefer to do it in wsql though)
In the alert trigger action I duplicated the call to the above query 4 times and appended the clause
with rows x to x
to the above query.
(the first one being with rows 1 to 1, the 2nd one being with rows 2 to 2 and so on)
for the specific row I wanted to report. I then got the top 4 consumers.
thanks for the reply!!!