PowerShell script for extracting IIS log details for specific SharePoint webapplication

Import-Module sqlps -DisableNameChecking

#Database server name
$serverName="xxxxx"
#IIS Logging database name
$databaseName="IISLoggingDB"
#Path of the file LogParser.exe
$LogParserPath="C:\Program Files (x86)\Log Parser 2.2"
#Siteid (taken from IIS server)
$SiteId="W3SVC17xxxxx"
#Path of IIS Log files
$IISLogPath="C:\inetpub\logs\LogFiles\$SiteId"
#IP Address of WFE server
$ip=get-WmiObject Win32_NetworkAdapterConfiguration|Where {$_.Ipaddress.length -gt 1}
$IPAddress=$ip.ipaddress[0]

function Main {
param
(
    [parameter(mandatory=$true)][string]$serverName,
    [parameter(mandatory=$true)][string]$databaseName        
)

 $SQLCmd="SELECT count(*) as TotalCount FROM $SiteId";
 #Invoking SQL commnds
 $IsRecordExists=Invoke-Sqlcmd -ServerInstance $serverName -Database $databaseName  -Query $SQLCmd -ErrorAction SilentlyContinue

 if($IsRecordExists.TotalCount -gt 0)
 {
    #Get last execution date  
    $SQLCmd="SELECT TOP 1 date FROM $SiteId WHERE sIp='$IPAddress' ORDER BY date DESC";
    #Invoking SQL commnds
    $LastExecutionDate=Invoke-Sqlcmd -ServerInstance $serverName -Database $databaseName  -Query $SQLCmd
    if($LastExecutionDate -ne $null)
    {
    $LastExecutionDate = get-date $LastExecutionDate.date -Format 'yyyy-MM-dd'

    #Get last execution time  
    $SQLCmd="SELECT TOP 1 time FROM $SiteId  where date='"+$LastExecutionDate+"' ORDER BY time DESC";
    #Invoking SQL commnds
    $LastExecutionTime=Invoke-Sqlcmd -ServerInstance $serverName -Database $databaseName  -Query $SQLCmd
    $LastExecutionTime = get-date $LastExecutionTime.time.AddSeconds(1) -Format 'HH:mm:ss' 
    #Last execution date time
    $LastExecutionDateTime = get-date($LastExecutionDate +" "+ $LastExecutionTime) -Format 'yyyy-MM-dd HH:mm:ss'
    $Todaydate=Get-Date -Format 'yyyy-MM-dd HH:mm:ss'
    #Locate the logparser path
    cd $LogParserPath

    if($LastExecutionDateTime -ne $null)
       {
           #Execute the logparser.exe
           #This will create a table and insert the IIS log deatils where the file name is having 'aspx' 
           .\logparser.exe "SELECT * INTO $SiteId FROM $IISLogPath\*.log WHERE (TO_TIMESTAMP(date, time) BETWEEN TIMESTAMP('$LastExecutionDateTime', 'yyyy-MM-dd HH:mm:ss') AND TIMESTAMP('$Todaydate', 'yyyy-MM-dd HH:mm:ss')) and (EXTRACT_FILENAME(cs-uri-stem) LIKE '%aspx%') and s-ip='$IPAddress'" -i:iisw3c -o:SQL -server:$serverName -database:$databaseName -createTable: ON
       }
    }
    else
    {
      #Locate the logparser path
      cd $LogParserPath
      #Execute the logparser.exe
      #This will create a table and insert the IIS log deatils where the file name is having 'aspx'
      .\logparser.exe "SELECT * INTO $SiteId FROM $IISLogPath\*.log WHERE EXTRACT_FILENAME(cs-uri-stem) LIKE '%aspx%' and s-ip='$IPAddress'" -i:iisw3c -o:SQL -server:$serverName -database:$databaseName -createTable: ON
    }
   
 }
 else
 {
     #Locate the logparser path
     cd $LogParserPath
     #Execute the logparser.exe
     #This will create a table and insert the IIS log deatils where the file name is having 'aspx'
     .\logparser.exe "SELECT * INTO $SiteId FROM $IISLogPath\*.log WHERE EXTRACT_FILENAME(cs-uri-stem) LIKE '%aspx%' and s-ip='$IPAddress'" -i:iisw3c -o:SQL -server:$serverName -database:$databaseName -createTable: ON
 }
}

Main $serverName $databaseName

Powershell Workflow: Install Powershell Vesrion 4 and Import SQLPS module

I had a requirement to collect the required site and list details from SharePoint sites and storing in SQL database. I have developed Powershell scripts and Powershell Workflows(for Multiple threading) to get the better Performance and then to reduce execution time.

In my environment Powershell Version 3.0 has installed already in the Server. I implemented powershell workflow scripts  as below,

GetSubSites.PS1 (Main script file calls other child PS1 files)

Add-PsSnapin Microsoft.SharePoint.PowerShell

## SharePoint DLL

