15 Replies Latest reply on May 31, 2016 5:37 AM by Deltona

    History Wireless Clients SQL Query

    Deltona

      Hi,

       

      I need to report on the Connected and Disconnected time for Wireless clients.

      The History Wireless Clients resource displayed on Wireless This AP view shows me the exact details I am after, however, there is no comparable report that gives me the same details.

      Does anyone (SolarWinds) know what query the History Wireless Clients resource is executing? If I have the query then I can create a Custom SQL report.

       

      Thanks in advance.

        • Re: History Wireless Clients SQL Query
          Deltona

          Just to get the ball rolling, I've looked up the query being executed when loading the History Wireless Clients resource using SAM's AppInsight and this is what I get.

           

          (@nodeid int,@thinap bigint,@startPeriod datetime,@endPeriod datetime)SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

          SELECT [T1].[C9] AS C1, DateAdd(second,DateDiff(second,GetUtcDate(),GetDate()),MIN([T1].[C10])) AS C2, DateAdd(second,DateDiff(second,GetUtcDate(),GetDate()),MAX([T1].[C11])) AS C3, MAX([T1].[C12]) AS C4, MAX([T1].[C13]) AS C5, SUM([T1].[C16]) AS C6, SUM([T1].[C15]) AS C7, AVG([T1].[C14]) AS C8

          FROM

          (

          SELECT [T2].[MACAddress] AS C9, DateAdd(second,DateDiff(second,GetDate(),GetUtcDate()),[T2].[FirstUpdate]) AS C10, DateAdd(second,DateDiff(second,GetDate(),GetUtcDate()),[T2].[LastUpdate]) AS C11, [T2].[IPAddress] AS C12, [T2].[SSID] AS C13, ISNULL([T2].[SignalStrength],0) AS C14, ISNULL([T2].[InBytes],0) AS C15, ISNULL([T2].[OutBytes],0) AS C16

          FROM dbo.Wireless_ClientsHistory AS T2

          LEFT JOIN dbo.Wireless_InterfacesHistory AS T4 ON DateAdd(second,DateDiff(second,GetDate(),GetUtcDate()),[T2].[LastUpdate]) = DateAdd(second,DateDiff(second,GetDate(),GetUtcDate()),[T4].[LastUpdate]) AND [T2].[ParentIndex] = [T4].[Index]

          LEFT JOIN dbo.Wireless_AccessPointsHistory AS T3 ON DateAdd(second,DateDiff(second,GetDate(),GetUtcDate()),[T4].[LastUpdate]) = DateAdd(second,DateDiff(second,GetDate(),GetUtcDate()),[T3].[LastUpdate]) AND [T4].[ParentIndex] = [T3].[Index]

          WHERE [T2].[NodeID] = @nodeid AND [T3].[Index] = @thinap AND ISNULL([T2].[MACAddress],'') <> ''

          ) AS T1

          WHERE [T1].[C11] >= @startPeriod AND [T1].[C11] <= @endPeriod OR [T1].[C10] >= @startPeriod AND [T1].[C10] <= @endPeriod OR [T1].[C10] < @startPeriod AND [T1].[C11] > @endPeriod

          GROUP BY [T1].[C9]

          ORDER BY [C3] DESC

           

          However, as I'm far from being a SQL guru, I don't know why I get a "This syntax is only allowed for parameterized queries" error when executing the query.

           

          Ideas?

            • Re: History Wireless Clients SQL Query
              JiriPsota

              I've changed your query little bit, I hope this is what you are looking fo. It's the same query we use for History Wireless Clients resource, but I've added extra columns with controller and AP name:

               

              DECLARE @startPeriod datetime

              DECLARE @endPeriod datetime

              SET @startPeriod='2013-10-29 23:00:00'

              SET @endPeriod='2013-10-30 09:43:45.830'

               

               

              SELECT [T1].[C18] AS Controller,[T1].[C17] AS APname,[T1].[C9] AS MACaddress, DateAdd(second,DateDiff(second,GetUtcDate(),GetDate()),MIN([T1].[C10])) AS Connected, DateAdd(second,DateDiff(second,GetUtcDate(),GetDate()),MAX([T1].[C11])) AS Disconnected, MAX([T1].[C12]) AS IPaddress, MAX([T1].[C13]) AS SSID, SUM([T1].[C16]) AS TransmittedBytes, SUM([T1].[C15]) AS ReceivedBytes, AVG([T1].[C14]) AS SignalStrength

              FROM

              (

              SELECT [T6].[Name] AS C18, [T5].Name AS C17,[T2].[MACAddress] AS C9, DateAdd(second,DateDiff(second,GetDate(),GetUtcDate()),[T2].[FirstUpdate]) AS C10, DateAdd(second,DateDiff(second,GetDate(),GetUtcDate()),[T2].[LastUpdate]) AS C11, [T2].[IPAddress] AS C12, [T2].[SSID] AS C13, ISNULL([T2].[SignalStrength],0) AS C14, ISNULL([T2].[InBytes],0) AS C15, ISNULL([T2].[OutBytes],0) AS C16

              FROM dbo.Wireless_ClientsHistory AS T2

              INNER JOIN dbo.Wireless_InterfacesHistory AS T4 ON DateAdd(second,DateDiff(second,GetDate(),GetUtcDate()),[T2].[LastUpdate]) = DateAdd(second,DateDiff(second,GetDate(),GetUtcDate()),[T4].[LastUpdate]) AND [T2].[ParentIndex] = [T4].[Index]

              INNER JOIN dbo.Wireless_AccessPointsHistory AS T3 ON DateAdd(second,DateDiff(second,GetDate(),GetUtcDate()),[T4].[LastUpdate]) = DateAdd(second,DateDiff(second,GetDate(),GetUtcDate()),[T3].[LastUpdate]) AND [T4].[ParentIndex] = [T3].[Index]

              JOIN Wireless_AccessPoints AS T5 ON ([T5].[Index]=[T3].[Index] AND [T5].[NodeID]=[T2].[NodeID])

              LEFT JOIN Wireless_Controllers AS T6 ON ([T6].[ID]=[T5].[ParentID])

              WHERE ISNULL([T2].[MACAddress],'''') <> ''''

              AND (DateAdd(second,DateDiff(second,GetDate(),GetUtcDate()),[T2].[LastUpdate]) >= @startPeriod

              AND DateAdd(second,DateDiff(second,GetDate(),GetUtcDate()),[T2].[LastUpdate]) <= @endPeriod

              OR DateAdd(second,DateDiff(second,GetDate(),GetUtcDate()),[T2].[FirstUpdate]) >= @startPeriod

              AND DateAdd(second,DateDiff(second,GetDate(),GetUtcDate()),[T2].[FirstUpdate]) <= @endPeriod

              OR DateAdd(second,DateDiff(second,GetDate(),GetUtcDate()),[T2].[FirstUpdate]) < @startPeriod

              AND DateAdd(second,DateDiff(second,GetDate(),GetUtcDate()),[T2].[LastUpdate]) > @endPeriod)

              ) AS T1

              GROUP BY [T1].[C18],[T1].[C17],[T1].[C9]

              ORDER BY 1,2,3 DESC