Posted on Leave a comment

Download Microsoft Sharepoint List Attachments using Powershell script

I stumble across a problem when trying to download attachment from the Sharepoint List. The list have more than 50,000 rows, the problem with the big list is Windows explorer is not able to display the list in Explorer view, so there is a need to use the Powershell script to download all the attachment programmatically.

Step 1. Make sure you have the Sharepoint Client dll required as specified in the following code


[void][Reflection.Assembly]::LoadFrom("$env:CommonProgramFiles\Microsoft Shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll")
[void][Reflection.Assembly]::LoadFrom("$env:CommonProgramFiles\Microsoft Shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll")

Step 2. Define the sharepoint site and the list library

The sharepoint site and the library is defined using the following $webUrl variable and $library respectively, also don’t forget to specify the local folder where the files will be downloaded.

$webUrl = "http://website.com/sites/sharepointsite" 
$library = "SharepointLibrary"
Local Folder to dump files
$tempLocation = "C:\temp\"

Step 3. Define how many rows the CamlQuery should return on each iteration

The beauty of the PowerShell Script is that you can specify how many rows to return each time, so the script will not have a problem recursively going through a big list that are over 50,000 and being able to download all the attachment. The following code shows that we are limiting the query to return 3000 rows.

$camlQuery = New-Object Microsoft.SharePoint.Client.CamlQuery $camlQuery.ViewXml ="<View> <RowLimit>3000</RowLimit></View>"

Step 4. Define the folder structure to hold all the attachment to be downloaded

The following code is using the combination of Title and ID as the folder name to store the attachment. It also check whether the folder already exists prior to creating a new one.


    $folderName=$listItem["Title"]+"_"+$listItem["ID"]
    $destinationfolder = $tempLocation + "\"+ $folderName 

     #check if folder is exist or not, if not exist then create new
  if (!(Test-Path -path $destinationfolder))        
   {            
     $dest = New-Item $destinationfolder -type directory      
     Write-Host "Created Folder with Name:" $folderName    
   }

The following is the full code to download the list attachments and put them in the local folder. The credential used is the user credential where the script is executed, that means the login user will need to have access to the SharePoint Site and able to download the attachement.


[void][Reflection.Assembly]::LoadFrom("$env:CommonProgramFiles\Microsoft Shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll")
[void][Reflection.Assembly]::LoadFrom("$env:CommonProgramFiles\Microsoft Shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll")
Clear-Host
#$cred = Get-Credential "user@microsoft.com"
#$credentials = New-Object Microsoft.Sharepoint.Client.SharePointOnlineCredentials($cred.Username, $cred.Password)
$webUrl = "http://website.com/sites/sharepointsite"

$clientContext = New-Object Microsoft.Sharepoint.Client.ClientContext($webUrl)
Write-Host "Connecting To Site: " $webUrl   

 $username = "$env:USERDOMAIN\$env:USERNAME"

$library = "SharepointLibrary" 
#Local Folder to dump files
$tempLocation = "C:\temp\"    

$global:web = $clientContext.Web;
$global:site = $clientContext.Site;

$clientContext.Load($web)
$clientContext.Load($site)

$listRelItems = $clientContext.Web.Lists.GetByTitle($library)

$clientContext.Load($listRelItems)
$clientContext.ExecuteQuery();
Write-Host "list item count " $listRelItems.ItemCount

$camlQuery = New-Object Microsoft.SharePoint.Client.CamlQuery
$camlQuery.ViewXml =" 3000"
 $listCollection = New-Object System.Collections.Generic.List[string] 
 $count = 0
