7 Replies Latest reply on Oct 10, 2018 4:54 PM by rschroeder

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

    rschroeder

      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

        • Re: How can I generate a simple daily report that shows the total number of "active" switch ports on my network?
          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

            • Re: How can I generate a simple daily report that shows the total number of "active" switch ports on my network?
              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?

                • Re: How can I generate a simple daily report that shows the total number of "active" switch ports on my network?
                  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

                  1 of 1 people found this helpful
                    • Re: How can I generate a simple daily report that shows the total number of "active" switch ports on my network?
                      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

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

                          So you said you wanted all interfaces that were up at any time during the day, so i figured I would use the maximum availability, if it was ever not 0 that means it was up at least one polling interval.

                           

                          Initially using just a max availability would give me a max for all of time, so i needed to split it into days, to do that I used the group by datetrunc to split things up per day.

                           

                          It doesn't actually mean that was the status at midnight, it means i dropped all the parts of the timestamp beyond the day, so this would be all times from 00:00 to 23:59 during that calendar day, based on your core server time.  That is what t2.date represents (t2 is the subquery where I figured out all this max availability per day stuff).  If you wanted to do it hour by hour you could use datetrunc('hour'.datetime) and it would truncate all the timestamps to just hourly values.

                           

                          Then I just counted the unique interface id's.  Now I haven't tested this but i would want to validate for things like interfaces that have been deleted, i don't know if they go back and purge them from the historical tables, also if you remove a device and then add it back in maybe you will get double counting.  I'd just keep an eye on it and try to suss out the behaviors of the historical tables before I swore to any of the truthiness in this.

                           

                          In order to use this query withe the custom chart resource you need one more column to use as the legend shows/group by values.  This should work

                           

                          select count(t2.interfaceid) as Interfaces, t2.date, 'Interface Count' as [Interface Count]

                          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

                    • Re: How can I generate a simple daily report that shows the total number of "active" switch ports on my network?
                      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 xmlns="http://schemas.solarwinds.com/2007/08/informationservice" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">

                            <Culture>en-US</Culture>

                          </i18n>

                          <SwisSettings xmlns="http://schemas.solarwinds.com/2007/08/informationservice" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">

                            <ApplicationTag>SWQL Studio</ApplicationTag>

                            <DataProviderTimeout>PT30S</DataProviderTimeout>

                          </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>

                            <Signature xmlns="http://www.w3.org/2000/09/xmldsig#">

                              <SignedInfo>

                                <CanonicalizationMethod Algorithm="http://www.w3.org/2001/10/xml-exc-c14n#">

                                </CanonicalizationMethod>

                                <SignatureMethod Algorithm="http://www.w3.org/2000/09/xmldsig#hmac-sha1">

                                </SignatureMethod>

                                <Reference URI="#_0">

                                  <Transforms>

                                    <Transform Algorithm="http://www.w3.org/2001/10/xml-exc-c14n#">

                                    </Transform>

                                  </Transforms>

                                  <DigestMethod Algorithm="http://www.w3.org/2000/09/xmldsig#sha1">

                                  </DigestMethod>

                                  <DigestValue>QnnyldXBv0PRkfCpO9NlBjVez/0=</DigestValue>

                                </Reference>

                              </SignedInfo>

                              <SignatureValue>MuZzz9Qy+jtatzHDIxHqJVSMwgo=</SignatureValue>

                              <KeyInfo>

                                <o:SecurityTokenReference>

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

                                  </o:Reference>

                                </o:SecurityTokenReference>

                              </KeyInfo>

                            </Signature>

                          </o:Security>

                        </s:Header>

                        <s:Body>

                          <QueryXml xmlns="http://schemas.solarwinds.com/2007/08/informationservice">

                            <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 &gt; addday(-30,GETDATE())

                       

                       

                      group by datetrunc('day',DateTime)

                       

                       

                      having max(Availability) &gt; 0) t2

                       

                       

                      group by t2.date

                       

                       

                      order by date

                      RETURN XML RAW</query>

                            <parameters>

                            </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