Query Help for Newb
in [Server Configuration]
|
From: sqlnewb on 12 Jul 2010 10:32 All, I am a sql newb and in a bind and all out of ideas at the moment but I was thrown into figuring a query out to report back what my manager wants to see. Basically I have a table similar to the one below that I need to write a query against that will give me this report style format once ran. This is going to be run on a schedule in one of our systems that will email the results daily to my manager. I am ok with simple select and join statements but do not know where to start with manipulating the queried data inside the query. Similar table: hostname severity_guid timestamp HOLSUSAS006 cb2f2b90-2da4-4075-bcaa-dd5d2cefbfd5 7/11/2010 3:27 HOLSUSAS004 0168a833-1732-411e-8205-c2f6cd91737d 7/11/2010 5:27 HOLSUSAS004 cb2f2b90-2da4-4075-bcaa-dd5d2cefbfd5 7/11/2010 9:45 HOLSUSAS006 cb2f2b90-2da4-4075-bcaa-dd5d2cefbfd5 7/11/2010 5:30 HOLSUSAS004 0168a833-1732-411e-8205-c2f6cd91737d 7/11/2010 11:13 HOLSUSAS004 0168a833-1732-411e-8205-c2f6cd91737d 7/11/2010 0:46 NASADEV01 cb2f2b90-2da4-4075-bcaa-dd5d2cefbfd5 7/11/2010 23:29 USPVUSFS0001 c4cf8a23-a106-4617-bab0-94da3ca74ef1 7/11/2010 0:45 SSISUSTST001 c4cf8a23-a106-4617-bab0-94da3ca74ef1 7/11/2010 3:27 HOLSUSFS001 0168a833-1732-411e-8205-c2f6cd91737d 7/11/2010 5:27 SSISUSBEX002 c4cf8a23-a106-4617-bab0-94da3ca74ef1 7/11/2010 9:45 NASADEV01 0168a833-1732-411e-8205-c2f6cd91737d 7/11/2010 5:30 NASADEV01 cb2f2b90-2da4-4075-bcaa-dd5d2cefbfd5 7/11/2010 11:13 SSISUSBEX002 c4cf8a23-a106-4617-bab0-94da3ca74ef1 7/11/2010 0:46 HOLSUSAS004 0168a833-1732-411e-8205-c2f6cd91737d 7/11/2010 23:29 NASADEV01 cb2f2b90-2da4-4075-bcaa-dd5d2cefbfd5 7/11/2010 0:45 HOLSUSAS004 0168a833-1732-411e-8205-c2f6cd91737d 7/11/2010 8:15 In this table the severity guid actually means the following: '0168A833-1732-411E-8205-C2F6CD91737D' = 'Critical' 'CB2F2B90-2DA4-4075-BCAA-DD5D2CEFBFD5' = 'Major' 'C4CF8A23-A106-4617-BAB0-94DA3CA74EF1' = 'Warning' My manager basically wants to see the number of particular alerts for a host in past 24 hours: Server Name Critical Major Warning HOLSUSAS006 0 2 0 HOLSUSAS004 5 1 0 NASADEV01 1 2 0 USPVUSFS0001 0 0 1 SSISUSTST001 0 0 1 SSISUSBEX002 0 0 2 Any direction or advice will be greatly appreciated. Thanks, Clay
From: Erland Sommarskog on 12 Jul 2010 16:21 sqlnewb (u61921(a)uwe) writes: > I am a sql newb and in a bind and all out of ideas at the moment but I > was thrown into figuring a query out to report back what my manager > wants to see. Basically I have a table similar to the one below that I > need to write a query against that will give me this report style format > once ran. This is going to be run on a schedule in one of our systems > that will email the results daily to my manager. I am ok with simple > select and join statements but do not know where to start with > manipulating the queried data inside the query. SELECT hostname, SUM(CASE severity_guid WHEN '0168A833-1732-411E-8205-C2F6CD91737D' THEN 1 ELSE 0 END) AS Critical, SUM(CASE severity_guid WHEN 'CB2F2B90-2DA4-4075-BCAA-DD5D2CEFBFD5' THEN 1 ELSE 0 END) AS Major, SUM(CASE severity_guid WHEN 'C4CF8A23-A106-4617-BAB0-94DA3CA74EF1' THEN 1 ELSE 0 END) AS Warning FROM tbl GROUP BY hostname -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: John Bell on 12 Jul 2010 17:23 On Mon, 12 Jul 2010 14:32:53 GMT, "sqlnewb" <u61921(a)uwe> wrote: >All, > >I am a sql newb and in a bind and all out of ideas at the moment but I was >thrown into figuring a query out to report back what my manager wants to see. >Basically I have a table similar to the one below that I need to write a >query against that will give me this report style format once ran. This is >going to be run on a schedule in one of our systems that will email the >results daily to my manager. >I am ok with simple select and join statements but do not know where to start >with manipulating the queried data inside the query. > >Similar table: >hostname severity_guid >timestamp >HOLSUSAS006 cb2f2b90-2da4-4075-bcaa-dd5d2cefbfd5 7/11/2010 3:27 >HOLSUSAS004 0168a833-1732-411e-8205-c2f6cd91737d 7/11/2010 5:27 >HOLSUSAS004 cb2f2b90-2da4-4075-bcaa-dd5d2cefbfd5 7/11/2010 9:45 >HOLSUSAS006 cb2f2b90-2da4-4075-bcaa-dd5d2cefbfd5 7/11/2010 5:30 >HOLSUSAS004 0168a833-1732-411e-8205-c2f6cd91737d 7/11/2010 11:13 >HOLSUSAS004 0168a833-1732-411e-8205-c2f6cd91737d 7/11/2010 0:46 >NASADEV01 cb2f2b90-2da4-4075-bcaa-dd5d2cefbfd5 7/11/2010 23:29 >USPVUSFS0001 c4cf8a23-a106-4617-bab0-94da3ca74ef1 7/11/2010 0:45 >SSISUSTST001 c4cf8a23-a106-4617-bab0-94da3ca74ef1 7/11/2010 3:27 >HOLSUSFS001 0168a833-1732-411e-8205-c2f6cd91737d 7/11/2010 5:27 >SSISUSBEX002 c4cf8a23-a106-4617-bab0-94da3ca74ef1 7/11/2010 9:45 >NASADEV01 0168a833-1732-411e-8205-c2f6cd91737d 7/11/2010 5:30 >NASADEV01 cb2f2b90-2da4-4075-bcaa-dd5d2cefbfd5 7/11/2010 11:13 >SSISUSBEX002 c4cf8a23-a106-4617-bab0-94da3ca74ef1 7/11/2010 0:46 >HOLSUSAS004 0168a833-1732-411e-8205-c2f6cd91737d 7/11/2010 23:29 >NASADEV01 cb2f2b90-2da4-4075-bcaa-dd5d2cefbfd5 7/11/2010 0:45 >HOLSUSAS004 0168a833-1732-411e-8205-c2f6cd91737d 7/11/2010 8:15 > >In this table the severity guid actually means the following: >'0168A833-1732-411E-8205-C2F6CD91737D' = 'Critical' >'CB2F2B90-2DA4-4075-BCAA-DD5D2CEFBFD5' = 'Major' >'C4CF8A23-A106-4617-BAB0-94DA3CA74EF1' = 'Warning' > > >My manager basically wants to see the number of particular alerts for a host >in past 24 hours: > >Server Name Critical Major Warning >HOLSUSAS006 0 2 0 >HOLSUSAS004 5 1 0 >NASADEV01 1 2 0 >USPVUSFS0001 0 0 1 >SSISUSTST001 0 0 1 >SSISUSBEX002 0 0 2 > > > >Any direction or advice will be greatly appreciated. > >Thanks, >Clay Hi Clay When posting it is better to post DDL and example data as insert statements, the easier it is for people to answer the more likely you will get answered quickly and with the correct answer. It is also good to post what you have tried t show you have put some effort into solving the issue already. So if you have the following tables and data CREATE TABLE alerts ( hostname varchar(15), severity_guid uniqueidentifier, timestamp datetime ) GO INSERT INTO alerts ( hostname, severity_guid, [timestamp] ) SELECT 'HOLSUSAS006','cb2f2b90-2da4-4075-bcaa-dd5d2cefbfd5','7/11/2010 3:27' UNION ALL SELECT 'HOLSUSAS004', '0168a833-1732-411e-8205-c2f6cd91737d', '7/11/2010 5:27' UNION ALL SELECT 'HOLSUSAS004', 'cb2f2b90-2da4-4075-bcaa-dd5d2cefbfd5', '7/11/2010 9:45' UNION ALL SELECT 'HOLSUSAS006', 'cb2f2b90-2da4-4075-bcaa-dd5d2cefbfd5', '7/11/2010 5:30' UNION ALL SELECT 'HOLSUSAS004', '0168a833-1732-411e-8205-c2f6cd91737d', '7/11/2010 11:13' UNION ALL SELECT 'HOLSUSAS004', '0168a833-1732-411e-8205-c2f6cd91737d', '7/11/2010 0:46' UNION ALL SELECT 'NASADEV01', 'cb2f2b90-2da4-4075-bcaa-dd5d2cefbfd5', '7/11/2010 23:29' UNION ALL SELECT 'USPVUSFS0001', 'c4cf8a23-a106-4617-bab0-94da3ca74ef1', '7/11/2010 0:45' UNION ALL SELECT 'SSISUSTST001', 'c4cf8a23-a106-4617-bab0-94da3ca74ef1', '7/11/2010 3:27' UNION ALL SELECT 'HOLSUSFS001', '0168a833-1732-411e-8205-c2f6cd91737d', '7/11/2010 5:27' UNION ALL SELECT 'SSISUSBEX002', 'c4cf8a23-a106-4617-bab0-94da3ca74ef1', '7/11/2010 9:45' UNION ALL SELECT 'NASADEV01', '0168a833-1732-411e-8205-c2f6cd91737d', '7/11/2010 5:30' UNION ALL SELECT 'NASADEV01', 'cb2f2b90-2da4-4075-bcaa-dd5d2cefbfd5', '7/11/2010 11:13' UNION ALL SELECT 'SSISUSBEX002', 'c4cf8a23-a106-4617-bab0-94da3ca74ef1', '7/11/2010 0:46' UNION ALL SELECT 'HOLSUSAS004', '0168a833-1732-411e-8205-c2f6cd91737d', '7/11/2010 23:29' UNION ALL SELECT 'NASADEV01', 'cb2f2b90-2da4-4075-bcaa-dd5d2cefbfd5', '7/11/2010 0:45' UNION ALL SELECT 'HOLSUSAS004', '0168a833-1732-411e-8205-c2f6cd91737d', '7/11/2010 8:15' GO CREATE TABLE Severities ( severity_guid uniqueidentifier, [description] varchar(10)) GO INSERT INTO Severities ( severity_guid, [description] ) SELECT '0168A833-1732-411E-8205-C2F6CD91737D' , 'Critical' UNION ALL SELECT 'CB2F2B90-2DA4-4075-BCAA-DD5D2CEFBFD5' , 'Major' UNION ALL SELECT 'C4CF8A23-A106-4617-BAB0-94DA3CA74EF1' , 'Warning' GO You can get all the alerts for yesterday using DECLARE @startdate datetime , @enddate datetime; SELECT @startdate = CAST(FLOOR(CAST(getdate()-1 AS float)) AS datetime), @enddate = CAST(FLOOR(CAST(getdate() AS float)) AS datetime) SELECT a.hostname, s.[description] FROM alerts a JOIN Severities s ON a.severity_guid = s.severity_guid WHERE a.[timestamp] < @enddate AND a.[timestamp] >= @startdate Then you can look up how to use a PIVOT to get the data you want see http://msdn.microsoft.com/en-us/library/ms177410.aspx So with a bit of playing you could combine them to come up with something like: DECLARE @startdate datetime , @enddate datetime; SELECT @startdate = CAST(FLOOR(CAST(getdate()-1 AS float)) AS datetime), @enddate = CAST(FLOOR(CAST(getdate() AS float)) AS datetime) SELECT hostname, [Critical], [Major], [Warning] FROM ( SELECT a.hostname, a.[timestamp], s.[description] FROM alerts a JOIN Severities s ON a.severity_guid = s.severity_guid WHERE a.[timestamp] < @enddate AND a.[timestamp] >= @startdate ) AS SourceTable PIVOT ( count([timestamp]) FOR [description] IN ([Critical], [Major], [Warning]) ) AS PivotTable; John
|
Pages: 1 Prev: Determining T-SQL within a transaction_id Next: SQLView joining two fields |