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.

Max Port Usage by Day Report (Web-based)

Hi guys,

I'm trying to build a report in the new web based reports but it is proving to be more difficult, the more time i spend trying to figure it all out.

What i'm trying to achieve is creating a report that will provide the following bits of information:

Custom Property.Location | Node Name | Number of Ports Active/Up (Max)

Time-based by Last 24 hours

Grouped by CP.Location

Sorted by # Active Ports Descending

Basically, how many ports have been found to be active in the past 24 hours per Node, per CP.Location.

Example:

Location                Node Name             Active Ports (Max)

San Diego            SD-SW03.lab.com              12

Any kind souls care to point me in the right direction?

UDT v3.2.2

TIA

  • Thor,

    UDT still doesn't support the web-reporting so you need to use the old report writer for this.

    Peter

  • Hi Peter,

    Thanks, that explains alot! Although i still need to create this report one way or the other, Report Writer is either missing selectable fields for UDT or the data i need just isn't there.

    I was using the UDT: Capacity report as baseline to build upon but can't select actual port count, only percentage of ports used.

    Here's what i got so far, though without Grouping by Custom Property or actual MAX Port Count.

    SELECT  TOP 10000 UDT_PortCapacity.NodeName AS NodeName,
    MAX(UDT_PortCapacity.PortPercentUsed) AS MAX_of_PortPercentUsed

    FROM
    UDT_PortCapacity

    GROUP BY UDT_PortCapacity.NodeName

    I can't find the table UDT_PortCapacity emoticons_confused.png

    I did find UDT_PortUsage_Daily, which is almost exactly what i need but with Max port count instead of AvgPortCount.

    Would it be possible to create this type of report?

  • I asked the team to validate this. Please stay tuned.

    Peter

  • Excellent. Thanks and looking forward to the update emoticons_cool.png

  • Please open the Report Writer --> New “Advanced SQL” --> Name the Report in the General Tab --> In the SQL Tab enter one of the below query. This should hopefully address your concern.

    The below "Where" clause can be commented or modified as per your need, currently it will return data for current date. Changing "Getdate()" to "Getdate()-1" will return data for previous date.

    SELECT 

                 TOP 10000 UDT_PortCapacity.NodeName AS NodeName,

                 MAX(UDT_PortCapacity.PortPercentUsed) AS MAX_of_PortPercentUsed

    FROM

                 UDT_PortCapacity

    Where

                 cast(datetime as DATE) = cast(Getdate() as DATE)

    GROUP BY

                 UDT_PortCapacity.NodeName

    Order by

                 1

  • Hi there,

    Almost there. There are two things I still need.

    1. Can i get this based on actual port count instead of Percent?

    2. Could you include the SQL query to group by a Custom Property?

    Thanks in advance emoticons_happy.png

  • Please find the below query for getting Actual Port Count, for the other question we would need a GTM session to explain and work it out, I suggest you call/open a support ticket with us.

    SELECT 

                TOP 10000 dbo.Nodes.Caption AS NodeName, 

                 MAX(CASE WHEN (UDT_PortUsage.AvgPortCount = 0 OR UDT_PortUsage.AvgActivePortCount = 0)

                THEN 0 ELSE (UDT_PortUsage.AvgActivePortCount) END) AS AvgActivePortUsedCount

    FROM    

                dbo.UDT_PortUsage LEFT OUTER JOIN dbo.Nodes ON dbo.UDT_PortUsage.NodeID = dbo.Nodes.NodeID

    WHERE 

                (dbo.UDT_PortUsage.Archive = 0) and cast (datetime as DATE) = cast (Getdate() as DATE)

    GROUP BY

                dbo.Nodes.Caption

    Order by

                2 desc