This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.

You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Custom SQL Report Chart - Time Column

elton over 8 years ago

Hello all,

I'm trying to create a custom chart based upon my custom SQL that retrieves 95th percentile traffic on business hours. I read I must implement ${FromTime} and ${ToTime}. However I do it I can't seem to get a time column that gives me data on my chart. Any help is greatly appreciated. Below is my custom SQL:

SET NOCOUNT OFF

SET ROWCOUNT 0

DECLARE @StartDate DateTime

DECLARE @EndDate DateTime

SET @StartDate = CAST((ROUND(CAST(GetDate() - 7 AS FLOAT), 0, 1)) as datetime)

SET @EndDate = GetDate()

SELECT TOP 10 Interfaces.InterfaceId,

Nodes.NodeID,

Nodes.City AS City,

Nodes.Caption AS NodeName,

Interfaces.InterfaceName AS Interface_Name,

Interfaces.CircuitID AS CircuitID,

Interfaces.LocalLoop AS LocalLoop,

Interfaces.InterfaceSpeed AS Interface_Speed,

Nodes.Vendor AS Vendor,

Interfaces.InterfaceID AS InterfaceID,

Interfaces.OutBandwidth AS Xmit_Bandwidth,

Interfaces.InBandwidth AS Recv_Bandwidth,

Maxbps_Out95,

Maxbps_In95,

Maxbps_95,

Case OutBandwidth

When 0 Then 0

Else (Maxbps_Out95/OutBandwidth)*100 End AS Util_Out95,

Case InBandwidth

When 0 Then 0

Else (Maxbps_In95/InBandwidth)*100 End AS Util_In95,

Case

When InBandwidth+OutBandwidth=0 Then 0

When InBandwidth=0 Then

(Maxbps_Out95/OutBandwidth)*100 When OutBandwidth=0 Then

(Maxbps_In95/InBandwidth)*100 Else

((Maxbps_In95/InBandwidth)+(Maxbps_Out95/OutBandwidth))*50 End AS AVERAGE_of_CircuitUtil_95RecvXmit,

(Maxbps_In95 + Maxbps_Out95)/2 AS AVERAGE_ofCircuitUtilBPS

FROM Nodes

INNER JOIN Interfaces ON Nodes.NodeID = Interfaces.NodeID

INNER JOIN (

SELECT Interfaces.InterfaceID,

dbo.GetInBps95th(Interfaces.InterfaceID, @StartDate, @EndDate) AS Maxbps_In95,

dbo.GetOutBps95th(Interfaces.InterfaceID, @StartDate, @EndDate) AS Maxbps_Out95,

dbo.GetMaxBps95th(Interfaces.InterfaceID, @StartDate, @EndDate) AS Maxbps_95

FROM InterfaceTraffic INNER JOIN Interfaces ON InterfaceTraffic.NodeID = Interfaces.NodeID

WHERE (InterfaceTraffic.DateTime >= @StartDate AND InterfaceTraffic.DateTime <= @EndDate) AND

(

(DATEPART(weekday, InterfaceTraffic.DateTime) = 2) OR

(DATEPART(weekday, InterfaceTraffic.DateTime) = 3) OR

(DATEPART(weekday, InterfaceTraffic.DateTime) = 4) OR

(DATEPART(weekday, InterfaceTraffic.DateTime) = 5) OR

(DATEPART(weekday, InterfaceTraffic.DateTime) = 6)) AND

(

(Interfaces.gmtOffset = 5) AND

(DatePart(Hour,InterfaceTraffic.DateTime) >= 5) AND

(DatePart(Hour,InterfaceTraffic.DateTime) <= 15)) OR

(

(Interfaces.gmtOffset = 6) AND

(DatePart(Hour,InterfaceTraffic.DateTime) >= 6) AND

(DatePart(Hour,InterfaceTraffic.DateTime) <= 16)) OR

(

(Interfaces.gmtOffset = 7) AND

(DatePart(Hour,InterfaceTraffic.DateTime) >= 7) AND

(DatePart(Hour,InterfaceTraffic.DateTime) <= 17)) OR

(

(Interfaces.gmtOffset = 8) AND

(DatePart(Hour,InterfaceTraffic.DateTime) >= 8) AND

(DatePart(Hour,InterfaceTraffic.DateTime) <= 18))

)

GROUP BY Interfaces.InterfaceID

) TrafficStat

ON Interfaces.InterfaceID = TrafficStat.InterfaceID

WHERE

(Nodes.Vendor = 'Cisco') AND

(Interfaces.CarrierName = 'CenturyLink')

ORDER BY AVERAGE_of_CircuitUtil_95RecvXmit ASC

0 lag over 8 years ago

Did you figure this out? I'm stuck on the same thing.
Cancel
Vote Up 0 Vote Down

Cancel
0 LadaVarga over 8 years ago