[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")




$serverName="DEFxxxx"

$databaseName="SharePointAdminDB"

$invocation = (Get-Variable MyInvocation).Value

$directorypath = Split-Path $invocation.MyCommand.Path




function Main {




param
(
[parameter(mandatory=$true)][string]$serverName,

[parameter(mandatory=$true)][string]$databaseName,

[parameter(mandatory=$true)][string]$WebApplicationUrl



)


$webApp = Get-SPWebApplication $webApplicationURL

foreach($site in $webApp.Sites)



{
foreach($subWeb in $site.AllWebs)



{
IterateSubSites($subWeb)




$SelectSQLCmd="Select COUNT(*) as _count from SPSITES where WebapplicationUrl='"+$webApplicationURL+"' and SitecollectionUrl='"+$site.Url+"' and SubSiteUrl='"+$subWeb.Url+"'";




$recordExists = Invoke-Sqlcmd -ServerInstance $serverName -Database $databaseName -Query $SelectSQLCmd




if($recordExists._count -gt 0)



{


$SQLCmd="UPDATE SPSITES SET WebapplicationUrl='"+$webApplicationURL+"' where WebapplicationUrl='"+$webApplicationURL+"' and SitecollectionUrl='"+$site.Url+"' and SubSiteUrl='"+$subWeb.Url+"'";

Invoke-Sqlcmd -ServerInstance $serverName -Database $databaseName -Query $SQLCmd




"Update: Site URL - "+$subWeb.Url



}
else



{
$SQLCmd="INSERT INTO SPSITES (WebapplicationUrl, SitecollectionUrl, SubSiteUrl) VALUES ('" + $webApplicationURL + "','"+$site.Url+"','"+$subWeb.Url+"')"

Invoke-Sqlcmd -ServerInstance $serverName -Database $databaseName -Query $SQLCmd

"Insert: Site URL - "+ $subWeb.Url



}



}
$subWeb.Dispose()



}
$site.Dispose()



}


#Recursive function iterates all subsites

function IterateSubSites ([Microsoft.SharePoint.SPWeb]$subSite)



{
if ($subSite -ne $null)



{
if($subSite.Webs -ne $null)



{
foreach($subsites in $subSite.Webs)



{
IterateSubSites($subsites)



}

}

}

}


#Get all webapplications from SPWebapplications sql table
function GetAllWebApplicationsFromTable {




param
(
[parameter(mandatory=$true)][string]$serverName,

[parameter(mandatory=$true)][string]$databaseName,

[parameter(mandatory=$true)][string]$SPFarmName,

[parameter(mandatory=$true)][string]$SQLCmd



)
$DBConnectionString = "Data Source=$serverName; Initial Catalog=$databaseName; Integrated Security=SSPI"

$conn = New-Object System.Data.SqlClient.SqlConnection($DBConnectionString)

$conn.Open()

$cmd = new-object system.data.sqlclient.sqlcommand($SQLCmd,$conn)

$adapter = New-Object System.Data.sqlclient.sqlDataAdapter $cmd

$dsSites = New-Object System.Data.DataSet

$adapter.Fill($dsSites) | Out-Null

$conn.Close()

if($dsSites.Tables[0].rows.count -gt 0)



{
$ret=$dsSites.Tables[0]

foreach ($set in $ret)



{
$webAppUrl = $set.WebapplicationUrl




Main $serverName $databaseName $webAppUrl



}

}

}

　

　
Start-Transcript

Set-ExecutionPolicy Unrestricted




#get Farm Name and match with SQL records get webapplication urls
$SPFarm = Get-spfarm

$SPFarmName = $SPFarm.Name




#Iterate all site collections and sub sites, store in SPSITES table
GetAllWebApplicationsFromTable $serverName $databaseName $SPFarmName "select * from SPWebApplications where FarmName='$SPFarmName'"




#Iterate all list details and store in SPLISTS table
."$directorypath\GetListDetails.ps1" $serverName $databaseName




#Iterate all list details and store in SPSiteCollectionDetails table
."$directorypath\GetSiteCollectionDetails.ps1" $serverName $databaseName




#Iterate all list workflow details and store in SPListWorkflowDetails table
."$directorypath\GetListWorkflowDetails.ps1" $serverName $databaseName




#Iterate all list workflow details and store in SPListWorkflowDetails table
."$directorypath\GetSiteGroupsandUsers.ps1" $serverName $databaseName




While executing the PowerShell  scripts it throws the Out of memory exception error because of the default memory size(MaxMemoryPerShellMB) is 150MB. 

I have increased the default memory(MaxMemoryPerShellMB) to 4GB,

http://blogs.technet.com/b/heyscriptingguy/archive/2013/07/30/learn-how-to-configure-powershell-memory.aspx 

However  Windows Remote Management (WinRM) service does not use the customized value of the MaxMemoryPerShellMB quota. Instead, the WinRM service uses the default value, which is 150 MB.

https://support.microsoft.com/en-us/kb/2842230

https://social.technet.microsoft.com/Forums/windows/en-US/51810d3b-6236-44f8-99fd-10f004ad8002/winrm-quotas-setting-ignored-since-kb2506143

None of above is worked for me. So finally i have installed Powershell Version 4.0 and then installed required Features back for using Invoke SQL commands in the powershell window.

You can install Powershell Version 4.0(x64 or x86) and restart the Server,
https://www.microsoft.com/en-us/download/details.aspx?id=40855

To run invoke-sqlcmd, the below three files are required,
Microsoft® Windows PowerShell Extensions for SQL Server® 2012
Microsoft® SQL Server® 2012 Shared Management Objects
Microsoft® System CLR Types for SQL Server® 2012

https://www.microsoft.com/en-us/download/details.aspx?id=29065

 Then navigate to "C:\Program Files\Microsoft SQL Server\110\Tools\PowerShell\Modules\SQLPS"

run .\SQLPS.PS1 file in SharePoint powershell window

Execute Import-Module SQLPS

At last i can able to execute SQL commands in the powershell Version 4.0