How can I generate a simple daily report that shows the total number of "active" switch ports on my network?

I'm interested in building a report, or even generating a simple query with SDK / SWQL / SQL, that shows the total number of switchport with active link on them, once a day.

Meaning that if any port has a link on it at any time during a 24-hour period it would count as an active link.

I'd like this aggregated into a chart that shows a line graph of the active ports changing.

If it can generate the query more than once a day and show hourly active-port numbers changing, that would be even more interesting.

First off, I didn't find a canned report in SolarWinds NPM that can do this. Are you aware of one? If so, what's it called?

Second, have you already built a custom report that can do this? If so, would you share it with me, or share how you built it with me?

Last, do you know (or do you have a good idea) how to build a SWQL or SQL query that can display the total number of active ports on demand--or over time?

Swift Packets!

Rick Schroeder

Find more posts tagged with

Accepted answers

mesverrum

Strange, you must have a different version than I do, but since it is complaining with that error I suspect this should get past it

select count(t2.interfaceid) as Interfaces, t2.date

from

(SELECT distinct datetrunc('day',DateTime) as Date, interfaceid

FROM Orion.NPM.InterfaceAvailability ia

where datetime > addday(-30,GETDATE())

group by datetrunc('day',DateTime), interfaceid

having max(Availability) > 0) t2

group by t2.date

order by date

All comments

mesverrum

So starting with the assumption that all your interfaces are monitored all the time, I'd do this one in SWQL:

select count(t2.interfaceid) as Interfaces, t2.date

from

(SELECT distinct datetrunc('day',DateTime) as Date, interfaceid

FROM Orion.NPM.InterfaceAvailability ia

where datetime > addday(-30,GETDATE())

group by datetrunc('day',DateTime)

having max(Availability) > 0) t2

group by t2.date

order by date

In my lab that executes in like .20 seconds but I know your environment is much larger so hopefully it doesn't time out or anything.

To turn it into an hourly report you can just change the datetrunc functions to 'hour' instead of day

rschroeder

I ran it on my SDK and got this error:

Here's where my SWQL / SQL ignorance shows. I bet this problem is fixable, but I don't know the syntax to change or add or remove.

Thank you for the first stab at it, though! Might you have an idea how to remedy the error, mesverrum?

rschroeder

The details of the error:

2018-10-10 12:01:09,194 [120] ERROR SolarWinds.InformationService.DataProviders.SqlQueryRelation - SWQL Studio (null) An SqlException occurred.

Message: Column 'dbo.InterfaceAvailability.InterfaceID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Query:

SET DATEFIRST 7;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SELECT count([T1].[C4]) AS C1, [T1].[C3] AS C2

FROM (SELECT DISTINCT DateAdd(day,DateDiff(day,0,DateAdd(second,DateDiff(second,GetDate(),GetUtcDate()),[T2].[DateTime])),0) AS C3, [T2].[InterfaceID] AS C4

FROM dbo.InterfaceAvailability AS T2

WHERE [T2].[DateTime] > DateAdd(second,DateDiff(second,GetUtcDate(),GetDate()),DateAdd(day,-30,GETDATE()))

GROUP BY DateAdd(day,DateDiff(day,0,DateAdd(second,DateDiff(second,GetDate(),GetUtcDate()),[T2].[DateTime])),0)

HAVING max([T2].[Availability]) > 0) AS T1

GROUP BY [T1].[C3]

ORDER BY [C2] ASC

2018-10-10 12:01:09,194 [120] ERROR SolarWinds.InformationService.Core.InformationService - SWQL Studio (null) Exception running query:

select count(t2.interfaceid) as Interfaces, t2.date

from

(SELECT distinct datetrunc('day',DateTime) as Date, interfaceid

FROM Orion.NPM.InterfaceAvailability ia

where datetime > addday(-30,GETDATE())

group by datetrunc('day',DateTime)

having max(Availability) > 0) t2

group by t2.date

order by date

RETURN XML RAW

2018-10-10 12:01:09,194 [120] ERROR SolarWinds.InformationService.Core.InformationService - SWQL Studio (null) Exception for Operation: <s:Envelope xmlns:s="http://www.w3.org/2003/05/soap-envelope" xmlns:a="http://www.w3.org/2005/08/addressing" xmlns:u="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd">

<s:Header>

<a:Action s:mustUnderstand="1">http://schemas.solarwinds.com/2007/08/informationservice/InformationService/QueryXml</a:Action>

<a:MessageID>urn:uuid:aaca1e1f-fee5-4974-884f-d0b9ffcf3610</a:MessageID>

<a:ReplyTo>

<a:Address>http://www.w3.org/2005/08/addressing/anonymous</a:Address>

</a:ReplyTo>

</i18n>

<ApplicationTag>SWQL Studio</ApplicationTag>

</SwisSettings>

<a:To s:mustUnderstand="1">net.tcp://npm:17777/SolarWinds/InformationService/v3/Orion/ssl</a:To>

