July 12, 2010 at 8:12 am
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:
hostnameseverity_guidtimestamp
HOLSUSAS006cb2f2b90-2da4-4075-bcaa-dd5d2cefbfd57/11/2010 3:27
HOLSUSAS0040168a833-1732-411e-8205-c2f6cd91737d7/11/2010 5:27
HOLSUSAS004cb2f2b90-2da4-4075-bcaa-dd5d2cefbfd57/11/2010 9:45
HOLSUSAS006cb2f2b90-2da4-4075-bcaa-dd5d2cefbfd57/11/2010 5:30
HOLSUSAS0040168a833-1732-411e-8205-c2f6cd91737d7/11/2010 11:13
HOLSUSAS0040168a833-1732-411e-8205-c2f6cd91737d7/11/2010 0:46
NASADEV01cb2f2b90-2da4-4075-bcaa-dd5d2cefbfd57/11/2010 23:29
USPVUSFS0001 c4cf8a23-a106-4617-bab0-94da3ca74ef17/11/2010 0:45
SSISUSTST001c4cf8a23-a106-4617-bab0-94da3ca74ef17/11/2010 3:27
HOLSUSFS0010168a833-1732-411e-8205-c2f6cd91737d7/11/2010 5:27
SSISUSBEX002c4cf8a23-a106-4617-bab0-94da3ca74ef17/11/2010 9:45
NASADEV010168a833-1732-411e-8205-c2f6cd91737d7/11/2010 5:30
NASADEV01cb2f2b90-2da4-4075-bcaa-dd5d2cefbfd57/11/2010 11:13
SSISUSBEX002c4cf8a23-a106-4617-bab0-94da3ca74ef17/11/2010 0:46
HOLSUSAS0040168a833-1732-411e-8205-c2f6cd91737d7/11/2010 23:29
NASADEV01cb2f2b90-2da4-4075-bcaa-dd5d2cefbfd57/11/2010 0:45
HOLSUSAS0040168a833-1732-411e-8205-c2f6cd91737d7/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 CriticalMajorWarning
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
July 12, 2010 at 8:36 am
Look at the CASE statement.
SELECT CASE
WHEN MyCol = 1 THEN 'Alert'
WHEN MyCol = 0 THEN 'OK'
ELSE 'No idea'
END
from MyTable
where MyCol is not null
July 12, 2010 at 9:13 am
This would produce the output you would be looking for.
declare @temp table (hostname varchar(20),severity_guid varchar(50),[timestamp] datetime)
insert into @temp (hostname,severity_guid,[timestamp]) values(
'HOLSUSAS006','cb2f2b90-2da4-4075-bcaa-dd5d2cefbfd5','7/11/2010 3:27')
insert into @temp (hostname,severity_guid,[timestamp]) values(
'HOLSUSAS004','0168a833-1732-411e-8205-c2f6cd91737d','7/11/2010 5:27')
insert into @temp (hostname,severity_guid,[timestamp]) values(
'HOLSUSAS004','cb2f2b90-2da4-4075-bcaa-dd5d2cefbfd5','7/11/2010 9:45')
insert into @temp (hostname,severity_guid,[timestamp]) values(
'HOLSUSAS006','cb2f2b90-2da4-4075-bcaa-dd5d2cefbfd5','7/11/2010 5:30')
insert into @temp (hostname,severity_guid,[timestamp]) values(
'HOLSUSAS004','0168a833-1732-411e-8205-c2f6cd91737d','7/11/2010 11:13')
insert into @temp (hostname,severity_guid,[timestamp]) values(
'HOLSUSAS004','0168a833-1732-411e-8205-c2f6cd91737d','7/11/2010 00:46')
insert into @temp (hostname,severity_guid,[timestamp]) values(
'NASADEV01','cb2f2b90-2da4-4075-bcaa-dd5d2cefbfd5','7/11/2010 23:29')
insert into @temp (hostname,severity_guid,[timestamp]) values(
'USPVUSFS0001','c4cf8a23-a106-4617-bab0-94da3ca74ef1','7/11/2010 00:45')
insert into @temp (hostname,severity_guid,[timestamp]) values(
'SSISUSTST001','c4cf8a23-a106-4617-bab0-94da3ca74ef1','7/11/2010 3:27')
insert into @temp (hostname,severity_guid,[timestamp]) values(
'HOLSUSFS001','0168a833-1732-411e-8205-c2f6cd91737d','7/11/2010 5:27')
insert into @temp (hostname,severity_guid,[timestamp]) values(
'SSISUSBEX002','c4cf8a23-a106-4617-bab0-94da3ca74ef1','7/11/2010 9:45')
insert into @temp (hostname,severity_guid,[timestamp]) values(
'NASADEV01','0168a833-1732-411e-8205-c2f6cd91737d','7/11/2010 5:30')
insert into @temp (hostname,severity_guid,[timestamp]) values(
'NASADEV01','cb2f2b90-2da4-4075-bcaa-dd5d2cefbfd5','7/11/2010 11:13')
insert into @temp (hostname,severity_guid,[timestamp]) values(
'SSISUSBEX002','c4cf8a23-a106-4617-bab0-94da3ca74ef1','7/11/2010 00:46')
insert into @temp (hostname,severity_guid,[timestamp]) values(
'HOLSUSAS004','0168a833-1732-411e-8205-c2f6cd91737d','7/11/2010 23:29')
insert into @temp (hostname,severity_guid,[timestamp]) values(
'NASADEV01','cb2f2b90-2da4-4075-bcaa-dd5d2cefbfd5','7/11/2010 00:45')
insert into @temp (hostname,severity_guid,[timestamp]) values(
'HOLSUSAS004','0168a833-1732-411e-8205-c2f6cd91737d','7/11/2010 8:15')
select distinct(hostname),
(select count(*) as criticalCnt
from @temp where severity_guid='0168A833-1732-411E-8205-C2F6CD91737D'
and hostname=t.hostname
group by hostname),
(select count(*) as criticalCnt
from @temp where severity_guid='CB2F2B90-2DA4-4075-BCAA-DD5D2CEFBFD5'
and hostname=t.hostname
group by hostname),
(select count(*) as criticalCnt
from @temp where severity_guid='C4CF8A23-A106-4617-BAB0-94DA3CA74EF1'
and hostname=t.hostname
group by hostname)
from @temp t
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
July 12, 2010 at 11:19 am
So many potential solutions. I like Steve's solution stuffed in a sub-query and broken out into separate columns. Then just do a count in the outer query and group by HostName and TimeStamp.
However, you should pick the one that works best for you. @=)
July 12, 2010 at 12:50 pm
This will run much faster:
select hostname,
sum(case when severity_guid='0168A833-1732-411E-8205-C2F6CD91737D'
then 1 else 0 end) as [Count_Of_0168A833-1732-411E-8205-C2F6CD91737D]
,sum(case when severity_guid='CB2F2B90-2DA4-4075-BCAA-DD5D2CEFBFD5'
then 1 else 0 end) as [Count_Of_CB2F2B90-2DA4-4075-BCAA-DD5D2CEFBFD5]
,sum(case when severity_guid='C4CF8A23-A106-4617-BAB0-94DA3CA74EF1'
then 1 else 0 end) as [Count_Of_C4CF8A23-A106-4617-BAB0-94DA3CA74EF1]
from @temp
group by hostname
July 12, 2010 at 1:59 pm
All,
Appreciate the input so far. I have played with all the queries and it looks like I am getting very close to my end goal.
Clay
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply