Hello,
We have moved off Manage Engine NetFlow over to SolarWinds NetFlow. With the previous product, we had a monthly report that would report on speed in/out, 95th percentile, and cost breakdown of traffic for certain customers (IP Groups) that we charge back usage on a monthly basis. We have the below query from an older post that pulls the 95th percentile for the past 7 days, of course we cannot get it to work in 2023.1 HCO
SELECT
-- GroupID,
GroupName,
-- NodeID,
NodeName,
Vendor,
Icon,
--InterfaceID,
InterfaceName,
InterfaceType,
interfaceIcon,
(TotalBytesIngress * 8/86400) AS TotalIngress95Bps,
(TotalBytesEgress * 8/86400) AS TotalEgress95Bps,
(MaxTotalBytes * 8/86400) AS MaxTotal95Bps,
(TotalBytesSourceIngress * 8/86400) AS TotalSourceIngress95Bps,
(TotalBytesSourceEgress * 8/86400) AS TotalSourceEgress95Bps,
(TotalBytesDestinationIngress * 8/86400) AS TotalDestinationIngress95Bps,
(TotalBytesDestinationEgress * 8/86400) AS TotalDestinationEgress95Bps
FROM (
SELECT
GroupID,
GroupName,
NodeID,
NodeName,
Vendor,
Icon,
InterfaceID,
InterfaceName,
InterfaceType ,
interfaceIcon
FROM (
SELECT
NodeID,
nodes.Caption AS NodeName,
nodes.Vendor,
nodes.Icon,
InterfaceID,
interfaces.Caption AS InterfaceName,
interfaces.InterfaceType ,
interfaces.icon AS interfaceIcon,
IpGroupsBySegments.IPGroupID AS GroupID,
IPAddressGroups.Name AS GroupName
FROM
Orion.Netflow.FlowsByInterface Flows
JOIN Orion.Netflow.IpGroupsBySegments IpGroupsBySegments ON IpGroupsBySegments.SourceIPSegmentID = Flows.SourceIPGroupSegmentID
AND IpGroupsBySegments.DestinationIPSegmentID = Flows.DestinationIPGroupSegmentID
JOIN Orion.Netflow.IPAddressGroups IPAddressGroups ON IPAddressGroups.IPAddressGroupID=IpGroupsBySegments.IPGroupID
JOIN Orion.Nodes AS nodes ON nodes.NodeID=Flows.NodeID
JOIN Orion.NPM.Interfaces AS interfaces ON interfaces.interfaceID=Flows.interfaceID
WHERE TimeStamp >= AddWeek(-7,GetUtcDate())
AND TimeStamp < GetUtcDate()
GROUP BY
Flows.SourceIPGroupSegmentID,
Flows.DestinationIPGroupSegmentID,
IpGroupsBySegments.IPGroupID,
IpGroupsBySegments.DisplayName,
NodeID,
nodes.Caption,
interfaces.Caption,
InterfaceID)
GROUP BY
GroupID,
GroupName,
InterfaceID,
Nodeid) AS A
LEFT JOIN (
SELECT
MAX(TotalBytesIngress) AS totalbytesIngress,
NodeID,
InterfaceID,
GroupID
FROM (
SELECT
TOP 95 PERCENT TotalBytesIngress,
NodeID,
InterfaceID,
GroupID
FROM (
SELECT
NodeID,
InterfaceID,
GroupID,
Timestamp,
SUM(TotalBytesIngress) AS TotalBytesIngress
FROM (
SELECT
NodeID,
InterfaceID,
IpGroupsBySegments.IPGroupID AS GroupID,
SUM(IngressBytes) AS TotalBytesIngress,
DateTrunc ('Day',
TimeStamp) AS TimeStamp
FROM
Orion.Netflow.FlowsByInterface AS Flows
JOIN Orion.Netflow.IpGroupsBySegments IpGroupsBySegments ON IpGroupsBySegments.SourceIPSegmentID = Flows.SourceIPGroupSegmentID
AND IpGroupsBySegments.DestinationIPSegmentID = Flows.DestinationIPGroupSegmentID
WHERE TimeStamp >= AddWeek(-7,GetUtcDate())
AND TimeStamp < GetUtcDate()
GROUP BY
DateTrunc ('Day',
TimeStamp),
IpGroupsBySegments.IPGroupID,
NodeID,
InterfaceID) AS FF
GROUP BY
GroupID,
InterfaceID,
NodeID,
TimeStamp) AS B
ORDER BY
TotalBytesIngress ASC) AS C
GROUP BY
GroupID,
InterfaceID,
NodeID) AS B0 ON (B0.GroupID = A.GroupID
AND B0.InterfaceID = A.InterfaceID
AND B0.NodeID = A.NodeID)
LEFT JOIN (
SELECT
MAX(TotalBytesEgress) AS totalbytesEgress,
NodeID,
InterfaceID,
GroupID
FROM (
SELECT
TOP 95 PERCENT TotalBytesEgress,
NodeID,
InterfaceID,
GroupID
FROM (
SELECT
NodeID,
InterfaceID,
GroupID,
Timestamp,
SUM(TotalBytesEgress) AS TotalBytesEgress
FROM (
SELECT
NodeID,
InterfaceID,
IpGroupsBySegments.IPGroupID AS GroupID,
SUM(EgressBytes) AS TotalBytesEgress,
DateTrunc ('Day',
TimeStamp) AS TimeStamp
FROM
Orion.Netflow.FlowsByInterface AS Flows
JOIN Orion.Netflow.IpGroupsBySegments IpGroupsBySegments ON IpGroupsBySegments.SourceIPSegmentID = Flows.SourceIPGroupSegmentID
AND IpGroupsBySegments.DestinationIPSegmentID = Flows.DestinationIPGroupSegmentID
WHERE TimeStamp >= AddWeek(-7,GetUtcDate())
AND TimeStamp < GetUtcDate()
GROUP BY
DateTrunc ('Day',
TimeStamp),
IpGroupsBySegments.IPGroupID,
NodeID,
InterfaceID) AS FF
GROUP BY
GroupID,
InterfaceID,
NodeID,
TimeStamp) AS B
ORDER BY
TotalBytesEgress ASC) AS C
GROUP BY
GroupID,
InterfaceID,
NodeID) AS B1 ON (B1.GroupID = A.GroupID
AND B1.InterfaceID = A.InterfaceID
AND B1.NodeID = A.NodeID)
LEFT JOIN (
SELECT
MAX(MaxTotalBytes) AS MaxTotalBytes,
NodeID,
InterfaceID,
GroupID
FROM (
SELECT
TOP 95 PERCENT MaxTotalBytes,
NodeID,
InterfaceID,
GroupID
FROM (
SELECT
NodeID,
InterfaceID,
GroupID,
Timestamp,
SUM(CASE WHEN TotalBytesEgress > TotalBytesIngress THEN TotalBytesEgress ELSE TotalBytesIngress END) AS MaxTotalBytes
FROM (
SELECT
NodeID,
InterfaceID,
IpGroupsBySegments.IPGroupID AS GroupID,
SUM(IngressBytes) AS TotalBytesIngress,
SUM(EgressBytes) AS TotalBytesEgress,
DateTrunc ('Day',
TimeStamp) AS TimeStamp
FROM
Orion.Netflow.FlowsByInterface AS Flows
JOIN Orion.Netflow.IpGroupsBySegments IpGroupsBySegments ON IpGroupsBySegments.SourceIPSegmentID = Flows.SourceIPGroupSegmentID
AND IpGroupsBySegments.DestinationIPSegmentID = Flows.DestinationIPGroupSegmentID
WHERE TimeStamp >= AddWeek(-7,GetUtcDate())
AND TimeStamp < GetUtcDate()
GROUP BY
DateTrunc ('Day',
TimeStamp),
IpGroupsBySegments.IPGroupID,
NodeID,
InterfaceID) AS FF
GROUP BY
GroupID,
InterfaceID,
NodeID,
TimeStamp) AS B
ORDER BY
MaxTotalBytes ASC) AS C
GROUP BY
GroupID,
InterfaceID,
NodeID) AS B2 ON (B2.GroupID = A.GroupID
AND B2.InterfaceID = A.InterfaceID
AND B2.NodeID = A.NodeID)
LEFT JOIN (
SELECT
MAX(TotalBytesSourceIngress) AS TotalBytesSourceIngress,
NodeID,
InterfaceID,
GroupID
FROM (
SELECT
TOP 95 PERCENT TotalBytesSourceIngress,
NodeID,
InterfaceID,
GroupID
FROM (
SELECT
NodeID,
InterfaceID,
GroupID,
Timestamp,
SUM(TotalBytesIngress) AS TotalBytesSourceIngress
FROM (
SELECT
NodeID,
InterfaceID,
IpGroupSegments.IPGroupID AS GroupID,
SUM(IngressBytes) AS TotalBytesIngress,
SUM(EgressBytes) AS TotalBytesEgress,
DateTrunc ('Day',
TimeStamp) AS TimeStamp
FROM
Orion.Netflow.FlowsByInterface AS Flows
JOIN Orion.Netflow.IpGroupSegments IpGroupSegments ON IpGroupSegments.IPSegmentID = Flows.SourceIPGroupSegmentID
WHERE TimeStamp >= AddWeek(-7,GetUtcDate())
AND TimeStamp < GetUtcDate()
GROUP BY
DateTrunc ('Day',
TimeStamp),
IpGroupSegments.IPGroupID,
NodeID,
InterfaceID) AS FF
GROUP BY
GroupID,
InterfaceID,
NodeID,
TimeStamp) AS B
ORDER BY
TotalBytesSourceIngress ASC) AS C
GROUP BY
GroupID,
InterfaceID,
NodeID) AS B3 ON (B3.GroupID = A.GroupID
AND B3.InterfaceID = A.InterfaceID
AND B3.NodeID = A.NodeID)
LEFT JOIN (
SELECT
MAX(TotalBytesSourceEgress) AS TotalBytesSourceEgress,
NodeID,
InterfaceID,
GroupID
FROM (
SELECT
TOP 95 PERCENT TotalBytesSourceEgress,
NodeID,
InterfaceID,
GroupID
FROM (
SELECT
NodeID,
InterfaceID,
GroupID,
Timestamp,
SUM(TotalBytesEgress) AS TotalBytesSourceEgress
FROM (
SELECT
NodeID,
InterfaceID,
IpGroupSegments.IPGroupID AS GroupID,
SUM(IngressBytes) AS TotalBytesIngress,
SUM(EgressBytes) AS TotalBytesEgress,
DateTrunc ('Day',
TimeStamp) AS TimeStamp
FROM
Orion.Netflow.FlowsByInterface AS Flows
JOIN Orion.Netflow.IpGroupSegments IpGroupSegments ON IpGroupSegments.IPSegmentID = Flows.SourceIPGroupSegmentID
WHERE TimeStamp >= AddWeek(-7,GetUtcDate())
AND TimeStamp < GetUtcDate()
GROUP BY
DateTrunc ('Day',
TimeStamp),
IpGroupSegments.IPGroupID,
NodeID,
InterfaceID) AS FF
GROUP BY
GroupID,
InterfaceID,
NodeID,
TimeStamp) AS B
ORDER BY
TotalBytesSourceEgress ASC) AS C
GROUP BY
GroupID,
InterfaceID,
NodeID) AS B4 ON (B4.GroupID = A.GroupID
AND B4.InterfaceID = A.InterfaceID
AND B4.NodeID = A.NodeID)
LEFT JOIN (
SELECT
MAX(TotalBytesDestinationIngress) AS TotalBytesDestinationIngress,
NodeID,
InterfaceID,
GroupID
FROM (
SELECT
TOP 95 PERCENT TotalBytesDestinationIngress,
NodeID,
InterfaceID,
GroupID
FROM (
SELECT
NodeID,
InterfaceID,
GroupID,
Timestamp,
SUM(TotalBytesEgress) AS TotalBytesDestinationIngress
FROM (
SELECT
NodeID,
InterfaceID,
IpGroupSegments.IPGroupID AS GroupID,
SUM(IngressBytes) AS TotalBytesIngress,
SUM(EgressBytes) AS TotalBytesEgress,
DateTrunc ('Day',
TimeStamp) AS TimeStamp
FROM
Orion.Netflow.FlowsByInterface AS Flows
JOIN Orion.Netflow.IpGroupSegments IpGroupSegments ON IpGroupSegments.IPSegmentID = Flows.DestinationIPGroupSegmentID
WHERE TimeStamp >= AddWeek(-7,GetUtcDate())
AND TimeStamp < GetUtcDate()
GROUP BY
DateTrunc ('Day',
TimeStamp),
IpGroupSegments.IPGroupID,
NodeID,
InterfaceID) AS FF
GROUP BY
GroupID,
InterfaceID,
NodeID,
TimeStamp) AS B
ORDER BY
TotalBytesDestinationIngress ASC) AS C
GROUP BY
GroupID,
InterfaceID,
NodeID) AS B5 ON (B5.GroupID = A.GroupID
AND B5.InterfaceID = A.InterfaceID
AND B5.NodeID = A.NodeID)
LEFT JOIN (
SELECT
MAX(TotalBytesDestinationEgress) AS TotalBytesDestinationEgress,
NodeID,
InterfaceID,
GroupID
FROM (
SELECT
TOP 95 PERCENT TotalBytesDestinationEgress,
NodeID,
InterfaceID,
GroupID
FROM (
SELECT
NodeID,
InterfaceID,
GroupID,
Timestamp,
SUM(TotalBytesEgress) AS TotalBytesDestinationEgress
FROM (
SELECT
NodeID,
InterfaceID,
IpGroupSegments.IPGroupID AS GroupID,
SUM(IngressBytes) AS TotalBytesIngress,
SUM(EgressBytes) AS TotalBytesEgress,
DateTrunc ('Day',
TimeStamp) AS TimeStamp
FROM
Orion.Netflow.FlowsByInterface AS Flows
JOIN Orion.Netflow.IpGroupSegments IpGroupSegments ON IpGroupSegments.IPSegmentID = Flows.DestinationIPGroupSegmentID
WHERE TimeStamp >= AddWeek(-7,GetUtcDate())
AND TimeStamp < GetUtcDate()
GROUP BY
DateTrunc ('Day',
TimeStamp),
IpGroupSegments.IPGroupID,
NodeID,
InterfaceID) AS FF
GROUP BY
GroupID,
InterfaceID,
NodeID,
TimeStamp) AS B
ORDER BY
TotalBytesDestinationEgress ASC) AS C
GROUP BY
GroupID,
InterfaceID,
NodeID) AS B6 ON (B6.GroupID = A.GroupID
AND B6.InterfaceID = A.InterfaceID
AND B6.NodeID = A.NodeID)
SELECT
NodeID,
InterfaceID,
IpGroupsBySegments.IPGroupID AS GroupID,
SUM(IngressBytes) AS TotalBytesIngress,
SUM(IngressPackets) AS TotalPacketsIngress,
SUM(EgressBytes) AS TotalBytesEgress,
SUM(EgressPackets) AS TotalPacketsEgress,
SUM(IngressBytes) + SUM(EgressBytes) AS TotalBytes,
SUM(IngressPackets) + SUM(EgressPackets) AS TotalPackets,
DateTrunc ('Day',TimeStamp) AS TimeStamp
FROM
Orion.Netflow.FlowsByInterface Flows
JOIN Orion.Netflow.IpGroupsBySegments IpGroupsBySegments ON IpGroupsBySegments.SourceIPSegmentID = Flows.SourceIPGroupSegmentID
AND IpGroupsBySegments.DestinationIPSegmentID = Flows.DestinationIPGroupSegmentID
JOIN Orion.Netflow.IPAddressGroups IPAddressGroups ON IPAddressGroups.IPAddressGroupID=IpGroupsBySegments.IPGroupID
JOIN Orion.Nodes AS nodes ON nodes.NodeID=Flows.NodeID
JOIN Orion.NPM.Interfaces AS interfaces ON interfaces.interfaceID=Flows.interfaceID
WHERE (TimeStamp >= '06/1/2023 00:00:00'
AND TimeStamp < '06/13/2023 00:00:00')
GROUP BY
Flows.SourceIPGroupSegmentID,
Flows.DestinationIPGroupSegmentID,
IpGroupsBySegments.IPGroupID,
IpGroupsBySegments.DisplayName,
NodeID,
nodes.Caption,
interfaces.Caption,
InterfaceID,
DateTrunc ('Day', TimeStamp)
SELECT
NodeID,
nodes.Caption AS NodeName,
InterfaceID,
interfaces.Caption AS InterfaceName,
IpGroupsBySegments.IPGroupID AS GroupID,
IPAddressGroups.Name AS GroupName,
SUM(IngressBytes) AS TotalBytesIngress,
SUM(IngressPackets) AS TotalPacketsIngress,
SUM(EgressBytes) AS TotalBytesEgress,
SUM(EgressPackets) AS TotalPacketsEgress,
SUM(IngressBytes) + SUM(EgressBytes) AS TotalBytes,
SUM(IngressPackets) + SUM(EgressPackets) AS TotalPackets,
TimeStamp
FROM
Orion.Netflow.FlowsByInterface Flows
JOIN Orion.Netflow.IpGroupsBySegments IpGroupsBySegments ON IpGroupsBySegments.SourceIPSegmentID = Flows.SourceIPGroupSegmentID
AND IpGroupsBySegments.DestinationIPSegmentID = Flows.DestinationIPGroupSegmentID
JOIN Orion.Netflow.IPAddressGroups IPAddressGroups ON IPAddressGroups.IPAddressGroupID=IpGroupsBySegments.IPGroupID
JOIN Orion.Nodes AS nodes ON nodes.NodeID=Flows.NodeID
JOIN Orion.NPM.Interfaces AS interfaces ON interfaces.interfaceID=Flows.interfaceID
WHERE (TimeStamp >= '06/1/2023 00:00:00'
AND TimeStamp < '06/13/2023 00:00:00')
GROUP BY
Flows.SourceIPGroupSegmentID,
Flows.DestinationIPGroupSegmentID,
IpGroupsBySegments.IPGroupID,
IpGroupsBySegments.DisplayName,
NodeID,
nodes.Caption,
interfaces.Caption,
InterfaceID,
Timestamp
When running this we get the following SELECT error, and unable to figure out where the issue is.
Any help is appreciated, thank you.
