KPI Generation

# =================================================================================
# Description: Statistics of SharePoint (Sites, Size and Users)
# =================================================================================
Add-PsSnapin Microsoft.SharePoint.Powershell –ErrorAction SilentlyContinue
Clear-Host
Write-Host ("Start of Script.")

$WebApp  = "http://"  #URL WebApplication
$region  = "APAC"  #Region
$webApps  = "apac, cn, hk, in"  #WebApplication
$urlGroup  = "http://"  #URL group report to receive by email
$groupName  = "Team SharePoint APAC Statistics"  #Group name report to receive by email
$smtpServer = "intsmtp2.hk.hsbc"  #SMTP to send emails

$size = 0
$Users = 0
$Sites = 0
$Number = 0

function CountUsers(){
 Write-Host ("`nRead SQL database with users ") -NoNewline
 $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
 $SqlConnection.ConnectionString = "Server=" + $site.ContentDatabase.Server + ";Integrated Security=True"
 $SqlConnection.Open()
 $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
 $query = "DECLARE @TableOut TABLE(tp_Login nvarchar(255))"
 foreach($database in Get-SPDatabase | where {$_.Type -eq "content database"}){
  Write-Host (".") -NoNewline
  $query += "INSERT INTO @TableOut(tp_Login)
       SELECT DISTINCT [tp_Login]
         FROM ["+$database.Name+"].[dbo].[UserInfo]
       WHERE tp_DomainGroup = 0 AND tp_IsActive = 1
          AND tp_ExternalTokenLastUpdated > DATEADD(month, -6, GETDATE())"
 }
 $SqlCmd.CommandText = $query +
 "SELECT COUNT(DISTINCT(tp_Login)) AS Qut_Usuarios_Ativos FROM @TableOut"
 $SqlCmd.Connection = $SqlConnection
 $Users = $SqlCmd.ExecuteScalar()
 $SqlConnection.Close()
 return $Users
}

#Count Users
 $site = Get-SPSite -Identity $WebApp
 $Users = CountUsers

#Count Size
 Get-SPSite -Limit All |ForEach-Object {$size=0}{ $size+=$_.Usage.Storage/1GB}
 $size = "{0:N3}" -f $size
  
#Site Count
 $Sites = (Get-SPSite -Limit all).Count
 
#Count Number of In-active sites (90 days)
 foreach ($TopSite in Get-SPSite -limit all ){
  $Days = ((get-date) - $TopSite.LastContentModifiedDate).days
  If ($Days -ge 90) {
  $Number++
  $output += write-host ($TopSite.url + " Days Since Last Modified " + $Days)
  }
 }

 

#Results
 Write-Host ("`n`nSize....: " + $size)
 Write-Host ("Sites...: " + $Sites)
 Write-Host ("Users...: " + $Users)
 Write-host ("Number of Inactive sites: " + $Number)

#--- Writes the list of statistics Group http://******/Lists/Statistics
 $uri = "*****/teamspace/_vti_bin/lists.asmx?WSDL"
 $listName = "Statistics"
 # Create the service
 $service = New-WebServiceProxy -Uri $uri -Namespace SpWs -UseDefaultCredential
 # Create xml query to retrieve list
 $xmlDoc = new-object System.Xml.XmlDocument
 $query = $xmlDoc.CreateElement("Query")
 $viewFields = $xmlDoc.CreateElement("ViewFields")
 $queryOptions = $xmlDoc.CreateElement("QueryOptions")
 $query.set_InnerXml("FieldRef Name='Full Name'")
 $rowLimit = "1000"
 $list = $null
 $service = $null
 try {
  $service = New-WebServiceProxy -Uri $uri -Namespace SpWs -UseDefaultCredential
 } catch {
  Write-Error $_ -ErrorAction:'SilentlyContinue'
 }
 # Get name attribute values (guids) for list and view
 $ndlistview = $service.getlistandview($listName, "")
 $strlistid = $ndlistview.childnodes.item(0).name
 $strviewid = $ndlistview.childnodes.item(1).name
 # Now, we use the service object to retrieve the list.
 if ( $service -ne $null ){
  try {
   $list = $service.GetListItems($strlistid, "", $query, $viewFields, $rowLimit, $queryOptions, "")
  } catch {
   Write-Error $_ -ErrorAction:'SilentlyContinue'
  }
 }
 # Create an xmldocument object and construct a batch element and its attributes.
 $xmldoc = new-object system.xml.xmldocument
 # note that an empty viewname parameter causes the method to use the default view
 $batchelement = $xmldoc.createelement("Batch") # Capital B
 $batchelement.setattribute("onerror", "continue")
 $batchelement.setattribute("listversion", "1")
 $batchelement.setattribute("viewname", $strviewid)

 [string]$monthGr = Get-Date -format MMM
 [string]$yearGr = Get-Date -format yyyy
 # Specify methods for the batch post using caml. to update or delete, specify the id of the item,
 # and to update or add, specify the value to place in the specified column
 $xml = "<Method ID='1' Cmd='New'>" +
        "<Field Name='ID'>1</Field>" +
    "<Field Name='Title'>" + $monthGr + " " + $yearGr + "</Field>" +
             "<Field Name='Region'>" + $region + "</Field>" +
             "<Field Name='Web_x0020_Applications'>" + $webApps + "</Field>" +
             "<Field Name='Active_x0020_Users'>" + $Users + "</Field>" +
             "<Field Name='Disk_x0020_Space'>" + ('{0:N0}' -f [int]$size) + "</Field>" +
             "<Field Name='Top_x0020_Level_x0020_Sites'>" + $Sites + "</Field>" +
     "</Method>"
 # Set the xml content
 $batchelement.innerxml = $xml
 $ndreturn = $null
 $ndreturn = $service.updatelistitems($strlistid, $batchelement)
 
 
