January 26, 2016 at 4:05 pm
I am working with a vendor system that logs into a database. I need to get those logs into Splunk using straight sql (there is a Splunk add-on for the vendor, but it only accepts sql --no stored procedures).
Unfortunately, the vendor system logs into two relevant tables
Event (id, eventID, eventTimeStamp, other relevant columns)
-there is one event for every logged event
EventProperties (eventID, propertyName, PropertyValue)
-there could be 0-15 properties for every logged event. the property names vary by event type
I am hoping to query for events and add a column that includes all property values
ID EVENTID Properties
1 1 propname1='value1', propname2='value2'
2 2 propname17='value 17'
How do I go about this? I can do it if I could use tsql, but I think that I have to stick to straigt sql.
So I tried
WITH EventProps(eventID, properties, level) AS
(Select Properties.eventID, CAST(min(Properties.propertyName+'='''+Properties.propertyValue + '''') as varchar(max)), 1
from EventProperties
inner join Events
on EventProperties.EventID = Events.EventID
where Events.eventTimeStamp> '2016/1/1'
Group by EventProperties.eventID
UNION ALL
Select ep.eventID, CAST(parms + ', ' + property.ParameterName+'='''+property.ParameterValue + '''' as varchar(max)), ep.level + 1
from EventProperties ep
cross apply (select top 1 EventProperties.ParameterName, EventProperties.parameterValue
from EventProperties
where EventProperties .eventID = ep.eventID
and ep.parms not like '%' + EventProperties .ParameterName + '=%'
order by EventProperties .ParameterName
) as property
where ep.level<15
)
But this fails because
The TOP or OFFSET operator is not allowed in the recursive part of a recursive common table expression 'EventProps'.
So my next option is to explicitly outer apply every property:
Select Events.EventID
, Coalesce(ep1.PropertyName+'='''+ep1.PropertyValue + ''', ', '') +
Coalesce(ep2.PropertyName+'='''+ep2.PropertyValue + ''', ', '') +
Coalesce(ep3.PropertyName+'='''+ep3.PropertyValue + '''', '')
from Events
outer apply ( select top 1 PropertyName, PropertyValue
from EventProperties
where eventID = Events.EventID
order by PropertyName) ep1
outer apply ( select top 1 PropertyName, PropertyValue
from EventProperties
where eventID = Events.EventID
and PropertyName>ep1.PropertyName
order by PropertyName) ep2
outer apply ( select top 1 PropertyName, PropertyValue
from EventProperties
where eventID = Events.EventID
and PropertyName>ep2.PropertyName
order by PropertyName) ep3
where Events.eventTimeStamp> '2016/1/1'
this works, but feels very unpleasant.
Is there a better way to do this?
January 27, 2016 at 1:20 am
Google for "FOR XML PATH String concatenation", then read some of the hits you'll find.
If you feel you need help piecing together the actual code, then post CREATE TABLE statements, INSERT statements with sample data, and the expected results.
January 27, 2016 at 5:50 am
Here's an excellent article on building you list: http://www.sqlservercentral.com/articles/comma+separated+list/71700/
If you're working towards converting the rows into individual columns, then this article is what you're looking for: http://www.sqlservercentral.com/articles/T-SQL/63681/
January 28, 2016 at 11:16 am
Thank you both. This is exactly what I needed.
I found that I did not need the CTE and could simply
SELECT EventID, <other event columns>
,Properties = STUFF((
SELECT ',' + PropertyName + '=' + PropertyValue
FROM EventProperties
WHERE EventID = Events.EventID
ORDER BY PropertyName
FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')
FROM Events
January 28, 2016 at 11:20 am
Excellent. Thanks for the feedback.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply