Interact Intranet: Document Types and Content Queries

We are going through a migration effort from our old intranet (Plumtree) to Interact Intranet.  In doing so we'd like to track a few things that aren't available from the out of the box statistics tool.  Fortunately the database shows that quite a lot of data is being tracked so in this post I'll write about the queries that I run to help us track our content.

Document Types

There are 7 documents types in the system:

• 10 - web based document
• 20 - document: link to something?
• 30 - document: link to something?
• 40 - document: upload a document (file)
• 50 - document: link to something on the network
• 100 - discussion forum threads, and for each document that has a comment section it also gets an entry in this table
• 110 - this is the catch all document type which makes it awkward to identify specific content types.  Type 110 includes: discussion forums posts, document comments, and activity wall posts (among others I'm sure!)

Important Queries (for me to remember)

who has submitted the most content

• in the system
  

  SELECT COUNT(CONTENT.AddedBy) as Count, PERSON.Firstname, PERSON.Surname
    FROM CONTENT
    INNER JOIN PERSON
    ON CONTENT.AddedBy=PERSON.PersonID
    WHERE CONTENT.TYPEID in ('40','10')
    GROUP BY CONTENT.AddedBy, PERSON.Surname, PERSON.Firstname
    ORDER BY COUNT(CONTENT.AddedBy) DESC

  
  
  
  
• in each area
  

  SELECT SECTION.Title as Area, COUNT(CONTENT.AddedBy) as Count, PERSON.Firstname, PERSON.Surname
    FROM CONTENT
    INNER JOIN PERSON
    ON CONTENT.AddedBy=PERSON.PersonID
     INNER JOIN SECTION
    ON SECTION.SectionID=CONTENT.SectionID
    WHERE CONTENT.TYPEID in ('40','10')
    GROUP BY CONTENT.AddedBy, PERSON.Surname, PERSON.Firstname, SECTION.title, CONTENT.SectionID
    ORDER BY COUNT(CONTENT.AddedBy) DESC

  
  
  
  

how many documents are file based and how many are web pages

• in the system
  

  SELECT CONTENT.TYPEID, COUNT(*) as Count
    FROM CONTENT
    Where CONTENT.TypeID in ('40', '10')
    Group BY CONTENT.TypeID

  
  
  
  
  If you are curious about the other types of documents you could run a query like this to see the count for each different type:
    SELECT TYPEID, COUNT(*) as Count
        FROM CONTENT
        GROUP BY TypeID
  
  
  
  NOTE: If you look at Interact Site Statistics "Size of Intranet" that seems to reflect the majority of what is shown here.  I have a 10 item discrepancy between the sum at this point, so perhaps 110, 50 and 30 aren't included in that number.

how many documents have been added

• to the system
  

  SELECT COUNT(*) as Count
    FROM CONTENT
    Where CONTENT.TypeID in ('40', '10')

  
• to each area/section
  

  SELECT SECTION.Title, COUNT(*)
    FROM CONTENT
    INNER JOIN SECTION
    ON SECTION.SectionID=CONTENT.SectionID
    WHERE CONTENT.TypeID in ('40','10')
    GROUP BY SECTION.Title
    ORDER BY SECTION.Title