#Install Azure AD Module & Import the SQL Module for PowerShell
install-module AzureAD
Import-Module "C:\Scheduled-Tasks\SQL_Module\SqlServer.psm1"

​

#Auto login Data & Connect to Azure 
$AppID = 'b7bbcf98-f84e-4xxxxxx27c667105'
$TenantID = '8d151exxxxxxx-e5e020c04470'
$Thumbprint = 'D5EA77Axxxxx5032AF04E022C71F8'
Connect-AzureAD -TenantId $tenantid -ApplicationId $appid -CertificateThumbprint $Thumbprint

​

#Gather Licnese Data From SQL to Translate SKU to Name   
$Query= "SELECT * FROM [Azure_Data].[dbo].[Office365_License]" 
$SQL_LicenseInfo=Invoke-Sqlcmd  -ConnectionString "Data Source=Media-Center\SQLExpress;Initial Catalog=Azure_Data; Integrated Security=True;" -Query "$Query"

​

#Gather Licnese Data From SQL to Translate SKU to Name   
$Query= "SELECT * FROM [Azure_Data].[dbo].[Office365_License]" 
$SQL_LicenseInfo=Invoke-Sqlcmd  -ConnectionString "Data Source=Media-Center\SQLExpress;Initial Catalog=Azure_Data; Integrated Security=True;" -Query "$Query"

​

#Gather Licnese Data From SQL to Translate SKU to Name   
$DeleteQuery= "DELETE FROM [dbo].[Azure_Licenses]" 
Invoke-Sqlcmd  -ConnectionString "Data Source=Media-Center\SQLExpress;Initial Catalog=Azure_Data; Integrated Security=True;" -Query "$DeleteQuery"

​

#Gather All SQL Users  
$Query= "SELECT ObjectID FROM [Azure_Data].[dbo].[Azure_User]" 
$SQL_ObjectIDs=Invoke-Sqlcmd  -ConnectionString "Data Source=Media-Center\SQLExpress;Initial Catalog=Azure_Data; Integrated Security=True;" -Query "$Query"

#Build Usable Array for SQL Data (System.Data.DataRow)
$SQL_Array=@()
Foreach($SQLItem in $SQL_ObjectIDs){
    $SQL_Array+=$SQLItem.Item(0);
}

#A) Loop Throught Each ObjectID to PUll Specfic Items from Azure thats in SQL
Foreach($User in $SQL_Array){
    Try{
        $AccountID=$Null 
        #Checks to see if can pull account by SQL ObjectID Match 
        $AccountID=Get-AzureADUser | Where{$_.ObjectID -eq $User}
        #B) Check to Assure Account was Found
        If($AccountID.objectID -eq $User){
            #C) Loop through each SKU to Insert to SQL 
            Foreach($SKU in $AccountID.Assignedlicenses.skuid){                
                #Match SKU to Name                 
                $MyVarx=($SQL_LicenseInfo | Where{$_.License_ID -eq $SKU} | select -ExpandProperty License_Name )
                #Build SQL Statement and Execute 
                $MyUPN=$AccountID.UserPrincipalName
                $MyObjectID=$AccountID.ObjectId
                $MyInserts="INSERT INTO [dbo].[Azure_Licenses]([UserObjectID],[UserPrincipalName],[License_Name])VALUES('$MyObjectID','$MyUPN','$MyVarx')"
                Invoke-Sqlcmd  -ConnectionString "Data Source=Media-Center\SQLExpress;Initial Catalog=Azure_Data; Integrated Security=True;" -Query "$MyInserts"
            }#C) Closed
        }#B) Closed
    }Catch{}
}# A) Closed