Custom UDT Summary and Details View Report

Hi guys,

I'm trying to build two new reports for UDT, but I'm getting stuck with the more advanced SQL queries that lookup date.

I hope one of you can help

1. A Summary View report, much like the Top 10 Nodes by Percent Ports Used resource, that groups the Nodes by <custom_Property> AND only lists historical "Archive" information for ports that have never been seen.

What I wish to end up having is an overview of % Ports Used based on Active Ports Vs Never Used ports. Alternatively, Active Ports Vs Ports Last Seen (haven't been used in at least 90 days).

Top 10 Nodes by # of Never Seen Ports

<custom_property>	Node	Ports Count	Ports Used	Ports Never Used	% Ports Used (ASC)
Datacenter A	RTR01	7	1	6	14
	FW01	11	2	9	18
	SW01	9	3	6	33
	SW02	27	15	12	55
Datacenter B	SW01	57	34	23	59
	SW02	25	15	10	60

2. The same type of query for use in a Node Details view, where the query is ${NodeID} dependant.

What I have so far is losely based off njoylif's work found here: Re: UDT report for used/unused ports per device

I can't figure out how to call the never seen/last seen -90 bit

I'm using UDT v3.2.4

Is this doable?

Regards,

Deltona

Find more posts tagged with

udt custom report

never seen udt report

udt top 10 report

Accepted answers

ctlswadmin

See if the query below gives you valid data. The logic being that, if a port is still active the last successful scan will be recent, and if the port was active at the time it will be considered to have been in use during that time.

AND (LastSuccessfulScan > dateadd(DAY,-90,getdate()) AND Enabled = '1')

The query should look like this:

(ISNULL([PortsTotal].[PortCount], 0) > 0)

(LastSuccessfulScan > dateadd(DAY,-90,getdate()) AND Enabled = '1')

BY [% PortsUsed] ASC

All comments

ctlswadmin

Hi Deltona, I haven't played around with the UDT tables in a while now. Can you confirm if "Last Scan" is considered last seen ? That's the only date reference I found on njoylif's query. Are you using the same tables as they are ?

If you're using the same query, or similar to add this to the end.

LastScan > dateadd(DAY,-90,getdate())

So the query would look like this.

WHERE (ISNULL([PortsTotal].[PortCount], 0) > 0)

AND LastScan > dateadd(DAY,-90,getdate())

ORDER BY [% PortsUsed] DESC, [ActivePortCount] DESC, [UnusedPorts] ASC

njoylif

Deltona, did that work for you? if not PM or LMK and I'll take a look...been a while so have to refresh

Deltona

Hi,

Thanks for the reply.

I am not sure whether to base it on last scan or last seen because I am not certain about the difference or whether a Last Seen exists. Logically it would have to be last seen but if this isn't logged in the database then how would this be done? Are entries overwritten?

This is the query that I'm using now. Bold and italic is what I've added/modified.

SELECT

[N].[Caption] AS Caption, Country

, ISNULL([PortsTotal].[PortCount], 0) AS PortCount

, ISNULL([PortsActive].[PortCount], 0) AS ActivePortCount

, (ISNULL([PortsTotal].[PortCount], 0) - ISNULL([PortsActive].[PortCount],0)) AS UnusedPorts

, CASE

WHEN ((ISNULL([PortsTotal].[PortCount], 0) = 0) OR (ISNULL([PortsActive].[PortCount], 0) = 0)) THEN 0

ELSE ((ISNULL([PortsActive].[PortCount], 0) * 100) / ISNULL([PortsTotal].[PortCount], 0))

END AS [% PortsUsed]

FROM dbo.Nodes AS N

INNER JOIN dbo.UDT_NodeCapability AS NC ON (([N].[NodeID] = [NC].[NodeID]) AND ([NC].[Capability] = 2))

RIGHT JOIN (

SELECT [P].[NodeID], COUNT([P].[PortID]) AS PortCount

FROM dbo.UDT_Port AS P

WHERE ([P].[Flag] = 0) AND ([P].[IsMonitored] = 1)

GROUP BY [P].[NodeID]

) AS PortsTotal ON ([PortsTotal].[NodeID] = [NC].[NodeID])

RIGHT JOIN (

SELECT [P].[NodeID], COUNT([P].[PortID]) AS PortCount

FROM dbo.UDT_Port AS P

WHERE ([P].[Flag] = 0) AND ([P].[IsMonitored] = 1)

GROUP BY [P].[NodeID], [P].[OperationalStatus]

HAVING ([P].[OperationalStatus] = 1)

) AS PortsActive ON ([PortsActive].[NodeID] = [NC].[NodeID])

WHERE (ISNULL([PortsTotal].[PortCount], 0) > 0)
AND LastScan > dateadd(DAY,-90,getdate())
ORDER BY [% PortsUsed] ASC

It's not displaying what I expected it would display, probably because it is based on LastScan.

Deltona

Thanks! Two brains are always better than one I say

ctlswadmin

AND (LastSuccessfulScan > dateadd(DAY,-90,getdate()) AND Enabled = '1')

The query should look like this:

(ISNULL([PortsTotal].[PortCount], 0) > 0)

(LastSuccessfulScan > dateadd(DAY,-90,getdate()) AND Enabled = '1')

BY [% PortsUsed] ASC

Deltona

Thanks again. I'll try this query for a few weeks and report back.