Reading XML with PowerShell

Here is an example of accessing XML data via PowerShell:

Script:

$xmlDocument = [xml] @"
<catalog>
   <book id="101">
      <author>Author1</author>
      <title>Title1</title>
      <genre>Genere1</genre>
      <price>45.95</price>
      <publish_date>2000-10-01</publish_date>
      <description>This is a description1.</description>
   </book>
   <book id="101">
      <author>Author2</author>
      <title>Title2</title>
      <genre>Genere2</genre>
      <price>75.99</price>
      <publish_date>1998-7-15</publish_date>
      <description>This is a description2.</description>
   </book>
</catalog>
"@

$xmlDocument.catalog.book | Select-Object -Property author, title, price

Result:

PS C:\Users\tnaveed\Desktop> C:\Users\tnaveed\Desktop\CheckUser.ps1

author                         title                         price                        
------                         -----                         -----                        
Author1                        Title1                        45.95                        
Author2                        Title2                        75.99                        

SharePoint 2010 to 2013 Migration

Following are few resources for migrating SharePoint 2010 Content Database to SharePoint 2013:

SharePoint 2013 Upgrade Process
http://www.microsoft.com/en-us/download/details.aspx?id=30371

SharePoint 2013 Upgrade Step by Step From SharePoint 2010
http://www.c-sharpcorner.com/UploadFile/Roji.Joy/sharepoint-2013-upgrade-step-by-step-from-sharepoint-2010

SharePoint 2010 database migration to SharePoint 2013
http://en.share-gate.com/blog/step-by-step-upgrade-sharepoint-2010-database-to-sharepoint-2013

SSIS - Remove Rows with Null Values

Scenario:
I was importing a CSV file with two columns (GPID, MemberOf) in a SSIS package when I found that GPID column might have null values and I don't need the entire row in my output if the GPID is null.

Solution:
One solution is to handle it via Script but why write code when Conditional Split is available.

Conditional Split has function ISNULL([Column Name]) which takes Column name to check if its null or not and returns the output if condition meets.

SQL Server Integration Service - NULL Values

Scenario:
I had a scenario where I will get the AD export in CSV format with two columns (samaccountname, GUID) and I had to check if the GUID is null then put the value "0" instead of null value.

Solution:
1. Load the CSV file into SSIS project
2. Make data connection.
3. Add the script component.

4. Edit the Script and overwrite the following function:

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    if (Row.GUID_Input.Length > 0)
    {
        Row.GUID_Output = Row.GUID_Input;
    }
    else
    {
        Row.GUID_Output = "0";
    }

}

Export AD Users from a Group via PowerShell

I have to write this script recenely to export user and their few properties via PowerShell.

Import-Module ActiveDirectory

$ADGroup = "GroupName"
write-host "Accessing all users from group:" $ADGroup
Get-ADGroupMember -identity $ADGroup -recursive | select distinguishedName,name | Export-csv -path C:\Temp\users.csv -NoTypeInformation
write-host "Done."

Watch For:
You might see the following error:
Get-ADGroupMember : The size limit for this request was exceeded

Reason:
Default number of objects returned from AD are limited to 5,000 by default.

Resolution: Open the config file for ADWS
C:\Windows\ADWS\Microsoft.ActiveDirectory.WebServices.exe.config
and add the following line under <appsettings> tag
<add key="MaxGroupOrMemberEntries" value="25000">

Real Work Happens Here

Yup...Real work happens here.

SharePoint 2007 - Delete Global Navigation using PowerShell

Scenario:
Recently support team at my client found out that for some reason too many links (more than 1000) got created under a heading node in the Global Navigation in one of our SharePoint 2007 portals and it is making the whole site collection slow.

When I try to delete the group node from the UI, it gets deleted but the child nodes become the parent nodes. Funny....but not so funny when it is in production environment. So I wrote following the script in Powershell 2.0 for SharePoint 2007 to kill the parent node (and it will automatically delete all the child nodes under it).

Solution:

[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.Sharepoint”)

$siteURL = "https://sharepoint2007portal"
$spSite = new-object Microsoft.Sharepoint.SPSite($siteUrl)
$spWeb = $spSite.RootWeb

Foreach($node in ($spWeb.Navigation.TopNavigationBar))
{
    
    if ($node.Title -eq "MyHeading")
    {
        write-host "Node: " $node.Title

        //this will delete the node and its child
        $node.Delete() 

        write-host "Deleted successfully."
    }
    
}
$spWeb.Dispose() 

write-host "Done."

Content Approval on Multiple Lists

