MSTICPy 1.0 pre-release

  1. Implementing pip “extras” to split dependencies into optional chunks
  2. Settings editor and management tools
  3. User defaults — preload data providers and other components automatically when you initialize MSTICPy at the start of each notebook
  4. SQL to KQL translator
  5. Pivot functions updates


pip install msticpy==1.0.0rc3

MSTICPy Dependencies and “extras”

pip install msticpy[timeseries]
MSTICPy exception when using a component with a missing dependency. It tells you which “extra” to use with “pip install msticpy” so that the dependency is installed.
Example of an exception when trying to use a component with a missing dependency

Settings editor and settings management

MSTICPy settings editor showing configuration for a threat intelligence provider.
Adding settings for VirusTotal (btw that isn’t a real VirusTotal key!)
# set an environment variable pointing to your existing config file
# %env MSTICPYCONFIG=./msticpyconfig2.yaml
mpedit = MpConfigEdit()
MpConfig file utility lets you manipulate settings files. Buttons show several functions including Load file, save file, convert, validate, show Key Vault secrets and others.
MpConfigFile in interactive mode

User Defaults

Settings editor showing adding the Splunk data connector to the auto-load list.
The query providers auto-load editor
>>> msticpy.current_providers

{'qry_azsent': < at 0x21604110ac8>,
'qry_myworkspace': < at 0x216041459c8>,
'qry_cybersoc': < at 0x21660d41308>,
'qry_splunk': < at 0x21661127208>,
'qry_local': < at 0x216605a7c48>,
'ti_lookup': <msticpy.sectools.tilookup.TILookup at 0x216611c7908>,
'geoip': <msticpy.sectools.geoip.GeoLiteLookup at 0x21660659c88>,
'pivot': <msticpy.datamodel.pivot.Pivot at 0x216602d8e88>,
'az_data': < at 0x21668aaf708>,
'azs_api': < at 0x21603f42388>,
'nb': <module 'msticnb' from 'e:\\src\\msticnb\\msticnb\\'>}

SQL to KQL Translator

  • Where and Select
  • Joins and Unions
  • Sub-queries
  • Grouping
sql = """
SELECT Message
FROM apt29Host a
SELECT ProcessGuid
FROM apt29Host
WHERE Channel = "Microsoft-Windows-Sysmon/Operational"
AND EventID = 1
AND LOWER(ParentImage) RLIKE '.*partial_string.*'
AND LOWER(Image) LIKE '%cmd.exe'
) b
ON a.ParentProcessGuid = b.ProcessGuid
WHERE Channel = "Microsoft-Windows-Sysmon/Operational"
AND EventID = 1
AND LOWER(Image) LIKE '%powershell.exe'
table_map = {"apt29Host": "SecurityEvent", "MyTable": "SigninLogs"}
kql = sql_to_kql(sql, table_map)
| join kind=inner (SecurityEvent
| where Channel == 'Microsoft-Windows-Sysmon/Operational'
and EventID == 1
and tolower(ParentImage) matches regex '.*partial.string.*'
and tolower(Image) endswith 'cmd.exe'
| project ProcessGuid) on $left.ParentProcessGuid == $right.ProcessGuid
| where Channel == 'Microsoft-Windows-Sysmon/Operational'
and EventID == 1
and tolower(Image) endswith 'powershell.exe'
| project Message

Pivot Updates

  • Pivot browser — that shows you entities and their pivot functions along with the help documentation for each function. You can also search for pivot functions by keyword of anything in the name or docstring of a function.
Pivot function browser showing a list of entities. The Account entity is selected and shows a list of all pivot functions for that entity. Below these lists is a help drop down showing the parameters and usage of the highlighted function.
Pivot browser
  • Persistent pipelines — we’ve extended this feature to allow you to save multi-step pandas pipelines as yaml files and re-execute them with different input DataFrames. We’ve also added a couple of useful pandas accessor methods that you can use in your pipelines. As a reminder, an example of a pandas pipeline using pivot functions is shown below, along with the output.
# Lookup IPs at VT, column="IPAddress")
# Filter on high severity
.query("Severity == 'high'")
# lookup whois info for IPs, column="Ioc", join="left")
# display sample of intermediate results
.mp_pivot.display(title="TI High Severity IPs", cols=["Ioc", "Provider", "Reference"], head=5)
# Query IPs that have login attempts, ip_address_list="Ioc")
# Send the output of this to a plot
title="High Severity IPs with Logon attempts",
source_columns=["UserPrincipalName", "IPAddress", "ResultType", "ClientAppUsed", "UserAgent", "Location"],
Execution results of a pandas pivot pipeline
description: Pipeline 1 description
- name: get_logons
step_type: pivot
function: util.whois
entity: IpAddress
comment: Standard pivot function
column: IpAddress
join: inner
- name: disp_logons
step_type: pivot_display
comment: Pivot display
title: "The title"
- Computer
- Account
query: Computer.str.startswith('MSTICAlerts')
head: 10
- name: tee_logons
step_type: pivot_tee
comment: Pivot tee
var_name: var_df
clobber: True





This is the account of the Microsoft Threat Intelligence Center (MSTIC).

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Learning Chatbot from Scratch: Progressive use of Rasa Forms

First impressions on Alexa’s Interactive Adventure Game Tool

9 Reasons Why Orgzit Is Better Than Google Spreadsheets

Testing in CI/CD

Creating a Slack bot (Kotlin) and deploying it in Heroku

Everyone debugs.

Cloudcraft: create your infrastructure in a snap

Pera Wallet Lookback at 2022

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store


This is the account of the Microsoft Threat Intelligence Center (MSTIC).

More from Medium


Network Monitoring Meets Deep Learning

Writing a Data Provider for MSTICPy

The Expansion of the Universe