# Send e-mail to URL and GROUP
 $smtp = new-object Net.Mail.SmtpClient($smtpServer)
 $msg = new-object Net.Mail.MailMessage
 $msg.From = "**@.hk"
 
 # List e-mails to destination
 $siteGroup = Get-SPSite($urlGroup)
 $groups = $siteGroup.RootWeb.SiteGroups
 foreach ( $grp in $groups ){
  if ( $grp.name -eq $groupName ){
   foreach ( $user in $grp.users ){
    $msg.To.Add($user.Email)
      }
  }
 }
 $siteGroup.Dispose()
 $msg.Subject = "Sharepoint APAC Statistics " + (Get-Date -Format "MMMM yyy")
 $msg.IsBodyHTML = $True
 $msg.Body  = "<p style='font-family:Sans-Serif, Tahoma, Arial; font-size:12px;'>" + $region + " statistics SharePoint automatically generated by the system on " + (Get-Date -Format "dd MMMM yyyy HH:mm") + ".</p>"
 $msg.Body += "<table cellpadding='4' cellspacing='0' style='font-family:Sans-Serif, Tahoma, Arial; font-size:12px;'>"
 $msg.Body += "<tr>"
 $msg.Body += "<td width='200' style='border-bottom:1px solid #CCCCCC;'>Total number of active users</td><td width='100' style='border-bottom:1px solid #CCCCCC;'><b>" + ('{0:N0}' -f [int]$Users) + "</b></td>"
 $msg.Body += "</tr><tr>"
 $msg.Body += "<td style='border-bottom:1px solid #CCCCCC;'>Total number of Site Collection</td><td style='border-bottom:1px solid #CCCCCC;'><b>" + ('{0:N0}' -f [int]$Sites) + "</b></td>"
 $msg.Body += "</tr><tr>"
 $msg.Body += "<td style='border-bottom:1px solid #CCCCCC;'>Total disk space usage</td><td style='border-bottom:1px solid #CCCCCC;'><b>" + ('{0:N0}' -f [int]$size) + " GB</b></td>"
 $msg.Body += "</tr><tr>"
 $msg.Body += "</tr><tr>"
 $msg.Body += "<td colspan='2'><a href='http:///Statistics/Year%202013.aspx'>Statistcs posted in the SharePoint Global</a></td>"
 $msg.Body += "</tr><tr>"
 $msg.Body += "<td colspan='2' style='font-size:14px;'><br><br><b>Teams<span style='color:#FF0000;'>Space</span></b> SharePoint APAC</td>"
 $msg.Body += "</tr><tr>"
 $msg.Body += "<td colspan='2' style='font-size:14px;'><a href='http://style='text-decoration:none; color:#696969;'>http://</a></td>"
 $msg.Body += "</tr><tr>"
 $msg.Body += "<td colspan='2' style='font-size:14px;'><a href=style='text-decoration:none; color:#696969;'>http://teams.</a></td>"
 $msg.Body += "</tr><tr>"
 $msg.Body += "<td colspan='2' style='font-size:14px;'><a href='httpc' style='text-decoration:none; color:#696969;'>http</a></td>"
 $msg.Body += "</tr><tr>"
 $msg.Body += "<td colspan='2' style='font-size:14px;'><a href=style='text-decoration:none; color:#696969;'>http://a></td>"
 $msg.Body += "</tr></table>"
 $smtp.Send($msg)
 
 
$site.Dispose()
Write-Host ("Final of Script.")