Skip to content

Instantly share code, notes, and snippets.

@nickwesselman
Last active June 12, 2020 12:29
Show Gist options
  • Save nickwesselman/1951c0d26ce0427e88c8f4e1f42a398d to your computer and use it in GitHub Desktop.
Save nickwesselman/1951c0d26ce0427e88c8f4e1f42a398d to your computer and use it in GitHub Desktop.
Sitecore Analytics Queries
/** Get the interactions. **/
select [InteractionId], [LastModified], [EventType], [PageUrl], [ItemLanguage], [DefinitionId], [EventText]
FROM
(SELECT TOP (1000) [InteractionId]
,[LastModified]
,[Events]
FROM [Sitecore.Xdb.Collection.Shard0].[xdb_collection].[Interactions]
UNION
SELECT TOP (1000) [InteractionId]
,[LastModified]
,[Events]
FROM [Sitecore.Xdb.Collection.Shard1].[xdb_collection].[Interactions]) as Interactions
cross apply openjson(Interactions.Events) with (
EventType nvarchar(256) '$."@odata.type"',
DefinitionId nvarchar(64) '$.DefinitionId',
EventText nvarchar(64) '$.Text',
PageUrl nvarchar(1024) '$.Url',
ItemLanguage nvarchar(5) '$.ItemLanguage'
) as events
/** Get the facets. **/
SELECT [InteractionId] ,[FacetKey],[ContactId],[LastModified],[FacetType],[IpAddress],[Browser],[SiteName]
FROM
(SELECT TOP (1000) [InteractionId]
,[FacetKey]
,[ContactId]
,[LastModified]
,[FacetData]
FROM [Sitecore.Xdb.Collection.Shard0].[xdb_collection].[InteractionFacets]
UNION
SELECT TOP (1000) [InteractionId]
,[FacetKey]
,[ContactId]
,[LastModified]
,[FacetData]
FROM [Sitecore.Xdb.Collection.Shard1].[xdb_collection].[InteractionFacets]) as InteractionFacets
cross apply openjson(InteractionFacets.FacetData) with (
FacetType nvarchar(256) '$."@odata.type"',
IpAddress nvarchar(64) '$.IpAddress',
Browser nvarchar(64) '$.Browser.BrowserMajorName',
SiteName nvarchar(1024) '$.SiteName'
) as facetdata
/** Get contact facets **/
SELECT [ContactId],[FacetKey],[LastModified],[FacetType],[IpAddress],[Browser],[SiteName],[ClassificationLevel]
FROM
(SELECT TOP (1000) [ContactId]
,[FacetKey]
,[LastModified]
,[FacetData]
FROM [Sitecore.Xdb.Collection.Shard0].[xdb_collection].[ContactFacets]
UNION
SELECT TOP (1000) [ContactId]
,[FacetKey]
,[LastModified]
,[FacetData]
FROM [Sitecore.Xdb.Collection.Shard1].[xdb_collection].[ContactFacets]) as ContactFacets
cross apply openjson(ContactFacets.FacetData) with (
FacetType nvarchar(256) '$."@odata.type"',
ClassificationLevel nvarchar(16) '$.ClassificationLevel',
IpAddress nvarchar(64) '$.IpAddress',
Browser nvarchar(64) '$.Browser.BrowserMajorName',
SiteName nvarchar(1024) '$.SiteName'
) as facetdata
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment