Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
Can someone please help me get this query adjusted for SQL statement? It works in a web-based report in Solar Winds but not in SQL.
DateTime BETWEEN AddMonth(-1,DateTrunc('month', GETUTCDATE())) AND DateTrunc('month', GETUTCDATE())
The full query is:
SELECT
Interfaces.InterfaceId,
Nodes.NodeID,
Nodes.Caption AS NodeName,
Interfaces.Caption AS Interface_Caption,
Interfaces.CustomProperties.Important_Interfaces AS Important_Ints,
Maxbps_In,
Maxbps_Out,
Maxbps_In95,
Maxbps_Out95,
Maxbps_In99,
Maxbps_Out99
FROM Orion.Nodes
INNER JOIN Orion.NPM.Interfaces ON Nodes.NodeID = Interfaces.NodeID INNER JOIN (
SELECT
OuterInterfaceTraffic.InterfaceID,
(SELECT MAX(InAverageBps) as maxInAverageBps FROM (
SELECT TOP 100 PERCENT InAverageBps
FROM Orion.NPM.InterfaceTraffic
INNER JOIN Orion.NPM.InterfacesCustomProperties AS ONICP ON InterfaceTraffic.InterfaceID = ONICP.InterfaceID
WHERE
InterfaceID = OuterInterfaceTraffic.InterfaceID AND ONICP.Important_Interfaces = 'Field_WAN' AND
DateTime BETWEEN AddMonth(-1,DateTrunc('month', GETUTCDATE())) AND DateTrunc('month', GETUTCDATE())
ORDER BY InAveragebps ASC )
) AS Maxbps_In, (SELECT MAX(OutAverageBps) as maxOutAverageBps FROM (
SELECT TOP 100 PERCENT OutAveragebps
FROM Orion.NPM.InterfaceTraffic
WHERE
InterfaceID = OuterInterfaceTraffic.InterfaceID AND
DateTime BETWEEN AddMonth(-1,DateTrunc('month', GETUTCDATE())) AND DateTrunc('month', GETUTCDATE())
ORDER BY OutAveragebps ASC )
) AS Maxbps_Out,
(SELECT MAX(InAveragebps) as maxInMaxbps FROM (
SELECT TOP 95 PERCENT InAveragebps
FROM Orion.NPM.InterfaceTraffic
WHERE
InterfaceID = OuterInterfaceTraffic.InterfaceID AND
DateTime BETWEEN AddMonth(-1,DateTrunc('month', GETUTCDATE())) AND DateTrunc('month', GETUTCDATE())
ORDER BY InMaxbps ASC )
) AS Maxbps_In95,
(SELECT MAX(OutAveragebps) as maxOutMaxbps FROM (
SELECT TOP 95 PERCENT OutAveragebps
FROM Orion.NPM.InterfaceTraffic
WHERE
InterfaceID = OuterInterfaceTraffic.InterfaceID AND
DateTime BETWEEN AddMonth(-1,DateTrunc('month', GETUTCDATE())) AND DateTrunc('month', GETUTCDATE())
ORDER BY OutMaxbps ASC )
) AS Maxbps_Out95,
(SELECT MAX(InAverageBps) as maxInAverageBps FROM (
SELECT TOP 99 PERCENT InAveragebps
FROM Orion.NPM.InterfaceTraffic
WHERE
InterfaceID = OuterInterfaceTraffic.InterfaceID AND
DateTime BETWEEN AddMonth(-1,DateTrunc('month', GETUTCDATE())) AND DateTrunc('month', GETUTCDATE())
ORDER BY InAveragebps ASC )
) AS Maxbps_In99, (SELECT MAX(OutAverageBps) as maxOutAverageBps FROM (
SELECT TOP 99 PERCENT OutAveragebps
FROM Orion.NPM.InterfaceTraffic
INNER JOIN Orion.NPM.InterfacesCustomProperties AS ONICP ON InterfaceTraffic.InterfaceID = ONICP.InterfaceID
WHERE
InterfaceID = OuterInterfaceTraffic.InterfaceID AND ONICP.Important_Interfaces = 'Field_WAN' AND
DateTime BETWEEN AddMonth(-1,DateTrunc('month', GETUTCDATE())) AND DateTrunc('month', GETUTCDATE())
ORDER BY OutAveragebps ASC )
) AS Maxbps_Out99 FROM Orion.NPM.InterfaceTraffic AS OuterInterfaceTraffic
INNER JOIN Orion.NPM.InterfacesCustomProperties AS ONICP ON OuterInterfaceTraffic.InterfaceID = ONICP.InterfaceID
WHERE OuterInterfaceTraffic.DateTime BETWEEN AddMonth(-1,DateTrunc('month', GETUTCDATE())) AND DateTrunc('month', GETUTCDATE())
AND ONICP.Important_Interfaces = 'Field_WAN'
GROUP BY OuterInterfaceTraffic.InterfaceID
) TrafficStat ON Interfaces.InterfaceID = TrafficStat.InterfaceID
Any help is greatly appreciated! Thanks!
Solved! Go to Solution.
Hi @Anonymous
If you want to convert the following code to SQL code,
DateTime BETWEEN AddMonth(-1,DateTrunc('month', GETUTCDATE())) AND DateTrunc('month', GETUTCDATE())
You could try the following SQL in SQL Server
BETWEEN DATEADD(MONTH,DATEDIFF(month, 0,GETUTCDATE() )-1,0) AND DATEADD(month, DATEDIFF(month, 0,GETUTCDATE() ), 0)
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous
If you want to convert the following code to SQL code,
DateTime BETWEEN AddMonth(-1,DateTrunc('month', GETUTCDATE())) AND DateTrunc('month', GETUTCDATE())
You could try the following SQL in SQL Server
BETWEEN DATEADD(MONTH,DATEDIFF(month, 0,GETUTCDATE() )-1,0) AND DATEADD(month, DATEDIFF(month, 0,GETUTCDATE() ), 0)
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
PERFECT! Thanks!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
12 | |
12 |