Powershell SharePoint Get all item data from document libraries – CSOM SharePoint Online

The powershell script below shows how to iterate through all libraries in a web on SharePoint Online and extract filename, version and checked out to name.

#Credentials to connect to office 365 site collection url 
$username = "me@isharepoint.onmicrosoft.com"
$password = "mypass"
$url = "https://isharepoint.sharepoint.com/"
$csvfile = "c:\export.csv"


$securePassword = $password |ConvertTo-SecureString -AsPlainText -force

<#
Write-Host "Load CSOM libraries" -foregroundcolor black -backgroundcolor yellow
Set-Location $PSScriptRoot
Add-Type -Path (Resolve-Path "Microsoft.SharePoint.Client.dll")
Add-Type -Path (Resolve-Path "Microsoft.SharePoint.Client.Runtime.dll")
Write-Host "CSOM libraries loaded successfully" -foregroundcolor black -backgroundcolor Green 
#>

Write-Host "authenticate to SharePoint Online Tenant site $url and get ClientContext object" -foregroundcolor black -backgroundcolor yellow  
$Context = New-Object Microsoft.SharePoint.Client.ClientContext($url) 
$credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($username, $securePassword) 
$Context.Credentials = $credentials 
$context.RequestTimeOut = 5000 * 60 * 10;
$web = $context.Web
$site = $context.Site 
$context.Load($web)
$context.Load($site)
try
{
  $context.ExecuteQuery()
  Write-Host "authenticateed to SharePoint Online Tenant site $url and get ClientContext object succeefully" -foregroundcolor black -backgroundcolor Green
}
catch
{
  Write-Host "Not able to authenticateed to SharePoint Online $_.Exception.Message" -foregroundcolor black -backgroundcolor Red
  return
}

#P.S. You can get Lists with single ExecuteQuery, but keeping the above code to connect to the site so that will be common for all samples
#You can try placing below 37 and 38 lines of below 21st line, it will still get the Lists by making single request to the server
#Get all lists Info
Write-Host "Getting all the list info" -foregroundcolor black -backgroundcolor yellow  
$Lists = $web.Lists
$Context.Load($Lists)
try
{
$Context.ExecuteQuery()
Write-Host "Successfully retrived all the list info" -foregroundcolor black -backgroundcolor green  

}
catch
{
Write-Host "Error while getting the list info" -foregroundcolor black -backgroundcolor Red  
}

#this bit creates the CSV if it does not already exist
$headers = "List", "Name", "Type", "Path", "Version", "Status"
$psObject = New-Object psobject
foreach($header in $headers)
{
 Add-Member -InputObject $psobject -MemberType noteproperty -Name $header -Value ""
}
$psObject | Export-Csv $csvfile -NoTypeInformation