Hello,
What do you need to achieve? In Custom chart you can show only some time-based value.That means for example
Time traffic
1:00 10
2:00 15
....
As I can see you have query which shows traffic during businnes hours grouped by interfaceid, that means every interface has only one value
Cancel
Vote Up 0 Vote Down

Cancel
0 lag over 8 years ago in reply to LadaVarga

It's probably easier to explain if I show you the underlying query. Currently, I have a pivot table that I work into Excel to show top 10 LUNs by several metrics (total overall system IOps, R/W/Total IOps per LUN).
This is a simplified tabular version of what I'm trying to report on:
SELECT
n.NodeID
,n.Caption
,n.IP_Address
,p.DateTime
,l.LUN_Name
,p.CP_Value
FROM Nodes n
INNER JOIN (
SELECT
CustomPollerStatus.Status AS LUN_Name
,CAST(CustomPollerStatus.RowID AS INT) AS RowID
,Nodes.NodeID
FROM CustomPollerAssignment
Inner Join CustomPollers on CustomPollerAssignment.PollerID = CustomPollers.PollerID
Inner Join CustomPollerStatus on CustomPollerAssignment.CustomPollerAssignmentID = CustomPollerStatus.CustomPollerAssignmentID
Inner Join Nodes on CustomPollerAssignment.NodeID = Nodes.nodeid
WHERE
CustomPollers.UniqueName = 'LUN'
) AS l ON n.NodeID = l.NodeID
INNER JOIN (
SELECT
--CONVERT(VarChar(19),DateTime) AS DateTime
convert(varchar, DateTime, 102) AS DateTime
,ROUND(AVG(CustomPollerStatistics_Detail.MAXRate),2) AS CP_Value
,CAST(CustomPollerStatistics_Detail.RowID AS INT) AS RowID
,Nodes.NodeID
FROM CustomPollerAssignment
LEFT Join CustomPollers on CustomPollerAssignment.PollerID = CustomPollers.PollerID
LEFT Join CustomPollerStatistics_Detail on CustomPollerAssignment.CustomPollerAssignmentID = CustomPollerStatistics_Detail.CustomPollerAssignmentID
LEFT Join Nodes on CustomPollerAssignment.NodeID = Nodes.nodeid
WHERE
CustomPollerStatistics_Detail.DateTime >= DateAdd(dd,-7,GetDate()) AND CustomPollerStatistics_Detail.DateTime <= GetDate()
AND
CustomPollers.UniqueName LIKE '%LTIO%'
GROUP BY
convert(varchar, DateTime, 102)
,CAST(CustomPollerStatistics_Detail.RowID AS INT)
,Nodes.NodeID
) AS p ON l.RowID = p.RowID
Where n.IP_Address='192.168.34.138'
AND
l.RowID IN (
SELECT Distinct
r.RowID
FROM CustomPollerAssignment
INNER JOIN (
Select Distinct
CustomPollerStatus.Status AS LUN_Name
,CustomPollerAssignment.PollerID
,CustomPollerStatus.RowID
,Nodes.NodeID
,Nodes.Caption
,Nodes.IP_Address
FROM CustomPollerAssignment
Inner Join CustomPollers on CustomPollerAssignment.PollerID = CustomPollers.PollerID
Inner Join CustomPollerStatus on CustomPollerAssignment.CustomPollerAssignmentID = CustomPollerStatus.CustomPollerAssignmentID
Inner Join Nodes on CustomPollerAssignment.NodeID = Nodes.nodeid
WHERE
CustomPollers.UniqueName = 'LUN'
AND Nodes.IP_Address = '192.168.34.138'
) AS r ON CustomPollerAssignment.PollerID = r.PollerID
INNER JOIN (
SELECT TOP 10
ROUND(AVG(CustomPollerStatistics_Detail.MAXRate),2) AS CP_Value
,CAST(CustomPollerStatistics_Detail.RowID AS INT) AS RowID
,Nodes.NodeID
FROM CustomPollerAssignment
LEFT Join CustomPollers on CustomPollerAssignment.PollerID = CustomPollers.PollerID
LEFT Join CustomPollerStatistics_Detail on CustomPollerAssignment.CustomPollerAssignmentID = CustomPollerStatistics_Detail.CustomPollerAssignmentID
LEFT Join Nodes on CustomPollerAssignment.NodeID = Nodes.nodeid
WHERE
CustomPollerStatistics_Detail.DateTime >= DateAdd(dd,-7,GetDate()) AND CustomPollerStatistics_Detail.DateTime <= GetDate()
AND
CustomPollers.UniqueName LIKE '%LTIO%'
GROUP BY
CAST(CustomPollerStatistics_Detail.RowID AS INT)
,Nodes.NodeID
ORDER BY
ROUND(AVG(CustomPollerStatistics_Detail.MAXRate),2) DESC
) AS q ON r.RowID = q.RowID
)
ORDER BY CP_Value desc, DateTime ASC
Cancel
Vote Up 0 Vote Down

Cancel