Scenario:
One of my colleague wanted to activate the Content Approval on a couple of lists and libraries within a site and we know that going to the settings of 25 libraries for the same setting is easy but a pain.

Solution:
This can be done through Powershell.

Add-PSSnapin "Microsoft.SharePoint.PowerShell"

$siteUrl="http://www.SharePointBlue.com"
$lists = @("Documents","New Documents","ListA")


$site=Get-SPSite $siteUrl
$web=$site.RootWeb

ForEach ($listName in $lists) 
{
    try
    {
        $list=$web.Lists[$listName]
        $list.EnableModeration = $false
        $list.Update()

        $MSG = $Now.ToString() +  " | Content Approval activated on list: " + $listName
        write-host $MSG
    }
    catch [system.exception]
    {
        $Now = [System.DateTime]::Now
        $MSG = $Now.ToString() +  "Exception: " + $_.Exception.Message
        write-host -f red $MSG
    }
}

Real Quick JSON Example

JSON is better to handle than XML. Was using it in a project so thought to post here,

<div id="dvResult">Loading...</div>

<script>
var data = [
    {"firstName":"Michael","lastName":"Jackson","SSN":"123456789"}, 
    {"firstName":"Barack","Obama":"Smith","SSN":"124456789"},
    {"firstName":"Peter","lastName":"Jackson","SSN":"125456789"}
];

var x = '';

for (i = 0; i < data.length; i++)
{
    x = x + data[i].firstName + " " + data[i].lastName + " " + data[i].SSN + "<br/>";
}

document.getElementById("dvResult").innerHTML = x;

</script>

Bulk Site Collection Deletion using PowerShell

Description:
Following powershell reads the list of site collections from the file and deleted them:

Add-PSSnapin "Microsoft.SharePoint.PowerShell"

function WriteLog
{
    Param([string]$message, [string]$logFilePath)
    Add-Content -Path $logFilePath -Value $message
}

$LogFile = "G:\DeletedSites.log"
$ListFile = "G:\List.csv"

$Sites = Import-CSV $ListFile

ForEach ($S in $Sites) 
{
    
    try
    {  

        $URL = $S.URL

        $Now = [System.DateTime]::Now
        $MSG = $Now.ToString() +  " | Deleting site: " + $URL
        write-host $MSG
        WriteLog $MSG $LogFile
        
        Remove-SPSite -Identity $URL -GradualDelete -Confirm:$false


    }
    catch [system.exception]
    {
        $Now = [System.DateTime]::Now
        $MSG = $Now.ToString() + "Exp: " + $_.Exception.Message
        write-host -f red $MSG
        WriteLog $MSG $LogFile
    }

    $Site = $Null
}

write-host "Done."

Get Claims Programmatically

And that's how you get all the claims for a user programmatically in .NET. Almost similar to my older post:

public void GetClaims()
{

    System.Security.Principal.IPrincipal ipl = System.Web.HttpContext.Current.User;
    System.Security.Claims.ClaimsIdentity claimsIdentity = (System.Security.Claims.ClaimsIdentity)ipl.Identity;
    foreach (System.Security.Claims.Claim oClaim in claimsIdentity.Claims)
    {
        Response.Write("ClaimType [" + oClaim.Type.ToLower() + "] has value [" + oClaim.Value + "]");
    }

}

Get Query String from JavaScript

I use this javascript function in every other project and every time I have to go to the internet and search for it. I am just too lazy to look and copy it from the old projects. So I am copying it from the internet to save it in my personal source code repository.

function getParameterByName(name) {
    name = name.replace(/[\[]/, "\\[").replace(/[\]]/, "\\]");
    var regex = new RegExp("[\\?&]" + name + "=([^&#]*)"),
        results = regex.exec(location.search);
    return results == null ? "" : decodeURIComponent(results[1].replace(/\+/g, " "));
}

var strValue = getParameterByName('Value');

document.write(strValue);

Sharepoint 2013 Training

I am providing following training to people who want to learn SharePoint Development.
Please comment on this post for your questions.

Module I - Introduction to SharePoint (8 hr)

1. What is a CMS?
2. Microsoft Echo System
3. SharePoint Job Roles
4. SharePoint Architecture
5. 3 Tier Architecture
6. Central Admin - Walkthroughof CA
7. SharePoint Site - Creating a Site Collection
8. List and Libraries - Creating a List
9. List and Libraries - Calendar, Tasks, Announcements, Document Lib, Images Lib
10. Columns - Text Col, Date Col, Choice Col, Calculated Col, Lookup Col
11. Views - Creating a View (Grouping, Filter, Sorting etc)

Module II - SharePoint Content Management (8 hr)

1. Site Collection & Subsite - Difference between Site Collection & Sub Site.
2. Site Settings - Change Site Name, Logo etc
3. Navigation - Updating the Left Navigation
4. Security - Site Owner (Owner), Member(Contributor), Visitor(Reader)
5. Document Library - Creating a Document Library, Checking In/Out, Versioning.
6. Content Type - Using Content Type in a Doc Lib.
7. Editing Pages - Adding text to pages.

Module III - Site Customization (2 hr)

1. Adding Pages - Creating a New Page
2. Web Part - What is a Web Part? 
3. Adding Web Parts Adding - OOB web parts (Content Editor Web Part, Image Viewer Web Part, Youtube Video embedding).
4. Advance Web Parts - List View Web Part etc
5. Layout - Modifying the Page Layout

Module IV - Workflow Development (6 hr)

1. Business Process - What is a Business Process?
2. Workflow - What is a Workflow?
3. Design - Design a workflow in Visio.
4. Workflow Parts - Condition, Action and Properties.
5. Develop - Develop a workflow using SharePoint Designer.

Module V - Site Branding with SharePoint Designer (8 hr)

1. HTML - Introto HTML5
2. Customizing Pages - Creating New Pages
3. WebPart - Customization Customizing the DataView Web Part
4. Master Page - What is a Master Page? Difference between ASPX and Master Page
5. Branding - Customizing the Master Page

Module VI - Web Part Development (8 hr)

1. SDLC - Software Development Life Cycle
2. C# - Intro to C#, Making the UI, Writing the code.
3. ASP.NET - Intro to ASP.NET, Making the UI, Writing the code.
4. Visual Web Part Development - Hello World Web Part, Web Part Life Cycle

Module VII - Web Part Development (8 hr)

1. CAML - Query SharePoint Data
2. Advance Web Part - Accessing data through Web Part
3. Feature - Deployment of Web Part through features
4. Scope - Farm, Site, Web, Web Application

Module VIII - Business Intelligence (8 hrs)

1. Reporting - What is a Report?
2. Excel - Introduction to Excel
3. Excel Services - Excel Web Access web part
4. Publishing - Publishing an Excel to SharePoint.

Module IX - Business Intelligence (8 hrs)

1. Excel Services - PowerPivot
2. Excel Services - PowerView
3. Dashboard - Lets build a Dynamic Dashboard.

Module X - App Development using Visual Studio (8 hr) - Optional

1. Java Script - Intro to JavaScript
2. App - What is an App?
3. SDLC - Software Development Life Cycle
4. App LifeCycle - Client Side Object Model
5. Development - Developing an App using Visual Studio
6. Deployment - Deploying an App

Module XI - App Development (8 hr) - Optional

1. JSOM - JavaScript Object Model
2. Advance App - Data driven app development
3. App Security - App Scope and Permissions

Playing Video in SharePoint

In my recent project I have used JWPlayer to play videos in a SharePoint portal and it works great.
Here is the demo:

Loading the player...

HarvardX Interactive Learning Challenge - Binomial Distribution

Input No. of Trials (n): Probability of Success (p):

Ref: http://harvardx.harvard.edu/news/harvardx-interactive-learning-challenge-now-open

SharePoint - Taxonomy

Taxonomy (Managed Metadata)
According to Microsoft Technet, A taxonomy is a formal classification system. A taxonomy groups the words, labels, and terms that describe something, and then arranges the groups into a hierarchy.
In layman terms, taxonomy is how to categorize the data in a structured way so that its easy to find the required information.

In this post, I will explain how to define taxonomy through Term Store, Content Types etc in SharePoint 2013. Taxonomy is managed by Taxonomy Term Store in SharePoint 2013 where we can create Terms(a Term is a specific word or phrase that you associated with an item on a SharePoint site), Term Sets(a Term Set is a group of related terms) and Groups(a group is a set of term sets).

Lets walk through following steps:

1. Configure Terms in Term Store
2. Create Content Type with Managed Metadata
3. Add Document and Tag
4. Metadata Navigation 

 1 - Configure Terms in Term Store

1. Go to Central Admin and select Managed Metadata Service to launch the Term Store.
2. Provide your ID in Term Store Administrators and hit Save in order to have the right permissions to create Terms.
3. Select the tiny arrow on Home node (Taxonomy_XXX) and create a new group called Function.
   
