Powershell to get DirectAccess connection history.

This is a simple powershell to get data from the DirectAccess database. It reguired some serious Bing’ing (and google) to get the time field. You will have to configure reporting database in DirectAccess config. I used windows internal database.

$server="\\.\pipe\MICROSOFT##WID\tsql\query"
$database="RaAcctDb"
#$table="connectiontable"
$table="sessiontable"
#$table="endpointsaccessedtable"
#$table="serverendpointtable"

$cs="server=$server;database=$database;Integrated Security=True;"
$connection=New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString=$cs
$connection.Open()

#$query="Select * from $table"
#$query="Select * from $database.INFORMATION_SCHEMA.TABLES"
$query="declare @start bigint=131277336299720000;select dateadd(mi,datediff(mi,getutcdate(),getdate()),([sessionstarttime]/864000000000.0-109207)) AS DATO,* from sessiontable join connectiontable on sessiontable.connectionid=connectiontable.connectionid where sessionstarttime >=@start"

$command=$connection.CreateCommand()
$command.CommandText=$query
$result=$command.ExecuteReader()

$resulttable=New-Object System.Data.DataTable
$resulttable.Load($result)
$resulttable | Out-GridView
$connection.Close()
 

Use Powershell to get LeakedCredentials from Azure using Graph

Leaked credentials listed from Azure using powershell and Microsoft Graph 
We need one Azure AD Premium X license to get this log.

Would it be nice to list all leakedcredentials using powershell?(or riskysignins or identiyriskevents). All of this could be achieved using powershell and REST api at Microsoft Graph. I have a scheduled task running to get this reports. Using a appilcation in Azure. All credentials are stored in SecretServer. First we need an Application Registration in Azure.

Application Registration list

The registered application. The home page URL can be any url, it is not used.

After we have created the AppReg. Add a password, app key. Combined with the application id this is our username and password.

Now it is time to give this app the required permissions from microsoft we can identify witch permissions are needed to run this query. https://developer.microsoft.com/en-us/graph/docs/api-reference/beta/api/leakedcredentialsriskevent_get 

Permission required.
Some of the permissions set in Azure.
Remeber to click “Grant Permissions” after they are added.

Next would be to set the enterprise application to “user assignment required” and “Enabled for users to sign-in.” also “Hide it from users.

Settings of the Enterprise application.

Now we are ready to start with our powershell script.

$loginURL="https://login.microsoft.com"
$resource="https://graph.microsoft.com"
$l_tenantdomain="<domain>.onmicrosoft.com"
$l_ClientID ="<APPID>"
$l_ClientSecret="<APP password Key>"
    $body= @{grant_type="client_credentials";
    resource=$resource;
    client_id=$l_ClientID;
    client_secret=$l_ClientSecret
}
$oauth=Invoke-RestMethod -Method Post -Uri $loginURL/$l_tenantdomain/oauth2/token?api-version=1.0 -Body $body
if ($oauth.access_token -ne $null)
 {
      $headerParams = @{'Authorization'="$($oauth.token_type) $($oauth.access_token)"
      }
 # https://developer.microsoft.com/en-us/graph/docs/api-reference/beta/api/leakedcredentialsriskevent_get
$url = "https://graph.microsoft.com/beta/leakedCredentialsRiskEvents"
$myReport = (Invoke-WebRequest -UseBasicParsing -Headers $headerParams -Uri $url)
} else {
Write-Host "ERROR: No Access Token"
} 
($myReport.Content | ConvertFrom-Json).value |where-object {$_.riskeventstatus -eq "active"} | ft risk<em>,user