Home Gitesh Portfolio Blog About Me Gallery Contact

Sitecore: XDB - Find all the interaction URLs in collection table

XDB - Find all the interaction URLs in collection table.

 

##Run this query on your shard table and it will go an find all the Urls and count them too.
DECLARE @tempTable TABLE (URLs varchar(Max))
 
INSERT INTO @tempTable
SELECT LEFT(SUBSTRING([Events], CHARINDEX('Url', [Events]), 2147483647), CHARINDEX(',', SUBSTRING([Events], CHARINDEX('Url', [Events]), 2147483647)) - 1) FROM [xdb_collection].[Interactions]
 
Select REPLACE(REPLACE(URLs, '"',''), 'Url:',''), COUNT(URLs) from @tempTable
GROUP BY URLs
ORDER By 
URLs ASC

Posted: 19/08/2023 4:06:13 p.m. by Gitesh Shah | with 0 comments