Interact Intranet: Administrative Queries

We are getting close to our go-live date with Interact Intranet and it is time for us to do final cleanup to ensure our site is as consistent as possible.  To supplement the out of the box metrics I created a post of document queries, and this post of queries is going to focus on content structure and organization.

Absolute vs Relative Links

If you have a tendency to link to content (documents, teams, areas, categories, etc) within your intranet you might find that some of your links are absolute and others are relative.  This becomes an issue if you use different URLs for intranet, extranet or outside users. Use this query to verify that all of your links are relative:

SELECT SECTION.Title, SECTION2.Title as Parent, SMI.URL from SECTION_PROPERTIES_SIDEMENUITEM as SMI
INNER JOIN SECTION
on SECTION.SectionID=SMI.SectionID
INNER JOIN SECTION as SECTION2
on SECTION.ParentID=SECTION2.SectionID
WHERE SMI.URL like 'http://%'

Links Specify Page Format

If you create links within your intranet to other areas you might find that some of your links were created using a URL which specifies formatting.  All links which aim at Section/Main should use the default.aspx as opposed to using a defined .aspx page such as MainTwoColumnsLeft MainOneColumnLeft, etc.  You can verify that your site is clean by using this query:

SELECT SECTION.Title, SECTION2.Title as Parent, SMI.URL  FROM SECTION_PROPERTIES_SIDEMENUITEM as SMI
INNER JOIN SECTION
on SECTION.SectionID=SMI.SectionID
INNER JOIN SECTION as SECTION2
on SECTION.ParentID=SECTION2.SectionID
WHERE SMI.URL like '/Interact/Pages/Section/Main%'
ORDER BY Parent

Inconsistent Links

If you create links on multiple areas pointing to the same piece of content you might find that some of your links aren't consistent.  You can verify that your links are consistent using this query and swapping out Expense and Award for your commonly used link titles:

SELECT SECTION.Title, SECTION2.Title as Parent, SMI.URL from SECTION
INNER JOIN SECTION_PROPERTIES_SIDEMENUITEM as SMI
on SECTION.SectionID=SMI.SectionID
INNER JOIN SECTION as SECTION2
on SECTION.ParentID=SECTION2.SectionID
WHERE SECTION.TypeID='3' and (SECTION.Title like 'Expense%' OR SECTION.Title like 'Award%')
ORDER BY SECTION.Title

Inconsistent Category Options

Within a category there are a series of options which can be set including things like sort order.  If you have a large intranet and you deviate from the defaults you might want to verify that your categories are somewhat consistent.  You can access the consistency of your categories using this query and swapping out the red text with however you've set your default options:

SELECT SECTION2.Title as Parent, SECTION.Title, SMI.ShowAuthor, SMI.DefaultOrdering, SMI.ShowPerPage, SMI.TemplateID FROM SECTION
INNER JOIN SECTION_PROPERTIES_SIDEMENUITEM as SMI
on SECTION.SectionID = SMI.SectionID
INNER JOIN SECTION as SECTION2
on SECTION.ParentID=SECTION2.SectionID
WHERE SECTION.TypeID='3' and SMI.TypeID='1' and (SMI.ShowAuthor!=1 or SMI.DefaultOrdering!=1 or SMI.ShowPerPage!=1 or SMI.TemplateID!=1)

What Categories Are Empty

Curious what categories on your site don't have any documents?
Sections >1790 (on my site) are admin and modules areas which is why I eliminated them.

SELECT SECTION.sectionID, Section2.Title as Parent, SECTION.Title FROM SECTION
INNER JOIN SECTION as section2
on SECTION.ParentID=section2.SectionID
WHERE SECTION.SectionID>'1790' and SECTION.Title not like ('%Home') and SECTION.TypeID='3'
and NOT EXISTS
(SELECT * FROM CONTENT_SECTION WHERE SECTION.SectionID=CONTENT_SECTION.SectionID)
ORDER BY Parent asc

User Last Login

Curious which users haven't logged into your site in the last few days, weeks months? This query will show the last time all users in your system logged in.

SELECT Surname, Firstname, DateLastAccessed from PERSON
ORDER BY DateLastAccessed desc

I'll continue to add to this list as I generate additional queries.  Feel free to lob out any suggestions for queries and I'll see if I can add them to the list.