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:
- 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.
- Execute this log parser query by substituting LOG folders path, from any one of the Web Front end
- 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).
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