Below widgets are counting number of flows per device and per interface. By default, flows are counted per hour (last 4 hours).
Each hour is defined by down sampling to '01:00:00' and it can be easily modified within the query, i.e. '00:15:00' if 15 minutes intervals are required.
Additionally, below where statement provides full hours.
Downsample(TOLOCAL(Timestamp),'01:00:00') >= AddDate('hour', -4, GETDATE())
If a current time is 18:33, then flows are counted as follows (4 full hours and current hour):
1. 13:00 - 14:00
2. 14:00 - 15:00
3. 15:00 - 16:00
4. 16:00 - 17:00
5. 18:00 - 18:33
Number of flows per Interface
SELECT
i.FullName AS [Interface]
,i.detailsurl as [_linkfor_Interface]
,'/Orion/images/StatusIcons/Small-' + i.StatusIcon AS [_IconFor_Interface]
,Downsample(TOLOCAL(flows.Timestamp),'01:00:00') AS [Hourly]
,count(*) as [Hourly Flows]
FROM (
SELECT Timestamp, InterfaceID
FROM Orion.Netflow.FlowsByInterface
WHERE
Downsample(TOLOCAL(Timestamp),'01:00:00') >= AddDate('hour', -4, GETDATE())
) flows
LEFT JOIN Orion.NPM.Interfaces i ON flows.InterfaceID = i.InterfaceID
--WHERE i.FullName like '%${SEARCH_STRING}%'
GROUP BY Downsample(TOLOCAL(flows.Timestamp),'01:00:00'), flows.InterfaceID, i.FullName, i.DetailsUrl, i.StatusIcon
ORDER BY count(*) DESC
Number of flows per Device
SELECT
n.Caption AS [Device]
,n.detailsurl as [_linkfor_Device]
,'/Orion/images/StatusIcons/Small-' + n.StatusIcon AS [_IconFor_Device]
,Downsample(TOLOCAL(flows.Timestamp),'01:00:00') AS [Hourly]
,count(*) as [Hourly Flows]
FROM (
SELECT Timestamp, InterfaceID
FROM Orion.Netflow.FlowsByInterface
WHERE
Downsample(TOLOCAL(Timestamp),'01:00:00') >= AddDate('hour', -4, GETDATE())
) flows
LEFT JOIN Orion.NPM.Interfaces i ON flows.InterfaceID = i.InterfaceID
LEFT JOIN Orion.Nodes n ON n.NodeID = i.NodeID
--WHERE i.FullName like '%${SEARCH_STRING}%'
GROUP BY Downsample(TOLOCAL(flows.Timestamp),'01:00:00'), n.Caption, n.DetailsUrl, n.StatusIcon
ORDER BY count(*) DESC
Kind regards,
Marcin.