I needed to track down all the access DB’s and wasn’t sure how to do it.

One of our developers came up with a script which I modified a bit.

Original credit goes to Ravi Konaparthi for the script.

This script will look for Access Services DB’s in a SharePoint 2010 Farm.
(This version only looks at a given Web application, but you can easily modify it to search everything – have a look at the comments)

$snapin = Get-PSSnapin | Where-Object {$_.Name -eq 'Microsoft.SharePoint.Powershell'}
if ($snapin -eq $null) 
{
	Write-Host "Loading SharePoint Powershell Snapin"
    Add-PSSnapin "Microsoft.SharePoint.Powershell"
}

#Foreach loop to loop through all webs, and compare the web template and write findings to .csv file.

function Get-AccessDB()
{
    log "In Get-AccessDB"
    $url = getURL
    #NOTE - if you want to look at ALL Sites and not just a specific web application, just remove -webapplication $url from the line below.
    $sites=Get-SPSite -webapplication $url  -limit all
    foreach($Site in $sites)
	{
		log "Inspecting Site $($site.url)"
		foreach($web in $site.AllWebs)
		{
			log "inspecting web $($web.url)"
			if ($web.WebTemplate -like "*ACCSRV*")
			{
				log "Found Access DB in $($web.url)" "Green"
				$hash = @{"[URL]"=$web.Url;}
				New-Object PSObject -Property $hash | Sort-Object
			}
		}
	$site.Dispose()
	}
}

function log($txt, $color)
{
  if ($color -eq $null) { $color = "White" }
  write-host $txt -foregroundcolor $color
}

function getURL()
{
    switch(hostname)
	{
		"SPDEVPC" {$machineURL = "http://yourDevURL.yoursite.com"}
		"SPStagePC" {$machineURL = "https://yourStgURL.yoursite.com"}
		"SPProdSrv" {$machineURL = "https://yourProdURL.yoursite.com"}
		default {write-host "This should be run from either SPDEVPC, SPSTAGEPC or SPPRodSRV - Press any key to exit this script"; read-host; exit}
	}
	return $machineURL
}

Get-AccessDB | Export-csv D:\accessdb.csv

SSL certificates expire every now and again.

After getting caught off guard about an expired SSL certificate, I thought I’d search and see if I could find a powershell script I could run on the web front ends where the certs are installed.

I found a post on stack overflow that was a good starting point:
http://stackoverflow.com/questions/16181725/powershell-script-to-find-currently-bound-expiring-certificates-in-iis

I made a few simple modifications, formatting the output slightly differently, and adding email to the script, but basically what you see below is mostly from the above post, credit goes to Ansgar Wiechers for posting the solution I used.

You’ll want to test the script as is, then change the $DaysToExpiration to something more reasonable such as 30.

import-module webadministration
$DaysToExpiration = 700 #change this once it's working

$expirationDate = (Get-Date).AddDays($DaysToExpiration)

$sites = Get-Website | ? { $_.State -eq "Started" } | % { $_.Name }
$certs = Get-ChildItem IIS:SSLBindings | ? {
           $sites -contains $_.Sites.Value
         } | % { $_.Thumbprint }

$body = Get-ChildItem CERT:LocalMachine/My | ? {
  $certs -contains $_.Thumbprint -and $_.NotAfter -lt $expirationDate
}
$body | select friendlyname, Subject, @{Name="Expiration"; Expression = {$_.NotAfter}} | fl | out-string

$PCName = $env:COMPUTERNAME
$EmailFrom = "$PCName@yourdomain.com"
$EmailTo = "YourEmail@yourdomain.com"
$EmailBody = $body | select friendlyname, Subject, @{Name="Expiration"; Expression = {$_.NotAfter}} | fl | out-string
$EmailSubject = "Certificates Expiring within $DaysToExpiration days"
$SMTPServer = "yoursmtpserver.yourdomain.com"

if ($EmailBody.Length -gt 1)
{
  Write-host "Sending Email"
  Send-MailMessage -From $EmailFrom -To $EmailTo -Subject $EmailSubject -body $EmailBody -SmtpServer $SMTPServer
}

I added this script to the “Scripts” folder on my Web servers, and then created a windows “Scheduled task” to run PowerShell along with the script, scheduled nightly. I let it run a few times with $DaysToExpiration set to 700 (to confirm I would actually get the email.) After I knew that it worked I changed the number to a more reasonable 45.

Problem:
A new Developer is brought on board and needs access to the ULS logs on 10 different machines.

2 part solution:
Part 1:
This only has to be done once, so I did this manually, A powershell script would be great for this, but I don’t have one – Sorry!

• Create a local group on each server called “LogReaders”
• Share the ULS logs folder on each server
• Add “read” permissions to the “LogReaders” group to the share/NTFS permissions for the ULS log folder

Part 2:
Use the following script, updating values to match your environment –
Run the script as needed, each time a new developer needs access to the log folders

#AddUserToLogs.ps1
#this script is an adaptation of a forum post by jrv: http://social.technet.microsoft.com/Forums/en-US/ITCG/thread/35b8022e-7c0e-49fb-b4c7-346b83ed3fd0/
#jrv (http://social.technet.microsoft.com/profile/jrv/?ws=usercard-mini) provided the function Add-LocalUser
# I added the wrapper to make it work for my needs
function main {

	$user = "userIDneededtobeadded"
	$domain = "yourdomain"
	$Group = "LogReaders"
	$computers = @("computernanme1", "computername2", "computername3", "etc..") 

	foreach ($Computer in $Computers) {
		write-host $computer -foregroundcolor green
		Add-LocalUser -Computer $Computer -group $group -userdomain $domain -username $user
	}
}

function Add-LocalUser{
     Param(
        $computer=$env:computername,
        $group="LogReaders",
        $userdomain=$env:userdomain,
        $username=$env:username
    )
        ([ADSI]"WinNT://$computer/$Group,group").psbase.Invoke("Add",([ADSI]"WinNT://$domain/$user").path)
}

main

For years I’ve had this handy PowerShell Cheat Sheet at my desk, the original came from:  http://blogs.msdn.com/b/powershell/archive/2007/01/25/powershell-cheat-sheet-redux-the-pdf-version.aspx

I’ve looked a few times for an update, but never found one so I created my own.

I emailed the original author, Ben Pearce from Microsoft to ask his permission to post my updated version online. Ben replied it was ok:

Hi Jack

Thanks for sending me this.  It’s really nice to hear that you found the cheat sheet useful.  I’ve stopped working so closely with PowerShell now and actually manage a team of Premier Field Engineers.  So, I haven’t updated the sheet and I`m not aware of new version circulating.

Thanks for taking the time to update it yourself and feel free to post this on your blog.
Thanks

Ben

That said here’s the updated version – it adds a 3rd page with a few extra tips and there is a bit thrown in about $true and $false on page 1.

PowerShell Cheat Sheet V2.00

I created a new 3 page cheat sheet for SharePoint to supplement the above sheet:

SharePoint PowerShell Cheat Sheet

If you want both in the same file they can be found here:

Combined PowerShell and SharePoint Cheat Sheet

I’ve introduced co-workers to PowerShell and the Cheat Sheet is always very popular:

Today a user had a simple request – about 450 users were added to the wrong SharePoint Group.
They needed to be moved from the Members group to the Visitors group.

My first thought was to rename the groups and change permissions, but the user had already started the process and had moved about 100 of them manually before calling me.

So I had to move 350 users, and couldn’t rename and re-permission either Group.

My first though was to just dump a list of all the user ID’s from the Members Group using Powershell – I would paste them into the GUI to add them to the Visitors Group.

I used powershell to get the list of users:

$web = get-spweb "http://urlto.yourdomain.com/yourweb"
$group = $web.groups | where {$_.name -eq "Name of your Group" }
foreach ($user in $group.users) { $user.userLogin + ";" }

The above script dumped a bunch of user ID’s to the screen and my intention was to copy that right off the PowerShell screen. (Note I added the semicolon to make it easier to add the whole list in one copy/paste operation)

My first snag was that you can only paste in 200 users at a time, not a big deal, I did a few small groups.

Now I just needed to delete all the users from the Members group- Easy Right?
Well, it would be if I had less than 30- SharePoint only shows 30 members of a group at a time. You can select all 30 pretty easily, but I wanted to delete 350.

Back to Powershell
This script deletes everyone in the given group:

$web = get-spweb "http://urlto.yourdomain.com/yourweb"
$group = $web.groups | where {$_.name -eq "Name of your Group" }
foreach ($user in $group.users)
 { 
   $group.RemoveUser($user)
 }

Job Done.

Of course, at this point, I thought, shoot, I could have/should have just scripted the whole copy operation – can’t be that hard right?

$web = get-spweb "http://urlto.yourdomain.com/yourweb"
$SourceGroup = $web.groups | where {$_.name -eq "Name of your Source Group" }
$TargetGroup = $web.groups | where {$_.name -eq "Name of your Target Group" }
foreach ($user in $Sourcegroup.users)
 { 
   Write-Host "Moving $user from $SourceGroup to $TargetGroup"
   $TargetGroup.AddUser($user)
   #$SourceGroup.RemoveUser($user)
 }

Disclaimer – I haven’t tried this- by the time I got this far, I had already deleted all the users so this is more of a “for future reference” kind of script.

Ideally there might be a return value on .addUser that would let me know if it was successful, or you might run the script twice, the first time with the remove statement commented out, Then do a quick visual check that your Target Group has the users you need, then run it again to empty the SourceGroup.