Drew Robinson's Blog

Azure, Powershell and Security things

Azure AD Reporting API, Download foreach user – sign-in status as CSV

#Draft, will be updating this with more text and images.

 

The following script will locate all users, then for each user it will dump out Azure AD reporting sign-in status in C:\temp\reporting\[thismonth[thisday]]

Example from lab, ehzure.com, I only have two users active, I’ll execute some user login activity at later time to provide a better screenshot

Header: signinDateTime,signinDateTimeInMillis,userDisplayName,userPrincipalName,userId,AppId,appDisplayName,ipAddress,loginStatus,deviceInformation,geoCoordinates,location,signinErrorCode,failureReason,mfaResult,mfaRequired,mfaAuthMethod,mfaAuthDetail,dataSource

CSV output screenshot:

 

CODE

 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
# Vars

$ClientID       = "[Your Client ID from Azure AD Applications]"            # client ID, reference https://docs.microsoft.com/en-us/azure/azure-resource-manager/resource-group-create-service-principal-portal      
$ClientSecret   = "[Your Client ID Secret from Azure AD Applications"      # client secret, reference https://docs.microsoft.com/en-us/azure/azure-resource-manager/resource-group-create-service-principal-portal      
$TenantDomain   = "[YourTenantDomain].onmicrosoft.com"                     # [Your Tenant domain].onmicrosoft.com
$LoginURL       = "https://login.microsoftonline.com"                      # OAuth Signin endpoint
$Resource       = "https://graph.windows.net"                              # Graph API to find all users 
$ReportingPath  = "C:\temp\reporting\"+ (get-date -Uformat %b%m)           # Folder location to dump reports, adds MonthDay, example C:\temp\reporting\Aug09
$ReportingUrl   = "https://graph.windows.net/" + $tenantdomain`
+"/users"+"?api-version=1.6"                                              # Create MS Graph API URL with your tenantdomain /users, this is for 'get all users'

# Test Reporting file path, (C:\temp\reporting\Aug09) if not exist -> create it
if(!(Test-Path -Path $ReportingPath )){
    New-Item -ItemType directory -Path $ReportingPath}

# Create Token
$Body       = @{grant_type="client_credentials";resource=$resource;client_id=$ClientID;client_secret=$ClientSecret}
$OAuth      = Invoke-RestMethod -Method Post -Uri $loginURL/$tenantdomain/oauth2/token?api-version=1.0 -Body $body

# Authorization Bearer
$Header = @{'Authorization'="$($OAuth.token_type) $($OAuth.access_token)"} 

# Get user list
$Users = ( (Invoke-WebRequest -UseBasicParsing -Headers $Header -Uri $reportingurl).content|ConvertFrom-Json).value |select userPrincipalName

# For Each user do x
ForEach ($User in $Users.userPrincipalname){
    # Prompt human which UPN we are dumping sign-in activity
    Write-host "Dumping User Azure AD Sign-in activity for " -ForegroundColor Yellow -NoNewline;Write-host $User
    # add ' ' to UPN for JSON request
    $UserUPN ="'"+$User+"'" 
    # create the reporting URL, signin activities and insert UPN, reference https://docs.microsoft.com/en-us/azure/active-directory/active-directory-reporting-api-getting-started-azure-portal
    $ReportingURL = 'https://graph.windows.net/' + $tenantdomain + '/activities/signinEvents?api-version=beta&'+'$filter=userPrincipalName+eq'
    $reportingurl = $ReportingUrl + "+$UserUpn"
    $UserReport =  ((Invoke-WebRequest -UseBasicParsing -Headers $Header -Uri $ReportingUrl).content|ConvertFrom-Json).value 
    
    #Output the UPN.csv to the reporting path on line 13
    $UserReportpath =$ReportingPath+"\"+$User+".csv"
    $UserReport |SORT signinDateTime | export-csv $UserReportpath -NoTypeInformation

    }

    #Sample script to regex extract ip addresses from all the CSV files in reportingpath 
    #note: you will have to adjust this or find better example, it most likely will pick up versions that are in the same 1.2.1.2 format. 
    $regex='(?<Address>((25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?))'
    $IPFileName = $ReportingPath+"\AA_FoundIPs.txt" 
    $files = LS $ReportingPath
    #Create an array to temp store the found IPs
    $IpArray=@()
    Foreach ($file in $files){
            $FoundIPs=(cat $file.fullname |select-string -Pattern $regex -AllMatches | % { $_.Matches }).value
            $IpArray+=$FoundIPs
        }
        
    #Sort IPs remove dups and output the array to a file in the same folder called AA_FoundIPs.txt
    $IPArray | Sort -Unique |out-file $IPFilename -Force