Sitecore xDB Troubleshooting

Below are the some of the useful xDB troubleshooting reference:

1. Determine the 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)

2. Determine the number of 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])

3. Extract 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’

4. Determine xDB data flow and 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 without ‘| 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”



For more detailed information related to xDB troubleshooting refer the below document:

Troubleshooting xDB data issues

Troubleshooting xDB Cloud