Archive

Archive for September, 2015

Query SharePoint 2010 HTTPRequest Pages for JQuery References Utilizing PowerShell

September 23, 2015 Leave a comment

I had a request from a customer who needed me to do a HTTP inline search query on a specific web application for all references of JQuery that was being utilized on all Site Collections and Sites within that Web Application.  Since the Web Application had over 1300 sites I needed to turn to PowerShell to iterate through all Sites, stream through the HTTPRequst pages and generate separate reports where JQuery references were found.

Below is how I accomplished this task using Powershell

————————————————————————————
Add-PSSnappin “Microsoft.SharePoint.PowerShell” -ErrorAction SilentlyContinue

$WebApp = Get-SPWebApplication(“URL OF WEB APPLICATION“)

$results = “PATH TO STORE HTTPRequest PAGE STREAMS
$JQueryResults = “PATH TO STORE JQUERY RESULTS FROM PAGE STREAMS

foreach ($site in $WebApp.Sites)
{
foreach ($web in $site.AllWebs)
{
[net.httpWebRequest] $request = [net.WebRequest]::create($web.Url)
$request.TimeOut = 600000
$request.UseDefaultCredentials = $true
[net.httpWebResponse] $response = $request.getResponse()
$responseURI = $response.ResponseURI
$responseAbsoluteURI = $responseURI.AbsoluteUri
$responseStream = $response.getResponseStream()
$streamReader = New-Object IO.StreamReader($responseStream)

$pathResults = $Results + “results_” + $web.Title + “.txt”
$result = $streamReader.ReadToEnd() >> $pathResults

$find = select-string -Path $pathResults -pattern “Jquery”
$jqueryPath = $JQueryResults + “jqueryresults_” + $web.Title + “.txt”
$web.Title >> $jqueryPath
$responseAbsoluteURI >> jqueryPath
“” >> $jqueryPath
$find  -replace [regex]::Escape($pathResults) >> $jqueryPath
}
}
————————————————————————————

 

 

Query CRM/TMT Database for Disabled Users / Generate Report / Email Utilizing PowerShell

September 22, 2015 Leave a comment

Working for the Department of Defense (DoD) I have the pleasure to work with many technologies that many people don’t.  This would be the case here for CRM/TMT.  I had a weekly request to query the CRM/TMT database to generate a report for all users within TMT whose accounts were disabled so that our CRM / TMT engineer could parse these accounts out.  So instead of manually executing a T-SQL query every week I turned to PowerShell to do all the heavy lifting, and automate the process.  Below is how I accomplish that.

On the SQL server where the TMT/CRM database lives I created a SQL Server Agent Job using PowerShell.

———————————————————————————————————
$Server = “SQL SERVER\INSTANCE
$Database = “CRMTMT_DATABASE
$query = “select b.ava_spidentity, max(a.createdon) lasacessdate from Audit
a inner join SystemUserExtensionBase b on a.ObjectId = b.SystemUserId
inner join systemuserbase c on b.SystemUserId = c.SystemUserId where a.Operation = 4 and c.isDisabled = 0
group by b.ava_spidentity”

$today = Get-Date
$filedate =$today.toString(‘MM-dd-yyyy’)

$extractfile = “PATH OF CRM REPORTS\CRM-DisabledAccounts_” + $filedate + “.csv”

$connectionTemplate = “Data Source={0};Integrated Security=SSPI;Initial Catalog={1};”
$connectionString = [string]::Format($connectionTemplate, $Server, $Database)
$connection = New-Object System.Data.SqlClient.sqlConnection
$connection.ConnectionString = $connectionString

$command = New-Object System.Data.SqlClient.SqlCommand
$command.CommandText = $query
$command.Connection = $connection

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $command
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$connection.Close()

$DataSet.Table[0] | Export-Csv $extractfile

—————————————————————————————————

On the server that will run the Task Scheduler Job and Send the Email via SMTP Services.

Create a new Task Schedule job and create a .ps1 file with the script below

—————————————————————————————————

$CRMDirectory = “\\PATH TO CRM REPORT ON SQL
$getCRMReports = Get-ChildItem $CRMDirectory
$getCRMReports | Copy-Item “\\LOCAL PATH TO COPY CRM REPORT
Get-ChildItem  “\\PATH TO CRM REPORTS ON SQL” | Remove-Item -Force

$directory = “\\LOCAL PATH WHERE CRM REPORTS WHERE COPIED
$GetLatestCRMDisabledList = Get-ChildItem $directory

$attachment = “LOCAL PATH OF CRM REPORTS\” + $GetLastestCRMDisabledList

att1 = New-Object Net.Mail.Attachment($attachment)
$email = New-Object System.Net.Mail.MailMessage
$email.From = “EMAIL OF SENDER
$email.To.Add = “EMAIL TO RECEIVER
$email.Subject = “EMAIL SUBJECT
$email.Body = “EMAIL BODY
$email.Attachments.Add($att1)
$SmtpServer = “IP / NAME OF SMTP SERVER’
$Smtp = New-Object Net.Mail.SmtpClient($smtpServer)
$Smtp.Send($email)

Get-ChildItem “\\LOCAL PATH OF CRM REPORTS” | Remove-Item -force

—————————————————————————————————-

I set the task schedule to run once a week on Thursdays, and call it a day.

Set SQL 2012 DBs to Read Only using PowerShell

September 9, 2015 Leave a comment

I needed to set all my SharePoint 2010 Content DBs to Read-Only within SQL for my DR COOP environment but I did not want to have to go to each DBs Properties-Options-ReadOnly setting since I have quite a few SharePoint Content DBs so I wrote a PowerShell script to help me out.

For those that are interested on how I accomplish this.

—————————————————————–

#Load SQL Server SMO assemblies
[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.Smo’) | out-null
#Make Connection to SQL Server/Instance
$sqlServer = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) “NAME OF SQL SERVER/INSTANCE”
#Grab all user DBs
$dbs = $sqlServer.databases

#Foreach loop to loop through all user dbs attached in SQL
foreach ($db in $dbs)
{
#If statement to search for only user dbs with SPContent as part of its name, and exclude the CA database.  Here you would substitute SPContent for the naming convention of your DBs.
if ($db.Name -like “*SPContent_*” -and $db.Name -notlike “*SPContent_Admin*”)
{
$dbname = $db.Name
$DBObject = $sqlServer.Databases[$dbname]
#Kill all the DBs processes to allow for database alter
Write-Host “Killing $dbname Proccess”
$sqlServer.KillAllProcesses($dbname)
#Set db object to ReadOnly set flag as $false; if you want to revert back set flag to $true (read/write)
Write-Host “Setting $dbname to Read-Only”
$DBObject.DatabaseOptions.ReadOnly = $false
#Alter DB Object
$DBObject.Alter()
}
}
Write-Host “Done’

——————————————————————