AzureAD_Logins
/**
* @file AzureAD_Login_Report
* @reportoverview A summary report that shows the AzureAD login statistics over a period of time. The tables in the report are grouped
* by the following categories:
* - App Display Name
* - User Display Name
* - Operating System
* - Browser
* - Country
* - City
* - Unique Users
*/
/**
* Main method. This gets the account status dispositions in Azure AD in the time range from LavaDB that matches the conditions.
* The overall table is also broken down into individual tables for each disposition sorted by their most recent event.
*
* @param {string || int} from - The start of the time range. Default is past day
* @param {string || int} to - The end of the time range. Default is the past minute
*
* @returns {object} - Returns an object containing all the tables/metric/alert obtained from the queries
*/
function main({from="-1d@m", to="@m"}) {
let rangeFrom = new Time(from)
let rangeTo = new Time(to)
validateTimeRange(rangeFrom, rangeTo)
setEnv("from", from)
setEnv("to", to)
// initializes all the tables to be used
let usedSignApp = new Table()
let userFreq = new Table()
let usedOS = new Table()
let userBrowser = new Table()
let signInCountry = new Table()
let signInCity = new Table()
let signInEvents = new Table()
let interval = "1d"
// breaks the time down into 1 day intervals and gets the total number of sign ins by the specified field
for (let t = rangeFrom; t.Before(rangeTo); t = t.Add(interval)) {
let to = t.Add(interval).After(rangeTo) ? rangeTo : t.Add(interval)
let from = t
usedSignApp.Append(fetchAzureSignInCountBy(from, to, "appDisplayName"))
userFreq.Append(fetchAzureSignInCountBy(from, to, "userDisplayName"))
usedOS.Append(fetchAzureSignInByBrowserOS(from, to, "operatingSystem"))
userBrowser.Append(fetchAzureSignInByBrowserOS(from, to, "browser"))
signInCountry.Append(fetchAzureSignInByLocation(from, to, "countryOrRegion"))
signInCity.Append(fetchAzureSignInByLocation(from, to, "city"))
signInEvents.Append(fetchAzureSignInByUserPrincipalName(from, to))
}
// aggregates each table to get the total over all the time ranges and sorts them by the total
usedSignApp = getTotalByField(usedSignApp, "appDisplayName").Sort(25, "total")
userFreq = getTotalByField(userFreq, "userDisplayName").Sort(10, "total")
usedOS = getTotalByField(usedOS, "operatingSystem").Sort(25, "total")
userBrowser = getTotalByField(userBrowser, "browser").Sort(25, "total")
signInCountry = getTotalByField(signInCountry, "countryOrRegion").Sort(25, "total")
signInCity = getTotalByField(signInCity, "city").Sort(15, "total")
let userEmailFreq = getTotalByField(signInEvents, "userPrincipalName")
// gets the total number of unique user emails
let uniqueUserEmail = userEmailFreq.Aggregate(({userPrincipalName}) => {
return {
columns: {
dcount: {userPrincipalName}
}
}
})
// gets the latest sign in event for each user
let latestSignIns = signInEvents.Aggregate(({userPrincipalName, createdDateTime}) => {
return {
groupBy: {userPrincipalName},
columns: {
max: {createdDateTime},
}
}
})
latestSignIns.Join(signInEvents, ({userPrincipalName, createdDateTime}) => "inner")
return {
usedSignApp,
userFreq,
usedOS,
userBrowser,
signInCountry,
signInCity,
userEmailFreq,
uniqueUserEmail,
latestSignIns
}
}
/**
* Thie method is a helper method to validate the time range passed by the user.
*
* @param {Time} from - The start of the time range
* @param {Time} to - The end of the time range
*
* @returns {boolean} - Returns true if the time range is valid
*/
function validateTimeRange(from, to) {
// checks to see if the start of the time range is after the end of the time range
if (from.After(to)) {
throw new Error("rangeFrom must be less than rangeTo", "RangeError")
}
// checks to see if the time range is more than 2 months
if (to.After(from.Add("60d"))) {
throw new Error("total duration must not exceed 2 months", "RangeError")
}
return true
}
/**
* This is a helper function used to get the total number of sign ins by user principal name. The table also contains
* the user display name and the time of the sign in.
*
* @param {Time} from
* @param {Time} to
*
* @returns {Table} table - Returns a table containing the total number of sign ins by user principal name
*/
function fetchAzureSignInByUserPrincipalName(from, to) {
let env = {from, to}
let fplTemplate = `
search {from="{{.from}}", to="{{.to}}"} sContent("@event_type", "@azureSignIn") and sContent("@azureSignIn.status.errorCode", "0")
let {userDisplayName, userPrincipalName, appDisplayName, ipAddress, createdDateTime} = f("@azureSignIn")
let {operatingSystem, browser} = f("@azureSignIn.deviceDetail")
let {city, countryOrRegion} = f("@azureSignIn.location")
let {latitude, longitude} = f("@azureSignIn.location.geoCoordinates")
`
let table = fluencyLavadbFpl(template(fplTemplate, env))
return table
}
/**
* Helper function that gets the total number of sign ins by the specified field.
*
* @param {Time} from - The start of the time range
* @param {Time} to - The end of the time range
* @param {string} field - The field to be grouped by
*
* @returns {Table} table - Returns a table containing the total number of sign ins by the specified field
*/
function fetchAzureSignInCountBy(from, to, field) {
let env = {from, to, field}
let fplTemplate = `
search {from="{{.from}}", to="{{.to}}"} sContent("@event_type", "@azureSignIn") and sContent("@azureSignIn.status.errorCode", "0")
let {{.field}} = f("@azureSignIn.{{.field}}")
aggregate total=count() by {{.field}}
`
let table = fluencyLavadbFpl(template(fplTemplate, env))
return table
}
/**
* Helper function that gets the total number of sign ins by operating system or browser and groups them by the specified field.
*
* @param {Time} from - The start of the time range
* @param {Time} to - The end of the time range
* @param {string} field - The field to be grouped by (expecting operatingSystem or browser)
*
* @returns {Table} table - Returns a table containing the total number of sign ins by operating system/browser
*/
function fetchAzureSignInByBrowserOS(from, to, field) {
let env = {from, to, field}
let fplTemplate = `
search {from="{{.from}}", to="{{.to}}"} sContent("@event_type", "@azureSignIn") and sContent("@azureSignIn.status.errorCode", "0")
let {operatingSystem, browser} = f("@azureSignIn.deviceDetail")
aggregate total=count() by {{.field}}
`
let table = fluencyLavadbFpl(template(fplTemplate, env))
return table
}
/**
* Helper function that gets the total number of sign ins by the specified location field.
*
* @param {Time} from - The start of the time range
* @param {Time} to - The end of the time range
* @param {string} field - The location field to be grouped by (expecting countryOrRegion or city)
*
* @returns {Table} table - Returns a table containing the total number of sign ins by the specified location field
*/
function fetchAzureSignInByLocation(from, to, field) {
let env = {from, to, field}
let fplTemplate = `
search {from="{{.from}}", to="{{.to}}"} sContent("@event_type", "@azureSignIn") and sContent("@azureSignIn.status.errorCode", "0")
let {{.field}} = f("@azureSignIn.location.{{.field}}")
aggregate total=count() by {{.field}}
`
let table = fluencyLavadbFpl(template(fplTemplate, env))
return table
}
/**
* This helper function groups the table by the specified field and gets the total number of sign ins.
*
* @param {Table} table - The table to be aggregated
* @param {string} field - The field to be grouped by
*
* @returns {Table} - Returns an aggregated table grouped by the specified field with the total number of sign ins
*/
function getTotalByField(table, field) {
return table.Aggregate((obj)=>{
let fieldValue = obj[field]
let total = obj["total"]
return {
groupBy: {[field]: fieldValue},
columns: {
sum: {total}
}
}
})
}
Updated 9 months ago