Sitecore Analytics Queries

InteractionsAndFacets.sql

/** 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