Write-Host "Displaying Lists with in the site started....." -foregroundcolor black -backgroundcolor yellow  
#Get List Title
foreach($list in $Lists)
{
	#if($list.BaseTemplate -eq "101" -and $list.Title -eq "Test1")   
	if($list.BaseTemplate -eq "101")   	
	{
		Write-Host "Started on library: " $list.Title -foregroundcolor black -backgroundcolor yellow

	
		$items = $list.GetItems([Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery())
		
		$Context.Load($items)
		$Context.ExecuteQuery()
		Write-Host "... Found " $items.Count " items" -foregroundcolor black -backgroundcolor yellow
		
		$totalItemCount = $items.Count
		$count = 0
		foreach($item in $items)
		{
			$status = ""
			$version = ""
			$count = $count+1
			
			Write-Host "... Processing file " $count " of " $totalItemCount
			
			if($item.FileSystemObjectType -eq "File")
			{
				$checkedout = $item["CheckoutUser"]
				
				if($checkedout)
				{
					$checkoutUser = $web.GetUserById($checkedout.LookupId);
					$Context.Load($checkoutUser)
					$Context.ExecuteQuery()
					$status = $checkoutUser.LoginName
				}
				
				$versionData = $item.File.Versions
				$Context.Load($versionData)
				$Context.ExecuteQuery()
				$versionData = $versionData[$versionData.Count-1]
				$version = $versionData.VersionLabel
			}
			
			$hash = @{
				 "List" =  $list.Title
				 "Name" = $item["FileLeafRef"]
				 "Type" = $item.FileSystemObjectType
				 "Path" = $item["FileRef"]
				 "Version" =$version
				 "Status" = $status
				  }
				  
			$newRow = New-Object PsObject -Property $hash
			Export-Csv $csvfile -inputobject $newrow -append -Force
		}
	}
}
Write-Host "Displaying Lists Completed" -foregroundcolor black -backgroundcolor Green




Client.SyncContext.InitializeAsync(store, new MobileServiceSyncHandler()) not working when offline

In Xamarin.IOS I found that Azure mobile app in offline mode wouldnt get past this line when offline.

await MobileService.SyncContext.InitializeAsync(store, new MobileServiceSyncHandler())

And if you remove the await and put a Wait at the end that just causes deadlock.

Only solution I found that works is:

System.Threading.Tasks.Task.Run(() => MobileService.SyncContext.InitializeAsync(store, new MobileServiceSyncHandler())).Wait();

This is as per Quickstarts sample so clearly they need to update it.

Xamarin C# Android getting height 0 and cant remove GlobalLayoutListener event

You can height or width 0 when trying to access during on Create. This is because the lifecycle of the view hasnt been completed yet.

You need to wait until the view layout has been completed.

view.Post(MyMethodThatNeedsHeight);
...
public void MyMethodThatNeedsHeight()
{
//do something
}

Most articles mention use of AddOnGlobalLayoutListener but it didnt seem possible to remove the event handler, which didnt work for me (for more info see https://forums.xamarin.com/discussion/19022/how-do-i-get-the-height-of-a-linearlayout-oncreate-method-in-an-activity)

Getting azure mobile app – 412 precondition failed after first successful time

If like me, you’re getting 412 precondition failed on azure mobile app service on any updates (patch) your posting to the service, even though the update worked the first time….then most likely its because your updating the same item without fetching the new data.

i.e. say you have item User and you update the users first name, then without reloading the user object you try to update the last name….it wont work because the Version column will be out of date.

You need to reload the item from your local store before adding new changes to it.

Create and download a backup from SQL Azure Database Service

Open command prompt and run the below command. In this case you’ll need to create C:\backups folder first.

"C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe" /Action:Export /SourceServerName:"tcp:xxxxx.database.windows.net,1433" /SourceDatabaseName:DbName /SourceUser:user /SourcePassword:password /TargetFile:C:\backups\backup.bacpac

You will need to replace the bits in bold with whatever is relevant for you.

Azure Mobile App – Get username from custom authentication when User.Identity.Name is blank

If you’ve setup custom authentication with Aspnet Identity as mentioned in my previous post or using some other custom authentication and you need to get the authorized users username, the below should do the trick:

ClaimsPrincipal claimsUser = (ClaimsPrincipal)this.User;
            string id = claimsUser.FindFirst(ClaimTypes.NameIdentifier).Value;

I used this to filter my GetAllItems table controller like so:

return Query().Where(todo=> todo.Username == id);

 

Step by step guide Adding Custom Authentication to Azure mobile app service walkthrough

This post assumes you have already created a new Azure Mobile app service. Im also assuming you already have an existing database on Azure with AspUsers tables like so:

usertables

Steps:

  1. Open Azure portal and select app services then select your mobile app

selectappservice

2. Choose Authentication/Authorization and turn App Service Authentication on.  In this post we’re only looking at custom authentication so in the Action to take dropdown select Allow requst no action. If you will have additional external auth then you may select that. Hit save changes.

turnonauth

3. OK now we need to edit our server code, if you’ve not already done so download the quick start project. For our example we’ll be looking at C# not Nodejs

quickstart

4. Open the project in Visual Studio. As we’re going to be using Asp.net Identity we need to add these Nuget packages as well as EntityFramework Identity. Make sure you have all of the below if not add them in:

packagaes

5. Under the App_Start folder modify Startup.MobileApp.cs and add the following lines to intiate Owin identity:

 public static void ConfigureMobileApp(IAppBuilder app)
        {
            //added to enable authentication
            app.CreatePerOwinContext(ApplicationDbContext.Create);
            app.CreatePerOwinContext<ApplicationUserManager>(ApplicationUserManager.Create);
            app.CreatePerOwinContext<ApplicationSignInManager>(ApplicationSignInManager.Create);

...

After the HttpConfiguration config = new HttpConfiguration(); add the following route. This will be used by your app later when we select “Custom” auth the app will call the below route on your service.

config.Routes.MapHttpRoute("custom", ".auth/login/custom", new { controller = "Auth" });

As Im using existing database for this Im also going to comment our the database seed intialization and uncomment the intialize with null line:

 // Use Entity Framework Code First to create database tables based on your DbContext
           // Database.SetInitializer(new siteauditsappInitializer());//not used as dont want code first to overwrite database

            // To prevent Entity Framework from modifying your database schema, use a null database initializer
            Database.SetInitializer<siteauditsappContext>(null);

6. Then add IdentityConfig.cs to the App_Start folder. Im not going to go into the code for this as you should ready have it, if not you should be able to get it from them Asp.net MVC project template when select IndivualAccounts for authentication.

7. Under Controllers folder create a new WebApi Controller called AuthController.cs with below code:

 public class AuthController : ApiController
    {

        public class LoginResult
        {
            public string authenticationToken { get; set; }
            public LoginResultUser user { get; set; }
        }

        public class LoginResultUser
        {
            public string userId { get; set; }
        }

        private ApplicationUserManager _userManager;
        private ApplicationSignInManager _signInManager;

        public ApplicationUserManager UserManager
        {
            get
            {
                return _userManager ?? HttpContext.Current.GetOwinContext().GetUserManager<ApplicationUserManager>();
            }
            private set
            {
                _userManager = value;
            }
        }

        public ApplicationSignInManager SignInManager
        {
            get
            {
                return _signInManager ?? HttpContext.Current.GetOwinContext().Get<ApplicationSignInManager>();
            }
            private set
            {
                _signInManager = value;
            }
        }



        public IHttpActionResult Post([FromBody] JObject assertion)
        {
            
            bool passValid = IsPasswordValid(assertion).Result;
            if (passValid) // user-defined function, checks against a database
            {

                var signingKey = Environment.GetEnvironmentVariable("WEBSITE_AUTH_SIGNING_KEY"); //This will be provided by Azure Mobile service when we publish our project
                
                var audience = "https://your app here.azurewebsites.net/"; // audience must match the url of the site
                var issuer = "https://your app here.azurewebsites.net/"; // audience must match the url of the site

                string username = assertion.GetValue("email").Value<string>();

                JwtSecurityToken token = AppServiceLoginHandler.CreateToken(new Claim[] { new Claim(JwtRegisteredClaimNames.Sub, username) },
                    signingKey,
                    audience,
                    issuer,
                    TimeSpan.FromHours(24));
                return Ok(new LoginResult()
                {
                    authenticationToken = token.RawData,
                    user = new LoginResultUser() { userId = username }
                });
            }
            else // user assertion was not valid
            {
                return Content(HttpStatusCode.Unauthorized, "Wrong credentials");
            }
        }

        private async Task<bool> IsPasswordValid(JObject assertion)
        {
            // this is where we would do checks agains a database
            try
            {
                string username = assertion.GetValue("email").ToString();
                string password = assertion.GetValue("password").ToString();
               
                
                var resultTask = SignInManager.PasswordSignInAsync(username, password, true, shouldLockout: true);
                var result = resultTask.Result;
                switch (result)
                {
                    case SignInStatus.Success:
                        return true;
                    case SignInStatus.LockedOut:
                        return false;
                    case SignInStatus.RequiresVerification:
                        return false;
                    case SignInStatus.Failure:
                    default:
                        return false;
                }

            }
            catch (Exception ex)
            {
                return false;
            }
        }
}

8. Add IdentityModel.cs to your Models folder:

using System.Data.Entity;
using System.Security.Claims;
using System.Threading.Tasks;
using Microsoft.AspNet.Identity;
using Microsoft.AspNet.Identity.EntityFramework;
using System;

namespace siteauditsappService.Models
{
    public class ApplicationUser : IdentityUser
    {

        public async Task<ClaimsIdentity> GenerateUserIdentityAsync(UserManager<ApplicationUser> manager)
        {
            // Note the authenticationType must match the one defined in CookieAuthenticationOptions.AuthenticationType
            var userIdentity = await manager.CreateIdentityAsync(this, DefaultAuthenticationTypes.ApplicationCookie);
            // Add custom user claims here
            return userIdentity;
        }

        public string FirstName { get; set; }
        public string LastName { get; set; }

    }



    public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
    {
        private const string connectionStringName = "Name=MS_TableConnectionString";

        public ApplicationDbContext()
            : base(connectionStringName, throwIfV1Schema: false)
        {
            Configuration.ProxyCreationEnabled = false;
            Configuration.LazyLoadingEnabled = false;
        }

        // protected override void OnModelCreating(DbModelBuilder modelBuilder)
        // {
        //     base.OnModelCreating(modelBuilder);
        //      modelBuilder.HasDefaultSchema("Application");//if users table has different scheme from dbo
        //   }


        public static ApplicationDbContext Create()
        {
            return new ApplicationDbContext();
        }


    }
}

In above code note the connection string should be for where you aspnet users database is. Store your connection string in web.config for dev but on Azure this will be in the Application Settings area. When you publish your project to Azure whatever connection string you have in  y ou web.config it will be replaced with the ones in Azure:

webconfig

9. Finally you should be ready to test this. Publish the project and then open you favourite Restful client. I google chrome extension Postman. In the client set body to json and do a post to the service with header Content-Type application/json and body like below:

postman

If credentials are correct you should get back token like so:

{
  "authenticationToken": "blablabbla",
  "user": {
    "userId": "myemail@hotmail.com"
  }
}

Finally add the

[Authorize]

attribute to all the table controllers that you want to secure.

 

Useful tip: What I found really useful was the ability to monitor issues when testing authentication using the Azure Log stream.

Next we need to modify our app to authenticate with this service. I show how to do this using Xamarin app.

 

Step by step – Creating an authenticated Azure mobile app with Xamarin walkthrough

In a previous post I showed how to setup to Azure mobile app backend C# service to use custom authenticated with Asp.Net Identity from an existing user database. If you’ve not done that yet please follow that post first.

Now that we have the back end ready for custom authentication we need to configure the app. In our example we’ll be modifying the quick starts Xamarin sample you get from Azure:

xamarin

You need to create a new login screen and set it is as default. Once done add username and password fields and a sign in button. In the button click event you need:

 
private MobileServiceClient client;
client = new MobileServiceClient(applicationURL);
var credentials = new JObject
			{
				["email"] = Helper.RetrieveString("Username"),
				["password"] = Helper.RetrieveString("Password")
			};
var user = await TodoItemManager.DefaultManager.CurrentClient.LoginAsync("custom", credentials);

The user field will now contain the token from the webservice. The Azure mobile service should will automatically add the token using X-Zumo-Auth headers to each call for data. You wont need to do anything.

Simples!

Add trace message diagnostics to Azure Log stream for Web and Mobile App Services

 

public class AzureApplicationLogTraceListener : TraceListener
    {
        private readonly string _logPath;
        private readonly object _lock = new object();

        public AzureApplicationLogTraceListener()
        {
            string instanceId = Environment.GetEnvironmentVariable("WEBSITE_INSTANCE_ID");
            if (instanceId != null)
            {
                string logFolder = Environment.ExpandEnvironmentVariables(@"%HOME%\LogFiles\application");
                Directory.CreateDirectory(logFolder);
                instanceId = instanceId.Substring(0, 6);
                _logPath = Path.Combine(logFolder, $"logs_{instanceId}.txt");

            }
        }

        public override void Write(string message)
        {
            if (_logPath != null)
            {
                lock (this)
                {
                    File.AppendAllText(_logPath, message);
                }
            }
        }

        public override void WriteLine(string message)
        {
            Write(message + Environment.NewLine);
        }
    }

I used this for diagnosing issues with Azure mobile app service authentication and it saved a lot of time!