Blog

Csom Context Sharepoint ClientContext

On 23/05/2023

c# Connect to Sharepoint with ClientContext appId and certificat ThumbPrint

 


using Microsoft.Identity.Client;
using Microsoft.SharePoint.Client;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Security;
using System.Security.Cryptography.X509Certificates;
using System.Text;
using System.Threading.Tasks;

namespace MY.PNP.Powershell.EXT.CSOM
{
    public class CsomContext : IDisposable
    {
        public ClientContext _clientContext;
        public ClientRuntimeContext _context;

        private async Task GetAccessToken(string tenantName, string clientId, string thumbprint)
        {
            var certificate = GetCert(thumbprint);
            var authority = $"https://login.microsoftonline.com/{tenantName}.onmicrosoft.com/";
            var azureApp = ConfidentialClientApplicationBuilder.Create(clientId)
                .WithAuthority(authority)
                .WithCertificate(certificate)
                .Build();

            var scopes = new string[] { $"https://{tenantName}.sharepoint.com/.default" };
            var authResult = await azureApp.AcquireTokenForClient(scopes).ExecuteAsync();
            return authResult.AccessToken;
        }

        public async Task CallClientObjectModel(string tenantName, string url, string clientId, string thumbprint)
        {
            var token = await GetAccessToken(tenantName, clientId, thumbprint);
            var siteUrl = url;//;$"https://{tenantName}.sharepoint.com";

            var context = new ClientContext(siteUrl);

            context.ExecutingWebRequest += (s, e) =>
            {
                e.WebRequestExecutor.RequestHeaders["Authorization"] =
                    "Bearer " + token;
            };

            var web = context.Web;
            context.Load(web);
            context.ExecuteQuery();
            Console.WriteLine(web.Title);
            Console.WriteLine(web.Url);
            _context = web.Context;
            _clientContext = context;
        }

        private X509Certificate2 GetCert(string thumbprint)
        {
            X509Store certStore = new X509Store(StoreName.My, StoreLocation.CurrentUser);
            certStore.Open(OpenFlags.ReadOnly);
            X509Certificate2Collection certCollection = certStore.Certificates.Find(
                                       X509FindType.FindByThumbprint,
                                         // Replace below with your cert's thumbprint
                                         thumbprint,
                                       false);
            X509Certificate2 cert = null;
            // Get the first cert with the thumbprint
            if (certCollection.Count > 0)
            {
                cert = certCollection[0];
                // Use certificate
                Console.WriteLine(cert.FriendlyName);
            }
            certStore.Close();
            return cert;
        }

        public void Dispose()
        {
            if (_context != null)
                _context.Dispose();
            if (_clientContext != null)
                _clientContext.Dispose();
        }
    }
}


 

Connect Graph API GraphServiceClient With Certificate thumbprint

On 10/05/2023


using Microsoft.Graph;
using Microsoft.Identity.Client;
using System;
using System.Linq;
using System.Net.Http;
using System.Net.Http.Headers;
using System.Security.Cryptography.X509Certificates;
using System.Threading.Tasks;
using Azure.Identity;
using System.Reflection.Metadata.Ecma335;
using Microsoft.Graph.Models;