4. Create your Term Set (Advisory) and Terms (Business, IT, KM) under Function group.
   
   Note: You can provide description of groups, term sets and terms on the right side.

 2 - Create Content Type with Managed Metadata

1. Launch the Site Collection where you want to use these terms and go to the Site Settings -> Site Columns.
2. Create a site column Advisory and type Managed Metadata
   
3. Under Term Set Settings expand taxonomy and select Advisory term set and hit OK.
   
   Note: This will map Advisory term set to the Advisory site column.
4. Create a new Site Content Type Function that will use the Advisory site column
   
5. Add the Function content type to a Document Library
   
   Note: Now the library is ready to use.

 3 - Add Document and Tag

1. Upload a document in Document Library and select Function content type.
2. In Advisory column select a the Term that you want to associate with this document and save.
   
3. Add a couple of more documents with different terms
   
4. And that's how you attach Taxonomy Terms (Managed Attributes) with different documents in SharePoint in order to categorize them.

 4 - Metadata Navigation 

1. Go to Library Settings of MyDocuments and select Metadata navigation settings.
   
   Note: You might have to activate Metadata Field Navigation and Filtering feature from Site Features if this option is not available. 
2. In Metadata Navigation Settings select Advisory as Hierarchy Field.
   
3. Go back to the library and Advisory Term Set will appear on the left navigation
   
4.  Select a Term from the navigation and you will see related documents appearing on left.
   

And that's how taxonomy is implemented in SharePoint.

Ref: http://office.microsoft.com/en-us/sharepoint-help/introduction-to-managed-metadata-HA102832521.aspx

SharePoint Community - Featured Member

I got an email today that I have been featured at SharePoint Community website......SWEEEEEEEEEEET.
Btw SharePoint Community is one of the largest online communities for SharePoint professionals.

SharePoint 2013 - Delete UserProfile Data with PowerShell

Description:
This PowerShell script will take two input files Users.csv (with all the user names under USERID column) and Properties.csv (with all the properties under TITLE column) and delete the values of UPA properties for the given users.

Script:

#PowerShell Script - Delete User Profile Properties Data in MySite 2013 UPA

Add-PSSnapin "Microsoft.SharePoint.PowerShell"

function WriteLog
{
    Param([string]$message, [string]$logFilePath)
    Add-Content -Path $logFilePath -Value $message
}


#################################################################################################

$logFile = "D:\PowerShellScripts\DeleteUserProfileDataInUPA\DeleteUserProfileDataInUPA.log"
$batchFile = "D:\PowerShellScripts\DeleteUserProfileDataInUPA\Users.csv"
$PropertiesFile = "D:\PowerShellScripts\DeleteUserProfileDataInUPA\Properties.csv"
$mysiteHostUrl = "http://company.MySite.com"


$mysite = Get-SPSite $mysiteHostUrl
$context = [Microsoft.Office.Server.ServerContext]::GetContext($mysite)
$upm =  New-Object Microsoft.Office.Server.UserProfiles.UserProfileManager($context)

$currentProfile = 0

$Properties = Import-CSV $PropertiesFile

$users = Import-CSV $batchFile
$totalProfiles = $users.Count
ForEach ($user in $users) 
{
    
    $currentProfile ++;
    $AccountName = $user.USERID
    
    $profile = $upm.GetUserProfile($AccountName)

    try
    {       
        
        forEach ($Property in $Properties)
        {
            $PropertyTitle = $Property.TITLE

            $OldValue = $profile[$PropertyTitle].Value;

            $profile[$PropertyTitle].Value = $null;
            $profile.Commit() 

            $now = [System.DateTime]::Now
            $msg = $now.ToString() +  " : Deleting value from "+ $PropertyTitle +": "+ $OldValue +" for " + $AccountName + " (" +$currentProfile + " of " + $totalProfiles + ")"
            write-host $msg
            WriteLog $msg $logFile

        }
        
    }
    catch [system.exception]
    {
        $msg = $Error[0].Exception.InnerException.InnerException.Message
        write-host -f red $msg
        WriteLog $msg $logFile
    }
        
    $profile = $Null
    $user = $Null
}


$mysite.Dispose(); 

SharePoint 2013 - Excel Services (BI Reporting)

Background:
Business intelligence (BI) is a set of theories, methodologies, architectures, and technologies that transform raw data into meaningful and useful information for business purposes. SharePoint 2013's Excel Services have made BI reports publishing a lot easier.

In this post, I am going to create a dynamic report using Power View in Excel and then will publish it in SharePoint 2013/Online. I will use a sample MS Access database (RptSampl.mdb) provided by Microsoft to build a new report. Same concepts covers the reports development for MS SQL Server. This post has following three sections:

