Find Questions Migrated Without the User

0

Please login or register to vote for this query.

(click on this box to dismiss)

Use this query to find where users have their post migrated but they never followed the question to the target site.

Craft CMS Meta

Q&A about the site for administrators, end users, developers and designers for Craft CMS

SELECT
    OwnerDisplayName
  , Posts.Id AS [Post Link]
  , PostHistory.CreationDate AS [Migration Date]
  , Posts.CreationDate AS [Original Post Date]
  , CASE  
    WHEN  SUBSTRING(
              PostHistory.Comment
            , 13
            , CHARINDEX(
                  '.'
                , SUBSTRING(
                      PostHistory.Comment
                    , 13
                    , LEN(PostHistory.Comment)
                  )
              ) -1
          ) = 'meta'
    THEN  SUBSTRING(
              PostHistory.Comment
            , 13
            , CHARINDEX(
                  '.'
                , SUBSTRING(
                      PostHistory.Comment
                    , 18 --skip 5 for 'meta.'
                    , LEN(PostHistory.Comment)
                  )
              ) + 4 -- add 5 to -1 for 'meta.'
          )
          
    ELSE  SUBSTRING(
              PostHistory.Comment
            , 13
            , CHARINDEX(
                  '.'
                , SUBSTRING(
                      PostHistory.Comment
                    , 13
                    , LEN(PostHistory.Comment)
                  )
              ) -1
          ) 
    END AS [Source Site]
FROM
  Posts
JOIN
  PostHistory ON Posts.Id = PostHistory.PostId
WHERE
  PostHistory.PostHistoryTypeId = 36 AND
  Posts.PostTypeId = 1 AND
  OwnerUserId IS NULL 
ORDER BY 5 ASC, OwnerDisplayName ASC, Posts.CreationDate DESC

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results