Do {
$allItems=$listRelItems.GetItems($camlQuery)
$clientContext.Load($allItems)
$clientContext.ExecuteQuery()
$camlQuery.ListItemCollectionPosition = $allItems.ListItemCollectionPosition
foreach ($listItem in $allItems)
 {
    $folderName=$listItem["Title"]+"_"+$listItem["ID"]
    $destinationfolder = $tempLocation + "\"+ $folderName 

     #check if folder is exist or not, if not exist then create new
  if (!(Test-Path -path $destinationfolder))        
   {            
     $dest = New-Item $destinationfolder -type directory      
     Write-Host "Created Folder with Name:" $folderName    
   }
    $clientContext.load($listItem)
    $clientContext.ExecuteQuery();

    $attach = $listItem.AttachmentFiles
    $clientContext.load($attach)
    $clientContext.ExecuteQuery();
    if($attach -ne $null){
        Write-Host "No of attachment:" $attach.Count
        foreach ($attachitem in $attach){
            Write-Host "Downloading Attachements started: "   $attachitem.FileName
            $attachpath = $webUrl + "/Lists/"+ $library + "/Attachments/" + $listItem["ID"] + "/" + $attachitem.FileName
            Write-Host "path: " $attachpath 
         
            $path = $destinationfolder + "\" + $attachitem.FileName
            Write-Host "Saving to the location:"  $path

            $siteUri = [Uri]$attachpath
            $client = new-object System.Net.WebClient
            $client.UseDefaultCredentials=$true
            
            try{
                  $client.DownloadFile($attachpath, $path)
                  $client.Dispose()
            } catch{
                write-error "Failed to download $url, $_ "
            }

        }
    }else {
     Write-Host   "For above current item don't have any attachments" 
    }
  }
Write-Host " List item" $count
$count++
} while ($camlQuery.ListItemCollectionPosition -ne $null)
     Write-Host   "Script execution done !" 

Please let me know if the above script is of useful to you and don’t forget to share or subscribe for more frequent update to the similar topic. You can also drop me a line or questions if you have any.

Posted on Leave a comment

4 Steps to download Microsoft Sharepoint Document Library recursively

I stumble across this problem when we try to decommissioning Microsoft Sharepoint. We had a huge document library and it is not possible to copy them from explorer view, so the solution is to use PowerShell script to do this automagically.

Step 1. Define the DLL that is required.

This is done through the following code snippets. It is crucial to have the 2 DLL to allow the copy function to work. The script will use the credential of the user login into the machine and executing the script. This removes the complexity having to enter the sharepoint credential into the script.

# Load the SharePoint 2013 .NET Framework Client Object Model libraries. # 
[void][Reflection.Assembly]::LoadFrom("c:\Microsoft.SharePoint.Client.dll")
[void][Reflection.Assembly]::LoadFrom("c:\Microsoft.SharePoint.Client.Runtime.dll")

Step 2. Define the sharepoint site URL and the Document Library repository

You can simply enter the sharepoint URL by replacing the following $serverURL variable. Enter the document library by replacing the $DocumentLibrary variable and don’t forget to define the destination folder.

$serverURL = “http://sharepoint.url/sites/sitename”
$destination = "C:\temp\"
$DocumentLibary = "Document Library Name"

Step 3. Choose whether you only want specific folder to be downloaded from the Document Library

Change the folder name that you are interest in downloading, in the following example we are only interested in downloading folder “Payments” and all the folder underneath it.


function Parse-Lists ($Lists)
{
$clientContext.Load($Lists)
$clientContext.Load($Lists.RootFolder.Folders)
$clientContext.ExecuteQuery()
    
    foreach ($Folder in $Lists.RootFolder.Folders)
        {
            if ($Folder.name -eq "Payments"){   #onlydownload selected folder
                recurse $Folder
            }
        }

}

Step 4. Execute the script via PowerShell window or from Command line.

To execute the script via command line you can execute the following Powershell command, with the assumption the name of the powershell script is “scriptname.ps1”

C:\Powershell.exe scriptname.ps1

Here are the full script to download the Sharepoint Document library, be careful the script will download the entire document library recursively, so please make sure you check Step 3 above. With great power comes great responsibility.

# Load the SharePoint 2013 .NET Framework Client Object Model libraries. # 
[void][Reflection.Assembly]::LoadFrom("c:\Microsoft.SharePoint.Client.dll")
[void][Reflection.Assembly]::LoadFrom("c:\Microsoft.SharePoint.Client.Runtime.dll")
Clear-Host

$serverURL = “http://sharepoint.url/sites/sitename”
#$siteUrl = $serverURL+"/documents”
$destination = "C:\temp\"
$DocumentLibary = "Document Library Name"
$downloadEnabled = $true
$versionEnabled = $false

# Authenticate with the SharePoint Online site. # 
#$username = ""
#$Password = ""
#$securePassword = ConvertTo-SecureString $Password -AsPlainText -Force  

$clientContext = New-Object Microsoft.SharePoint.Client.ClientContext($serverURL) 
#$credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($username, $securePassword) 
#$clientContext.Credentials = $credentials 
if (!$clientContext.ServerObjectIsNull.Value) 
{ 
    Write-Output "Connected to SharePoint Online site: '$serverURL'"
} 


