History Wireless Clients SQL Query

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?

Bump

Bump

BUMP

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

Excellent, thanks!

This really should be a default report and or resource that can be added to a view.

Regards,

Deltona

Here is one more query, may be you will find it useful

http://thwack.solarwinds.com/docs/DOC-171677

JiriPsota

I am getting a notice in Configuration Wizard upon completion that this report was not converted. Any idea what changes between 10.4.x and 10.6.+ have caused this and what is needed to fix it?

I am not sure what you are talking about. Your SQL report is using new Wireless tables that were introduced in NPM 10.5, so it couldn't have worked before upgrade in NPM 10.4.x.

Anyway, does it mean the report doesn't work for you now in NPM 10.6?

Sorry my bad. This report works just fine, I have misread the report name. It is the Wireless Session History custom report that couldn't be converted.

I've recreated it and it works ok now.

Thanks