Category Archives: Windows PowerShell

My Windows PowerShell blog. This is my personal review and opinion!

Part 2: Customize SharePoint Online User Interface using JavaScript | CSOM | PowerShell

2016-11-02_12-42-56

This is a continuation of part 1 and we will cover about adding a custom action in site action of the web.Β Note: You can think about any solution. This blog post is to show an option in PowerShell and Client Side Object Model to customize the Site Actions of the web.

  1. To know Default Custom Action and Location ID’s – Navigate here.
  2. How to work with User Custom Action – Navigate here.

Here is the sample code to add a link in Site Actions!

#Choose the Location as Microsoft.SharePoint.SiteSettings
$UserCustomAction.Location = "Microsoft.SharePoint.SiteSettings"

#Choose the Group as SiteTasks (Site Actions)
$UserCustomAction.Group = "SiteTasks"

Site Actions Before

2016-11-02_12-30-44

Site Actions After

12

Full Code

Import-Module C:\Temp\SharePointOnlinePowerShell\Microsoft.SharePoint.Client.dll
$Admin = "Chendrayan@Contoso.onmicrosoft.com"
$Password = "Password01" | ConvertTo-SecureString -AsPlainText -Force 
$Context = [Microsoft.SharePoint.Client.ClientContext]::new("https://contoso.sharepoint.com/Sites/PublishingPortal")
$Context.Credentials = [Microsoft.SharePoint.Client.SharePointOnlineCredentials]::new($Admin,$Password)

$UserCustomAction = $Context.Web.UserCustomActions.Add()

#Choose the Location as Microsoft.SharePoint.SiteSettings
$UserCustomAction.Location = "Microsoft.SharePoint.SiteSettings"

#Choose the Group as SiteTasks (Site Actions)
$UserCustomAction.Group = "SiteTasks"

$UserCustomAction.Name = "Demo"
$UserCustomAction.Sequence = 1000
$UserCustomAction.Url = "http://www.bing.com"
$UserCustomAction.Title = "Search Bing"
$UserCustomAction.Update()

$Context.ExecuteQuery()

Enjoy PowerShell!

Part 1: Customize SharePoint Online User Interface using JavaScript | CSOM | PowerShell

 

Woman’s hand drawing a light globe with a felt pen

 

During my web-ex session “Manage SharePoint Online Using CSOM” I showed few of the custom functions wrapped up as script module. I will be posting the recording session by next week and module will be shared in Git! In the session content management team was actively posting questions and this blog series shares all the questions and solutions we discussed.

Question: Can we Inline Java Script and modify the ribbons, menu items etc in SharePoint Online? If yes can you show step by step procedure?

Answer: Start from here!

Step 1: Create a folder and save all the required files and test the simple java script code to show Hello, World!

console.log('Message appears here!')

1

Step 2: Now, we need to know the existing custom actions attached in the list, the below code does it for you!

Import-Module .\Microsoft.SharePoint.Client.dll
$Admin = "Admin@domain.onmicrosoft.com"
# Using Clear Text Password is not adviced in the script (Use Get-Credential)
$Password = "P@ssw0rd!" | ConvertTo-SecureString -AsPlainText -Force
$Credential = [pscredential]::new($Admin,$Password)
$Context = [Microsoft.SharePoint.Client.ClientContext]::new('https://contoso.sharepoint.com/sites/csom')
$Context.Credentials = [Microsoft.SharePoint.Client.SharePointOnlineCredentials]::new($Credential.UserName,$Credential.Password)

$UserCustomActionCollection = $Context.Web.Lists.GetByTitle('Custom').UserCustomActions
$Context.Load($UserCustomActionCollection )
$Context.ExecuteQuery()
foreach($UserCustomAction in $UserCustomActionCollection)
{
    $UserCustomActionCollection
}

For now, we haven’t attached any custom user actions so the script returns nothing for the list custom (In your case if you have any user custom actions attached you get to see whole bunch of information)!

Step 3:Β Now, we will add a simple menu in the SharePoint List item menu (The code is in MSDN link and we will use the same but in PowerShell). Take a look at the screen shot which is default out of the box options.

3

Let us add a menu name as Bing to open the bing search engine ! Β below is the code which does it easily

Import-Module .\Microsoft.SharePoint.Client.dll
$Admin = "Admin@domain.onmicrosoft.com"
# Using Clear Text Password is not adviced in the script (Use Get-Credential)
$Password = "P@ssw0rd" | ConvertTo-SecureString -AsPlainText -Force
$Credential = [pscredential]::new($Admin,$Password)
$Context = [Microsoft.SharePoint.Client.ClientContext]::new('https://contoso.sharepoint.com/sites/csom')
$Context.Credentials = [Microsoft.SharePoint.Client.SharePointOnlineCredentials]::new($Credential.UserName,$Credential.Password)

$UserCustomActionCollection = $Context.Web.Lists.GetByTitle('Custom').UserCustomActions
$Context.Load($UserCustomActionCollection )
$Context.ExecuteQuery()

$NewAction = $UserCustomActionCollection.Add()
$NewAction.Location = 'EditControlBlock'
$NewAction.Title = "Bing It!"
$NewAction.Description = "Search in Bing!"
$NewAction.Url = "http://www.bing.com"
$NewAction.Update()
$Context.ExecuteQuery()

It works! Here is the output πŸ™‚