public class ClientCredentialsAuthProvider
    {
        private readonly IConfidentialClientApplication msalClient;
        private readonly string[] scopes;
        protected GraphServiceClient _graphClient;

        public ClientCredentialsAuthProvider()
        {
            try
            {

                string appId = "922b7a94-268f-4ac0-ad5e-4d44fe4429cf";
                string tenantId = "3533ab30-c2f0-48fd-b4c5-f5dc6ca77ec3";
                string thumbprint = "AD120A05FF3AAC9A71A6DD71530E96306C29B395 ";// "AD120A05FF3AAC9A71A6DD71530E96306C29B395";
                var scopes = new[] { "https://graph.microsoft.com/.default" };//"https://graph.microsoft.com/.default", "User.Read"

                // Values from app registration
                var clientId = appId;
                var clientCertificate = GetCert(thumbprint);

                // using Azure.Identity;
                var options = new TokenCredentialOptions
                {
                    AuthorityHost = AzureAuthorityHosts.AzurePublicCloud
                };

                var clientCertCredential = new ClientCertificateCredential(
                    tenantId, clientId, clientCertificate, options);

                this._graphClient = new GraphServiceClient(clientCertCredential, scopes);

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public async Task GetUserByMail(string mail)
        {

            try
            {
                var result = await this._graphClient.Users.GetAsync((requestConfiguration) =>
                {
                    requestConfiguration.QueryParameters.Select = new string[] { "displayName", "id", "mail" };
                    requestConfiguration.QueryParameters.Filter = string.Format("mail eq '{0}'", mail);
                    requestConfiguration.QueryParameters.Orderby = new string[] { "displayName" };
                    requestConfiguration.QueryParameters.Count = true;
                    requestConfiguration.Headers.Add("ConsistencyLevel", "eventual");
                });

                if (result.OdataCount == 1)
                {
                    Microsoft.Graph.Models.User user = result.Value[0];
                    return user;
                }
                return null;

            }
            catch (Exception ex)
            {

                throw;
            }
        }

        public async Task CreateTeams(string teamsName, string teamsDescription, string ownerId)
        {
            try
            {
                GraphServiceClient graphClient = null;
                string getUser = string.Format("https://graph.microsoft.com/v1.0/users('{0}')", ownerId);

                var requestBody = new Team
                {
                    DisplayName = teamsName,
                    Description = teamsDescription,
                    Members = new List
                    {
                        new ConversationMember
                        {
                            OdataType = "#microsoft.graph.aadUserConversationMember",
                            Roles = new List
                            {
                                "owner",
                            }
                            ,
                            AdditionalData = new Dictionary
                            {
                                {
                                    "user@odata.bind" , getUser
                                },
                            },
                        },
                    },
                    AdditionalData = new Dictionary
                    {
                        {
                            "template@odata.bind" , "https://graph.microsoft.com/v1.0/teamsTemplates('standard')"
                        }
                    }
                };
                var result = await this._graphClient.Teams.PostAsync(requestBody);

                return "";
            }
            catch (Exception ex)
            {

                throw;
            }
        }

        private X509Certificate2 GetCert(string thumbprint)
        {
            X509Store certStore = new X509Store(StoreName.My, StoreLocation.CurrentUser);
            certStore.Open(OpenFlags.ReadOnly);
            X509Certificate2Collection certCollection = certStore.Certificates.Find(
                                       X509FindType.FindByThumbprint,
                                 // Replace below with your cert's thumbprint
                                 thumbprint,
                                       false);
            X509Certificate2 cert = null;
            // Get the first cert with the thumbprint
            if (certCollection.Count > 0)
            {
                cert = certCollection[0];
                // Use certificate
                Console.WriteLine(cert.FriendlyName);
            }
            certStore.Close();
            return cert;
        }
    }

JSON Formatting Show File Size

On 03/05/2023

Show File size in kilo bytes in sharepoint list

 


{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "attributes": {
  },
  "style": {
    "flex-directon": "row",
    "justify-content": "left",
    "align-items": "center",
    "flex-wrap": "nowrap"
  },
  "children": [
    {
      "elmType": "span",
      "txtContent": "=[$File_x0020_Size]/1000 + ' kbytes'"
    }
  ]
}

 

PowerAutomate history Unexpected Error Unable To Fetch

On 21/04/2023

PowerAutomate history - Unexpected error. Unable to fetch

WfhistoryerrorI my case to solve this issue, i press "ctrl + R" then "ctrl + F5", then i should wait a few minutes before opening les boxes and it's ok

Wfhistoryerror2If this error persists, you can download, full history in a csv file that you can analize, yes you need to rean JSON code and Sharepoint http requests

in your flow error message copy le run ID to seek it in your csv file in url

https://make.powerautomate.com/environments/Default-d035406542/solutions/b03b5e55-c8d9-ed11-a7c7-000d3a2726a5/flows/21fb6dc1-0412-3540684-f0a6881c120f/runs/08585210145218379217946437495CU195Csvflow

JSON Formatting Is Member of Group

On 11/04/2023

show button if user if member of a sharepoint group

Compaire now + 30 days

@now>[$testDate]+2592000000
{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "style": {
    "flex-directon": "row",
    "justify-content": "left",
    "align-items": "center",
    "flex-wrap": "nowrap"
  },
  "children": [
    {
      "elmType": "div",
      "style": {
        "display": "=if(([$ItemStatus] == 'Pending' || [$ItemStatus] == '') && ([$PermMask]=='0x1b03c5b1bff' || [$PermMask]=='0x7ffffffffffbffff'), 'inherit','none')",
        "flex-directon": "row",
        "justify-content": "left",
        "align-items": "center",
        "flex-wrap": "wrap"
      },
      "children": [
        {
          "elmType": "button",
          "customRowAction": {
            "action": "setValue",
            "actionInput": {
              "ItemStatus": "Approved",
              "ApprovalActionBy": "@me"
            }
          },
          "attributes": {
            "class": "ms-fontColor-themePrimary ms-fontColor-themeDarker--hover"
          },
          "style": {
            "border": "none",
			"display": "=if([$PermMask]>='0x1b03c5b1bff', 'inherit','none')",
            "background-color": "transparent",
            "cursor": "pointer",
            "display": "flex",
            "flex-directon": "row",
            "justify-content": "left",
            "align-items": "center",
            "flex-wrap": "wrap"
          },
          "children": [
            {
              "elmType": "span",
              "attributes": {
                "iconName": "SkypeCircleCheck"
              },
              "style": {
                "padding": "4px"
              }
            },
            {
              "elmType": "span",
              "txtContent": "Approve",
              "style": {
                "word-break": "keep-all"
              }
            }
          ]
        },
        {
          "elmType": "button",
          "customRowAction": {
            "action": "setValue",
            "actionInput": {
              "ItemStatus": "Rejected",
              "ApprovalActionBy": "@me"
            }
          },
          "attributes": {
            "class": "ms-fontColor-themePrimary ms-fontColor-themeDarker--hover"
          },
          "style": {
            "border": "none",
			"display": "=if(([$ItemStatus] == 'Pending' || [$ItemStatus] == ''), 'inherit','none')",
            "background-color": "transparent",
            "cursor": "pointer",
            "display": "flex",
            "flex-directon": "row",
            "justify-content": "left",
            "align-items": "center",
            "flex-wrap": "wrap"
          },
          "children": [
            {
              "elmType": "span",
              "attributes": {
                "iconName": "Blocked"
              },
              "style": {
                "padding": "4px"
              }
            },
            {
              "elmType": "span",
              "txtContent": "Reject",
              "style": {
                "word-break": "keep-all"
              }
            }
          ]
        }
      ]
    },
    {
      "elmType": "div",
      "children": [
        {
          "elmType": "span",
          "txtContent": "='This item is ' + toLowerCase([$ItemStatus])",
          "style": {
            "display": "=if([$ItemStatus] == 'Pending' ||[$ItemStatus] == '' , 'none','inherit')",
            "padding-left": "5px",
            "word-break": "keep-all"
          }
        }
      ]
    }
  ]
}

PowerShell List Big Files

On 07/04/2023

Get Sharepoint all Big files with powershell and Sharepoint Search

 


Import-Module PnP.PowerShell
# Connect-PnPOnline -Url $url -UseWebLogin
$select = "Title,SiteTitle,SPSiteURL,SPWebUrl,ViewsLifeTimeUniqueUsers,ViewsLifeTime,language,Created,Size,Path,LastModifiedTime,ViewsLastMonths3,ViewsLastMonths3Unique,LastAnalyticsUpdateTime,ViewerCount"


$sortlist = @{Size = 'descending' }
$siteName = "serviceanimationreseauFPFSITW"

$search = Submit-PnPSearchQuery -Query "ContentTypeId:0x0101*" -MaxResults 500 -SelectProperties $select -TrimDuplicates $true -SortList $sortlist -StartRow 0 
$ret = $search.ResultRows.Count
$count = 0
$stop = $false
$itemsToSave = @();
while ($ret -eq 500 -and -not $stop) {
  # Disconnect-PnPOnline

  #  -MaxResults 10
  $lastSize = 0
  foreach ($row in $search.ResultRows) {


    $mo = 0;
    [Int64]::TryParse($row["Size"], [ref] $mo ) | Out-Null
    $nice = '{0:N0}' -f $mo
    $data = [PSCustomObject]@{
      "Title"                    = $row["Title"]
      "Size"                     = $row["Size"]
      "SizeMo"                   = '{0:N0}' -f ($mo / 1000000)
      "NiceSize"                 = $nice
      "SiteTitle"                = $row["SiteTitle"]
      "ViewsLifeTimeUniqueUsers" = $row["ViewsLifeTimeUniqueUsers"]
      "ViewsLifeTime"            = $row["ViewsLifeTime"]
      "language"                 = $row["language"]
      "Created"                  = $row["Created"]
      "LastModifiedTime"         = $row["LastModifiedTime"]
      "ViewsLastMonths3"         = $row["ViewsLastMonths3"]
      "ViewsLastMonths3Unique"   = $row["ViewsLastMonths3Unique"]
      "LastAnalyticsUpdateTime"  = $row["LastAnalyticsUpdateTime"]
      "ViewerCount"              = $row["ViewerCount"]
      "Path"                     = $row["Path"]
      "SPSiteURL"                = $row["SPSiteURL"]
      "SPWebUrl"                 = $row["SPWebUrl"]
    }
    $lastSize = $data.Size
    if ([int64]::Parse($data.Size) -lt 10000000) {
      $stop = $true;
      break;
    }
    $itemsToSave += $data
  }
  $count += 500
  $search = Submit-PnPSearchQuery -Query "ContentTypeId:0x0101*" -MaxResults 500 -SelectProperties $select -TrimDuplicates $true -SortList $sortlist -StartRow $count
  $ret = $search.ResultRows.Count
  Write-Host "$($itemsToSave.Count) $($count) $($lastSize)"
 
}


$itemsToSave | Export-Csv -Path "AllSites_8.csv" -Encodin:UTF8 -NoTypeInformation -Delimiter ";"
Disconnect-PnPOnline

Get SharePoint Audit Logs

On 05/04/2023

Create certification for azure app

    
        # Create certificate
        $mycert = New-SelfSignedCertificate -DnsName "myCertificate.org" -CertStoreLocation "cert:\CurrentUser\My" -NotAfter (Get-Date).AddYears(1) -KeySpec KeyExchange
        
        $mypwd = ConvertTo-SecureString -String "myCertificatePsw" -Force -AsPlainText
        # Export certificate to .pfx file
        $mycert | Export-PfxCertificate -FilePath myCertificate.pfx -Password $mypwd
        
        # Export certificate to .cer file
        $mycert | Export-Certificate -FilePath myCertificate.cer  
    

Connect to site

    
        $url = "https://m365x6422vvvvd.sharepoint.com/";
        $appId = "868d7a0c-a3dc-45af-b4a7-f72a70f61a60";
        $thumbprint = "A17177BB0E8A465F6AD08B0CEAE2F369C46D6481";
        $tenantId = "3533ab30-c2f0-48fd-b4c5-f5dc6ca77ec3"

        Connect-PnPOnline  -Url $url -Tenant $tenantId -Thumbprint $thumbprint -ClientId $appId
    

Export audit

premission required

Office 365 Management APIs (3) :: ActivityFeed.Read

possible filters

  • FilePreviewed
  • FileAccessed
  • SignInEvent
  • FileModifiedExtended
  • FileUploaded
  • PageViewed
  • PagePrefetched
  • FileCheckedIn
  • FileModified
  • FolderCreated
  • ListUpdated
  • ListViewed
    
        $ele = Get-PnPUnifiedAuditLog -ContentType SharePoint -StartTime (Get-Date).AddDays(-2) -EndTime (Get-Date).AddDays(-1)

        $ele = Get-PnPUnifiedAuditLog -ContentType SharePoint

        $ele = Get-PnPUnifiedAuditLog -ContentType SharePoint | Where-Object {$_.Operation -eq "PageViewed"} | Select-Object CreationTime,Operation,Workload,UserId,ObjectId,SourceFileName,SiteUrl,SourceFileExtension,SourceRelativeUrl

        $ele | Export-Csv -Path "Audit_3.csv" -Encodin:UTF8 -NoTypeInformation -Delimiter ";"
    

with sharepoint search request

    
        kqlQuery = "ContentTypeId:0x0101009D1CB255DA76424F860D91F20E6C4118*";//news
        kqlQuery = "ContentTypeId:0x0101* language=fr ViewsLastMonths3=0";//documents
        kqlQuery = "ContentTypeId:0x0101* ViewsLifeTime=0";
        var seletvvv = "Title,ViewsLifeTimeUniqueUsers,ViewsLifeTime,language,Created,Size,Path,LastModifiedTime,ViewsLastMonths3,ViewsLastMonths3Unique,LastAnalyticsUpdateTime";

        &sortlist='Size:descending'
        &sortlist='ViewsLifeTime:descending'
    

Power Automate add line to Excel

On 29/03/2023

Copy an Excel file from a template with an Sharepoint Http request

Createfolder 1

 


/_api/web/getFileByServerRelativeUrl('/sites/test@{variables('TemplateFileName')}')/copyTo(strNewUrl='/sites/test@{variables('ExportFolder')}/@{variables('ExportFileName')}',bOverWrite=true)

 

Header

 


{
  "Accept": "application/json; odata=verbose",
  "content-type": "application/json; odata=verbose",
  "X-HTTP-Method": "PATCH",
  "If-Match": "*"
}

Create An Excel Table

Excel createtable

Add your Datas in an Excel line

Excel addlineLine : 

 


@{items('Appliquer_à_chacun_2')}