January 8, 2015 at 7:33 am
Hey
First time here, but have been enjoying the articles for some time now on this site.
But got a query question - I am trying to make a query that will group my errors messages together - my problem is that each of the error messages is unique, due to them having an unique id in them - so guess my question is can I do something, that ind my mind could look like this:
"GROUP BY LIKE '%ThePartToGroupBy%'"
Or something similar to that?
Thanks
January 8, 2015 at 7:42 am
x 45204 (1/8/2015)
HeyFirst time here, but have been enjoying the articles for some time now on this site.
But got a query question - I am trying to make a query that will group my errors messages together - my problem is that each of the error messages is unique, due to them having an unique id in them - so guess my question is can I do something, that ind my mind could look like this:
"GROUP BY LIKE '%ThePartToGroupBy%'"
Or something similar to that?
Thanks
Can't do that.
But you could group by the first x characters (for example). Or the first x words.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 8, 2015 at 7:42 am
You could create additional "virtual" columns using a CASE expression:
WITH BaseData AS (
SELECT
MyColumn =
CASE
WHEN SomeColumn LIKE '%Anything%' THEN 'Anything'
WHEN SomeColumn LIKE '%Whatever%' THEN 'Whatever'
END
FROM SomeTable
)
SELECT MyColumn, COUNT(*) -- Any aggregate here
FROM BaseData
GROUP BY MyColumn
Hope this helps
-- Gianluca Sartori
January 8, 2015 at 8:15 am
x 45204 (1/8/2015)
HeyFirst time here, but have been enjoying the articles for some time now on this site.
But got a query question - I am trying to make a query that will group my errors messages together - my problem is that each of the error messages is unique, due to them having an unique id in them - so guess my question is can I do something, that ind my mind could look like this:
"GROUP BY LIKE '%ThePartToGroupBy%'"
Or something similar to that?
Thanks
I'm assuming you have a table similar to the following which logs error messages.
create table MyErrorLog
(
ErrorLogID int not null identity(1,1) primary key,
ErrorLogTime datetime not null default getdate(),
ErrorLogMsg varchar(8000) not null
);
One approach you can take is to leverage master.sys.messages as a helper table for parsing the messages. What I'm doing below is joining MyErrorLog to sys.messages on [message_id], and then grouping on the generic [text] column which contains message text without specific object name references.
Here I'm inserting a few sample records; two records for two types of errors.
insert into MyErrorLog ( ErrorLogMsg )
values ('Error 208: Invalid object name ''ayz''.');
insert into MyErrorLog ( ErrorLogMsg )
values ('Error 208: Invalid object name ''bvc''.');
insert into MyErrorLog ( ErrorLogMsg )
values ('Error 2812: Could not find stored procedure ''sp_ssss''.');
insert into MyErrorLog ( ErrorLogMsg )
values ('Error 2812: Could not find stored procedure ''sp_jhhw''.');
Here I have a query that returns a count of errors grouped by error type. There are multiple records in sys.messages for different languages, so you'll need to filter on appropriate language_id.
select m.message_id, m.text, count(distinct ErrorLogID)ErrorCount
from MyErrorLog l
join master.sys.messages m on m.language_id = 1033
and l.ErrorLogMsg like 'Error '+cast(m.message_id as varchar(9))+':%'
group by m.message_id, m.text;
message_id text ErrorCount
2812 Could not find stored procedure '%.*ls'. 2
208 Invalid object name '%.*ls'. 2
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 8, 2015 at 4:55 pm
You could also GROUP directly on the CASE expression itself:
SELECT
CASE
WHEN message_text LIKE '%ThePartToGroupBy%' THEN '%ThePartToGroupBy%'
ELSE message_text END AS message,
COUNT(*),
...
FROM ...
GROUP BY
CASE
WHEN message_text LIKE '%ThePartToGroupBy%' THEN '%ThePartToGroupBy%'
ELSE message_text END
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 9, 2015 at 6:14 am
ScottPletcher (1/8/2015)
You could also GROUP directly on the CASE expression itself:
SELECT
CASE
WHEN message_text LIKE '%ThePartToGroupBy%' THEN '%ThePartToGroupBy%'
ELSE message_text END AS message,
COUNT(*),
...
FROM ...
GROUP BY
CASE
WHEN message_text LIKE '%ThePartToGroupBy%' THEN '%ThePartToGroupBy%'
ELSE message_text END
If he's parsing something like error messages containing object name references, then there is no way to consistently detemrine what '%ThePartToGroupBy%' should be.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 9, 2015 at 9:16 am
Eric M Russell (1/9/2015)
ScottPletcher (1/8/2015)
You could also GROUP directly on the CASE expression itself:
SELECT
CASE
WHEN message_text LIKE '%ThePartToGroupBy%' THEN '%ThePartToGroupBy%'
ELSE message_text END AS message,
COUNT(*),
...
FROM ...
GROUP BY
CASE
WHEN message_text LIKE '%ThePartToGroupBy%' THEN '%ThePartToGroupBy%'
ELSE message_text END
If he's parsing something like error messages containing object name references, then there is no way to consistently detemrine what '%ThePartToGroupBy%' should be.
Not necessarily true: there could still be enough fixed words in the text to recognize the pattern. Presumably he's already identified that string in this case, since he explicitly mentioned wanting to do it.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 14, 2015 at 1:38 am
Thanks guys - it dont solve the issue i was having 100%, as the messages I needed to group together simply was too complex and no real pattern could be found, to dynamic group each type of message together, without having to identify each and every one of them - but thanks got a few more tips in my sql tool kit.
January 14, 2015 at 7:17 am
x 45204 (1/14/2015)
Thanks guys - it dont solve the issue i was having 100%, as the messages I needed to group together simply was too complex and no real pattern could be found, to dynamic group each type of message together, without having to identify each and every one of them - but thanks got a few more tips in my sql tool kit.
If the error messages contain the sqlserver error code in any position (it can vary), then my earlier suggestion of joining to sys.messages should at least allow you to group by specific error type.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 14, 2015 at 8:14 am
Building on the CASE statement version, use of cross apply to simplify
;WITH TestData (RandomString)
AS
(
SELECTA.RandomString
FROM(
VALUES('Chuff'),
('Chuff1'),
('Chufff'),
('Chuffty'),
('ff'),
('uff'),
('huff')
) AS A(RandomString)
)
SELECTCA1.GroupedThing,
Cnt = COUNT(*)
FROMTestData AS TD
CROSS
APPLY(SELECT CASE WHEN TD.RandomString LIKE 'Chu%' THEN 'Group1' ELSE 'Group2' END) AS CA1(GroupedThing)
GROUPBY CA1.GroupedThing
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply