Query AD and Update SQL Express Table Using PowerShell

Requirement

Query AD [Active Directory] computers and insert into SQL Express.

Considerations

Requirement is just an one liner. We need to consider a lot before we deliver this to production.

  • Do we need all computers from AD or just enabled? Yeah! We need only enabled computers. If Admin disables / enables a computer or remove / adds a computer in AD the SQL express table should update accordingly.
  • Do we need only client operating systems? Yeah! We need this is for Client OS reporting.

Summary

To summarize the requirement customer needs a solution which should do the below tasks.

  • Insert if new Computer found in AD.
  • Drop if computer is removed.
  • Update properties if any change identified.

Solution

  • Query AD with Filter ON [Enabled -eq $true and OperatingSystem -eq ‘Windows 7 Enterprise’].
  • Insert If exists.
  • Drop Row if SQL Express rows doesn’t match AD value.[Removed from AD but exists in SQL table]
  • If new computer found insert into table.

PowerShell Code

 #Establish Connection to SQL Express
$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=Localhost\SQLExpress; Initial Catalog=Employee; Integrated Security=SSPI")
$conn.Open()

#Query AD and Update SQL Table
Get-ADComputer -Filter {(Enabled -eq $true) -and (OperatingSystem -eq 'Windows 7 Enterprise')} -Properties * | %{

$Name = $_.Name 
$OS = $_.OperatingSystem 
$LastLogonDate  = $_.LastLogonDate 
$PasswordLastSet = $_.PasswordLastSet
$Description = $_.Description 
$DN = $_.DistinguishedName
$Enabled = $_.Enabled

$cmd = $conn.CreateCommand()
$query = @"
IF EXISTS (Select * from ComputerInformation Where ComputerName = '$Name')
Delete from ComputerInformation where ComputerName <> '$Name'
ELSE
INSERT INTO ComputerInformation VALUES ('$Name' , '$OS' , '$LastLogonDate' , '$PasswordLastSet' , '$Description' , '$DN' , '$Enabled')
"@
$cmd.CommandText = $query
$cmd.ExecuteNonQuery()
}

#Close the connection
$conn.Close()

 

Exploring LYNC Contacts Using PowerShell

Exploring LYNC Contact folders using PowerShell.

Get-MailboxFolderStatistics will display all the folders information in users mailbox. So using this we can explore LYNC Contacts with the folder type ‘Quick Contacts’.

Get-MailboxFolderStatistics -Identity <MailboxID> | 
? {$_.FolderType -eq 'QuickContacts'}

Lync Contact Folders with 0 EntriesLync Contact Folders with 0 Entries

If we delete lync contacts programmatically it may appear as GUID as well. Like shown below.

Get-MailboxFolderStatistics -Identity <MailboxID> | 
Select Name , ItemsinFolder | ft -AutoSize

2Underneath Contacts we can see two GUID – That’s LYNC and GAL Contacts

Refer this article to remove LYNC contact entries using PowerShell and EWS

How to Explore all folders?

PowerShell Tip: String Manipulation using Regular Expression

Summary

  • Regular Expression is very Powerful and best way to play with strings.
  • Using REGEX in PowerShell is best way to query log files, pattern matching etc.., however we should use it as applicable.

Example Code

#Given Name : Chendrayan Venkatesan
#Required Output: Venkatesan , Chendrayan
"Chendrayan Venkatesan" -replace "([a-z]+)\s([a-z]+)" ,'$2, $1' 

#Given Name : Chendrayan12345 Venkatesan
#Required Output: Venkatesan, Chendrayan
'Chendrayan12345 Venkatesan' -replace "\d+" -replace "([a-z]+)\s([a-z]+)" ,'$2, $1'

 

Get OU Included in SharePoint User Profile Synchronization Configurations

Summary

This TechNet Wiki is to share PowerShell Snippet which retrieves Included OU in SharePoint 2010 User Profile Synchronization. Ensure you are farm admin while executing the script. Please do read the code and execute in acceptance and then in Production.

Background

This morning we received some email alerts “Your Mysite is marked for deletion”. Indeed user ID is not disabled but moved from one OU to other OU So what’s wrong in it. Nothing much if your SharePoint 2010 UP SYNC Connections has all the OU’s. If not it’s a challenge. You can review more articles in web. We started analyzing the issue – Technically each and every SP admin had different opinion.

Conclusion

Hey SP Admins get me the Included OU list in SharePoint UPS SYNC. Follow the below process

  1. Disable MySite Clean Up Timer Job.
  2. Edit Sync settings.
  3. Drill down the Forest and get me the OU names.

Solution

Above steps are easy to draft as solution document but it’s huge work for SP Admins (Indeed I am part of SP Admin Team). Running Mysite clean up job manually will become an operational task. We do have one step solution – Open SharePoint Shell Management and execute the below code

Add-PSSnapin Microsoft.SharePoint.PowerShell
$userprofileService = @(Get-SPServiceApplication | Where-Object {$_.TypeName -eq 'User Profile Service Application'})[0]
$context = [Microsoft.SharePoint.SPServiceContext]::GetContext($ups.ServiceApplicationProxyGroup,[Microsoft.SharePoint.SPSiteSubscriptionIdentifier]::Default)
$ConfigMgr = New-Object Microsoft.Office.Server.UserProfiles.UserProfileConfigManager($context)
$AD = $ConfigMgr.ConnectionManager
$AD | Select -ExpandProperty NamingContexts | Select -ExpandProperty ContainersIncluded

 

SharePoint 2010 Publishing Feature Activation

One of our clients reported an issue while editing the home page of SharePoint portal.

Error: “An Unexpected Error Occurred”

Impact: Unable to save the changes made.

Reason Provided by the customer: Power User tried to deactivate and activate the publishing feature.

Team tried reactivating the publishing feature which caused more pain. The Navigation bar was broken in the home page. (Expected SP 2010 Behavior).

Fix: Activate SP 2010 Publishing Feature using PowerShell (Force it with Proper Bag Parameter)

1. Installed SharePoint Manager 2010

2. Explored the property bag to identify the TRUE value of Publishing Feature

3. Though the User Interface shows the feature as ACTIVATED and PowerShell output gave the same result. But, the knot is the [“__PublishingFeatureActivated”] was set to FALSE in property bag.

4. No option to right click to activate (Sounds More Cranky) – But no other go, I took a chance to implement the below PowerShell code.

#Solution: SharePoint 2010 - PowerShell
#To activate the publishing feature in SP2010 portal
[System.Reflection.Assembly]::LoadWithPartialName("System.Web") | Out-Null 
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint") | Out-Null 
$site = Get-SPSite "http://www.yourportal.com/"
$web = $site.openweb()
$Web.AllProperties["__PublishingFeatureActivated"]="True" 
$Web.Update()
#If SP 2010 fails to read the __PagesListsID (Internal Name). I prefer to do the below
 
$web = get-spweb "http://www.yourportal.com/"
$correctId = $web.Lists["Pages"].ID
$web.AllProperties["__PagesListId"] = $correctId.ToString()
$web.Update()

 

Delete Lync Contact Entries in Exchange Online Using PowerShell

PowerShell code to remove LYNC Contacts entries in Exchange Online.

Code

#Target MailboxID's
$MailboxNames =  "TargetMailBoxID1" , "TargetMailBoxID1"

#Any Exchange Admin ID with appropriate permissions
$AdminID = "AdminID"

#Fetch password as secure string
$AdminPwd = Read-Host "Enter Password" -AsSecureString

#Load the Exchange Web Service DLL
$dllpath = "C:\Microsoft.Exchange.WebServices.dll"
[Reflection.Assembly]::LoadFile($dllpath)

#Create a Exchange Web Service
$Service = New-Object Microsoft.Exchange.WebServices.Data.ExchangeService([Microsoft.Exchange.WebServices.Data.ExchangeVersion]::Exchange2013_SP1)

#Credentials to impersonate the mail box
$Service.Credentials = New-Object System.Net.NetworkCredential($AdminID , $AdminPwd)
foreach($MailboxName in $MailboxNames)
{
#Impersonate using Exchange WebService Class
$Service.ImpersonatedUserId = New-Object Microsoft.Exchange.WebServices.Data.ImpersonatedUserId([Microsoft.Exchange.WebServices.Data.ConnectingIdType]::SmtpAddress, $MailboxName)
$Service.AutodiscoverUrl($MailboxName,{$true})

#Assing EWS URL
$service.Url = 'https://outlook.office365.com/EWS/Exchange.asmx'
Write-Host "Processing Mailbox: $MailboxName" -ForegroundColor Green

#Fetch Root Folder ID
$RootFolderID = New-object Microsoft.Exchange.WebServices.Data.FolderId([Microsoft.Exchange.WebServices.Data.WellKnownFolderName]::Root, $MailboxName)
$RootFolder = [Microsoft.Exchange.WebServices.Data.Folder]::Bind($Service,$RootFolderID)

#Create a Folder View
$FolderView = New-Object Microsoft.Exchange.WebServices.Data.FolderView(1000)
$FolderView.Traversal = [Microsoft.Exchange.WebServices.Data.FolderTraversal]::Deep

#Retrieve Folders from view
$response = $RootFolder.FindFolders($FolderView)

#Query Folder which has display name like Lync Contacts
$Folder =  $response | ? {$_.FolderClass -eq 'IPF.Contact.MOC.QuickContacts'}
$Folder | Select DisplayName , TotalCount

#Purge the items

$Folder.Empty([Microsoft.Exchange.WebServices.Data.DeleteMode]::SoftDelete, $false)
$Folder.Load()

Perform Delete
$Folder.Delete([Microsoft.Exchange.WebServices.Data.DeleteMode]::SoftDelete)
Write-Host "Removed Lync Contacts Folder on: $MailboxName mailbox" -ForegroundColor Green

}

 

Validate SharePoint 2010 List Fields Using PowerShell

One of my client requested for validating the list fields. The list is deployed as part of custom solutions. There is no big challenge doing it by inserting validation formula. However I took a quick look of PowerShell property to achieve the same.

List Available in SharePoint 2010

4909681_orig

Where Title is set to 255 and Age is a Number Data Type accepts range between 18 and 30. We can do this in GUI as well. Let’s see the code in PowerShell

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue
$web = Get-SPWeb -Identity 'http://host.com/subsite'
$List = $web.Lists["ListName"]
$Field = $List.Fields['Age']
$field.ValidationFormula = "=AND([Multiline]>18,[Multiline]<30)"
$field.ValidationMessage = "The value of this field is between 18 and 30"
$field.Update()

3339245_orig

Avoid Accidental Deletion of SharePoint List

One of my client requested a settings in SharePoint 2010 to avoid accidental deletion of Lists.
Indeed we can recover it from Recycle Bin within 30 days, but they need to avoid deletion. Alternatively we can make a permission settings to avoid deletion.

Let’s see how to do this using PowerShell.

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue
$web = Get-SPWeb -Identity 'http://domain.com/windows_test'
$list = $web.Lists['PowerShell']
$list | GM

6196305_orig

AllowDeletion is a Property which will allow us to set and get.

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue
$web = Get-SPWeb -Identity 'http://domaincom/windows_test'
$list = $web.Lists['PowerShell']
$list.AllowDeletion = $false
$list.Update()

3411834_orig

Reference Class
http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.splist.aspx

SharePoint 2010 : Query SharePoint List Items missing required fields.

In most of the organizations Content Managers decides the fields and its property for the List or Library. They are Power Users and have certain limitations to fetch reports.

We started discussing about Power Query and ended up in PowerShell.

Requirement:
Need to fetch Document Library Items which miss some required fields.

Scenario:
We all know that SharePoint will not allow us to submit the item if we miss the required fields. However in some cases content managers might have changed the settings and revert. During this interval we may have few items created with no required fields.

Code

$web = Get-SPWeb 'http://domain.com/subsite'
$list = $web.Lists["LibraryName"]
$data = $list.Items | ? {$_.MissingRequiredFields -eq $true}
$data | Select Title , MissingRequiredFields , ID | 
Export-Csv D:\MissingRequiredFields.csv -NoTypeInformation -Encoding UTF8