Press "Enter" to skip to content

Useful xDB troubleshooting queries

There is not much structure to be found in this post, I will mainly use it as a location to keep track of useful xDB troubleshooting tips and tricks.

How does all my xDB data flow and how to pinpoint/analyze issues:

  • To check what user you are within xDB you will need to have the value of your SC_ANALYTICS_GLOBAL_COOKIE. Copy the value withouth ‘| False’.
  • To force a Session_End you can upload the following .aspx file to a location on your website: “here – Now browse through your website(s), and end the session by hitting the sessionkill.aspx. This should trigger the the xDB data to be send towards your SQL shards.
  • Connect to your SQL server and execute the query below on every shard:

SELECT *
FROM [xdb_collection].[DeviceProfiles]
where DeviceProfileId like ‘%VALUEFROMCOOKIE%’
order by LastModified desc

  • The ID that is found in column “LastKnownContactId” – will map a new “ContactId” – “XXX” – This ID will be used for identification, for example in the Interaction table.
  • To check whether page events are written to the “interaction” tables

                SELECT * from

SELECT * from [xdb_collection] .[Interactions]
where ContactId = ‘XXX’

  • In case you are missing interactions, you might want to check Application Insights whether there are Session_End submits that are failing [KQL] :

traces
| where timestamp > ago(30d)
| where operation_Name contains “GET /SESSION END”

Extracting Email addresses from xDB shards:

select c.contactid, c.created, c.LastModified, cf.FacetData, JSON_VALUE(cf.FacetData, ‘$.PreferredEmail.SmtpAddress’) AS Email
from [db-shard0db].[xdb_collection].[ContactFacets] cf
inner join [db-shard0db].[xdb_collection].[Contacts] c on c.ContactId = cf.ContactId
where cf.FacetKey = ‘Emails’

How many visits by language and month, run against your reporting database:        

select SiteNameId,
DimensionKey,
DATEPART(year,[Date]) as ‘Year’,
DATEPART(month,[Date]) as ‘Month’,
sum(visits) as ‘NumberOfInteractions’
from [dbo].[Fact_LanguageMetrics] lm
inner join [dbo].[DimensionKeys] dk on lm.DimensionKeyId = dk.DimensionKeyId
group by SiteNameId,
DimensionKey,
DATEPART(year,[Date]),
DATEPART(month,[Date])
order by SiteNameId,
DimensionKey,
DATEPART(year,[Date]),
DATEPART(month,[Date])

How many interactions for each facet by month, run against your xDB shards:

select facetKey,
DATEPART(year,lastmodified) as ‘Year’,
DATEPART(month,lastmodified) as ‘Month’,
count(distinct interactionId) as ‘NumberOfInteractions’
from [xdb_collection].[InteractionFacets]
group by facetKey, DATEPART(year,lastmodified), DATEPART(month,lastmodified)
order by facetKey, DATEPART(year,lastmodified), DATEPART(month,lastmodified)

Be First to Comment

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.