September 17, 2010 at 9:43 am
hi,
I'm trying to get a resultset with with events and some event parameters from two views.
The event parameters come from a different view than the events general columns.
For each event parameter a row is available from the event parameters view.
I know the event number and the column identifieres from the event parameters view.
How can i return a resultset in which i get a row per event and the event parameters.
Using a ordinary join, obviously returns a row for each parameter of that event.
How can I join these?
SELECT
Event.vEvent.DateTime,
Event.vEvent.EventDisplayNumber ,
Event.vEventParameter.ParameterValue ,
Event.vEvent.DateTime,
vEventLoggingComputer.ComputerName,
vEventUserName.UserName
FROM
Event.vEvent
INNER JOIN
vEventLoggingComputer ON vEventLoggingComputer.EventLoggingComputerRowId = Event.vEvent.LoggingComputerRowId
INNER JOIN
vEventUserName ON vEventUserName.EventUserNameRowId = Event.vEvent.UserNameRowId
RIGHT OUTER JOIN
Event.vEventParameter ON vEventParameter.EventOriginId = Event.vEvent.EventOriginId
WHERE
vEvent.EventDisplayNumber IN (631,634)
AND
DateTime <= GetDate() AND DateTime >= ((getdate())-5)
And Event.vEventParameter.ParameterIndex In(1,2,4,5)
Now for each event I get 4 rows, which i want in one row.
DateTimeEventIDParameterComputerUserName
9/13/10 9:07 AM631MyGroupMYSERVERActUserName
9/13/10 9:07 AM631GroupDomainMYSERVERActUserName
9/13/10 9:07 AM631ActAccountMYSERVERActUserName
9/13/10 9:07 AM631ActDomainMYSERVERActUserName
This is how I want it
DateTimeEventIDParameter1Parameter2Parameter4Parameter5ComputerUserName
-------------------------------------------------------------------------------------------------------------------
9/13/10 9:07 AM631MyGroupGroupDomainActAccountActDomainMYSERVERActUserName
The concerned views:
-- [Event].[vEvent]
SELECT [EventOriginId]
,[DateTime]
,[EventPublisherRowId]
,[EventChannelRowId]
,[EventCategoryRowId]
,[EventLevelId]
,[LoggingComputerRowId]
,[EventNumber]
,[EventDisplayNumber]
,[UserNameRowId]
,[RawDescriptionHash]
,[ParameterHash]
,[EventDataHash]
FROM [OperationsManagerDW].[Event].[vEvent]
-- [Event].[vEventParameter]
SELECT [EventOriginId]
,[ParameterIndex]
,[ParameterValue]
FROM [OperationsManagerDW].[Event].[vEventParameter]
September 17, 2010 at 10:01 am
Can we get you to post your table structure and some sample data? Please see the first two links in my signature for how to do so.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 17, 2010 at 10:15 am
And also the desired results. For example, if you want a single field with a delimited list of the parameters, you might want to use a subquery with a FOR XML clause, but if you want separate fields, you'll probably want to use a CROSSTAB or PIVOT. You can find links to those in WayneS' signature as well.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 17, 2010 at 10:53 am
I updated my post with additional information. Should a pivot be the right solution?
September 17, 2010 at 11:53 am
Yes, the pivot should do it assuming that your database is at compat 90. The only thing I had to change is the right outer join part, it is not correct, because you still use the event's column in the predicate thus negating the right join purpose:
select
pt.[DateTime], pt.EventDisplayNumber,
[1] Parameter1, [2] Parameter2,
[4] Parameter4, [5] Parameter5,
pt.ComputerName, pt.UserName
from
(
select
e.[DateTime], e.EventDisplayNumber,
ep.ParameterValue, ep.ParameterIndex,
elc.ComputerName, eun.UserName
from
Event.vEvent e inner join vEventLoggingComputer elc
on elc.EventLoggingComputerRowId = e.LoggingComputerRowId
inner join vEventUserName eun
on eun.EventUserNameRowId = e.UserNameRowId
inner join Event.vEventParameter ep
on ep.EventOriginId = e.EventOriginId
where
e.EventDisplayNumber in (631,634)
and e.[DateTime] between
dateadd(day, -5, getDate()) and getDate()
and ep.ParameterIndex in (1, 2, 4, 5)
) source
pivot
(
max(ParameterValue)
for source.ParameterIndex in ([1], [2], [4], [5])
) pt
order by 1, 2;
Oleg
September 20, 2010 at 1:23 am
Oleg,
Thanks for your reply. The query works!
Great example for me to learn about the pivot function.
Now the next challenge is to add a filter to the parameter part. I only want to get a result when parameter 1 is like 'ABC_%'
You used a for loop, but I think i have to do a normal select with where clause to filter on parameter 1.
September 20, 2010 at 9:57 am
I did not quite understand the "you used for loop" part. I don't have any loop in the query. If your requirement to still get the crosstab including parameters 1, 2, 4 and 5, but now you need to only include those records which have parameter 1 beginning with ABC_ then you can just add a predicate to the pivot. Adding a condition to the source unfortunately will not do the trick because it will filter out only unwanted parameter 1 values, but not the rest. Please beware though that
pt.[1] like 'ABC_%'
will fail to do the trick. This is because the underscore has a special meaning and if you simply use it as written above, you will also grab the records where parameter 1 value begins with ABC but does not have the underscore. The latter has to be escaped and the tail of your query (whatever above is still the same) will now look like this:
pivot
(
max(ParameterValue)
for source.ParameterIndex in ([1], [2], [4], [5])
) pt
where pt.[1] like 'ABC!_%' escape '!';
Oleg
September 21, 2010 at 4:32 am
Thanks for your help. The query works great (after removing the semicolon)
Did not know the underscore had a special meaning.
September 21, 2010 at 10:03 am
Percent sign means 0 or more of any characters, and underscore means exactly 1 of any characters. If you need to find the match for the value which itself should contain any of these, they themselves must be escaped. For example, suppose you want to find values containing percent sign. Obviously
where some_value like '%%%'
will not work, because the engine will translate the request as find the match of the input which contains 0 or more of any characters followed by 0 or more of any characters followed by 0 or more of any characters, which is reduced as input consisting of 0 or more of any characters. The solution should then be:
where some_value like '%\%%' escape '\';
Basically, you designate any character you wat to serve as an escape character so the character following the escape can be interpreted literally. SQL Server does not have any predefined escapes (like \ character used by C# or Java or Javascript).
Oleg
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply