Find All Users Who Accessed 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 the log parser, pass it to PowerShell to query e-mails from the 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 a list of users. Alright, we got the list of user names. And now, we needed their E-mail IDs. Let’s query the 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 a 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 a user name, we’ll have to pass only “Salaudeen” to Get-Email function).

Salaudeen Rajack

Information Technology Professional with Two decades of SharePoint Experience.

One thought on “Find All Users Who Accessed SharePoint Sites in the Past One Year

  • March 27, 2014 at 6:50 AM

    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

    Reply

Leave a Reply