Extract Data from Excel using powershell

----

1. This script opens an Excel file and reads data from a sheet named Sheet. 

2. Replace "SELECT * FROM [Sheet1$]" with the query that suits your needs.

3. Also, please ensure that the Microsoft.ACE.OLEDB.12.0 provider is installed on your machine, as it’s required to read data from Excel1.


```sh

#Read data from Excel
$cnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=YES';Data Source=c:temptest.xlsx;Mode=Share Deny Write;Jet OLEDB:Engine Type=37;"
$cn = New-Object System.Data.OleDb.OleDbConnection $cnStr
$cn.Open()
$cmd = $cn.CreateCommand()
$cmd.CommandText = "SELECT * FROM [Sheet1$]"
$rdr = $cmd.ExecuteReader()
$dt = new-object System.Data.DataTable
$dt.Load($rdr)
$dt | Out-GridView


```
Please note that this script reads data from Excel using SQL,but it doesn’t extract SQL queries stored in Excel. 
If you have SQL queries stored in cells of an Excel sheet and you want to extract them, you would need to adjust the script to select those specific cells.

Get Team chat details using Microsoft.Graph

----
1. Install-Module Microsoft.Graph
2. Connect MicrosoftGraph API  Module
3. Get chat id using Get-MgChat commandlet
4. Get Chat member for particular chat id using Get-MgChatMember
5. Get chat messages\Chat history for particular chat using Get-MgChatMessage
```sh
Install-Module Microsoft.Graph
Import-Module Microsoft.Graph

Connect-MgGraph

<# "Get-MgChat" ,"Get-MgChatMember","Get-MgChatMessage" | 
& {Process{Find-mggraphcommand Get-MgChat|select permissions|
&{Process{Connect-MgGraph -Scopes $_}} }} #>

$displayName="< Display Name>"
$user=Get-MgUser  -Filter "DisplayName eq '$displayName'"

Get-MgChat  | select Topic,ChatType,id,WebUrl

$chatID=$(Get-MgChat  | select id -First 1).id


#Get all Chat members for chat 
Get-MgChatMember -ChatId $chatID | select AdditionalProperties).AdditionalProperties

#Get ACS chat members
(Get-MgChatMember -ChatId $chatID |Where DisplayName -eq raz| select AdditionalProperties).AdditionalProperties

#Get AAD chat members
(Get-MgChatMember -ChatId $chatID |Where DisplayName -eq 'razesh'| select AdditionalProperties).AdditionalProperties

#Get chat messages for chat id

Get-MgChatMessage -ChatId $chatID |& {Process{ if ($($_.From.User.DisplayName)) {Write-host "From :$($_.From.User.DisplayName)" ;"Message:$($_.body.Content)";  Write-Host ""}}}

```

Invoke Microsoft Graph API endpoint using accesstoken in az cli

----

To get an access token for Graph API call, you can follow the steps below:


Go to the Azure portal and sign in to your account.
Navigate to the Azure Active Directory resource that you want to use.
1. Click on the App registrations tab.
2. Select the app that you want to use.
3. Click on the Certificates and  secrets tab.
4. Click on the New client secret button.
5. Enter a description for the client secret and select an expiration date.
6. Click on the Add button.
7. Copy the value of the client secret and store it securely.
8  Use the copied client secret value to authenticate your requests.
9. You can use the following code snippet in PowerShell to get an access token:


```sh
$tenantId = "< your-tenant-id >"
$appId = "<your-app-id >"
$clientSecret = "< your-client-secret >"
$resource = "https://graph.microsoft.com"
$body = @{grant_type="client_credentials";client_id="$appId";client_secret="$clientSecret";resource="$resource"}
$oauthUrl = "https://login.microsoftonline.com/$tenantId/oauth2/token"
$oauth = Invoke-RestMethod -Method Post -Uri $oauthUrl -Body $body
$accessToken = $oauth.access_token

# Note: Replace < your-tenant-id >, < your-app-id >, and < your-client-secret  > with your own values.
```

