Sql-server – SQL Server 2008R2 – MAX Result and Hour per Day

sql serversql-server-2008-r2t-sql

I have a table full of network interface statistics.

InterfaceID TimeStamp                      RCV_bps       XMT_bps
----------- ------------------------------ ------------- -------------
275         Nov 19 2015 12:12AM            1785.593      8.859444
275         Nov 19 2015 12:21AM            1769.675      9.540664
275         Nov 19 2015 12:30AM            1826.036      10.22184
275         Nov 19 2015 12:39AM            1754.356      8.177711
275         Nov 19 2015 12:48AM            1820.504      7.496235
275         Nov 19 2015 12:57AM            1824.645      8.177948
275         Nov 19 2015  1:06AM            1795.941      8.177711
275         Nov 19 2015  1:15AM            1780.149      7.496235
275         Nov 19 2015  1:24AM            1817.059      8.177475
275         Nov 19 2015  1:33AM            1799.519      7.496452
275         Nov 19 2015  1:42AM            1840.726      8.177711
275         Nov 19 2015  1:51AM            3986.634      7.496235
275         Nov 19 2015  2:00AM            1886.375      8.177475
275         Nov 19 2015  2:09AM            1771.905      7.496019
275         Nov 19 2015  2:18AM            1803.834      8.769002
275         Nov 19 2015  2:27AM            1807.718      7.574132
275         Nov 19 2015  2:36AM            1788.274      8.859444
275         Nov 19 2015  2:45AM            1855.727      7.496452
275         Nov 19 2015  2:54AM            1826.8        7.496235
275         Nov 19 2015  3:03AM            1870.37       8.177711

I need to join this to another table that has interface details

InterfaceID InterfaceName DeviceName
----------- ------------- ----------
275         Ethernet-1    Router-A

The requirement is to generate a report that shows the maximum values for RCV_bps and XMT_bps, and show what hour they occurred in.

I know I can get the hour blocks by grouping with a datepart function.

SELECT
     d.DeviceName
    ,d.InterfaceName
    ,r.maxRCV_hour
    ,r.maxRCV
    ,t.maxXMT_hour
    ,t.maxXMT
FROM Details d
INNER JOIN (
    SELECT
        InterfaceID
        ,datepart(hh, s.timestamp) maxRCV_hour
        ,MAX(RCV_bps) maxRCV
    FROM Statistics s
    WHERE TimeStamp > (GetDate()-1)
    GROUP BY
        InterfaceID
        ,datepart(hh, s.timestamp)
) r ON r.InterfaceID = d.InterfaceID
INNER JOIN (
    SELECT
        InterfaceID
        ,datepart(hh, s.timestamp) maxXMT_hour
        ,MAX(XMT_bps) maxXMT
    FROM Statistics s
    WHERE TimeStamp > (GetDate()-1)
    GROUP BY
        InterfaceID
        ,datepart(hh, s.timestamp)
) t ON t.InterfaceID = d.InterfaceID

But I cannot see how to take those values and return only the max for rcv and xmt. Based on the above time limits for only the last 1 day, I am getting 24 rows of data for hours 0 – 23 in each subquery…

Subquery 'r':

InterfaceID maxRCV_hour maxRCV
----------- ----------- -------------
275         0           1826.036
275         1           3986.634
275         2           1886.375
275         3           1895.214
275         4           1874.77
275         5           3999.568
275         6           2322.544
275         7           3327.41
275         8           6931.27
275         9           9165.674
275         10          9548.88
275         11          7349.096
275         12          5908.13
275         13          7247.869
275         14          9452.746
275         15          7118.653
275         16          5124.106
275         17          4012.313
275         18          3444.12
275         19          1236.745
275         20          2314.144
275         21          3430.802
275         22          1307.774
275         23          1594.491

Subquery 't':

InterfaceID maxXMT_hour maxXMT
----------- ----------- -------------
275         0           10.22184
275         1           8.177711
275         2           8.859444
275         3           12.26692
275         4           12.26657
275         5           12.26657
275         6           12.26657
275         7           8.859187
275         8           2969.632
275         9           13.60028
275         10          9.68881
275         11          8.177711
275         12          8.177711
275         13          7.496452
275         14          7.496452
275         15          8.177948
275         16          8.17783
275         17          8.859154
275         18          8.859444
275         19          8.859187
275         20          8.199979
275         21          8.859444
275         22          8.177761
275         23          8.859699

But when I run the full query I get 576 rows (so something about the way I am joining things is definitely off)

What I want is 1 row of data showing the largest values and their respective hours for both the RCV and XMT side.

Based on my results in the subqueries above, I would like the end result to look like this:

DeviceName InterfaceName maxRCV_hour maxRCV  maxXMT_hour maxXMT
---------- ------------- ----------- ------- ----------- --------
Router-A   Ethernet-1       10       9548.88     8       2969.632

My assumption is that I need to use a CTE or windowing, but those are a little beyond my current strengths and I could use some assistance figuring this out.

Any help is greatly appreciated!!!

Best Answer

You don't necessarily need to use CTEs or windowing functions: there are many ways to skin this cat.

This is the simplest that comes to my mind:

-- SAMPLE DATA:
-- remember to post data this way next time you ask for help,
-- so that people don't have to do it for you
IF OBJECT_ID('tempdb..#statistics') IS NOT NULL
    DROP TABLE #statistics;
GO
CREATE TABLE #statistics(
   InterfaceID int NOT NULL 
  ,TimeStamp   DATETIME  NOT NULL
  ,RCV_bps     decimal(9,3) NOT NULL
  ,XMT_bps     decimal(9,6) NOT NULL
);
INSERT INTO #statistics(InterfaceID,TimeStamp,RCV_bps,XMT_bps) VALUES ('275','Nov 20 2015 12:12AM','1785.593','8.859444');
INSERT INTO #statistics(InterfaceID,TimeStamp,RCV_bps,XMT_bps) VALUES ('275','Nov 20 2015 12:21AM','1769.675','9.540664');
INSERT INTO #statistics(InterfaceID,TimeStamp,RCV_bps,XMT_bps) VALUES ('275','Nov 20 2015 12:30AM','1826.036','10.22184');
INSERT INTO #statistics(InterfaceID,TimeStamp,RCV_bps,XMT_bps) VALUES ('275','Nov 20 2015 12:39AM','1754.356','8.177711');
INSERT INTO #statistics(InterfaceID,TimeStamp,RCV_bps,XMT_bps) VALUES ('275','Nov 20 2015 12:48AM','1820.504','7.496235');
INSERT INTO #statistics(InterfaceID,TimeStamp,RCV_bps,XMT_bps) VALUES ('275','Nov 20 2015 12:57AM','1824.645','8.177948');
INSERT INTO #statistics(InterfaceID,TimeStamp,RCV_bps,XMT_bps) VALUES ('275','Nov 20 2015  1:06AM','1795.941','8.177711');
INSERT INTO #statistics(InterfaceID,TimeStamp,RCV_bps,XMT_bps) VALUES ('275','Nov 20 2015  1:15AM','1780.149','7.496235');
INSERT INTO #statistics(InterfaceID,TimeStamp,RCV_bps,XMT_bps) VALUES ('275','Nov 20 2015  1:24AM','1817.059','8.177475');
INSERT INTO #statistics(InterfaceID,TimeStamp,RCV_bps,XMT_bps) VALUES ('275','Nov 20 2015  1:33AM','1799.519','7.496452');
INSERT INTO #statistics(InterfaceID,TimeStamp,RCV_bps,XMT_bps) VALUES ('275','Nov 20 2015  1:42AM','1840.726','8.177711');
INSERT INTO #statistics(InterfaceID,TimeStamp,RCV_bps,XMT_bps) VALUES ('275','Nov 20 2015  1:51AM','3986.634','7.496235');
INSERT INTO #statistics(InterfaceID,TimeStamp,RCV_bps,XMT_bps) VALUES ('275','Nov 20 2015  2:00AM','1886.375','8.177475');
INSERT INTO #statistics(InterfaceID,TimeStamp,RCV_bps,XMT_bps) VALUES ('275','Nov 20 2015  2:09AM','1771.905','7.496019');
INSERT INTO #statistics(InterfaceID,TimeStamp,RCV_bps,XMT_bps) VALUES ('275','Nov 20 2015  2:18AM','1803.834','8.769002');
INSERT INTO #statistics(InterfaceID,TimeStamp,RCV_bps,XMT_bps) VALUES ('275','Nov 20 2015  2:27AM','1807.718','7.574132');
INSERT INTO #statistics(InterfaceID,TimeStamp,RCV_bps,XMT_bps) VALUES ('275','Nov 20 2015  2:36AM','1788.274','8.859444');
INSERT INTO #statistics(InterfaceID,TimeStamp,RCV_bps,XMT_bps) VALUES ('275','Nov 20 2015  2:45AM','1855.727','7.496452');
INSERT INTO #statistics(InterfaceID,TimeStamp,RCV_bps,XMT_bps) VALUES ('275','Nov 20 2015  2:54AM','1826.8','7.496235');
INSERT INTO #statistics(InterfaceID,TimeStamp,RCV_bps,XMT_bps) VALUES ('275','Nov 20 2015  3:03AM','1870.37','8.177711');
GO

IF OBJECT_ID('tempdb..#details') IS NOT NULL
    DROP TABLE #details;
GO
CREATE TABLE #details (
    InterfaceID int NOT NULL,
    InterfaceName varchar(50) NOT NULL,
    DeviceName varchar(50) NOT NULL
);

INSERT INTO #details VALUES(275, 'Ethernet-1', 'Router-A');
GO

-- POSSIBLE SOLUTION:
-- use CROSS APPLY TOP(1)/ORDER BY to find the value
SELECT
     d.DeviceName
    ,d.InterfaceName
    ,r.maxRCV_hour
    ,r.maxRCV
    ,t.maxXMT_hour
    ,t.maxXMT
FROM #Details d
CROSS APPLY (
    SELECT TOP(1)
        InterfaceID
        ,datepart(hh, s.timestamp) maxRCV_hour
        ,MAX(RCV_bps) maxRCV
    FROM #Statistics s
    WHERE TimeStamp > (GetDate()-1)
    GROUP BY
        InterfaceID
        ,datepart(hh, s.timestamp)
    HAVING s.InterfaceID = d.InterfaceID
    ORDER BY maxRCV DESC
) AS r
CROSS APPLY (
    SELECT TOP(1)
        InterfaceID
        ,datepart(hh, s.timestamp) maxXMT_hour
        ,MAX(XMT_bps) maxXMT
    FROM #Statistics s
    WHERE TimeStamp > (GetDate()-1)
    GROUP BY
        InterfaceID
        ,datepart(hh, s.timestamp)
    HAVING s.InterfaceID = d.InterfaceID
    ORDER BY maxXMT DESC
) AS t;

Related Solutions

Sql-server – SQL Server 2008R2 Logon failed

Have you considered using a trace to audit logins and/or triggers.

in case you find a trigger using this particular login the following post - Login failed due to trigger execution underlines the security context (see Microsoft answers) in which logon triggers are executed.

Hope this helps.

Sql-server – SQL Server BPA 2008R2

While PowerShell is high on my list of things to learn I don't know much about it right now and I needed a csv quick, so I wrote a simple query in T-SQL to get the data I needed out of the XML in a tabular format and then just copied that into excel and saved it.

-- get XML from file
DECLARE @xml XML;
SELECT 
    @xml = CAST(A.[BulkColumn] AS XML)
FROM OPENROWSET(BULK 'C:\bpa_results.xml', SINGLE_BLOB) A;

-- query XML data
WITH XMLNAMESPACES ('http://schemas.microsoft.com/powershell/2004/04' as x)
SELECT
    S.*
FROM @xml.nodes('x:Objs/x:Obj') A ([obj]) -- "root" objects
CROSS APPLY A.[obj].nodes('x:LST/x:Obj') B ([obj]) -- findings objects
CROSS APPLY 
    (
        SELECT 
            A.[obj].value('@RefId', 'NVARCHAR(500)') [root_obj_RefId],
            B.[obj].value('@RefId', 'NVARCHAR(500)') [findings_obj_RefId],
            B.[obj].value('x:Props[1]/x:S[@N="ResultNumber"][1]', 'NVARCHAR(500)') [ResultNumber],
            B.[obj].value('x:Props[1]/x:S[@N="ResultId"][1]', 'NVARCHAR(500)') [ResultId],
            B.[obj].value('x:Props[1]/x:S[@N="ModelId"][1]', 'NVARCHAR(500)') [ModelId],
            B.[obj].value('x:Props[1]/x:S[@N="SubModelId"][1]', 'NVARCHAR(500)') [SubModelId],
            B.[obj].value('x:Props[1]/x:S[@N="RuleId"][1]', 'NVARCHAR(500)') [RuleId],
            B.[obj].value('x:Props[1]/x:S[@N="ComputerName"][1]', 'NVARCHAR(500)') [ComputerName],
            B.[obj].value('x:Props[1]/x:S[@N="Context"][1]', 'NVARCHAR(500)') [Context],
            B.[obj].value('x:Props[1]/x:S[@N="Source"][1]', 'NVARCHAR(500)') [Source],
            B.[obj].value('x:Props[1]/x:S[@N="Severity"][1]', 'NVARCHAR(500)') [Severity],
            B.[obj].value('x:Props[1]/x:S[@N="NeutralSeverity"][1]', 'NVARCHAR(500)') [NeutralSeverity],
            B.[obj].value('x:Props[1]/x:S[@N="Category"][1]', 'NVARCHAR(500)') [Category],
            B.[obj].value('x:Props[1]/x:S[@N="Title"][1]', 'NVARCHAR(500)') [Title],
            B.[obj].value('x:Props[1]/x:S[@N="Problem"][1]', 'NVARCHAR(500)') [Problem],
            B.[obj].value('x:Props[1]/x:S[@N="Impact"][1]', 'NVARCHAR(500)') [Impact],
            B.[obj].value('x:Props[1]/x:S[@N="Resolution"][1]', 'NVARCHAR(500)') [Resolution],
            B.[obj].value('x:Props[1]/x:S[@N="Compliance"][1]', 'NVARCHAR(500)') [Compliance],
            B.[obj].value('x:Props[1]/x:S[@N="Help"][1]', 'NVARCHAR(500)') [Help],
            B.[obj].value('x:Props[1]/x:S[@N="Excluded"][1]', 'NVARCHAR(500)') [Excluded]
    ) S
WHERE   S.[ResultNumber] IS NOT NULL
    AND S.[Severity] != 'Information';

Best Answer

Related Solutions

Sql-server – SQL Server 2008R2 Logon failed

Sql-server – SQL Server BPA 2008R2

Related Question