Get all site collections Used space and hits

function GetAllWebApplications()

{

 write-host "Sitecollection List Extraction started...." -foregroundcolor yellow

 $webApps = Get-SPWebApplication

 foreach($webApp in $webApps)

 {

 Write-Host "Web Application : " $webApp.name "web URl :" $webApp.Url

 #Fetch the site collections from the web application.

 GenerateAllSitecollectionsInWebapplication $webApp.Url $webApp.ContentDatabases.Name

 }

}

function GenerateAllSitecollectionsInWebapplication ($url , $webAppCntDB)

{

 try

 {

 $rootSite = New-Object Microsoft.SharePoint.SPSite($url)

 $spWebApp = $rootSite.WebApplication

  

 foreach($spSite in $spWebApp.Sites)

 {

 try{

  $list = $spSite.url

 write-host $list -foregroundcolor green

 

 #Code to get the storage used in MB's

 [int]$usage = $spSite.usage.storage/1MB

 #Code to get the quota template name

 $contentService = [Microsoft.SharePoint.Administration.SPWebService]::ContentService

 $quotaTemplate = $contentService.QuotaTemplates | where {$_.QuotaID -match $spSite.Quota.QuotaID}

 write-host  $outputString -ForegroundColor Green | Format-Table –AutoSize

$WebApp = $url

$siteCollurl = $spSite.Url

$contDB = $spSite.ContentDatabase.Name

$siteCollSize = $usage.ToString() + " MB"

$prowner=""

if(![string]::IsNullOrEmpty($spSite.Owner))

{

$prowner = $spSite.Owner.ToString().split('|')[1]

}

$sconwer=""

if(![string]::IsNullOrEmpty($spSite.SecondaryContact))

{

$sconwer = $spSite.SecondaryContact.ToString().split('|')[1]

}

[int]$hits = $spSite.Usage.Hits

[int]$visits = $spSite.Usage.Visits

$lastModifiedDate = ""

if(![string]::IsNullOrEmpty($spSite.LastContentModifiedDate))

{

$lastModifiedDate = $spSite.LastContentModifiedDate.ToString().split(' ')[0].ToString()

}

 $strFileName = "c:\inside-new.xlsx"

$strProvider = "Provider=Microsoft.ACE.OLEDB.12.0"

$strDataSource = "Data Source ="+$strfilename

$strExtend = "Extended Properties=Excel 8.0"

$objConn = new-object System.Data.OleDb.OleDbConnection( ` "$strProvider;$strDataSource;$strExtend")

$sqlCommand = new-object System.Data.OleDb.OleDbCommand($strQuery)

$objConn.open()

$sqlCommand.Connection = $objConn

$strQuery = "Insert into [Sheet1$] (WebApplication,URL,ContentDatabase,SiteCollectionSize,PrimaryOwner,SecondaryOwner,NoOfHits,NoOfVisits,LastModifiedDate) values('$WebApp','$siteCollurl','$contDB','$siteCollSize','$prowner','$sconwer',$hits,$visits,'$lastModifiedDate')"

$sqlCommand.CommandText = $strQuery

$sqlCommand.ExecuteNonQuery()

$objConn.Close()

}

catch

{

$WebApp = $url

$siteCollurl = $spSite.Url

$contDB = $spSite.ContentDatabase.Name

$siteCollSize = $usage.ToString() + " MB"

$prowner=""

if(![string]::IsNullOrEmpty($spSite.Owner))

{

$prowner = $spSite.Owner.ToString().split('|')[1]

}

$sconwer=""

if(![string]::IsNullOrEmpty($spSite.SecondaryContact))

{

$sconwer = $spSite.SecondaryContact.ToString().split('|')[1]

}

[int]$hits = $spSite.Usage.Hits

[int]$visits = $spSite.Usage.Visits

$lastModifiedDate = ""

if(![string]::IsNullOrEmpty($spSite.LastContentModifiedDate))

{

$lastModifiedDate = $spSite.LastContentModifiedDate.ToString().split(' ')[0].ToString()

}

 $strFileName = "c:\inside-new.xlsx"

$strProvider = "Provider=Microsoft.ACE.OLEDB.12.0"

$strDataSource = "Data Source ="+$strfilename

$strExtend = "Extended Properties=Excel 8.0"

$objConn = new-object System.Data.OleDb.OleDbConnection( ` "$strProvider;$strDataSource;$strExtend")

$sqlCommand = new-object System.Data.OleDb.OleDbCommand($strQuery)

$objConn.open()

$sqlCommand.Connection = $objConn

$strQuery = "Insert into [Sheet1$] (WebApplication,URL,ContentDatabase,SiteCollectionSize,PrimaryOwner,SecondaryOwner,NoOfHits,NoOfVisits,LastModifiedDate) values('$WebApp','$siteCollurl','$contDB','$siteCollSize','$prowner','$sconwer',$hits,$visits,'$lastModifiedDate')"

$sqlCommand.CommandText = $strQuery

$sqlCommand.ExecuteNonQuery()

$objConn.Close()

}

 }

 }

 catch

 {

 $WebApp = $url

$siteCollurl = ""

$contDB = $webAppCntDB

$siteCollSize = ""

$prowner=""

$sconwer=""

[int]$hits = 0;

[int]$visits = 0;

$lastModifiedDate = ""

 $strFileName = "c:\inside-new.xlsx"

$strProvider = "Provider=Microsoft.ACE.OLEDB.12.0"

$strDataSource = "Data Source ="+$strfilename

$strExtend = "Extended Properties=Excel 8.0"

$objConn = new-object System.Data.OleDb.OleDbConnection( ` "$strProvider;$strDataSource;$strExtend")

$sqlCommand = new-object System.Data.OleDb.OleDbCommand($strQuery)

$objConn.open()

$sqlCommand.Connection = $objConn

$strQuery = "Insert into [Sheet1$] (WebApplication,URL,ContentDatabase,SiteCollectionSize,PrimaryOwner,SecondaryOwner,NoOfHits,NoOfVisits,LastModifiedDate) values('$WebApp','$siteCollurl','$contDB','$siteCollSize','$prowner','$sconwer',$hits,$visits,'$lastModifiedDate')"

$sqlCommand.CommandText = $strQuery

$sqlCommand.ExecuteNonQuery()

$objConn.Close()

 write-host "Unable to Extract Site Collections , May be Sitecollections does not Exist..." -foregroundcolor red

 continue

 }

}

cls

Add-PSSnapin "Microsoft.Sharepoint.Powershell"

 GetAllWebApplications

 write-host "Completed Extracting all the Site collections" -ForegroundColor Yellow