1. Importing the Data 
2. Designing a Report 
3. Publishing a Report to SharePoint.

Lets get started.

Importing the Data into a Excel Report

---

1. Download sample database from Microsoft's site.
2. Launch MS Excel 2013 and go to POWERPIVOT tab.


1. If you don't see this menu then enable it by going to File -> Options.
2. Under Add-Ins select Manage: COM Add-ins and then hit Go.
   
3. Check Microsoft Office PowerPivot for Excel 2013 and then hit OK.
   
3. POWERPIVOT tool bar will look like this
   
4. Select Manage and the following window will appear where we will connect to the database, import the data and will arrange the relationship between tables.
   
5. Select Get External Data -> From Database -> From Access
   
6. Select RptSampl.mdb from your downloads  folder and it doesn't not need any login and password then hit Next.
   
7. Choose Select from list of tables and views to choose the data to import in the next screen and hit Next again
8. Select the tables you want to to add in your report. I will select Products and Suppliers then hit Finish.
   
   You will see data from two tables will get imported into Excel. It will also increase the size of the Excel file. Select Close after data import.
9. You can select Diagram View from the tool bar to see the relation between tables.
   
10. Hit Save and it will save the data and close the Dialog.

Designing a Excel Report

---

1.  Go to INSERT tab and select Power View in Reports section.
   
2. This will open a new work sheet, select Power View tab, show you the imported tables so we can design our report here.
   
3. Expand tables and select fields that needs to be displayed in the report. For this report I will select ProductName and UnitPrice columns from Products table.
   
4. This is your basic report. You can apply filter by clicking the filter button and can perform sorting by clicking the title of the column inside the report.
   
5. You can present the information in a dynamic Chart too by selecting Bar Chart from DESIGN tab
   
   And your report should look like this
   
6. You can add other columns like UnitsInStock, UnitsOnOrder to see how they will look in the report's chart.
   
7. Save the Excel work book in local folder with the name Report.xlsx.

Publishing a Excel Report to SharePoint Online/2013

---

1. Upload the Report.xlsx to a document library in SharePoint Online.
2. Click the Report.xlsx inside the document library and SharePoint will open the report inside the browser with Excel Online which is a part of Office Web App (OWA).
   

And thats how you develop and publish a BI report using Excel Services.

SharePoint 2013 - Displaying Charts using Excel Web Access

Excel Web Access web part comes with Excel Services capabilities in SharePoint. This web part is used to display data from an Excel workbook on a SharePoint site so that user can share reports and charts with other users.

In this post, I am going to explain how to display a chart from an excel file to a SharePoint Page using Excel Web Access web part.

1. Create an Excel work book with the following data in it.
   
2. Insert a graph chart by selecting the data rang
   
3. Save the workbook (mine is called Sales.xlsx) and upload it in a document library in SharePoint.
   
4. Now edit and add an Excel Web Access web part on any page where you want to display the chart.
   
5. Edit web part to see its properties
   
6. Set Workbook (select Salex.xlsx from the document library) and Named Item properties (Chart 1) and then hit OK on the web part. By default all charts are named as Chart 1, Chart 2 etc
   
7. You should be able to see the chart from the Excel workbook on the page.
   

Every time you have a new data in Excel workbook, just upload the file into the document library and refresh the page and you will see the updated chart.

SharePoint Online(2013) - Web Part Deployment

In my previous post, I have explained how to develop a SharePoint 2013 web part using Visual Studio. Now lets deploy that web part to the production environment e.g A site collection in SharePoint Online.

1. Open the existing Web Part project in Visual Studio 2013.
2. Select Feature1 and you can change the scope of the deployment here. Scope: Site means this feature will be a Site Collection feature and web part inside this feature will be available in the whole site collection.
   
3. Select Build -> Publish -> Publish to File System. Provide location and hit Publish. This will generate the WSP (myVisualWebPart.wsp).
   
4. Go to the production site (my production site is on SharePoint Online).
5. Go to Site Settings -> Solutions under Web Designer Galleries.
   
6. Select Upload Solution -> Choose file and select the WSP file from your desktop and upload it.
   
7. Once the solution package is uploaded select Activate. This action will activate the feature and make the web part available in the web part gallery.
   
8. If you go to Site Settings -> Site collection features under Site Collection Administration. You will see your feature activated here.
   
   
9. Now go to any page on the production site and add your web part. It should be available under Custom category.
   
   

Thanks heaven for the easy deployment =).