function HTTPDownloadFile($ServerFileLocation, $DownloadPath)
{
#Download the file from the version's URL, download to the $DownloadPath location
    $webclient = New-Object System.Net.WebClient
    $webclient.credentials = $credentials
    Write-Output "Download From ->'$ServerFileLocation'"
    Write-Output "Write to->'$DownloadPath'"
    $webclient.Headers.Add("X-FORMS_BASED_AUTH_ACCEPTED", "f")
    $webclient.DownloadFile($ServerFileLocation,$DownloadPath)
}

function DownloadFile($theFile, $DownloadPath)
{
    $fileRef = $theFile.ServerRelativeUrl;
    Write-Host $fileRef;
    $fileInfo = [Microsoft.sharepoint.client.File]::OpenBinaryDirect($clientContext, $fileRef);
    $fileStream = [System.IO.File]::Create($DownloadPath)
    $fileInfo.Stream.CopyTo($fileStream);
    $fileStream.Close()
}

function Get-FileVersions ($file, $destinationFolder)
{
    $clientContext.Load($file.Versions)
    $clientContext.ExecuteQuery()
    foreach($version in $file.Versions)
    {
        #Add version label to file in format: [Filename]_v[version#].[extension]
        $filesplit = $file.Name.split(".") 
        $fullname = $filesplit[0] 
        $fileext = $filesplit[1] 
        $FullFileName = $fullname+"_v"+$version.VersionLabel+"."+$fileext           

        #Can't create an SPFile object from historical versions, but CAN download via HTTP
        #Create the full File URL using the Website URL and version's URL
        $ServerFileLocation = $siteUrl+"/"+$version.Url

        #Full Download path including filename
        $DownloadPath = $destinationfolder+"\"+$FullFileName
        
        if($downloadenabled) {HTTPDownloadFile "$ServerFileLocation" "$DownloadPath"}

    }
}

function Get-FolderFiles ($Folder)
{
    $clientContext.Load($Folder.Files)
    $clientContext.ExecuteQuery()

    foreach ($file in $Folder.Files)
        {

            $folderName = $Folder.ServerRelativeURL
            $folderName = $folderName -replace "/","\"
            $folderName = $destination + $folderName
            $fileName = $file.name
            $fileURL = $file.ServerRelativeUrl
            
                
            if (!(Test-Path -path $folderName))
            {
                $dest = New-Item $folderName -type directory 
            }
                
            Write-Output "Destination -> '$folderName'\'$filename'"

            #Create the full File URL using the Website URL and version's URL
            $ServerFileLocation = $serverUrl+$file.ServerRelativeUrl

            #Full Download path including filename
            $DownloadPath = $folderName + "\" + $file.Name
                    
            #if($downloadEnabled) {HTTPDownloadFile "$ServerFileLocation" "$DownloadPath"}
            if($downloadEnabled) {DownloadFile $file "$DownloadPath"}

            if($versionEnabled) {Get-FileVersions $file $folderName}
            
    }
}


function Recurse($Folder) 
{
       
    $folderName = $Folder.Name
    $folderItemCount = $folder.ItemCount

    Write-Output "List Name ->'$folderName'"
    Write-Output "Number of List Items->'$folderItemCount'"

    if($Folder.name -ne "Forms")
        {
            #Write-Host $Folder.Name
            Get-FolderFiles $Folder
        }
 
    Write-Output $folder.ServerRelativeUrl
 
    $thisFolder = $clientContext.Web.GetFolderByServerRelativeUrl($folder.ServerRelativeUrl)
    $clientContext.Load($thisFolder)
    $clientContext.Load($thisFolder.Folders)
    $clientContext.ExecuteQuery()
            
    foreach($subfolder in $thisFolder.Folders)
        {
            Recurse $subfolder  
        }       
}


function Parse-Lists ($Lists)
{
$clientContext.Load($Lists)
$clientContext.Load($Lists.RootFolder.Folders)
$clientContext.ExecuteQuery()
    
    foreach ($Folder in $Lists.RootFolder.Folders)
        {
            if ($Folder.name -eq "Payments"){   #onlydownload selected folder
                recurse $Folder
            }
        }

}

$rootWeb = $clientContext.Web
$LibLists = $rootWeb.lists.getByTitle($DocumentLibary)
$clientContext.Load($rootWeb)
$clientContext.load($LibLists)
$clientContext.ExecuteQuery()

Parse-Lists $LibLists

 

Please let me know if the above script is useful, feel free to subscribe to my blog, share this script or ask me any questions related to the script.

Posted on Leave a comment

Password Protect PDF document using windows Powershell script

Password protect PDF document can be done using Windows Powershell script. I stumble across a powerful script which allow to password protect PDF document using the script. The requirement that I have is to password protect a list of batch PDF document with a specified password that are different between each document and then sending each password protected PDF document to different individual emails.

So after researching for a while I found the script that does the job, but then I come across another hurdle, how do I get the list of password and the list of pdf, so my solution is to put the list of pdf files along with the password in an excel document, and let the script open the excel find the first pdf document, then read the designated password next to it, and run the script to password protect the file and save the password protected file in a different location.

So hunting around for the powershell script to read excel proven to be fruitful, so I combine both of them into one script. Here are the step by step on what I had done and how to modify this to suit your need. This proven to speed up the manual work by a lot. Not to imagine the time and cost savings that comes along with it.

Step 1. Create the list with location of PDF document and the password that you want to protect the document with.

In picture below, we have the source of document in column B, and the target encrypted PDF file name will be located in the location specified in column C. The password for each document will be based on the information in column D. Save this Excel file in C:\temp\ location and lets call the file Book2.xlsx for this exercise.

Step 2. Open your favourite text editor and paste in the following code, you can also use the “Windows PowerShell ISE“:

[System.Reflection.Assembly]::LoadFrom("itextsharp.dll")

function PSUsing {
 param (
 [System.IDisposable] $inputObject = $(throw "The parameter -inputObject is required."),
 [ScriptBlock] $scriptBlock = $(throw "The parameter -scriptBlock is required.")
 )
 
 Try {
 &$scriptBlock
 }
 Finally {
 if ($inputObject.psbase -eq $null) {
 $inputObject.Dispose()
 } else {
 $inputObject.psbase.Dispose()
 }
 }
}

$xlCellTypeLastCell = 11 
$startRow,$col=2,1

$excel=new-object -com excel.application
$wb=$excel.workbooks.open("c:\temp\Book2.xlsx")

for ($i=1; $i -le $wb.sheets.count; $i++)
 {
 $j=0;
 $sh=$wb.Sheets.Item($i)
 $endRow=$sh.UsedRange.SpecialCells($xlCellTypeLastCell).Row
 $rangeAddress=$sh.Cells.Item($startRow+1,$col).Address() + ":" +$sh.Cells.Item($endRow+1,$col).Address()
 $sh.Range($rangeAddress).Value2 | foreach {
 $contract=$sh.Cells.Item($startRow + $j,$col).Value2
 $filesource = $sh.Cells.Item($startRow + $j,$col+1).Value2
 $filedest = $sh.Cells.Item($startRow + $j,$col+2).Value2
 $dob=$sh.Cells.Item($startRow + $j,$col+3).Value2
 
 New-Object PSObject -Property @{Contract=$contract;Dob=$dob}
 
 $file = New-Object System.IO.FileInfo $filesource
 $fileWithPassword = New-Object System.IO.FileInfo $filedest
 $password = $dob
 PSUsing ( $fileStreamIn = $file.OpenRead() ) { 
 PSUsing ( $fileStreamOut = New-Object System.IO.FileStream($fileWithPassword.FullName,[System.IO.FileMode]::Create,[System.IO.FileAccess]::Write,[System.IO.FileShare]::None) ) { 
 PSUsing ( $reader = New-Object iTextSharp.text.pdf.PdfReader $fileStreamIn ) {
 [iTextSharp.text.pdf.PdfEncryptor]::Encrypt($reader, $fileStreamOut, $true, $password, $password, [iTextSharp.text.pdf.PdfWriter]::ALLOW_PRINTING)
 }
 }
 }
 
 $j++
 }
}
$excel.Workbooks.Close()

 

Step 3. Make sure you have the itextsharp.dll library located in the same location as the PowerShell Script.

In this example it should be located in C:\Temp\. You should have the folder structure that looks similar to the one below.

Step 4. Put the source PDF files as specified in the step 1. Execute the Powershell Script by clicking on the execute button if you are using Windows PowerShell ISE.

Step 5. If everything went well, you should be able to see the execution message as per the picture below.

Step 6. The destination folders specified in excel step 1. should contain all the password protected PDF, as shown below.

Now you can tested these Password Protected PDF files by opening it using the password that you had specified in Step 1.

Please leave me a comments if you like this post. Like it if this help you to save some time in automating some of the boring task.

 

Posted on Leave a comment

Windows 10 can’t see network drive

