Developer forum

Forum » CMS - Standard features » Orphaned statsv2 data

Orphaned statsv2 data

Scott Forsyth
Scott Forsyth
Reply

I was working on a cleanup script for stats and noticed that there appears to be a lot of orphaned stats entries. I tested on a current active DW9 and DW8 site. Both are getting daily orphaned entries.

Here a couple examples that have results in the two active sites that I tested on.

--Orphaned Statv2Object records
SELECT TOP 50 * FROM Statv2Object o WHERE o.Statv2ObjectSessionID NOT IN (SELECT Statv2SessionSessionID FROM Statv2Session) ORDER BY Statv2ObjectTimestamp DESC
 
--Orphaned Statv2SessionBot records
SELECT top 10 * FROM Statv2SessionBot sb WHERE sb.Statv2SessionSessionID NOT IN (SELECT Statv2SessionSessionID FROM Statv2Session) ORDER BY Statv2SessionTimestamp DESC

Is this expected?

Thanks,

Scott

Replies

 
Nicolai Pedersen
Nicolai Pedersen
Reply

Statv2SessionBot and Statv2Session are 2 tables containing the same data - but if DW detects the UA as a bot, the bot version is used. So all records in Statv2SessionBot  would be orphaned to Statv2Session - that is by design. And probably, you orphaned statv2object records can be found in Statv2SessionBot instead.

Makes sense?

BR Nicolai

 
Scott Forsyth
Scott Forsyth
Reply

Hi Nicolai,

That makes a lot of sense and explains a lot. I have two follow-up questions then.

Is the data duplicated in each, or in just one or the other? I assume that when DW detects if it's a bot, it will use one or the other, and not both. Is that correct? When doing cleanup of the stats, it sounds like we could trim the bot data much more frequently.

I updated the query but I'm still getting orphaned daily results. Am I misunderstanding the relationship?

SELECT * FROM Statv2Object o WHERE o.Statv2ObjectSessionID NOT IN (SELECT Statv2SessionSessionID FROM Statv2Session)
AND o.Statv2ObjectSessionID NOT IN (SELECT Statv2SessionSessionID FROM Statv2SessionBot)
ORDER BY Statv2ObjectTimestamp DESC
 
Thanks,
 
Scott

 

You must be logged in to post in the forum