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:
iamashishsharma.wordpress.com 