I have a requirement where I need to fetch a report to pull all the active users in the SharePoint farm. We are using windows authentication in the farm.

Is there any way through which I can get the count of active users;

Below is query that I'm currently using in DB :

Select distinct tp_Title, d. dirname
from WSS_Content.dbo.UserInfo U inner join WSS_Content.dbo.AllDocs D 
             on U.tp_SiteID = D.SiteID 
              where tp_ExternalTokenLastUpdated > DATEADD(d,-1,SYSDATETIME()) 

But i do not want to query the DB instead want to find a way in Sharepoint to achieve this.

Any approach to achieve this will be appreciated.

  • 3
    define "active user". You mean all active AD accounts? You'll have to query AD for that. Aug 30, 2018 at 14:10

2 Answers 2


I think if you have configured audit settings for a site collection, you can easily get the active users.

Another custom solution



param($url = $(Read-Host -prompt "Root Site Collection Path"))

#Get the PowerShell Snapin
Add-PSSnapin "Microsoft.SharePoint.PowerShell"

#Get Root Site
$root = Get-SPSite $url

#If site was found / valid
if($root -ne $null)

     foreach($subSite in $root.AllWebs)
       $subSiteTitle = $subSite.Title
        Write-Host $subSiteTitle -ForegroundColor Magenta
        $subSiteURL = $subSite.Url
        Write-Host $subSiteURL -ForegroundColor Cyan

        #get each user
         Write-Host " ------------------------------------- " 
        foreach($user in $siteUsers){
            Write-Host   $userLoginName -ForegroundColor Yellow


        Write-Host " ------------------------------------- " 


  • This is for a particular site, i want to get the list of users across farm
    – Muskan
    Sep 11, 2018 at 11:01
  • I've corrected the error,I assumed, if you could get it for one site, you should be able to just iterate and get it for all, apologies.
    – Napstar
    Sep 11, 2018 at 19:38
  • There are more than 400 application running under the a single farm, Iterating the user per site is not feasible. i want to know a method where I can find the list of all users in a farm who are active i.e have logged in system for ex say last 30 days
    – Muskan
    Sep 12, 2018 at 9:52
  • The only other option I can think of for the scenario you mentioned, is to query the User Profile Service.
    – Napstar
    Sep 13, 2018 at 14:05
  • But then there is not flag in user profile through which I can identify the active users.
    – Muskan
    Sep 17, 2018 at 9:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.