SharePoint 2013: Service ticket history report

Introduction

I needed to extract the number of IT service tickets created per day and the cumulative growth of the ticket database, and then plot this graphically.  Such a graph would be of interest to organization users and management and also to IT staff, whose daily work efforts are primarily driven by user ticket submission.  Developing this is very simple using just a bit of PowerShell.  This posting shows you how.

Procedure

1. First, you'll need to gather some key data that will be needed in the PowerShell script:
1. Identify the path to the site hosting the ticket list.
2. Identify the name of this list.
3. Create a list to hold the ticket history metric data.  At a minimum, it will need these fields:
1. Ticket Create Date
2. Tickets Created
3. Cumulative Total
4. Identify the site that will be hosting the ticket metrics list.
5. Identify the name of this list you created.
6. Determine the start and end dates of the history you want to examine.
2. Next, on a farm server, open an elevated instance of the SharePoint Management Shell, and then execute the following script:
   

   
   If ( (Get-PSSnapin -Name "Microsoft.SharePoint.PowerShell" -ErrorAction SilentlyContinue) -eq $null )
   {
       Add-PsSnapin Microsoft.SharePoint.PowerShell
   }
   # Get source list
   # StartDate Example: 1/1/2013
   $StartDate = Get-Date "[your start date]"
   # Note that the start date actually retained is:
   # "1/1/2013 12:00 AM"
   # EndDate Example: 12/31/2013
   $EndDate = Get-Date "[your end date]"
   # this actually enters the date as "12/31/2013 12:00 AM"
   # To get something that will work best for this simple code,
   # add a day, which will then give: "1/1/2014 12:00 AM"
   $EndDate = $EndDate.AddDay(1)
   $SourceWebURL = "http://[URL to your ticket site]"
   $SourceWebListName = "[name of ticket list]"
   $SourceWeb = Get-SPWeb $SourceWebURL
   $List = $SourceWeb.Lists[$SourceWebListName]
   $SourceWeb.Dispose()
   # Now filter out unwanted dates:
   $ListItems = $List.Items | Where {($_['Created'] -ge $StartDate) -and ($_['Created'] -lt $EndDate)}
   # note that the operator "lt" was used.  This is all
   # that was needed, given the format of the EndDate.
   
   # Establish a connection to the Metrics list
   $SourceWebURL = "http://[URL to your metrics site]"
   $SourceWebListName = "[name of metrics list]"
   $SourceWeb = Get-SPWeb $SourceWebURL
   $MetricsList = $SourceWeb.Lists[$SourceWebListName]
   
   # Set looping variables
   $IncDate = $StartDate
   $CumulativeTotal = 0
   
   # Begin loop
   While ($IncDate -lt $EndDate)
   {
    $NewItem = $MetricsList.Items.Add()
    $FilteredItems = $ListItems | Where {$_['Created'].ToShortDateString() -eq $IncDate.ToShortDateString()}
    $NumberCreated = $FilteredItems.Count
    $NewItem["TicketDate"] = $IncDate.ToShortDateString()
    $NewItem["NumberCreated"] = $NumberCreated
    $CumulativeTotal = $CumulativeTotal + $NumberCreated
    $NewItem["CumulativeTotal"] = $CumulativeTotal
    $NewItem.Update()
    Write-Host (" On {0}: {1}({2})"  -f $IncDate.ToShortDateString(), $NumberCreated, $CumulativeTotal)
    $IncDate = $IncDate.AddDays(1)
   }
   $SourceWeb.Dispose()
   

3. Then add and configure chart web part:
1. Add a chart web part to a page.
2. Connect it to the metrics list
3. Configure primary and secondary axes as desired.
4. Lastly, an example
   Here's an example chart showing both daily tickets created and also the cumulative total.  Using separate axes is necessary here for otherwise the daily counts would not be visible
   
   .

References

• Changing Modified, and Created details in SharePoint
• Powershell and last modified date
• Windows PowerShell Tip of the Week: More fun with dates (and times)
• PowerShell Tutorial 9: Getting Loopy
• Calculated Columns In SharePoint 2010

Notes

• You would think that you could filter the returned set also using the date obtained from ToShortDateString().  However, I found this not to be the case: the list items that were filtered using this approach frequently included list items having valid dates that should have been included in the filtered set, but weren't.  Using the full date avoided this problem and returned the expected set of list items.  Why this was so, was not immediately apparent to me.
• TIP: when graphing the result listing, the full listing may contain periods you don't want included in the graph.  For example, for a multi-year listing of tickets, you may want to graph just the results for a particular year.  To accomplish this, add another column to the results list, say, Year, and then, when configuring the data connection for the Chart web part, filter on the desired year.  This can even be automated by adding a Calculated column that calculates the value based upon the Created date of the ticket.