Wednesday, March 5, 2014

Find All Users who Accessed the SharePoint Sites in the Past One Year

Requirement: Get the E-mail ids of all users who accessed SharePoint sites during the past one year!

Solution: Lets use Log Parser and PowerShell to fulfill this requirement. Here is how:

  1. Locate your SharePoint web application's log folders from all web front servers (usually: C:\WINDOWS\system32\LogFiles\W3SVCxxxxxxxxx\") using IIS. Make a note of them.
  2. Execute this log parser query by substituting LOG folders path, from any one of the Web Front end
  3. Use the data we received from log parser, pass it to PowerShell to query e-mails from active directory.
Log parser query to get all users:
@path=C:\Program Files\Log Parser 2.2\

LogParser -i:W3C "SELECT TO_LOWERCASE(cs-username)  As UserName, count(*) as [Total Hits] INTO PortalUsersFeb.csv FROM E:\MOSS2007\LogFiles\W3SVC1297965057\*, \\MOSS07-WFE02\E$\MOSS2007\LogFiles\W3SVC1297965057\*, \\MOSS07-WFE03\E$\MOSS2007\LogFiles\W3SVC1297965057\* WHERE date > '2013-03-01' AND cs-username Is Not Null group by TO_LOWERCASE(cs-username)" -o:CSV

pause
Place the above code in a batch file(.bat) and execute from any SharePoint web front end. This should give a CSV file with list of users. Alright, we got the list of user names. And now, we needed their E-mail IDs. Lets query active directory to get the E-mail ids of the users.

Query AD to Get user E-mail from user ID:
#Function to Check if an User exists in AD
function Get-Email()
   {
   Param( [Parameter(Mandatory=$true)] [string]$UserLoginID )
 
  #Search the User in AD
  $forest = [System.DirectoryServices.ActiveDirectory.Forest]::GetCurrentForest()
  #To Search on other forests use these three lines:
  #$ForestName ="corporate.crescent.org"
  #$adCtx = New-Object System.DirectoryServices.ActiveDirectory.DirectoryContext("forest", $ForestName)    
  #$forest = ([System.DirectoryServices.ActiveDirectory.Forest]::GetForest($adCtx))    
  
  #Search in all domains
  foreach ($Domain in $forest.Domains)
  {
   $context = new-object System.DirectoryServices.ActiveDirectory.DirectoryContext("Domain", $Domain.Name)
         $domain = [System.DirectoryServices.ActiveDirectory.Domain]::GetDomain($context)
   
   $root = $domain.GetDirectoryEntry()
         $search = [System.DirectoryServices.DirectorySearcher]$root
         $search.Filter = "(&(objectCategory=User)(samAccountName=$UserLoginID))"
         $result = $search.FindOne()

         #If user found
         if ($result -ne $null)
         {
           $result.Properties["mail"]
         }
   }  
 }

##Read the CSV file - Map the Columns to Named Header
$CSVData = Import-CSV -path "E:\Users.csv" -Header("UserAccount")
#Iterate through each Row in the CSV
foreach ($row in $CSVData)
 {
    $mail = Get-Email $row.UserAccount
 $row.UserAccount +","+ $mail >>"E:\data\Mails.csv"
  }
This script outputs a CSV file by searching AD. Please note, the $UserLoginID parameter is simply user's login name without domain. (E.g. if Global\Salaudeen is the user name, we'll have to pass only "Salaudeen" to Get-Email function).



You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Check out these SharePoint products:

1 comment :

  1. Hi Sal,
    Fantastic post! - keep them coming.....

    Do you know how to list the users and the URLS i.e. sites / pages they have visited over the past one year? This would be for an intranet SharePoint 2010 publishing site with AD authentication turned on.
    Thanks Cliff

    ReplyDelete

Please Login and comment to get your questions answered!

You might also like:

Related Posts Plugin for WordPress, Blogger...