I had a problem where my windows 10 can’t see network drive, all the mapped drive can’t be accessed. I had been pulling my hair over this issue. I got a new HUAWEI router (NBN) but somehow this muck up my Windows 10 mapped drive. I had a NAS drive that was mapped in windows 10 and every now and again the Windows 10 Home will no longer able to access the NAS drive. Strangely all the other computers, I had several Mac in the house, they are all working fine connected via the same router. Here are some instruction on how to fix Windows 10 Network file sharing, it works for me, so I hope it will save you some time.

After countless of hours of tweaking and google searching for why windows 10 can’t see network drive, I found the following Method is the most effective to resolve the issue.

 

Step 1. Go to the settings from clicking “Windows” button on your bottom left corner and select settings as shown in the picture.Windows Options

Step 2. Click on the Network & Internet icon

Windows 10 can't see network drive

Step 3. You will see the similar screen to the one below, scroll down until you find the “Sharing option” and click on it.

Network and Internet

Continue reading Windows 10 can’t see network drive

Posted on Leave a comment

WannaCrypt attacks prevention guide to ensure you didn’t fall victim

A lot of people around the world was infected by the worm WannaCrypt or wannacry that locks your files and demand some ransom (ransomware) with payment via bit coin. To prevent yourself from this attack please make sure that you had updated the windows patch to the latest version.

Details are below.

  • In March, we released a security update which addresses the vulnerability that these attacks are exploiting. Those who have Windows Update enabled are protected against attacks on this vulnerability. For those organizations who have not yet applied the security update, we suggest you immediately deploy Microsoft Security Bulletin MS17-010.
  • For customers using Windows Defender, we released an update earlier today which detects this threat as Ransom:Win32/WannaCrypt. As an additional “defense-in-depth” measure, keep up-to-date anti-malware software installed on your machines. Customers running anti-malware software from any number of security companies can confirm with their provider, that they are protected.
  • This attack type may evolve over time, so any additional defense-in-depth strategies will provide additional protections. (For example, to further protect against SMBv1 attacks, customers should consider blocking legacy protocols on their networks).

If you still run obsolete version of windows like windows XP, you should upgrade to the latest, but for whatever reason you can’t upgrade due to some legacy software that are still running, then you are lucky because Microsoft release security updates for those run out windows as well.

Download English language security updates: Windows Server 2003 SP2 x64, Windows Server 2003 SP2 x86, Windows XP SP2 x64, Windows XP SP3 x86, Windows XP Embedded SP3 x86, Windows 8 x86, Windows 8 x64

Download localized language security updates: Windows Server 2003 SP2 x64, Windows Server 2003 SP2 x86, Windows XP SP2 x64, Windows XP SP3 x86, Windows XP Embedded SP3 x86, Windows 8 x86, Windows 8 x64

If you require our assistance to help with the path, don’t hesitate to let us know.

Posted on Leave a comment

Windows button not working after Windows 10 Upgrade

“Open Windows PowerShell as Administrator
Method 1: Press Windows Key + R on your keyboard Key in PowerShell and hit
Enter. Right click on the PowerShell icon on the taskbar and select Run as
Administrator.
Method 2: Right click Start button Choose Command prompt (Admin) Key in
Powershell in the black window and hit Enter. Paste the following command in the
Administrator: Windows PowerShell window and press Enter key:
Get-AppXPackage -AllUsers | Foreach {Add-AppxPackage -DisableDevelopmentMode
-Register “$($_.InstallLocation)\AppXManifest.xml”}
Wait for PowerShell to execute and complete the command. Ignore the few errors
(in red color) that may pop up. When it finishes, try hitting Start and
hopefully it’ll start working.
Posted on Leave a comment

How to unprotect Excel workbook if you don’t have the password

This method only works for Excel in .xlsx format. Hence, if your Excel workbook is in .xls format, just open it and then save it as .xlsx format.
Step 1: Change the Excel file name extension from .xlsx to .zip. Just click on Yes to make sure when prompted.


Step 2: Open the ZIP archive as 7-ZIP. Open the folder xl->worksheets and you can see threesheet.xml files. Select the sheet1.xml file inside the xl>worksheets directory. Right click on it and select Edit. Choose to open it withNotepad.


Step 3: Delete the tag beginning with sheetProtection. Then save and close the sheet1.xml file.

Step 4: Update the modified sheet1.xml file in the ZIP archive when prompted. Then close the ZIP archive.

Step 5: Repeat Step 2 for sheet2.xml, sheet3.xml … for all the sheet that is protected.


Step 6: Change the ZIP fie name extension back to .xlsx. At this point, the Excel sheet has been unprotected. Open it and you can edit the sheet without password.

“With great power comes great responsibility”