4

Step 4: If we rerun the Get Custom Actions script we get to see information about the custom actions we added. The below image illustrate it

5

If you re run the script List Drop Down Menu it gets duplicated! So, don’t do that if accidentally did that like me not an issue quickly clean the dirt by running the below piece of code

foreach($UserCustomAction in $UserCustomActionCollection)
{
    $UserCustomAction.DeleteObject()
    $Context.ExecuteQuery()
}

Clear! Now, it’s time to use java script πŸ™‚ since I have very basic knowledge in java I showed the example using the MSDN code sample πŸ™‚ Yes Copy + Paste !

In this example we will add a custom ribbon user interface in list display form Β – All I did is removing the unnecessary nodes from the XML and below is my example

$here = @"
<CommandUIExtension>
<CommandUIDefinitions>
<CommandUIDefinition Location="Ribbon.ListForm.Display.Manage.Controls._children"> 
<Button Id="{B511A716-54FF-4EAE-9CBE-EA02B51B626E}" 
Command="{4E2F5DC0-FE2C-4466-BB2D-3ED0D1917763}" 
Image32by32="~site/_layouts/Images/BingSearch/bing32.png" 
Image16by16="~site/_layouts/Images/BingSearch/bing16.png" 
Sequence="0" 
LabelText="Hello, World" 
Description="SharePoint Demo" 
TemplateAlias="o1" /> 
</CommandUIDefinition>
</CommandUIDefinitions>
<CommandUIHandlers>
<CommandUIHandler Command="{4E2F5DC0-FE2C-4466-BB2D-3ED0D1917763}" CommandAction="javascript:alert('Hello, World!')" />
</CommandUIHandlers>
</CommandUIExtension>
"@

I don’t have the BingSearch and bing image in my layouts so image breaks but code works as expected

6

 

Sample code is below, you need to change the URL, credentials and list name as applicable! Exception handling is not in place because this is built during the demo

Import-Module .\Microsoft.SharePoint.Client.dll
$Admin = "admin@contoso.onmicrosoft.com"
# Using Clear Text Password is not adviced in the script (Use Get-Credential)
$Password = "P@ssw0rd!" | ConvertTo-SecureString -AsPlainText -Force
$Credential = [pscredential]::new($Admin,$Password)
$Context = [Microsoft.SharePoint.Client.ClientContext]::new('https://contoso.sharepoint.com/sites/csom')
$Context.Credentials = [Microsoft.SharePoint.Client.SharePointOnlineCredentials]::new($Credential.UserName,$Credential.Password)
$UserCustomActionCollection = $Context.Web.Lists.GetByTitle('Custom').UserCustomActions
$Context.Load($UserCustomActionCollection )
$Context.ExecuteQuery()
$here = @"
<CommandUIExtension>
<CommandUIDefinitions>
<CommandUIDefinition Location="Ribbon.ListForm.Display.Manage.Controls._children"> 
<Button Id="{B511A716-54FF-4EAE-9CBE-EA02B51B626E}" 
Command="{4E2F5DC0-FE2C-4466-BB2D-3ED0D1917763}" 
Image32by32="~site/_layouts/Images/BingSearch/bing32.png" 
Image16by16="~site/_layouts/Images/BingSearch/bing16.png" 
Sequence="0" 
LabelText="Hello, World" 
Description="SharePoint Demo" 
TemplateAlias="o1" /> 
</CommandUIDefinition>
</CommandUIDefinitions>
<CommandUIHandlers>
<CommandUIHandler Command="{4E2F5DC0-FE2C-4466-BB2D-3ED0D1917763}" CommandAction="javascript:alert('Hello, World!')" />
</CommandUIHandlers>
</CommandUIExtension>
"@
$NewAction = $UserCustomActionCollection.Add()
$NewAction.Location = 'CommandUI.Ribbon.DisplayForm'
$NewAction.CommandUIExtension = $here
$NewAction.Update()
$Context.ExecuteQuery()

Go through the MSDN reference links for more information! In next part I will show bit more automation of UI settings using PowerShell. Cheers πŸ™‚ Enjoy PowerShell πŸ™‚

Import User Profile Picture from Exchange Online using EWS and PowerShell

One of our customer requested a solution to import user profile picture from Exchange Online. Not a big task but this article is to share the simple and clean method to carry out the task without MSOnline module. Yes, we use only PowerShell! At customer environment user uploads profile picture in intranet portal which saves the picture in file share, yeah I got the same question why can’t they get it from the file share? We can but not easy to do because of the profile image file naming convention, every user has an intranet portal profile ID so the files are saved as ID_DATETIMESTAMP. E.G β€œPROFILEID_YYYYMMDDHHMMSS”. There are many possibilities to do like query intranet portal DB to retrieve image file information and retrieve, use MSOnline module Get-UserPhoto etc.

Outclass is to use PowerShell and get it done on the fly. All we need is the EWS url for the profile picture residing in the Exchange Online and it’s shared below with sizes for reference and hew is the documentation for Get User Photos by using EWS in Exchange

1 https://outlook.office365.com/ews/Exchange.asmx/s/GetUserPhoto?email=”UserEmail” &size=HR96x96 HR96x96
2 https://outlook.office365.com/ews/Exchange.asmx/s/GetUserPhoto?email=”UserEmail” &size=HR240x240 HR240x240
3 https://outlook.office365.com/ews/Exchange.asmx/s/GetUserPhoto?email=”UserEmail” &size=HR648x648 HR648x648

 

All set to spin up PowerShell ISE for scripting! Simply use Invoke-WebRequest cmdlet with OutFile parameter to save the profile image in disk like shown below

$Param =@{
Uri = "https://outlook.office365.com/ews/Exchange.asmx/s/GetUserPhoto?email=Chendrayan.Venkatesan@contoso.com&size=HR648x648"
Credential = (Get-Credential "Chendrayan.Venkatesan@contoso.com")
OutFile = "C:\Temp\UserID.jpg"
}

Invoke-WebRequest @Param

Yay, its easy to get the profile picture information at ease without using Get-UserPhoto cmdlet and no need of Set-Content with Encoding parameters (Saves more time) ! here is my picture – Output of the above snippet!

Chen

Check out the sample script below which use ADSI Searcher to retrieve user AD information and save the picture in disk as sAMAccountName_DisplayName.jpg πŸ™‚

function Get-xUserPhoto
{
<#
.Synopsis
   Imports User Profile Picture from Exchange Online using PowerShell and EWS
.DESCRIPTION
   This is a sample script to export user profile picture from exchange online using EWS and PowerShell.
   Use Invoke-Webrequest cmdlet
.EXAMPLE
   Get-xUserPhoto -Email Chendrayan.Venkatesan@contoso.com -Path C:\Temp\ -Credential "Chendrayan.Venkatesan@contoso.com"
   Import single user profile picture from Exchange Online and save it in C:\Temp as sAMAccountName_DisplayName.jpg
.EXAMPLE
   "Chendrayan.Venkatesan@contoso.com" , "Chendrayan.Venkatesan@contoso.com" | Get-xUserPhoto -Path C:\temp -Credential "Chendrayan.Venkatesan@contoso.com"
   Retrieves multiple user profile picture and save it in C:\temp
#>
    param
    (
        [Parameter(Mandatory,ValueFromPipeline,ValueFromPipelineByPropertyName)]
        $Email,

        [Parameter(Mandatory=$False)]
        [ValidateSet("96x96","240x240","648x648")]
        $Size="648x648",

        [Parameter(Mandatory)]
        $Path,

        [Parameter(Mandatory)]
        [pscredential]
        [System.Management.Automation.CredentialAttribute()]
        $Credential
    )

    process
    {
        try
        {
            $DEObj = ([adsisearcher]"(&(objectclass=user)(objectclass=person)(mail=$Email))").FindOne().GetDirectoryEntry()
            $Uri = [String]::Concat("https://outlook.office365.com/ews/Exchange.asmx/s/GetUserPhoto?email=",$Email,"&size=HR",$Size)
            if(Test-Path -Path $Path)
            {
                $File = [string]::Concat($Path,"\",$DEObj.sAMAccountName, "_", $DEObj.DisplayName,".jpg")
                Invoke-WebRequest -Uri $Uri -Credential $Credential -OutFile $File
            }
            else
            {
                Write-Warning "Please Check the Path"
            }
        }
        catch
        {
            $_.Exception.Message 
        }
    }
}

 

VS Binary Module Tip: PowerShell 5.0 SDK Reference Assemblies – Nuget

PowerShell 5.0 SDK Reference Assemblies is available in NuGet. If you are building binary modules you can add the references from Visual Studio just by executing the below command in NPM Console

Install-Package Microsoft.PowerShell.5.ReferenceAssemblies -Verbose

In the NPM UI we can get it in one go – By Clicking Install
1
Cool, its easy to add the reference to our project and start building the binary module easily. When we build cmdlet we need to run and test the functionality and each time we execute it’s necessary to close the PowerShell Console Host or ISE host if the binary module is loaded and we can’t rebuild the solution. It will end up in Dll in use error. We can easily over come this by following the below steps.

  • Right Click the Solution from Solution Explorer.
  • Choose Properties and in the Right Pane Select Debug
  • Click Select External Program. Navigate to “C:\Windows\System32\WindowsPowerShell\v1.0\”.
  • Select PowerShell.exe
  • Select the option Enable native code debugging

3
So after building the code simple click “Start” in Visual Studio which open the PowerShell Console.
Additionally, we can load our module with the help of Command line arguments.

-NoExit -Command "Import-Module .\PowerShellDemo.dll"

4
Enjoy PowerShell πŸ™‚ πŸ™‚ πŸ™‚

PowerShell Tip: Extract Images from DOCX File

Summary

It’s an immense pleasure to explore $objects! We do IT Automation tasks using C# or PowerShell. It depends on the requirement and business needs. Recently our team was engaged to extract images from the DOCX files. It’s possible by simply renaming it to ZIP and extract to get the images folder which is not optimal solution at our client environment. So, we used OPEN XML SDK 2.5 (Supported for Office 2016) to accomplish the task.

Requirement

Extract all the images from the given DOCX File. Example Get-Image -FilePath “C:\Temp\Document.Docx”

Solution

  • OPEN XML SDK 2.5
  • Windows PowerShell
  • Visual Studio 2015 (C# Class Library – Binary Module)
  • PowerShell Code

    #using namespace DocumentFormat.OpenXml;            
    [void][System.Reflection.Assembly]::LoadWithPartialName('DocumentFormat.OpenXML')            
    Function Get-Image            
    {            
        Param            
        (            
            [Parameter(Mandatory,            
                       ValueFromPipeline=$true,            
                       ValueFromPipelineByPropertyName=$true,            
                       Position=0,            
                       HelpMessage="Please Enter the Full Path")]            
            [string]            
            $FullName            
        )            
        Process            
        {            
            $Document = [DocumentFormat.OpenXml.Packaging.WordprocessingDocument]::Open($FullName,$false)            
            foreach($Image in $Document.MainDocumentPart.ImageParts)            
            {            
                 [uri]$Uri = $Image.Uri            
                 $ImageFileName = $Uri.ToString().Split("/").Where({$_},'Last',1)            
                 [System.IO.Stream]$Stream = $Document.Package.GetPart($Uri).GetStream()            
                 [System.Drawing.Bitmap]$BitMap = [System.Drawing.Bitmap]::new($Stream)            
                 $BitMap.Save("C:\Temp\Images\" + $ImageFileName)            
            }            
        }            
                   
    }            
                
    Get-Item 'C:\Temp\Demo.Docx' | Get-Image

    Cool, here is the C# Binary cmdlet code to do the same

    [Cmdlet(VerbsCommon.Get,"Image")]
    public class GetImage : Cmdlet
    {
        [Parameter(Mandatory = true,
                   Position = 0)]
        public string FilePath;
        protected override void ProcessRecord()
        {
            //base.ProcessRecord();
            FileStream filestream = System.IO.File.OpenRead(FilePath);
            FileInfo fileinfo = new FileInfo(FilePath);
            WordprocessingDocument document = WordprocessingDocument.Open(filestream, false);
            var images = document.MainDocumentPart.ImageParts;
            foreach(ImagePart image in images)
            {
                Uri uri = image.Uri;
                string mainimage = uri.ToString().Split('/').Last();
                Stream imgstream = document.Package.GetPart(uri).GetStream();
                Bitmap bitmapImg = new Bitmap(imgstream);
                bitmapImg.Save(@"C:\Temp\" + mainimage);
            }
        }
    }

    Conclusion

    We prefer to pick a tool which costs less to deliver the solution. So, at my client place we opted for PowerShell to achieve few automation task with respect to Data Manipulation. To loop through multiple files just used the below snippet:

    Get-ChildItem 'C:\Temp\ProjectFolder\' | Get-Image

    Enjoy PowerShell!

    Note: You can modify the filename of the image as required. In our case it needs to be the same because its customized.

    Converting JSON Time Format in SharePoint Document Library Metadata


    The SharePoint farm has aΒ Document Library and a List which are linked for some requirements, while querying information we received some data whichΒ are not in proper format. Challenge is to split, remove characters and count the total which is little tedious

    Example- The Field3 in CSVΒ yields [“\/Date(1444995273796)\/”,”\/Date(1444996968515)\/”]

    It’s a JSON format and the time format is EPOCH (UNIX format) – With Reference to the link MSDNΒ My advice to customer was to use below codeΒ snippet to break the mystery!

    To get the count: (Unfortunately, this is what customer need!) – No Fun in it because this kills the actual flavor of SharePoint development and data.

    (@"
    ["\/Date(1444995273796)\/","\/Date(1444996968515)\/"]
    "@ | ConvertFrom-Json).Count
    
    #Output
    #2

    The reason for the SPContext Code by developer is unknown. So here comes the ConvertFrom-JSON power in Windows PowerShell Version 3+ πŸ™‚ πŸ™‚ πŸ™‚

    To get the information about the data in JSON: [We will deep dive in Part 2 of this Blog]

    @"
    ["\/Date(1444995273796)\/","\/Date(1444996968515)\/"]
    "@ | ConvertFrom-Json
    
    #Output
    
    #Friday, October 16, 2015 11:34:33 AM
    #Friday, October 16, 2015 12:02:48 PM

    The file which is generated as report is done using C# and CSOM – So, in nextΒ blog post (Part 2)Β we will cover Binary Module using C# Class Library, PowerShell Module, String Manipulation and Data consolidation.

    Enjoy PowerShell !

    Show or Hide SharePoint List Using CSOM | C# | PowerShell

    Disclaimer: This is pretty old topic just remade it using CSOM and PowerShell at my client work place.

    During SharePoint due diligence many suggested to use SharePoint Manager Tools, CAML Query Builder etc. It’s better to keep tools handy but gathering and executing the client requirement is better than the best.

    Environment: Client has WSS 3.0, MOSS 2007 and SharePoint 2010 platforms, they want to organize the structure and keep the contents similar. An ideal goal is to move to upgraded versions.

    During this big discussion development team suggested to make a script which reads the web, site, lists and document library properties and set the same in each farm. Dude! writing script is easy and deploying is very easy as well. But, before we do that let’s know why properties are set differently ? Who made the decision? Business Justification is much more important so I suggested Power Users or SharePoint L1 to decide and deploy the solution.

    How? Power Users like GUI based solutions they don’t want to execute console applications or scripts. As usual we picked up a call and I was shocked by listening to content manager requirement – “Need one liner cmdlet and we will create Lists and Set it’s Property. It may be one or many!” It’s that simple PowerShell reached to content managers as well. πŸ™‚ I was happy because I am going to meet their needs in short span of time!

    To kick on the demo in a catchy way. I showed them how PowerShell one liner looks like πŸ™‚

    Get-EvenNumbers -From 1 -To 10

    It’s that simple to use, I haven’ t used any validation in this function very simple code which prints the even number using range operators. The function code is below

    Function Get-EvenNumbers
    {
        param(
        [Parameter(Mandatory = $true)]
        $From,
        [Parameter(Mandatory=$true)]
        $To
        )
    
        ($From..$To).ForEach({if($_ %2 -eq 0){$_}})
    }
    Get-EvenNumbers -From 1 -To 10

    Solution for Content Managers:

    Consume CSOM and build binary module using Class Library πŸ™‚ πŸ™‚ πŸ™‚ This time I used Visual Studio 2013 just for a change πŸ™‚

    using System;
    using System.Collections.Generic;
    using System.Text;
    using System.Security;
    using System.Net;
    using System.Management.Automation;
    using Microsoft.SharePoint.Client;
    
    [Cmdlet(VerbsCommon.Set,"SPListVisibility")]
    public class SetSPListVisibility : Cmdlet
    {
        [Parameter(Mandatory = true)]
        public string SPUrl;
    
        [Parameter(Mandatory = true)]
        public string SPListName;
    
        [Parameter(Mandatory = true)]
        public bool ListVisibility;
        protected override void ProcessRecord()
        {
            ClientContext ctx = new ClientContext(SPUrl);
            NetworkCredential credentials = CredentialCache.DefaultNetworkCredentials;
            ctx.Credentials = credentials;
    
            List list = ctx.Web.Lists.GetByTitle(SPListName);
            ctx.Load(list);
    
            list.Hidden = ListVisibility;
            list.Update();
            ctx.ExecuteQuery();
            ctx.Dispose();
        }
    }

    Usage (Just for Demo):

    Import-Module C:\Scripts\Module\CSOM_Demo.Dll
    #Hide the List
    Set-SPListVisibility -SPUrl http://weburl -SPListName "PowerShell" -ListVisibility $false
    
    #Show the List
    Set-SPListVisibility -SPUrl http://weburl -SPListName "PowerShell" -ListVisibility $true

    In next my next blog I will share the complete module! It’s that’s easy ! Happy SharePointing and Enjoy PowerShell πŸ™‚ πŸ™‚ πŸ™‚

    Reason: Content manager need this kind of solution for re usability. Smart Guy!

    SharePoint Content Management using PowerShell | Dude! it’s a Due Diligence – Part 1

    October-26-2015: Due to SharePoint tasks I missed MVP Summit 2015; Energy drained out due to disappointment!. So, I engaged 16 hours of my time with SharePoint team to perform a due diligence. Traveled in and around Netherlands for consultations with clients, developers and IT Professionals.

    Introduction: Though we know each other we gathered at a party lounge for a friendly handshake and a BIER πŸ™‚ and that’s Hertog Jan a typical Dutch Bier!

    Daan_Samson_Hertog_Jan3

     

    Not huge if we are four in numbers to share! – Cheers!!!

    Challenge 1: Our security team will not allow us to install any third party components and products not guaranteed by Microsoft. So, we need to code to inventory of SharePoint farm(s). Includes MOSS 2007 & WSS 3.0,Β  SharePoint 2007 and SharePoint 2013.

    Challenge 2: Developer team is busy in SharePoint Online training, we can engage them only twice a week.

    Challenge 3: We need to plot a blue print of SharePoint farm without third party tools. The same needs to be shared with IT Managers before we design a plan for deployment or migrations or transformations.

    πŸ™‚ πŸ™‚ πŸ™‚ Let’s keep the challenges apart. We may come across many while working on the Test and Production environment. So, we decided to get back to work on October-27-2015 with fresh mind! Yeah! we concluded not to work at home or not even to bing for solutions a day prior to work. A fresh mind is most important to work independently!

    Requirement 1: Get all the items from Large List – We can see 30,000 + items we need a report in Excel.

    I quickly showed the option in ribbon “Export to Excel” πŸ™‚ “Assuming things will not help at work place!” Clients need Large List items reports to be shared in email and uploaded SharePoint portal. If it’s in portal why in email? To gain customer satisfaction we stopped asking questions, we gathered our tasks and grabbed a cup of coffee to our desk.

    th

    oops! That’s not a large list, it’s a Document Library enabled with Document Set! The complete web is built and deployed using custom code. Exploring is not a challenge but need to be handled it with care. Many columns are Interconnected or mapped with multiple lists in the same web.

    CAML Query is SharePoint IT Professionals best friend, Just three things we need to do Query, Select Fields, and set Attributes to do this we need a tool; Indeed that will be PowerShell. The mission begins now!

    12

    Dude, that’s SharePoint 2010 hosted on Windows 2008 R2 Standard. PowerShell Version 2.0 πŸ™‚ πŸ™‚ πŸ™‚ – Developers Team looked at me and laughed – Do you remember this version? Can we raise a request to upgrade the version of PowerShell? (Nope! PowerShell 4.0 stable version requires some workaround in SharePoint box and PS 5.0 Product Preview is not stable version) shall we do a timer job? (No! No! No! – Folks, don;t forget MOSS 2007 we did many without Shell Mangement – Now, we have a tool to manage!. Go and Check your Visual Studio version and updates – I assume you may need one more Bier!)

    Dusted up my old SharePoint script file and modified it as required

    Step 1: Export – Items to CSV

    Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction 0
    $spsite = Get-SPWeb -Identity http://www.contoso.com/web
    $splist = $spsite.Lists["Large List"]
    $spQuery = New-Object Microsoft.SharePoint.SPQuery
    $camlquery = "<Where><Eq><FieldRef Name='FSObjType'></FieldRef><Value Type='Text'>1</Value></Eq></Where>"
    $spQuery.Query = $camlQuery
    $spQuery.ViewFields = [String]::Concat("<FieldRef Name='Project_Number'/><FieldRef Name='Created'/><FieldRef Name='Editor'/><FieldRef Name='Author'/><FieldRef Name='Number_of_downloads'/>")
    $spQuery.ViewAttributes = [String]::Concat("Scope='RecursiveALL'")
    $spQuery.ViewFieldsOnly = $true
    $spListItems = $spList.GetItems($spQuery)
    $collections = @()
    Foreach($spListItem in $spListItems)
    {
        $results = New-Object psobject -Property @{
        "Number" = $spListItem["Project_Number"]
        "Created By" = $spListItem["Author"].Split("#")[1]
        "Status" = $SPListItem.ModerationInformation.Status
        "Modified By" = ($spListItem["Editor"]).Split("#")[1]
        "Number of Downloads" = ($spListItem["Number_of_downloads"])
        }
        $collections += $results 
    }
    $Collections #| Export-Clixml C:\temp\LargeList_Report.xml

    Step 2: Send in email as attachment and close the requirement!

    $param = @{
    FROM = "From@contoso.com"
    TO = "To@contoso.com"
    Subject = "Large List Report"
    SMTPServer = "SMTP Server"
    Attachment = "C:\temp\LargeList_Report.csv"
    }
    Send-MailMessage @param

    Requirement 2: We have removed the existing Approval Workflow and lost the track of Content Approved Items – We can get that in search result.

    Yes, this time no SharePoint Shell Management, kicked on Windows PowerShell 5.0 Product Preview from my local machine and got the report. Lucky me! the SharePoint document library will not allow duplicate items πŸ™‚ so, search results returns one URL (customized search page!) and team didn’t reset the Search Index πŸ™‚ “25% of the tasks completed – Yes, users can search and read contents from the application

    The items found in search results URL will be re-processed : Set Content Approval to $true πŸ™‚

    (Get-Content C:\Temp\Large_list.csv).Foreach({
    (Invoke-WebRequest -Uri "http://www.contoso.com/web/Pages/SearchPage.aspx?k=$_" -UseDefaultCredentials | 
    Select -ExpandProperty Links).href | Select -Last 1
    })

    Requirement 3: Approve all documents – No need to think about the tasks which are already fired. So, concluded to change moderation status information to approved for the items found in search result.

    Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction 0
    $web = Get-SPWeb -Identity 'http://www.contoso.com/web'
    $list = $web.Lists["Large List"]
    $ids = Get-Content C:\Temp\Approved_Id.txt 
    foreach($id in $ids)
    {
        $item = $list.GetItemById($id)
        $item.ModerationInformation.Status = "Approved"
        "Approving Project {0} - Status: {1}" -F ($item["Project_Number"] , $item.ModerationInformation.Status)
        $item.Update()
    }

    Content mangers did a sanity checks and confirmed all set to good! So, it’s time for us to deploy new (or) upgraded solution. Moved to look at developer team. Oops! really pity – a laptop with 4 GB RAM running Visual Studio 2010 in SharePoint Development environment.

    lgf01a201311081300

    It’s lunch time πŸ™‚ πŸ™‚ πŸ™‚ we have few more fun to flow in! Gained energy with BitterBal

    l_6572_dutch-food-1

    Continued discussion with development at lunch and assessed the challenges – We discussed about buying some more time πŸ™ “My Mind voice: I could have been to MVP Summit 2015!”. That was just a discussion not a conclusion; Post lunch we were back at desk to continue content management automation.

    I requested developer to ignore all exiting πŸ™‚ DOC LIB items and automation tasks. So, they can develop ASP.NET form to map with DOC LIB columns – Yayyyyyy! Now we have Metadata Columns in the library! – Need a coffee! πŸ™‚

    Requirement 4: Deploy the new solutions – Yep deploying new WSP! One liner using PowerShell – Deployment went fine and development team signed off (Will be back into production support as required), But Application didn’t turned up well!

    It’s time for us to show the Power of Shell πŸ™‚ πŸ™‚ πŸ™‚

    I expected the below shiny error πŸ™‚ But I should explain this to IT Managers like “sorry, we are encountering an unexpected error

    Capture

    πŸ™‚ Woot! Go ! Open up SharePoint Management Shell and execute the below piece of code with start and end time parameters – Pipe and fetch the correlation ID. It’s that simple!

    Start Time : Grab it from the error screen shot

    End Time : Just increase by 1 minute or 2 (Assumption!)

    Get-SPLogEvent -StartTime "05/11/2015 11:22:08" -EndTime "05/11/2015 11:23:08" | Where-Object 
    {$_.Correlation -eq "8a343106-0d90-492f-a683-dd0fb59c0058"}

    “Missing LCID for <Column>” πŸ™‚ he he he. Missing English and French!

    New solution includes new fields which is set to be mandatory; managed term store now need two more language English and French – Dutch is by default!

    Okay, I had the service packs binaries so installed English and French Language Packs to confirm the fix- Voila! we were back on track – 45% of the tasks accomplished! Note: If you are in RTM – Download the latest service pack – Extract and Delete any entries in Update folder which is in the year 2013. So, you can install the SharePoint 2010 Service Packs as RTM and upgrade to the current farm build version id required.

    We have done only the initial phase of the work. Few more SharePoint 2010 IT Professional tasks are shared below

    1. Update all missing fields.
      • How to find the missing fields? We may have newly added columns πŸ™‚
    2. Read other list and update the DOC LIB.
      • Dude! it’s Note Field so we have HTML tags Export and Import in PowerShell or editing in DOC LIB using data sheet looks ugly πŸ™
    3. Approve all the approved items in the DOC LIB.
      • After manipulating the items – Approve the items if it was approved and pending should remain as pending. πŸ™‚ We haven’t touched workflow
    4. Most of the information are rendering in SharePoint portal by reading Active Directory.
      • Few users left the organization – We need to update author names with common ID (This is interesting πŸ™‚ )
    5. What happened to LYNC Presence in the SharePoint portal? LYNC Client is upgraded to Skype for Business. Need to investigate further more! May be browser add in!
      • Not my cup of tea though still I will figure out the reason!
    6. Who created the item? Who Modified the same?
      • We sorted this – But will discuss this again!
    7. Read the department of author and update it in the DOC LIB field (This is very interesting task because we need to explore User Profile Service Application)
    8. Complete the content automation – After all set to good we will start the due diligence for migrate the farm to SharePoint 2013. Or, will think about a POC using SharePoint 2016 Technical Preview!
      • I am waiting ! Not, that easy but possible!
    9. Mean while we need to upgrade the Operating System of MOSS 2007 – Since Windows 2003 server has no support we need to upgrade it to Windows 2008 R2 Standard and strictly needs to be in 32 bit.

    More real time scripts, SharePoint Developer and IT Professional discussion about PowerShell will be continued in next blog πŸ™‚

     

    Create your own ISE Addon using Visual Studio 2013

    Windows PowerShell discussion with System Administrators and Developers: Feedback – ISE has no good Add-On’s. Many uses ISE Steroids which is an excellent add-on for PowerShell ISE. We do have script browser and analyzer now.

    Comparing ISE with Visual Studio is not fair πŸ™‚ Why do you want to do that? WMF 5.0 and it’s features are far better than earlier versions. So, indeed PowerShell ISE is also grown up πŸ™‚

    We don’t have commands to meet all our needs in Windows PowerShell so we create a function , Proxy Functions, Workflow, Class or modules for various needs. Similarly, we can create our own add on for ISE. Let us see a quick demo of it using Visual Studio 2013.

    We can easily create this using with the available VSIX if not you can opt for WPF Control Library and create a add-on as required by adding Microsoft.PowerShell.GPowerShell. In this article we will make our job easy by using VSIX – Click to download .

    Read the documentation and install the VSIX as required. Now, we will create a simple CustomAddon which will insert a code or some string into the current ISE file.

    Open Visual Studio 2013 and choose Visual C# and select PowerShell ISE Add-on for VS2013. Name it as required! In our case I have named it as CustomAddOn as shown below

    1

    Now open up the UserControl1.xaml.cs code – Just double click it and the below will be the default code.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows;
    using System.Windows.Controls;
    using System.Windows.Data;
    using System.Windows.Documents;
    using System.Windows.Input;
    using System.Windows.Media;
    using System.Windows.Media.Imaging;
    using System.Windows.Navigation;
    using System.Windows.Shapes;
    using Microsoft.PowerShell.Host.ISE;
    
    namespace CustomAddOn
    {
        /// <summary>
        /// Interaction logic for UserControl1.xaml
        /// </summary>
        public partial class UserControl1 : IAddOnToolHostObject
        {
            public UserControl1()
            {
                InitializeComponent();
            }
    
         <strong>   public ObjectModelRoot HostObject
            {
                get
                {
                    throw new NotImplementedException();
                }
                set
                {
                    throw new NotImplementedException();
                }
            }</strong>
        }
    }
    

    Okay, we have the pseudo code – let’s do some modification and test the ISE add-on to insert a code or some string in ISE. To do that first we need to comment the below lines and just add get and set like shown below

    public ObjectModelRoot HostObject
            {
                //get
                //{
                //    throw new NotImplementedException();
                //}
                //set
                //{
                //    throw new NotImplementedException();
                //}
                get;
                set;
            }

    Now, it’s time for us to add the controls and code as required. For, testing let us add a button and trigger an event which inserts code or string in ISE current file.

    So, I have added a button in XAML file and the code looks like below

    <UserControl x:Class="CustomAddOn.UserControl1"
                 xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
                 xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
                 xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" 
                 xmlns:d="http://schemas.microsoft.com/expression/blend/2008" 
                 mc:Ignorable="d" 
                 d:DesignHeight="300" d:DesignWidth="300">
        <Grid>
            <Button Content="Insert Code" HorizontalAlignment="Left" Margin="32,57,0,0" VerticalAlignment="Top" Width="215" RenderTransformOrigin="-0.846,-0.893" Name="code" Click="click_insert"/>
    
        </Grid>
    </UserControl>
    

    Just make a note – I have made the name of the button as code and created a click event but haven’t did any code for event firing. so, to do that we will jump back to CS file and code as given below.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows;
    using System.Windows.Controls;
    using System.Windows.Data;
    using System.Windows.Documents;
    using System.Windows.Input;
    using System.Windows.Media;
    using System.Windows.Media.Imaging;
    using System.Windows.Navigation;
    using System.Windows.Shapes;
    using Microsoft.PowerShell.Host.ISE;
    
    namespace CustomAddOn
    {
        /// <summary>
        /// Interaction logic for UserControl1.xaml
        /// </summary>
        public partial class UserControl1 : IAddOnToolHostObject
        {
            public UserControl1()
            {
                InitializeComponent();
            }
    
            private void click_insert(object sender , RoutedEventArgs e)
            {
                HostObject.CurrentFile.Editor.InsertText("Get-Service");
            }
    
            public ObjectModelRoot HostObject
            {
                //get
                //{
                //    throw new NotImplementedException();
                //}
                //set
                //{
                //    throw new NotImplementedException();
                //}
                get;
                set;
            }
        }
    }
    

    Do right click on the CustomAddon in solution explorer and Build your solution then spin up your PowerShell ISE and execute the below piece of code

    Import-Module C:\Temp\CustomAddOn\CustomAddOn\bin\Debug\CustomAddOn.dll
    $psISE.CurrentPowerShellTab.VerticalAddOnTools.Add("CustomAddOn" , [CustomAddOn.UserControl1] , $true)

    πŸ™‚ πŸ™‚ πŸ™‚ πŸ™‚ πŸ™‚ Tada! We have our Custom Add on in ISE

    2

    In PowerShell we can do the same using below one liner πŸ™‚

    3

    That’s it for now ! We will see more WPF controls and building ISE Add-On’s using Visual Studio 2013!

    Cheers! Enjoy PowerShell!

    Retrieve Ticket Information – Using Service Desk Plus REST API and PowerShell – Part 1 – Get_Request

    Recently I was reading the article Service Desk Plus REST API. Well! yeah not really great for Automation at this point of time but partially served my needs.I love automation so thought of spinning up PowerShell to do few tasks which I perform on daily basis with minimum tool permission. Indeed, service desk plus has reporting feature and can be automated (Personally not interested to deep dive in it). Reporting automation in tool depends on the server hardware / software capacity and tool configuration. Consider Mid level managers and multiple technicians configured reporting for their needs we may fall in performance degradation. Many will do SQL query to pull reports. Do, remember after pulling reports we should work on Excel to make good reports.

    What can we do with Service Desk Plus REST API?

    1. Request Operations [We will cover only this part in this blog!]
    2. Notes Related Operations
    3. Work log Related Operations
    4. Change Related Operations
    5. CMDB Related Operations
    6. Technician Related Operations

    Let’s start Automating Reporting, to begin with ensure you have below perquisites

    1. Know about your server host name or URL of the tool and the port information – Eg: http://ServerName:<port>
    2. Keep the API documentation handy – we need to refer to this while coding.
    3. Contact your tool admin and get the API key – Do remember if you are a tool admin you can’t generate one for yourself with the same ID.

    You can fetch Request Number by querying your SQL DB using PowerShell – Since, we have enough code in the internet we will skip the PowerShell code for retrieving inform from SQL DB.

    My first attempt failed because I didn’t read the API documentation πŸ™‚

    REST_API1Ay! it’s doing something! grab the API documentation and follow the instructions. We need to post the operation name and we have got our API key already from tool admin!. Now use the PowerShell code like shown below

    $Params = @{OPERATION_NAME=’GET_REQUEST’;TECHNICIAN_KEY=’ASK YOUR TOOL ADMIN’;}
    $ticketid = ‘Get it manually for now’
    $data = Invoke-RestMethod -Uri http://localhost:80/sdpapi/request/$ticketid/ -Method Post -Body $Params

    Okay! This gave me what I need πŸ™‚ πŸ™‚ πŸ™‚ XML πŸ™‚ πŸ™‚ πŸ™‚

    REST_API2

    To retrieve the status information we need to see the response code like shown below

    $data.API.response.operation.result

    REST_API3

    Okay! let us see the ticket information in PowerShell ISE πŸ™‚ But! how do we know that information and status are in result and detail node? Simple, see the API documentation of Viewing Request – Example is shown in the below image

    REST_API4Cool! We are good to fire the below PowerShell code.

    $data.API.response.operation.Details.parameter

    TaDa! The sample output is below

    REST_API5

    Service Desk Plus documentation time format is human readable format and this is not why? Well! I am not from manage engine team so I have no answer but as a System Admin I can read that as EPOCH time format. So, again do little more code in PowerShell and convert this time format.

    “Date and Time Format is {0}” -f (([datetime]’1/1/1970′).AddSeconds([int](1439388404268 / 1000)))

    REST_API6

    So, I ended up converting it to Short Date String!

    (([datetime]’1/1/1970′).AddSeconds([int](1439388404268Β  / 1000)).ToShortDateString())

    That’s it for now, in next post we will see exporting multiple requests to XML for our automation demo! Enjoy PowerShell πŸ™‚ πŸ™‚ πŸ™‚ πŸ™‚