#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-f8xxxxxxxx1667105'
$TenantID = '8d151ea2xxxxxxxe1c04470'
$Thumbprint = 'D5EA77ADxxxxxxx12C71F8'
Connect-AzureAD -TenantId $tenantid -ApplicationId $appid -CertificateThumbprint $Thumbprint

#-------------------------------------------------------------------------------------------------
#                 Compare Data to see what accounts are new and not in SQL
#-------------------------------------------------------------------------------------------------

#Gather All Azure AD Users
$Userdata=Get-AzureADUser | Select *

#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);
}

#Compare Values allow only new accounts into SQL 
$AccountID=Compare-Object -ReferenceObject $SQL_Array -DifferenceObject $Userdata.ObjectID | ? {$_.SideIndicator -eq '=>'} | Select -ExpandProperty inputobject

#Loop Through New Accounts to be Added by ObjectID
$NewUserData=@()
Foreach($Account in $AccountID){
    #Gather All User Data in Azure AD for Select ObjectID
    $NewUserData+=Get-AzureADUser | Select * | Where{$_.ObjectID -eq $AccountID}
}

#Loop Through and Insert into SQL 
Foreach($Item in $NewUserData){   

    #Variables 
    $UserPrincipalName = $Item.UserPrincipalName
    $DisplayName = $Item.DisplayName
    $AccountEnabled = $Item.AccountEnabled
    $ObjectId = $Item.ObjectId
    $JobTitle = $Item.JobTitle
    $Mobile = $Item.Mobile
    $TelephoneNumber = $Item.TelephoneNumber
    $GivenName = $Item.GivenName
    $Surname = $Surname

    #Default Template for Interting Data into SQL
    $MySQL_Insert="INSERT INTO [dbo].[Azure_User]
               ([UPN]
               ,[DisplayName]
               ,[AccountEnabled]
               ,[ObjectId]
               ,[JobTitle]
               ,[Mobile]
               ,[TelephoneNumber]
               ,[Surname]
               ,[GivenName])
         VALUES
               ('$UserPrincipalName',
                '$DisplayName',
                '$AccountEnabled',
                '$ObjectId',
                '$JobTitle',
                '$Mobile',
                '$TelephoneNumber',
                '$Surname',
                '$GivenName')"

    #Command to Execute and Insert to SQL 
    Invoke-Sqlcmd  -ConnectionString "Data Source=Media-Center\SQLExpress;Initial Catalog=Azure_Data; Integrated Security=True;" -Query $MySQL_Insert
    
}