----

Example 1: 


```sh
$tenantId = "< your-tenant-id >"
$appId = "<your-app-id >"
$clientSecret = "< your-client-secret >"
$resource = "https://graph.microsoft.com"
$body = @{grant_type="client_credentials";client_id="$appId";client_secret="$clientSecret";resource="$resource"}
$oauthUrl = "https://login.microsoftonline.com/$tenantId/oauth2/token"
$oauth = Invoke-RestMethod -Method Post -Uri $oauthUrl -Body $body
$accessToken = $oauth.access_token

$headers = @{
    "Authorization" = "Bearer $accessToken"
}
$response=Invoke-RestMethod -Uri "https://graph.microsoft.com/v1.0/users" -Headers $headers 
$response.value  | select id , displayname

# Note: Replace < your-tenant-id >, < your-app-id >, and < your-client-secret  > with your own values.
# Note: Need User.Read.All, User.ReadWrite.All, Directory.Read.All, Directory.ReadWrite.All Permissions to access MS graph users API endpoint
```


Example 2:

```sh
$tenantId = "< your-tenant-id >"
$appId = "<your-app-id >"
$clientSecret = "< your-client-secret >"
$resource = "https://graph.microsoft.com"
$body = @{grant_type="client_credentials";client_id="$appId";client_secret="$clientSecret";resource="$resource"}
$oauthUrl = "https://login.microsoftonline.com/$tenantId/oauth2/token"
$oauth = Invoke-RestMethod -Method Post -Uri $oauthUrl -Body $body
$accessToken = $oauth.access_token

$headers = @{
    "Authorization" = "Bearer $accessToken"
}
$response=Invoke-RestMethod -Uri "https://graph.microsoft.com/v1.0/users" -Headers $headers 
$response. value  | select id , displayname

(Invoke-RestMethod -Uri "https://graph.microsoft.com/v1.0/groups?$filter=resourceProvisioningOptions/Any (x:x eq 'Team')" -Headers $headers).value |select id , displayname , mailNickName , mail


# Note: Replace < your-tenant-id >, < your-app-id >, and < your-client-secret  > with your own values.
# Note: Need 	Group.Read.All, Group.ReadWrite.All, Directory.Read.All, Directory.ReadWrite.All  Permissions to access MS graph groups API endpoint

```



Example 3:

```sh
$tenantId = "< your-tenant-id >"
$appId = "<your-app-id >"
$clientSecret = "< your-client-secret >"
$displayName ="< displayName>"
$resource = "https://graph.microsoft.com"
$body = @{grant_type="client_credentials";client_id="$appId";client_secret="$clientSecret";resource="$resource"}
$oauthUrl = "https://login.microsoftonline.com/$tenantId/oauth2/token"
$oauth = Invoke-RestMethod -Method Post -Uri $oauthUrl -Body $body
$accessToken = $oauth.access_token

$headers = @{
    "Authorization" = "Bearer $accessToken"
     "Content-Type"  = "application/json"
}

$filter='$filter'
$uri = "https://graph.microsoft.com/v1.0/users?$filter=displayName eq '$displayName' "
$response=Invoke-RestMethod -Uri $uri -Headers $headers 
$response. value  | select id , displayname

# Note: Replace < your-tenant-id >, < your-app-id >, and < your-client-secret  > with your own values.
# Note: Need User.Read.All, User.ReadWrite.All, Directory.Read.All, Directory.ReadWrite.All Permissions to access MS graph users API endpoint

```

How to invoke Azure Management API with Access token using AZ CLI

#### list of steps to invoke Azure Management API with Azure access token using AZ CLI.

1. log into Azure  using Az CLI 
2. Get access token using account get-access-token 
3. add bearer accesstoken as authorization Header
4. Invoke Azure Managment API 

```sh

az login
$subId=(az account list --query "[?isDefault].id" )|ConvertFrom-Json
$accesstoken =  [string](az account get-access-token | ConvertFrom-Json).accessToken
$headers = @{ Authorization = "Bearer $accesstoken" }
$url="< Azure Management API >"
$r= Invoke-WebRequest  -Uri  $url -Headers $headers
If ($r.StatusCode -eq 200 ) {($r.Content |ConvertFrom-Json).value}

# Replace  < Azure Management API > with your values  
```

Example: 

```sh
az login
$subId=(az account list --query "[?isDefault].id" )|ConvertFrom-Json
$accesstoken =  [string](az account get-access-token | ConvertFrom-Json).accessToken
$headers = @{ Authorization = "Bearer $accesstoken" }
$url="https://management.azure.com/subscriptions/$subId/providers/Microsoft.Compute/locations/eastus/runCommands?api-version=2022-11-01"
$r= Invoke-WebRequest  -Uri  $url -Headers $headers
If ($r.StatusCode -eq 200 ) {($r.Content |ConvertFrom-Json).value}
```
________

App Registration in Azure

----

#### Register App using AZ Cli 

1. Login into azure using az cli 
2. Create azure active directory service principle usign AZ CLI

```sh

$AppName="<AppName >"
az login
$subId=az account list --query "[?isDefault].id" | ConvertFrom-Json
az account set --subscription $subid
az ad sp create-for-rbac -n $AppName
```
-----

#### Register App using AZ Portal



1. Go to the Azure portal and sign in to your account.
2. Navigate to the Azure Active Directory resource that you want to use.
3. Click on the App registrations tab and click on New registration
4. Enter User-facing Display name  and click on Register

Create Azure Communication identity and token using az cli

### Add the Azure Communication Services extension for Azure CLI by using the az extension command.

1. Add communication extension in az cli  (one time activity)
2. Connect to Azure using Az Cli
3. Create Identity using az communication user-identity 
4. Issue Identity Token for specific scope (voip, chat)


```sh 
az extension add --name communication
$acsName="< acs-Name >"
$resouceGroup="< Resource Group Name >"

$connection=az communication list-key --name $acsName --resource-group $resouceGroup  --query "primaryConnectionString"

$rawId=az communication user-identity user create --connection-string $connection  --query "rawId"

az communication identity token issue --scope chat voip  --user $rawId --connection-string $connection  --query "token"


Note:

#$rawId=az communication identity user create --connection-string $connection  --query "rawId"

#This command is implicitly deprecated because command group 'communication identity token' is deprecated and will be removed in a future release. Use communication user-identity token' instead.
```
___

Example :

```sh 

az extension add --name communication

az login

$acsName="aztds-acs-poc0821"

$resouceGroup="rg-aztds-acs-poc"

$connection=az communication list-key --name $acsName --resource-group $resouceGroup  --query "primaryConnectionString"

$rawId=az communication user-identity user create --connection-string $connection  --query "rawId"

az communication identity token issue --scope chat voip  --user $rawId --connection-string $connection  --query "token"

```

Get Teams User's Availability using Microsoft Graph API

Get Teams User's Availability using Microsoft Graph API through Invoke-RestMethod 


```sh
# Import the required modules

Install-Module -Name AzureAD
Install-Module MicrosoftTeams


Import-Module AzureAD
Import-Module MicrosoftTeams

	# Connect to Azure AD
Connect-AzureAD

	# Get an access token for the Graph API
$accessToken = (Get-AzureADToken -Resource "https://graph.microsoft.com").AccessToken

	# Set the Graph API endpoint URL
$graphApiUrl = "https://graph.microsoft.com/v1.0/users//presence"

	# Set the headers for the request
$headers = @{
    "Authorization" = "Bearer $accessToken"
}

	# Send the request to the Graph API and get the response
$response = Invoke-RestMethod -Method Get -Uri $graphApiUrl -Headers $headers

	# Output the presence status of the user
$response.availability
```