SQL Query

Question

Hello to Every Body,

***Thanks alot for answer my questions and i like this forum because of this, i have solved many problems in Orion Solarwinds***

Now the question is that,

When i Execute report using Peak Recieve and Peak Transmit Fileds, then Orion Report Writer Execute it and give me required report well done!, but after that output i have to compare the Values between Peak Receive and Peak Transmit  and in result maximum value put into 3rd column with name Peak Utilization using MAX formula in Excel Sheet

Above discussion mean 3rd column work totaly is customized and time consuming because i have more than 500 network nodes.

So, please help me regarding this like

Is there any query in SQL, that automatically pick Maximum value from Peak Receive and Peak Transmit Fileds and Put int 3rd Column like Peak Utilization

Please answer me to solve this problem.

i want to solve this my problem to save time and generate quicker reports

Thanks Alot again

lchance · Answer

i'm not certain i am on target with what you are asking here but i will take a shot at it...

here's a report i made with the time frame of TODAY (could be any other) for all Cisco nodes with the Custom NODE Property of Department.Name 'wide%'

i also used Report Grouping inside this report (it looks best with grouping).

it shows each node and those interfaces which have a peak util over 1% and catches the timestamp for it's MAX (PEAK) REC UTIL or (PEAK) XMIT UTIL.

you could modify this easily for your own Util % or Time Frame (as for me - i didn't want anything less than 1%) and this probably would not port well to Excel but it does work well and publishes well by Report Writer...Good Luck!

SELECT  TOP 10000 Convert(DateTime,Floor(Cast((DateTime) as Float)*24)/24,0) AS SummaryDateTime,
Nodes.Caption AS NodeName,
Interfaces.Caption AS Interface_Caption,
Interfaces.InterfaceAlias AS Interface_Alias,
Interfaces.InterfaceSpeed AS Interface_Speed,
MAX(InterfaceTraffic.In_Maxbps) AS MAX_of_Peak_Receive_bps,
MAX(InterfaceTraffic.Out_Maxbps) AS MAX_of_Peak_Transmit_bps,
MAX(Case InBandwidth
    When 0 Then 0
    Else (In_Averagebps/InBandwidth) * 100
End) AS MAX_of_Recv_Percent_Utilization,
MAX(Case OutBandwidth
    When 0 Then 0
    Else (Out_Averagebps/OutBandwidth) * 100
End) AS MAX_of_Xmit_Percent_Utilization,
MAX(Convert(Char,DateTime,108)) AS MAX_of_Time_of_Day,
Nodes.NodeID AS NodeID,
Interfaces.InterfaceID AS InterfaceID

FROM 
(Nodes INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID))  INNER JOIN InterfaceTraffic ON (Interfaces.InterfaceID = InterfaceTraffic.InterfaceID AND InterfaceTraffic.NodeID = Nodes.NodeID)

WHERE 
( DateTime BETWEEN 40211 AND 40211.4583333333 )
 AND  
(
  (Nodes.Department LIKE 'wide%') AND 
  (
   NOT (Case InBandwidth
    When 0 Then 0
    Else (In_Averagebps/InBandwidth) * 100
End < 1) AND 
   NOT (Case OutBandwidth
    When 0 Then 0
    Else (Out_Averagebps/OutBandwidth) * 100
End < 1))
)

GROUP BY Convert(DateTime,Floor(Cast((DateTime) as Float)*24)/24,0), 
Nodes.Caption, Interfaces.Caption, Interfaces.InterfaceAlias, Interfaces.InterfaceSpeed, Nodes.NodeID, Interfaces.InterfaceID

ORDER BY SummaryDateTime ASC, 2 ASC