Tuesday, October 16, 2012

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.

Thursday, October 11, 2012

Interact Intranet: Noise Words


To improve processing time for search queries it is common for applications and databases to ignore certain words which have low value.  SQL Server has it's own list of noise words which you can edit and change.
Interact also has a list however I've been told they are hard-coded into the system and not customizable.
Here is the list of Interact's noise words:

1 2 3 4 5 6 7 8 9 0 a b c d e f g h i j k l m n o p q r s t u v w x y z not about after all also an and another any are as at be because been before being between both but by came can come could did do each for from get got has had he have her here him himself his how if in into is it like make many me might more most much must my never now of on only or other our out over said same see should since some still such take than that the their them then there these they this those through to too under up very was way we well were what where which while who with would you your & ? use

Curiously if you look at the Interact database you'll notice there is a table called NoiseWords which has a single, unpopulated, column called WRD.

Reference (login required): http://extranet.interact-intranet.com/Interact/Pages/Content/Document.aspx?id=4609