<o:Security s:mustUnderstand="1" xmlns:o="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd">

<u:Timestamp u:Id="_0">

<u:Created>2018-10-10T17:01:09.214Z</u:Created>

<u:Expires>2018-10-10T17:06:09.214Z</u:Expires>

</u:Timestamp>

<c:SecurityContextToken u:Id="uuid-1e91db8d-b7b1-4128-b836-ddcfa8aebdca-7023" xmlns:c="http://schemas.xmlsoap.org/ws/2005/02/sc">

<c:Identifier>urn:uuid:a1c49f43-1901-46d7-987d-231f2567c56d</c:Identifier>

</c:SecurityContextToken>

</CanonicalizationMethod>

</SignatureMethod>

</Transform>

</Transforms>

</DigestMethod>

<DigestValue>QnnyldXBv0PRkfCpO9NlBjVez/0=</DigestValue>

</Reference>

</SignedInfo>

<SignatureValue>MuZzz9Qy+jtatzHDIxHqJVSMwgo=</SignatureValue>

<o:SecurityTokenReference>

<o:Reference URI="#uuid-1e91db8d-b7b1-4128-b836-ddcfa8aebdca-7023">

</o:Reference>

</o:SecurityTokenReference>

</KeyInfo>

</Signature>

</o:Security>

</s:Header>

<s:Body>

<query>select count(t2.interfaceid) as Interfaces, t2.date

from

(SELECT distinct datetrunc('day',DateTime) as Date, interfaceid

FROM Orion.NPM.InterfaceAvailability ia

where datetime > addday(-30,GETDATE())

group by datetrunc('day',DateTime)

having max(Availability) > 0) t2

group by t2.date

order by date

RETURN XML RAW</query>

</parameters>

</QueryXml>

</s:Body>

</s:Envelope>

2018-10-10 12:01:09,194 [120] ERROR SolarWinds.InformationService.Core.InformationService - SWQL Studio (null) Exception caught in method SolarWinds.InformationService.Core.InformationService.RunQuery

System.Data.SqlClient.SqlException (0x80131904): Column 'dbo.InterfaceAvailability.InterfaceID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)

at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)

at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()

at System.Data.SqlClient.SqlDataReader.get_MetaData()

at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)

at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)

at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)

at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)

at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)

at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)

at SolarWinds.InformationService.DataProviders.SqlQueryRelation.<GetEnumerator>d__8.MoveNext()

at SolarWinds.Data.Query.PhysicalQueryPlan.ProviderPassThroughScanOp.<GetEnumeratorInternal>d__5.MoveNext()

at SolarWinds.Data.Query.PhysicalQueryPlan.ProjectOp.<GetEnumeratorInternal>d__7.MoveNext()

at SolarWinds.Data.Query.PhysicalQueryPlan.PhysicalQueryPlan.<GetEnumerator>d__4.MoveNext()

at SolarWinds.InformationService.Serialization.XmlResponseSerializer..ctor(IQueryResultReader reader, Boolean includeQueryPlan, IQueryStatisticsContext statistics, I18n i18N)

at SolarWinds.InformationService.Serialization.XmlSerializer.GetSerializer()

at SolarWinds.InformationService.Serialization.XmlSerializer.PrepareSerialize(IQueryExpr query, ISchema schema, IQueryResultReader reader)

at SolarWinds.InformationService.Core.InformationService.RunQuery(String query, IDictionary`2 parameters, IDataSerializer serializer)

ClientConnectionId:2da49255-0e51-4697-8921-ef6b2bc3398c

Error Number:8120,State:1,Class:16

mesverrum

Strange, you must have a different version than I do, but since it is complaining with that error I suspect this should get past it

select count(t2.interfaceid) as Interfaces, t2.date

from

(SELECT distinct datetrunc('day',DateTime) as Date, interfaceid

FROM Orion.NPM.InterfaceAvailability ia

where datetime > addday(-30,GETDATE())

group by datetrunc('day',DateTime), interfaceid

having max(Availability) > 0) t2

group by t2.date

order by date

rschroeder

That definitely dumped out nice clean answers. Thank you!

Questions (to help me submit fewer newbie questions in the future, hoping I've leaned a bit more from your expertise):

What does "t2.date" represent?
The Date column shows the last 30 days, with a time stamp of 12:00:00 a.m.
- What does this tell me specifically? That at midnight there are x number of interfaces with links?
- Is there a way to show how many interfaces are active at 11 a.m. or 1 p.m.
Is there an elegant way to put this into a custom query and show it as a line graph? I'll try a few stabs at that on my own, but if you have a method, please share it. Normally, when I fail to get the right kind of report going from a custom SWQL query like yours, I eventually find I've made a mistake at one of the very first steps--like choosing table instead of chart, or choosing node instead of interface, etc. If you share instructions, please don't assume I've got the basics down. (Plus, showing the full method of building the custom view or Resource benefits others who may find this during their search for an answer).

Thanks again for speeding my packets!

Rick Schroeder