MSTICPy has a lot of functionality distributed across many classes and modules. However, there is no simple way to discover where these functions are and what types of data the function is relevant to.
Pivot functions bring this functionality together grouped around Entities.
Entities are representations real-world objects found commonly in CyberSec investigations. Some examples are:
MSTICPy has had entity classes from the very early days but, until now, these have only been used sporadically in the rest of the package.
The pivot functionality exposed operations relevant to a particular entity as methods of that entity. These operations could include:
The name comes from the common practice of Cyber investigators navigating between related entities. For example an entity/investigation chain might look like the following:
Step | Source | Operation | Target |
---|---|---|---|
1 | Alert | Review alert -> | Source IP(A) |
2 | Source IP(A) | Lookup TI -> | Related URLs |
Malware names | |||
3 | URL | Query web logs -> | Requesting hosts |
4 | Host | Query host logons -> | Accounts |
At each step there are one or more directions that you can take to follow the chain of related indicators of activity in a possible attack.
Bringing these functions into a few, well-known locations makes it easier to use MSTICPy to carry out this common pivoting pattern in Jupyter notebooks.
from msticpy.nbtools.nbinit import init_notebook
init_notebook(namespace=globals());
Processing imports.... Checking configuration.... No errors found. No warnings found. Setting notebook options....
The pivoting library depends on a number of data providers used in MSTICPy. These normally need to be loaded an initialized before starting the Pivot library.
This is mandatory for data query providers such as the AzureSentinel, Splunk or MDE data providers. These usually need initialization and authentication steps to load query definitions and connect to the service.
Note: you do not have to authenticate to the data provider before loading Pivot.
However, some providers are populated with additional queries only after connecting
to the service. These will not be added to the pivot functions unless you create a new Pivot object.
This is optional with providers such as Threat Intelligence (TILookup) and GeoIP. If you do not initialize these before starting Pivot they will be loaded with the defaults as specified in your msticpyconfig.yaml. If you want to use a specific configuration for any of these, you should load and configure them before starting Pivot.
az_provider = QueryProvider("AzureSentinel")
Please wait. Loading Kqlmagic extension...
You can either pass an explicit list of providers to Pivot or let it look for them in the notebook global namespace. In the latter case, the Pivot class will use the most recently-created instance of each that it finds.
You can add additional functions as pivot functions by creating a registration template and importing the function. Details of this are covered later in the document.
Because we haven't yet loaded the Pivot library nothing is listed.
entities.Host.get_pivot_list()
[]
You will usually see some output as provider libraries are loaded.
from msticpy.datamodel.pivot import Pivot
Pivot(namespace=globals())
Using Open PageRank. See https://www.domcop.com/openpagerank/what-is-openpagerank
<msticpy.datamodel.pivot.Pivot at 0x15899aa6188>
Note: Although you can assign the created Pivot object to a variable you normally don't need to do so.
You can access the current Pivot instance using the class attributePivot.current
Notice that TILookup was loaded even though we did not create an instance of TILookup beforehand.
Pivot.current.providers
{'AzureSentinel': <msticpy.data.data_providers.QueryProvider at 0x158999b9bc8>, 'TILookup': <msticpy.sectools.tilookup.TILookup at 0x15899a93348>}
print("Host pivot functions\n")
display(entities.Host.get_pivot_list())
print("\nIpAddress pivot functions\n")
display(entities.IpAddress.get_pivot_list())
Host pivot functions
['AzureSentinel.list_related_alerts', 'AzureSentinel.az_net_analytics', 'AzureSentinel.get_info_by_hostname', 'AzureSentinel.auditd_all', 'AzureSentinel.sudo_activity', 'AzureSentinel.cron_activity', 'AzureSentinel.user_group_activity', 'AzureSentinel.all_syslog', 'AzureSentinel.squid_activity', 'AzureSentinel.user_logon', 'AzureSentinel.list_logons_for_host', 'AzureSentinel.list_host_logon_failures', 'AzureSentinel.get_ips_for_host', 'AzureSentinel.get_heartbeat_for_host', 'AzureSentinel.list_azure_network_flows_by_host', 'AzureSentinel.list_host_events', 'AzureSentinel.list_host_events_by_id', 'AzureSentinel.list_other_events', 'AzureSentinel.get_host_logon', 'AzureSentinel.list_host_logons', 'AzureSentinel.list_all_logons_by_host', 'AzureSentinel.list_host_processes', 'AzureSentinel.get_process_tree', 'AzureSentinel.get_parent_process', 'AzureSentinel.list_processes_in_session', 'util.dns_validate_tld', 'util.dns_is_resolvable', 'util.dns_in_abuse_list']
IpAddress pivot functions
['AzureSentinel.list_alerts_for_ip', 'AzureSentinel.list_aad_signins_for_ip', 'AzureSentinel.list_azure_activity_for_ip', 'AzureSentinel.list_azure_network_flows_by_ip', 'AzureSentinel.list_activity_for_ip', 'AzureSentinel.get_info_by_ipaddress', 'AzureSentinel.list_logons_for_source_ip', 'AzureSentinel.get_host_for_ip', 'AzureSentinel.get_heartbeat_for_ip', 'AzureSentinel.list_indicators', 'AzureSentinel.list_indicators_by_ip', 'AzureSentinel.list_indicators_by_hash', 'AzureSentinel.list_indicators_by_filepath', 'AzureSentinel.list_indicators_by_domain', 'AzureSentinel.list_indicators_by_email', 'AzureSentinel.list_indicators_by_url', 'ti.lookup_ip', 'ti.lookup_ipv4', 'ti.lookup_ipv4_OTX', 'ti.lookup_ipv4_Tor', 'ti.lookup_ipv4_VirusTotal', 'ti.lookup_ipv4_XForce', 'ti.lookup_ipv6', 'ti.lookup_ipv6_OTX', 'util.whois', 'util.ip_type', 'util.geoloc_mm', 'util.geoloc_ips']
Data queries are grouped into a container with the name of the data provider to which they belong. E.g. AzureSentinel queries are in a container of that name, Spunk queries would be in a "Splunk" container.
TI lookups are put into a "ti" container
All other built-in functions are added to the "other" container.
The containers themselves are callable and will return a list of their contents. Containers are also iterable - each iteration returns a tuple (pair) of name/function values.
In notebooks/IPython you can also use tab completion to get to the right function.
entities.Host.AzureSentinel()
list_related_alerts function az_net_analytics function get_info_by_hostname function auditd_all function sudo_activity function cron_activity function user_group_activity function all_syslog function squid_activity function user_logon function list_logons_for_host function list_host_logon_failures function get_ips_for_host function get_heartbeat_for_host function list_azure_network_flows_by_host function list_host_events function list_host_events_by_id function list_other_events function get_host_logon function list_host_logons function list_all_logons_by_host function list_host_processes function get_process_tree function get_parent_process function list_processes_in_session function
[query for query, _ in entities.Host.AzureSentinel if "logon" in query]
['user_logon', 'list_logons_for_host', 'list_host_logon_failures', 'get_host_logon', 'list_host_logons', 'list_all_logons_by_host']
entities.Host
msticpy.datamodel.entities.host.Host
Pivot functions have flexible input types. They can be used with the following types of parameters:
Pivot functions normally return results as a dataframe (although some complex functions such as Notebooklets can return composite results objects containing multiple dataframes and other object types.
from msticpy.datamodel.entities import IpAddress, Host, Url, Account
print("List 'other' pivot functions for IpAddress\n")
IpAddress.util()
print()
print("-------------------------------\n")
print("Print help for a function - IpAddress.util.type\n")
IpAddress.util.ip_type?
List 'other' pivot functions for IpAddress whois function ip_type function geoloc_mm function geoloc_ips function ------------------------------- Print help for a function - IpAddress.util.type
Signature: IpAddress.util.ip_type(ip: str = None, ip_str: str = None) -> str Docstring: Validate value is an IP address and deteremine IPType category. (IPAddress category is e.g. Private/Public/Multicast). Parameters ---------- ip_str : str The string of the IP Address Returns ------- str Returns ip type string using ip address module File: e:\src\microsoft\msticpy\msticpy\sectools\ip_utils.py Type: function
If in doubt, use help(entity.container.func) or entity.container.func?
IpAddress.util.ip_type("10.1.1.1")
ip | result | |
---|---|---|
0 | 10.1.1.1 | Private |
display(IpAddress.util.ip_type("10.1.1.1"))
display(IpAddress.util.ip_type(ip_str="157.53.1.1"))
display(IpAddress.util.whois("157.53.1.1"))
display(IpAddress.util.geoloc_mm(value="157.53.1.1"))
ip | result | |
---|---|---|
0 | 10.1.1.1 | Private |
ip | result | |
---|---|---|
0 | 157.53.1.1 | Public |
asn | asn_cidr | asn_country_code | asn_date | asn_description | asn_registry | nets | nir | query | raw | raw_referral | referral | |
---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | NA | NA | US | 2015-04-01 | NA | arin | [{'cidr': '157.53.0.0/16', 'name': 'NETACTUATE-MDN-04', 'handle': 'NET-157-53-0-0-1', 'range': '... | None | 157.53.1.1 | None | None | None |
CountryCode | CountryName | State | City | Longitude | Latitude | Asn | edges | Type | AdditionalData | IpAddress | |
---|---|---|---|---|---|---|---|---|---|---|---|
0 | US | United States | None | None | -97.822 | 37.751 | None | {} | geolocation | {} | 157.53.1.1 |
Behind the scenes the Pivot api is using a mapping of entity attributes to supply the right value to the function parameter.
ip1 = IpAddress(Address="10.1.1.1")
ip2 = IpAddress(Address="157.53.1.1")
display(IpAddress.util.ip_type(ip1))
display(IpAddress.util.ip_type(ip2))
display(IpAddress.util.whois(ip2))
display(IpAddress.util.geoloc_mm(ip2))
ip | result | |
---|---|---|
0 | 10.1.1.1 | Private |
ip | result | |
---|---|---|
0 | 157.53.1.1 | Public |
asn | asn_cidr | asn_country_code | asn_date | asn_description | asn_registry | nets | nir | query | raw | raw_referral | referral | |
---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | NA | NA | US | 2015-04-01 | NA | arin | [{'cidr': '157.53.0.0/16', 'name': 'NETACTUATE-MDN-04', 'handle': 'NET-157-53-0-0-1', 'range': '... | None | 157.53.1.1 | None | None | None |
CountryCode | CountryName | State | City | Longitude | Latitude | Asn | edges | Type | AdditionalData | IpAddress | |
---|---|---|---|---|---|---|---|---|---|---|---|
0 | US | United States | None | None | -97.822 | 37.751 | None | {} | geolocation | {} | 157.53.1.1 |
Many of the underlying functions will accept either single values or collections (usually in DataFrames) of values as input. Even in cases where the underlying function does not accept iterables as parameters, the Pivot library will usually be able to iterate through each value and collate the results to hand you back a single dataframe.
Note: there are some exceptions to this - usually where the underlying function
is long-running or expensive and has opted not to accept iterated calls.
Notebooklets are an example of these.
Where the function has multiple parameters you can supply a mixture of iterables and single values.
You can also use multiple iterables for multiple parameters.
For example:
list_1 = [1, 2, 3, 4]
list_2 = ["a", "b", "c"]
entity.util.func(p1=list_1, p2=list_2)
The function will execute with the pairings (1, "a"), (2, "b") and (3, "c) - (4, _) will be ignored
from msticpy.datamodel import txt_df_magic
md("Use our magic function to convert pasted-in list to dataframe")
Use our magic function to convert pasted-in list to dataframe
%%txt2df --headers --name ip_df1
AllExtIPs
9, 172.217.15.99
10, 40.85.232.64
11, 20.38.98.100
12, 23.96.64.84
13, 65.55.44.108
14, 131.107.147.209
15, 10.0.3.4
16, 10.0.3.5
17, 13.82.152.48
AllExtIPs | |
---|---|
9 | 172.217.15.99 |
10 | 40.85.232.64 |
11 | 20.38.98.100 |
12 | 23.96.64.84 |
13 | 65.55.44.108 |
14 | 131.107.147.209 |
15 | 10.0.3.4 |
16 | 10.0.3.5 |
17 | 13.82.152.48 |
ip_list1 = ip_df1.AllExtIPs.values[-6:]
display(IpAddress.util.ip_type(ip_list1))
display(IpAddress.util.ip_type(ip_str=list(ip_list1)))
display(IpAddress.util.whois(value=tuple(ip_list1)))
display(IpAddress.util.geoloc_mm(ip_list1))
ip | result | |
---|---|---|
0 | 23.96.64.84 | Public |
1 | 65.55.44.108 | Public |
2 | 131.107.147.209 | Public |
3 | 10.0.3.4 | Private |
4 | 10.0.3.5 | Private |
5 | 13.82.152.48 | Public |
ip | result | |
---|---|---|
0 | 23.96.64.84 | Public |
1 | 65.55.44.108 | Public |
2 | 131.107.147.209 | Public |
3 | 10.0.3.4 | Private |
4 | 10.0.3.5 | Private |
5 | 13.82.152.48 | Public |
nir | asn_registry | asn | asn_cidr | asn_country_code | asn_date | asn_description | query | nets | raw | referral | raw_referral | |
---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | NaN | arin | 8075 | 23.96.0.0/14 | US | 2013-06-18 | MICROSOFT-CORP-MSN-AS-BLOCK, US | 23.96.64.84 | [{'cidr': '23.96.0.0/13', 'name': 'MSFT', 'handle': 'NET-23-96-0-0-1', 'range': '23.96.0.0 - 23.... | NaN | NaN | NaN |
1 | NaN | arin | 8075 | 65.52.0.0/14 | US | 2001-02-14 | MICROSOFT-CORP-MSN-AS-BLOCK, US | 65.55.44.108 | [{'cidr': '65.52.0.0/14', 'name': 'MICROSOFT-1BLK', 'handle': 'NET-65-52-0-0-1', 'range': '65.52... | NaN | NaN | NaN |
2 | NaN | arin | 3598 | 131.107.0.0/16 | US | 1988-11-11 | MICROSOFT-CORP-AS, US | 131.107.147.209 | [{'cidr': '131.107.0.0/16', 'name': 'MICROSOFT', 'handle': 'NET-131-107-0-0-1', 'range': '131.10... | NaN | NaN | NaN |
3 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
4 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
5 | NaN | arin | 8075 | 13.64.0.0/11 | US | 2015-03-26 | MICROSOFT-CORP-MSN-AS-BLOCK, US | 13.82.152.48 | [{'cidr': '13.104.0.0/14, 13.64.0.0/11, 13.96.0.0/13', 'name': 'MSFT', 'handle': 'NET-13-64-0-0-... | NaN | NaN | NaN |
CountryCode | CountryName | State | City | Longitude | Latitude | Asn | edges | Type | AdditionalData | IpAddress | |
---|---|---|---|---|---|---|---|---|---|---|---|
0 | US | United States | Virginia | Washington | -78.1539 | 38.7095 | None | {} | geolocation | {} | 23.96.64.84 |
1 | US | United States | Virginia | Boydton | -78.3750 | 36.6534 | None | {} | geolocation | {} | 65.55.44.108 |
2 | US | United States | Washington | Redmond | -122.1257 | 47.6722 | None | {} | geolocation | {} | 131.107.147.209 |
3 | US | United States | Virginia | Washington | -78.1539 | 38.7095 | None | {} | geolocation | {} | 13.82.152.48 |
Using a dataframe as input requires a slightly different syntax since you not only need to pass the dataframe as a parameter but also tell the function which column to use for input.
To specify the column to use, you can use the name of the parameter that the underlying function expects or one of these generic names:
Note these generic names are not shown in the function help
display(IpAddress.util.ip_type(data=ip_df1, input_col="AllExtIPs"))
display(IpAddress.util.ip_type(data=ip_df1, ip="AllExtIPs"))
display(IpAddress.util.whois(data=ip_df1, column="AllExtIPs"))
display(IpAddress.util.geoloc_mm(data=ip_df1, src_col="AllExtIPs"))
ip | result | |
---|---|---|
0 | 172.217.15.99 | Public |
1 | 40.85.232.64 | Public |
2 | 20.38.98.100 | Public |
3 | 23.96.64.84 | Public |
4 | 65.55.44.108 | Public |
5 | 131.107.147.209 | Public |
6 | 10.0.3.4 | Private |
7 | 10.0.3.5 | Private |
8 | 13.82.152.48 | Public |
ip | result | |
---|---|---|
0 | 172.217.15.99 | Public |
1 | 40.85.232.64 | Public |
2 | 20.38.98.100 | Public |
3 | 23.96.64.84 | Public |
4 | 65.55.44.108 | Public |
5 | 131.107.147.209 | Public |
6 | 10.0.3.4 | Private |
7 | 10.0.3.5 | Private |
8 | 13.82.152.48 | Public |
nir | asn_registry | asn | asn_cidr | asn_country_code | asn_date | asn_description | query | nets | raw | referral | raw_referral | |
---|---|---|---|---|---|---|---|---|---|---|---|---|
9 | NaN | arin | 15169 | 172.217.15.0/24 | US | 2012-04-16 | GOOGLE, US | 172.217.15.99 | [{'cidr': '172.217.0.0/16', 'name': 'GOOGLE', 'handle': 'NET-172-217-0-0-1', 'range': '172.217.0... | NaN | NaN | NaN |
10 | NaN | arin | 8075 | 40.80.0.0/12 | US | 2015-02-23 | MICROSOFT-CORP-MSN-AS-BLOCK, US | 40.85.232.64 | [{'cidr': '40.112.0.0/13, 40.76.0.0/14, 40.120.0.0/14, 40.80.0.0/12, 40.74.0.0/15, 40.125.0.0/17... | NaN | NaN | NaN |
11 | NaN | arin | 8075 | 20.36.0.0/14 | US | 2017-10-18 | MICROSOFT-CORP-MSN-AS-BLOCK, US | 20.38.98.100 | [{'cidr': '20.40.0.0/13, 20.33.0.0/16, 20.36.0.0/14, 20.64.0.0/10, 20.34.0.0/15, 20.128.0.0/16, ... | NaN | NaN | NaN |
12 | NaN | arin | 8075 | 23.96.0.0/14 | US | 2013-06-18 | MICROSOFT-CORP-MSN-AS-BLOCK, US | 23.96.64.84 | [{'cidr': '23.96.0.0/13', 'name': 'MSFT', 'handle': 'NET-23-96-0-0-1', 'range': '23.96.0.0 - 23.... | NaN | NaN | NaN |
13 | NaN | arin | 8075 | 65.52.0.0/14 | US | 2001-02-14 | MICROSOFT-CORP-MSN-AS-BLOCK, US | 65.55.44.108 | [{'cidr': '65.52.0.0/14', 'name': 'MICROSOFT-1BLK', 'handle': 'NET-65-52-0-0-1', 'range': '65.52... | NaN | NaN | NaN |
14 | NaN | arin | 3598 | 131.107.0.0/16 | US | 1988-11-11 | MICROSOFT-CORP-AS, US | 131.107.147.209 | [{'cidr': '131.107.0.0/16', 'name': 'MICROSOFT', 'handle': 'NET-131-107-0-0-1', 'range': '131.10... | NaN | NaN | NaN |
15 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
16 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
17 | NaN | arin | 8075 | 13.64.0.0/11 | US | 2015-03-26 | MICROSOFT-CORP-MSN-AS-BLOCK, US | 13.82.152.48 | [{'cidr': '13.104.0.0/14, 13.64.0.0/11, 13.96.0.0/13', 'name': 'MSFT', 'handle': 'NET-13-64-0-0-... | NaN | NaN | NaN |
CountryCode | CountryName | State | City | Longitude | Latitude | Asn | edges | Type | AdditionalData | IpAddress | |
---|---|---|---|---|---|---|---|---|---|---|---|
0 | US | United States | None | None | -97.8220 | 37.7510 | None | {} | geolocation | {} | 172.217.15.99 |
1 | CA | Canada | Ontario | Toronto | -79.4195 | 43.6644 | None | {} | geolocation | {} | 40.85.232.64 |
2 | US | United States | Virginia | Washington | -78.1539 | 38.7095 | None | {} | geolocation | {} | 20.38.98.100 |
3 | US | United States | Virginia | Washington | -78.1539 | 38.7095 | None | {} | geolocation | {} | 23.96.64.84 |
4 | US | United States | Virginia | Boydton | -78.3750 | 36.6534 | None | {} | geolocation | {} | 65.55.44.108 |
5 | US | United States | Washington | Redmond | -122.1257 | 47.6722 | None | {} | geolocation | {} | 131.107.147.209 |
6 | US | United States | Virginia | Washington | -78.1539 | 38.7095 | None | {} | geolocation | {} | 13.82.152.48 |
You might want to return a data set that is joined to your input set. To do that use the "join" parameter.
The value of join can be:
To preserve all rows from the input, use a "left" join. To keep only rows that have a valid result from the function use "inner" or "right"
Note while most functions only return a single output row for each input row
some return multiple rows. Be cautious using "outer" in these cases.
display(IpAddress.util.geoloc_mm(data=ip_df1, src_col="AllExtIPs", join="left"))
AllExtIPs | CountryCode | CountryName | State | City | Longitude | Latitude | Asn | edges | Type | AdditionalData | IpAddress | |
---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 172.217.15.99 | US | United States | None | None | -97.8220 | 37.7510 | None | {} | geolocation | {} | 172.217.15.99 |
1 | 40.85.232.64 | CA | Canada | Ontario | Toronto | -79.4195 | 43.6644 | None | {} | geolocation | {} | 40.85.232.64 |
2 | 20.38.98.100 | US | United States | Virginia | Washington | -78.1539 | 38.7095 | None | {} | geolocation | {} | 20.38.98.100 |
3 | 23.96.64.84 | US | United States | Virginia | Washington | -78.1539 | 38.7095 | None | {} | geolocation | {} | 23.96.64.84 |
4 | 65.55.44.108 | US | United States | Virginia | Boydton | -78.3750 | 36.6534 | None | {} | geolocation | {} | 65.55.44.108 |
5 | 131.107.147.209 | US | United States | Washington | Redmond | -122.1257 | 47.6722 | None | {} | geolocation | {} | 131.107.147.209 |
6 | 10.0.3.4 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
7 | 10.0.3.5 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
8 | 13.82.152.48 | US | United States | Virginia | Washington | -78.1539 | 38.7095 | None | {} | geolocation | {} | 13.82.152.48 |
A significant difference between the functions that we've seen so far and data query functions is that the latter do not accept generic parameter names.
When you use a named parameter in a data query pivot, you must specify the name that the query function is expecting. If in doubt, use "?" prefix to show the function help.
Example:
Host.AzureSentinel.list_host_events_by_id?
ws = WorkspaceConfig(workspace="CyberSecuritySoc")
az_provider.connect(ws.code_connect_str)
Use the edit_query_time
function to set/change the time range used by queries.
With no parameters it defaults to a period of [UtcNow - 1 day] to [UtcNow].
Or you can specify a timespan to use with the TimeSpan class.
help(Pivot.edit_query_time)
Help on function edit_query_time in module msticpy.datamodel.pivot: edit_query_time(self, timespan: Union[msticpy.common.timespan.TimeSpan, NoneType] = None) Display a QueryTime widget to get the timespan. Parameters ---------- timespan : Optional[TimeSpan], optional Pre-populate the timespan shown by the QueryTime editor, by default None
from msticpy.common.timespan import TimeSpan
ts = TimeSpan(start="2020-10-01", period="1d")
Pivot.current.edit_query_time(timespan=ts)
VBox(children=(HTML(value='<h4>Set time range for pivot functions.</h4>'), HBox(children=(DatePicker(value=dat…
You can also just set the timespan directly on the pivot object
Pivot.current.timespan = ts
Host.AzureSentinel()
list_related_alerts function az_net_analytics function get_info_by_hostname function auditd_all function sudo_activity function cron_activity function user_group_activity function all_syslog function squid_activity function user_logon function list_logons_for_host function list_host_logon_failures function get_ips_for_host function get_heartbeat_for_host function list_azure_network_flows_by_host function list_host_events function list_host_events_by_id function list_other_events function get_host_logon function list_host_logons function list_all_logons_by_host function list_host_processes function get_process_tree function get_parent_process function list_processes_in_session function
host = Host(HostName="VictimPc")
Host.AzureSentinel.get_heartbeat_for_host(host)
TenantId | SourceSystem | TimeGenerated | MG | ManagementGroupName | SourceComputerId | ComputerIP | Computer | Category | OSType | OSName | OSMajorVersion | OSMinorVersion | Version | SCAgentChannel | IsGatewayInstalled | RemoteIPLongitude | RemoteIPLatitude | RemoteIPCountry | SubscriptionId | ResourceGroup | ResourceProvider | Resource | ResourceId | ResourceType | ComputerEnvironment | Solutions | VMUUID | Type | _ResourceId | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 8ecf8077-cf51-4820-aadd-14040956f35d | OpsManager | 2020-12-03 18:01:11.167000+00:00 | 00000000-0000-0000-0000-000000000001 | AOI-8ecf8077-cf51-4820-aadd-14040956f35d | f6638b82-98a5-4542-8bec-6bc0977f793f | 13.89.108.248 | VictimPc.Contoso.Azure | Direct Agent | Windows | 10 | 0 | 10.20.18040.0 | Direct | False | -93.62 | 41.59 | United States | d1d8779d-38d7-4f06-91db-9cbc8de0176f | DefendTheFlag | Microsoft.Compute | VictimPc | /subscriptions/d1d8779d-38d7-4f06-91db-9cbc8de0176f/resourceGroups/DefendTheFlag/providers/Micro... | virtualMachines | Azure | "behaviorAnalyticsInsights", "security", "networkMonitoring", "dnsAnalytics", "securityCenterFre... | 14fa800d-e9b0-4dea-86ac-679933d59253 | Heartbeat | /subscriptions/d1d8779d-38d7-4f06-91db-9cbc8de0176f/resourcegroups/defendtheflag/providers/micro... |
Host.AzureSentinel.list_host_logons(host_name="VictimPc").head()
TenantId | Account | EventID | TimeGenerated | SourceComputerId | Computer | SubjectUserName | SubjectDomainName | SubjectUserSid | TargetUserName | TargetDomainName | TargetUserSid | TargetLogonId | LogonProcessName | LogonType | LogonTypeName | AuthenticationPackageName | Status | IpAddress | WorkstationName | TimeCreatedUtc | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 8ecf8077-cf51-4820-aadd-14040956f35d | NT AUTHORITY\SYSTEM | 4624 | 2020-10-01 22:39:36.987000+00:00 | f6638b82-98a5-4542-8bec-6bc0977f793f | VictimPc.Contoso.Azure | VictimPc$ | CONTOSO | S-1-5-18 | SYSTEM | NT AUTHORITY | S-1-5-18 | 0x3e7 | Advapi | 5 | 5 - Service | Negotiate | - | - | 2020-10-01 22:39:36.987000+00:00 | |
1 | 8ecf8077-cf51-4820-aadd-14040956f35d | NT AUTHORITY\SYSTEM | 4624 | 2020-10-01 22:39:37.220000+00:00 | f6638b82-98a5-4542-8bec-6bc0977f793f | VictimPc.Contoso.Azure | VictimPc$ | CONTOSO | S-1-5-18 | SYSTEM | NT AUTHORITY | S-1-5-18 | 0x3e7 | Advapi | 5 | 5 - Service | Negotiate | - | - | 2020-10-01 22:39:37.220000+00:00 | |
2 | 8ecf8077-cf51-4820-aadd-14040956f35d | NT AUTHORITY\SYSTEM | 4624 | 2020-10-01 22:39:42.603000+00:00 | f6638b82-98a5-4542-8bec-6bc0977f793f | VictimPc.Contoso.Azure | VictimPc$ | CONTOSO | S-1-5-18 | SYSTEM | NT AUTHORITY | S-1-5-18 | 0x3e7 | Advapi | 5 | 5 - Service | Negotiate | - | - | 2020-10-01 22:39:42.603000+00:00 | |
3 | 8ecf8077-cf51-4820-aadd-14040956f35d | CONTOSO\RonHD | 4624 | 2020-10-01 22:40:00.957000+00:00 | f6638b82-98a5-4542-8bec-6bc0977f793f | VictimPc.Contoso.Azure | VictimPc$ | CONTOSO | S-1-5-18 | RonHD | CONTOSO | S-1-5-21-1661583231-2311428937-3957907789-1105 | 0x117a0f7f | Advapi | 4 | 4 - Batch | Negotiate | - | VictimPc | 2020-10-01 22:40:00.957000+00:00 | |
4 | 8ecf8077-cf51-4820-aadd-14040956f35d | NT AUTHORITY\SYSTEM | 4624 | 2020-10-01 22:40:14.040000+00:00 | f6638b82-98a5-4542-8bec-6bc0977f793f | VictimPc.Contoso.Azure | VictimPc$ | CONTOSO | S-1-5-18 | SYSTEM | NT AUTHORITY | S-1-5-18 | 0x3e7 | Advapi | 5 | 5 - Service | Negotiate | - | - | 2020-10-01 22:40:14.040000+00:00 |
The example below shows using the host entity as an initial parameter
(Pivot is using the attribute mapping assign the host_name
function parameter the value of host.fqdn
).
The second parameter is a list of event IDs specified explicitly.
Host.AzureSentinel.list_host_events_by_id?
Signature: Host.AzureSentinel.list_host_events_by_id(*args, **kwargs) -> Union[pandas.core.frame.DataFrame, Any] Docstring: Retrieves list of events on a host Parameters ---------- add_query_items: str (optional) Additional query clauses end: datetime Query end time event_list: list (optional) List of event IDs to match (default value is: has) host_name: str Name of host host_op: str (optional) The hostname match operator (default value is: has) query_project: str (optional) Column project statement start: datetime Query start time table: str (optional) Table name (default value is: SecurityEvent) File: c:\users\ian\anaconda3\envs\condadev\lib\functools.py Type: function
(
Host.AzureSentinel.list_host_events_by_id( # Pivot query returns DataFrame
host, event_list=[4624, 4625, 4672]
)
[["Computer", "EventID", "Activity"]] # we could have save the output to a dataframe
.groupby(["EventID", "Activity"]) # variable but we can also use pandas
.count() # functions/syntax directly on the output
)
Computer | ||
---|---|---|
EventID | Activity | |
4624 | 4624 - An account was successfully logged on. | 520 |
4672 | 4672 - Special privileges assigned to new logon. | 436 |
Some data queries accept "list" items as parameters (e.g. many of the IP queries accept a list of IP addresses). These work as expected, with a single query calling sending the whole list as a single parameter.
ip_list = [
"203.23.68.64",
"67.10.68.45",
"182.69.173.164",
"79.176.167.161",
"167.220.197.230",
]
IpAddress.AzureSentinel()
list_alerts_for_ip function list_aad_signins_for_ip function list_azure_activity_for_ip function list_azure_network_flows_by_ip function list_activity_for_ip function get_info_by_ipaddress function list_logons_for_source_ip function get_host_for_ip function get_heartbeat_for_ip function list_indicators function list_indicators_by_ip function list_indicators_by_hash function list_indicators_by_filepath function list_indicators_by_domain function list_indicators_by_email function list_indicators_by_url function
IpAddress.AzureSentinel.list_aad_signins_for_ip?
Signature: IpAddress.AzureSentinel.list_aad_signins_for_ip(*args, **kwargs) -> Union[pandas.core.frame.DataFrame, Any] Docstring: Lists Azure AD Signins for an IP Address Parameters ---------- add_query_items: str (optional) Additional query clauses end: datetime (optional) Query end time ip_address_list: list The IP Address or list of Addresses start: datetime (optional) Query start time (default value is: -5) table: str (optional) Table name (default value is: SigninLogs) File: c:\users\ian\anaconda3\envs\condadev\lib\functools.py Type: function
IpAddress.AzureSentinel.list_aad_signins_for_ip(ip_address_list=ip_list).head(5)
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 | ... | 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 | Type | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 8ecf8077-cf51-4820-aadd-14040956f35d | Azure AD | 2020-10-01 13:02:35.957000+00:00 | /tenants/4b2462a4-bbee-495a-a0e1-f23ae524cc9c/providers/Microsoft.aadiam | Sign-in activity | 1.0 | SignInLogs | 0 | None | 0 | affb9968-fde2-4369-bd7e-d529369d6da1 | Microsoft.aadiam | Microsoft.aadiam | Brandon | 4 | US | Azure Advanced Threat Protection | 7b7531ad-5926-4f2d-8a1d-38495ad33e17 | [] | [\r\n {\r\n "key": "IsCAEToken",\r\n "value": "False"\r\n }\r\n] | singleFactorAuthentication | ... | None | {'city': 'Lewisville', 'state': 'Texas', 'countryOrRegion': 'US', 'geoCoordinates': {'latitude':... | None | [] | 5d995a60-e8ef-4ca8-acdd-41c2db788100 | 182 | none | [] | [] | none | none | none | Azure Advanced Threat Protection | 7b7531ad-5926-4f2d-8a1d-38495ad33e17 | {'errorCode': 0} | AzureAD | Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/87.0.427... | Brandon | 9dadd76f-3237-4e1d-84e7-e45c59867492 | brandon@seccxpninja.onmicrosoft.com | 4b2462a4-bbee-495a-a0e1-f23ae524cc9c | SigninLogs | |||||||
1 | 8ecf8077-cf51-4820-aadd-14040956f35d | Azure AD | 2020-10-01 14:02:40.100000+00:00 | /tenants/4b2462a4-bbee-495a-a0e1-f23ae524cc9c/providers/Microsoft.aadiam | Sign-in activity | 1.0 | SignInLogs | 0 | None | 0 | 9d67aa98-e889-417b-888d-e75611c1a458 | Microsoft.aadiam | Microsoft.aadiam | Brandon | 4 | US | Azure Advanced Threat Protection | 7b7531ad-5926-4f2d-8a1d-38495ad33e17 | [] | [\r\n {\r\n "key": "IsCAEToken",\r\n "value": "False"\r\n }\r\n] | singleFactorAuthentication | ... | None | {'city': 'Lewisville', 'state': 'Texas', 'countryOrRegion': 'US', 'geoCoordinates': {'latitude':... | None | [] | 70141716-651c-4f23-a1f8-e06015497f00 | 176 | none | [] | [] | none | none | none | Azure Advanced Threat Protection | 7b7531ad-5926-4f2d-8a1d-38495ad33e17 | {'errorCode': 0} | AzureAD | Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/87.0.427... | Brandon | 9dadd76f-3237-4e1d-84e7-e45c59867492 | brandon@seccxpninja.onmicrosoft.com | 4b2462a4-bbee-495a-a0e1-f23ae524cc9c | SigninLogs | |||||||
2 | 8ecf8077-cf51-4820-aadd-14040956f35d | Azure AD | 2020-10-01 15:02:45.205000+00:00 | /tenants/4b2462a4-bbee-495a-a0e1-f23ae524cc9c/providers/Microsoft.aadiam | Sign-in activity | 1.0 | SignInLogs | 0 | None | 0 | d3c71898-c2f7-4563-ae0c-82851116852d | Microsoft.aadiam | Microsoft.aadiam | Brandon | 4 | US | Azure Advanced Threat Protection | 7b7531ad-5926-4f2d-8a1d-38495ad33e17 | [] | [\r\n {\r\n "key": "IsCAEToken",\r\n "value": "False"\r\n }\r\n] | singleFactorAuthentication | ... | None | {'city': 'Lewisville', 'state': 'Texas', 'countryOrRegion': 'US', 'geoCoordinates': {'latitude':... | None | [] | 422d0e7e-9e69-48ea-85a7-34bcb7a20101 | 166 | none | [] | [] | none | none | none | Azure Advanced Threat Protection | 7b7531ad-5926-4f2d-8a1d-38495ad33e17 | {'errorCode': 0} | AzureAD | Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/87.0.427... | Brandon | 9dadd76f-3237-4e1d-84e7-e45c59867492 | brandon@seccxpninja.onmicrosoft.com | 4b2462a4-bbee-495a-a0e1-f23ae524cc9c | SigninLogs | |||||||
3 | 8ecf8077-cf51-4820-aadd-14040956f35d | Azure AD | 2020-10-01 17:45:14.507000+00:00 | /tenants/4b2462a4-bbee-495a-a0e1-f23ae524cc9c/providers/Microsoft.aadiam | Sign-in activity | 1.0 | SignInLogs | 0 | None | 0 | b1d3e8fa-fe53-4b6f-b683-debb7b482f87 | Microsoft.aadiam | Microsoft.aadiam | Brandon | 4 | US | Microsoft Cloud App Security | 05a65629-4c1b-48c1-a78b-804c4abdd4af | [] | [\r\n {\r\n "key": "IsCAEToken",\r\n "value": "False"\r\n }\r\n] | singleFactorAuthentication | ... | None | {'city': 'Lewisville', 'state': 'Texas', 'countryOrRegion': 'US', 'geoCoordinates': {'latitude':... | None | [] | 70959618-8d07-4004-a68f-0b93c1409200 | 150 | none | [] | [] | none | none | none | Windows Azure Active Directory | 00000002-0000-0000-c000-000000000000 | {'errorCode': 0} | AzureAD | Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/87.0.427... | Brandon | 9dadd76f-3237-4e1d-84e7-e45c59867492 | brandon@seccxpninja.onmicrosoft.com | 4b2462a4-bbee-495a-a0e1-f23ae524cc9c | SigninLogs | |||||||
4 | 8ecf8077-cf51-4820-aadd-14040956f35d | Azure AD | 2020-10-01 10:02:18.923000+00:00 | /tenants/4b2462a4-bbee-495a-a0e1-f23ae524cc9c/providers/Microsoft.aadiam | Sign-in activity | 1.0 | SignInLogs | 0 | None | 0 | ac81524b-bb83-4a0a-a3f8-577a14dda295 | Microsoft.aadiam | Microsoft.aadiam | Brandon | 4 | US | Azure Advanced Threat Protection | 7b7531ad-5926-4f2d-8a1d-38495ad33e17 | [] | [\r\n {\r\n "key": "IsCAEToken",\r\n "value": "False"\r\n }\r\n] | singleFactorAuthentication | ... | None | {'city': 'Lewisville', 'state': 'Texas', 'countryOrRegion': 'US', 'geoCoordinates': {'latitude':... | None | [] | c2bcb991-75ad-42f4-a6c0-1a90686dfd00 | 210 | none | [] | [] | none | none | none | Azure Advanced Threat Protection | 7b7531ad-5926-4f2d-8a1d-38495ad33e17 | {'errorCode': 0} | AzureAD | Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/87.0.427... | Brandon | 9dadd76f-3237-4e1d-84e7-e45c59867492 | brandon@seccxpninja.onmicrosoft.com | 4b2462a4-bbee-495a-a0e1-f23ae524cc9c | SigninLogs |
5 rows × 59 columns
In this case the pivot function will iterate through the values of the iterable, making a separate query for each and then joining the results.
We can see that this function only accepts a single value for "account_name".
Account.AzureSentinel.list_aad_signins_for_account?
Signature: Account.AzureSentinel.list_aad_signins_for_account(*args, **kwargs) -> Union[pandas.core.frame.DataFrame, Any] Docstring: Lists Azure AD Signins for Account Parameters ---------- account_name: str The account name to find add_query_items: str (optional) Additional query clauses end: datetime (optional) Query end time start: datetime (optional) Query start time (default value is: -5) table: str (optional) Table name (default value is: SigninLogs) File: c:\users\ian\anaconda3\envs\condadev\lib\functools.py Type: function
accounts = [
"ofshezaf",
"moshabi",
]
Account.AzureSentinel.list_aad_signins_for_account(account_name=accounts)
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 | ... | 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 | Type | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 8ecf8077-cf51-4820-aadd-14040956f35d | Azure AD | 2020-10-01 11:04:42.689000+00:00 | /tenants/4b2462a4-bbee-495a-a0e1-f23ae524cc9c/providers/Microsoft.aadiam | Sign-in activity | 1.0 | SignInLogs | 0 | None | 0 | 2e6fd17c-1227-433e-b3a3-80a74374a7dc | Microsoft.aadiam | Microsoft.aadiam | Ofer Shezaf | 4 | IL | Azure Portal | c44b4083-3bb0-49c1-b47d-974e53cbdf3c | [] | [\r\n {\r\n "key": "Login Hint Present",\r\n "value": "True"\r\n },\r\n {\r\n "key":... | multiFactorAuthentication | ... | None | {'city': 'Tiberias', 'state': 'Hazafon', 'countryOrRegion': 'IL', 'geoCoordinates': {'latitude':... | {} | [] | c8bfc04f-28bf-40b4-a9c1-07fd5bd9f800 | 918 | none | [] | [] | none | 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/85.0.418... | Ofer Shezaf | 9c459db5-0407-43fe-a2ea-126757297beb | ofshezaf@microsoft.com | 4b2462a4-bbee-495a-a0e1-f23ae524cc9c | SigninLogs | |||||||
1 | 8ecf8077-cf51-4820-aadd-14040956f35d | Azure AD | 2020-10-01 11:19:36.626000+00:00 | /tenants/4b2462a4-bbee-495a-a0e1-f23ae524cc9c/providers/Microsoft.aadiam | Sign-in activity | 1.0 | SignInLogs | 0 | None | 0 | 4bdf65b2-99af-4bd4-ab7c-ffbc5a1d5038 | Microsoft.aadiam | Microsoft.aadiam | Mor Shabi | 4 | IL | Azure Portal | c44b4083-3bb0-49c1-b47d-974e53cbdf3c | [] | [\r\n {\r\n "key": "Login Hint Present",\r\n "value": "True"\r\n },\r\n {\r\n "key":... | multiFactorAuthentication | ... | None | {'city': 'Herzliya', 'state': 'Tel Aviv', 'countryOrRegion': 'IL', 'geoCoordinates': {'latitude'... | {} | [] | 4a40c63d-5e43-4af0-a0e5-2ae5df81e500 | 3600 | none | [] | [] | none | 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/85.0.418... | Mor Shabi | 7b77cfef-7ac7-4121-a834-561291927ad1 | moshabi@microsoft.com | 4b2462a4-bbee-495a-a0e1-f23ae524cc9c | SigninLogs | |||||||
2 | 8ecf8077-cf51-4820-aadd-14040956f35d | Azure AD | 2020-10-01 11:19:40.787000+00:00 | /tenants/4b2462a4-bbee-495a-a0e1-f23ae524cc9c/providers/Microsoft.aadiam | Sign-in activity | 1.0 | SignInLogs | 0 | None | 0 | 4460b859-84c1-4751-bddb-b305516cbed4 | Microsoft.aadiam | Microsoft.aadiam | Mor Shabi | 4 | IL | Azure Portal | c44b4083-3bb0-49c1-b47d-974e53cbdf3c | [] | [\r\n {\r\n "key": "Login Hint Present",\r\n "value": "True"\r\n },\r\n {\r\n "key":... | singleFactorAuthentication | ... | None | {'city': 'Herzliya', 'state': 'Tel Aviv', 'countryOrRegion': 'IL', 'geoCoordinates': {'latitude'... | {} | [] | 4a40c63d-5e43-4af0-a0e5-2ae5c182e500 | 1526 | none | [] | [] | none | 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/85.0.418... | Mor Shabi | 7b77cfef-7ac7-4121-a834-561291927ad1 | moshabi@microsoft.com | 4b2462a4-bbee-495a-a0e1-f23ae524cc9c | SigninLogs |
3 rows × 59 columns
The same rules as outline earlier for multiple parameters of different types apply to data queries
project = "| project UserPrincipalName, Identity"
Account.AzureSentinel.list_aad_signins_for_account(account_name=accounts, add_query_items=project)
UserPrincipalName | Identity | |
---|---|---|
0 | ofshezaf@microsoft.com | Ofer Shezaf |
1 | moshabi@microsoft.com | Mor Shabi |
2 | moshabi@microsoft.com | Mor Shabi |
This is similar to using dataframes for other pivot functions.
We must use the data
parameter to specify the input dataframe.
You supply the column name from your input dataframe as the value of
the parameters expected by the function.
account_df = pd.DataFrame(accounts, columns=["User"])
display(account_df)
User | |
---|---|
0 | ofshezaf |
1 | moshabi |
Now we have our dataframe:
account_df
as the value of the data
parameter.User
On each iteration, the column value from a subsequent row will be extracted and given as the parameter value for the function parameter.
Note:
If the function parameter type is a "list" type - i.e. it expects a list of values
the parameter value will be sent as a list and only a single query is executed.
If the query function has multiple "list" type parameters, these will be
populated in the same way.
Note2:
If you have multiple parameters fed by multiple input columns AND one or more
of the function parameters is not a list type, the the query will be broken
into queries for each row. Each sub-query getting its values from a single row
of the input dataframe.
Account.AzureSentinel.list_aad_signins_for_account(data=account_df, account_name="User", add_query_items=project)
UserPrincipalName | Identity | |
---|---|---|
0 | ofshezaf@microsoft.com | Ofer Shezaf |
1 | moshabi@microsoft.com | Mor Shabi |
2 | moshabi@microsoft.com | Mor Shabi |
These work in the same way as the functions described earlier. However, there are a few peculiarities of the Threat Intel functions:
Queries for individual providers are broken out into separate functions
You will see multiple lookup_ipv4
functions, for example: one with no suffix
and one for each individual TI provider with a corresponding suffix.
This is a convenience to let you use a specific provider more quickly. You
can still use the generic function (lookup_ipv4
) and supply a providers parameter
to indicate which providers you want to use.
Some providers treat these interchangably and use the same endpoint for both. Other providers do not explicitly support IPV6 (e.g. the Tor exit nodes provider). Still others (notably OTX) use different endpoints for IPv4 and IPv6.
If you are querying IPv4 you can use either the lookup_ip
function or one
of the lookup_ipv4
functions. In most cases, you can also use these functions
for a mixture of IPv4 and v6 addresses. However, in cases where a provider
does not support IPv6 or uses a different endpoint for IPv6 queries you
will get no responses.
This table shows the mapping between and entity type and IoC Types:
Entity | IoCType |
---|---|
IpAddress | ipv4, ipv6 |
Dns | domain |
File | filehash (incl |
md5, sha1, sha256) | |
Url | url |
Note: Where you are using a File entity as a parameter, there is a complication.
A file entity can have multiple hash values (md5, sha1, sha256 and even sha256 authenticode).
Thefile_hash
attibute of File is used as the default parameter.
In cases where a file has multiple hashes the highest priority hash (in order
sha256, sha1, md5, sha256ac) is returned.
If you are not using file entities as parameters (and specifying the input values
explicitly or via a Dataframe or iterable, you can ignore this.
IpAddress.ti()
lookup_ip function lookup_ipv4 function lookup_ipv4_OTX function lookup_ipv4_Tor function lookup_ipv4_VirusTotal function lookup_ipv4_XForce function lookup_ipv6 function lookup_ipv6_OTX function
from msticpy.datamodel.entities import Url, Dns, File
dns = Dns(DomainName="fkksjobnn43.org")
Dns.ti.lookup_dns(dns)
Ioc | IocType | SafeIoc | QuerySubtype | Provider | Result | Severity | Details | RawResult | Reference | Status | |
---|---|---|---|---|---|---|---|---|---|---|---|
0 | fkksjobnn43.org | dns | fkksjobnn43.org | None | OTX | True | high | {'pulse_count': 35, 'names': ['Jaff - Malware Domain Feed V2', 'Jaff - Malware Domain Feed V2', ... | {'indicator': 'fkksjobnn43.org', 'alexa': 'http://www.alexa.com/siteinfo/fkksjobnn43.org', 'whoi... | https://otx.alienvault.com/api/v1/indicators/domain/fkksjobnn43.org/general | 0 |
0 | fkksjobnn43.org | dns | None | OPR | True | warning | {'rank': None, 'error': 'Domain not found'} | {'status_code': 404, 'error': 'Domain not found', 'page_rank_integer': 0, 'page_rank_decimal': 0... | https://openpagerank.com/api/v1.0/getPageRank?domains[0]=fkksjobnn43.org | 0 | |
0 | fkksjobnn43.org | dns | fkksjobnn43.org | None | VirusTotal | True | information | {'verbose_msg': 'Domain found in dataset', 'response_code': 1, 'detected_urls': [], 'positives':... | {'undetected_downloaded_samples': [], 'whois_timestamp': 1603963073, 'detected_downloaded_sample... | https://www.virustotal.com/vtapi/v2/domain/report | 0 |
0 | fkksjobnn43.org | dns | fkksjobnn43.org | None | XForce | True | information | {'score': 0, 'cats': None, 'categoryDescriptions': None, 'reason': None, 'reasonDescription': 0,... | {'result': {'url': 'fkksjobnn43.org', 'cats': {'General Business': True}, 'score': 1, 'categoryD... | https://api.xforce.ibmcloud.com/url/fkksjobnn43.org | 0 |
Dns.ti.lookup_dns(value="fkksjobnn43.org")
Ioc | IocType | SafeIoc | QuerySubtype | Provider | Result | Severity | Details | RawResult | Reference | Status | |
---|---|---|---|---|---|---|---|---|---|---|---|
0 | fkksjobnn43.org | dns | fkksjobnn43.org | None | OTX | True | high | {'pulse_count': 35, 'names': ['Jaff - Malware Domain Feed V2', 'Jaff - Malware Domain Feed V2', ... | {'indicator': 'fkksjobnn43.org', 'alexa': 'http://www.alexa.com/siteinfo/fkksjobnn43.org', 'whoi... | https://otx.alienvault.com/api/v1/indicators/domain/fkksjobnn43.org/general | 0 |
0 | fkksjobnn43.org | dns | None | OPR | True | warning | {'rank': None, 'error': 'Domain not found'} | {'status_code': 404, 'error': 'Domain not found', 'page_rank_integer': 0, 'page_rank_decimal': 0... | https://openpagerank.com/api/v1.0/getPageRank?domains[0]=fkksjobnn43.org | 0 | |
0 | fkksjobnn43.org | dns | fkksjobnn43.org | None | VirusTotal | True | information | {'verbose_msg': 'Domain found in dataset', 'response_code': 1, 'detected_urls': [], 'positives':... | {'undetected_downloaded_samples': [], 'whois_timestamp': 1603963073, 'detected_downloaded_sample... | https://www.virustotal.com/vtapi/v2/domain/report | 0 |
0 | fkksjobnn43.org | dns | fkksjobnn43.org | None | XForce | True | information | {'score': 0, 'cats': None, 'categoryDescriptions': None, 'reason': None, 'reasonDescription': 0,... | {'result': {'url': 'fkksjobnn43.org', 'cats': {'General Business': True}, 'score': 1, 'categoryD... | https://api.xforce.ibmcloud.com/url/fkksjobnn43.org | 0 |
hashes = [
"02a7977d1faf7bfc93a4b678a049c9495ea663e7065aa5a6caf0f69c5ff25dbd",
"06b020a3fd3296bc4c7bf53307fe7b40638e7f445bdd43fac1d04547a429fdaf",
"06c676bf8f5c6af99172c1cf63a84348628ae3f39df9e523c42447e2045e00ff",
]
File.ti.lookup_file_hash_VirusTotal(hashes)
Ioc | IocType | SafeIoc | QuerySubtype | Provider | Result | Severity | Details | RawResult | Reference | Status | |
---|---|---|---|---|---|---|---|---|---|---|---|
0 | 02a7977d1faf7bfc93a4b678a049c9495ea663e7065aa5a6caf0f69c5ff25dbd | sha256_hash | 02a7977d1faf7bfc93a4b678a049c9495ea663e7065aa5a6caf0f69c5ff25dbd | None | VirusTotal | True | high | {'verbose_msg': 'Scan finished, information embedded', 'response_code': 1, 'resource': '02a7977d... | {'scans': {'Bkav': {'detected': True, 'version': '1.3.0.9899', 'result': 'W32.AIDetectVM.malware... | https://www.virustotal.com/vtapi/v2/file/report | 0 |
1 | 06b020a3fd3296bc4c7bf53307fe7b40638e7f445bdd43fac1d04547a429fdaf | sha256_hash | 06b020a3fd3296bc4c7bf53307fe7b40638e7f445bdd43fac1d04547a429fdaf | None | VirusTotal | True | high | {'verbose_msg': 'Scan finished, information embedded', 'response_code': 1, 'resource': '06b020a3... | {'scans': {'Bkav': {'detected': False, 'version': '1.3.0.9899', 'result': None, 'update': '20201... | https://www.virustotal.com/vtapi/v2/file/report | 0 |
2 | 06c676bf8f5c6af99172c1cf63a84348628ae3f39df9e523c42447e2045e00ff | sha256_hash | 06c676bf8f5c6af99172c1cf63a84348628ae3f39df9e523c42447e2045e00ff | None | VirusTotal | True | high | {'verbose_msg': 'Scan finished, information embedded', 'response_code': 1, 'resource': '06c676bf... | {'scans': {'Bkav': {'detected': True, 'version': '1.3.0.9899', 'result': 'W32.AIDetectVM.malware... | https://www.virustotal.com/vtapi/v2/file/report | 0 |
To specify the source column you can use either "column" or "obs_column"
hashes_df = pd.DataFrame(
[(fh, f"item_{idx}", "stuff") for idx, fh in enumerate(hashes)],
columns=["hash", "ref", "desc"],
)
display(hashes_df)
File.ti.lookup_file_hash_VirusTotal(data=hashes_df, column="hash")
hash | ref | desc | |
---|---|---|---|
0 | 02a7977d1faf7bfc93a4b678a049c9495ea663e7065aa5a6caf0f69c5ff25dbd | item_0 | stuff |
1 | 06b020a3fd3296bc4c7bf53307fe7b40638e7f445bdd43fac1d04547a429fdaf | item_1 | stuff |
2 | 06c676bf8f5c6af99172c1cf63a84348628ae3f39df9e523c42447e2045e00ff | item_2 | stuff |
Ioc | IocType | SafeIoc | QuerySubtype | Provider | Result | Severity | Details | RawResult | Reference | Status | |
---|---|---|---|---|---|---|---|---|---|---|---|
0 | 02a7977d1faf7bfc93a4b678a049c9495ea663e7065aa5a6caf0f69c5ff25dbd | sha256_hash | 02a7977d1faf7bfc93a4b678a049c9495ea663e7065aa5a6caf0f69c5ff25dbd | None | VirusTotal | True | high | {'verbose_msg': 'Scan finished, information embedded', 'response_code': 1, 'resource': '02a7977d... | {'scans': {'Bkav': {'detected': True, 'version': '1.3.0.9899', 'result': 'W32.AIDetectVM.malware... | https://www.virustotal.com/vtapi/v2/file/report | 0 |
1 | 06b020a3fd3296bc4c7bf53307fe7b40638e7f445bdd43fac1d04547a429fdaf | sha256_hash | 06b020a3fd3296bc4c7bf53307fe7b40638e7f445bdd43fac1d04547a429fdaf | None | VirusTotal | True | high | {'verbose_msg': 'Scan finished, information embedded', 'response_code': 1, 'resource': '06b020a3... | {'scans': {'Bkav': {'detected': False, 'version': '1.3.0.9899', 'result': None, 'update': '20201... | https://www.virustotal.com/vtapi/v2/file/report | 0 |
2 | 06c676bf8f5c6af99172c1cf63a84348628ae3f39df9e523c42447e2045e00ff | sha256_hash | 06c676bf8f5c6af99172c1cf63a84348628ae3f39df9e523c42447e2045e00ff | None | VirusTotal | True | high | {'verbose_msg': 'Scan finished, information embedded', 'response_code': 1, 'resource': '06c676bf... | {'scans': {'Bkav': {'detected': True, 'version': '1.3.0.9899', 'result': 'W32.AIDetectVM.malware... | https://www.virustotal.com/vtapi/v2/file/report | 0 |
Because pivot functions can take dataframes as inputs and return them as outputs, you can create chains of pivot functions. You can also add other items to the chain that input or output dataframes.
For example, you could build a chain that included the following:
To make building these types of pipelines easier we've implemented some
pandas helper functions. These are available in the mp_pivot
property of pandas DataFrames, once Pivot is imported.
run
lets you run a pivot function as a pandas pipeline operation.
Let's take an example of a simple pivot function using a dataframe as input
IpAddress.util.whois(data=my_df, column="Ioc")
We can us mp_pivot.run to do this:
(
my_df
.query("UserCount > 1")
.mp_pivot.run(IpAddress.util.whois, column="Ioc")
.drop_duplicates()
)
The pandas extension takes care of the data=my_df
parameter. We still have
to add any other required parameters (like the column specification in this case.
When it runs it returns its output as a DataFrame and the next operation
(drop_duplicates()) runs on this output.
Depending on the scenario you might want to preserve the existing dataframe
contents (most of the pivot functions only return the results of their specific
operation - e.g. whois returns ASN information for an IP address). You
can carry the columns of the input dataframe over to the output from
the pivot function by adding a join
parameter to the mp_pivot.run() call.
Use a "left" to keep all of the input rows regardless of whether the pivot
function returned a result for that row.
Use an "inner" join to return only rows where the input had a positive result
in the pivot function.
.mp_pivot.run(IpAddress.util.whois, column="Ioc", join="inner")
There are also a couple of convenience functions. These only work in an IPython/Jupyter environment.
mp_pivot.display
will display the intermediate results of the dataframe in the middle
of a pipeline. It does not change the data at all, but does give you the
chance to display a view of the data partway through processing. This
is useful for debugging but its main purpose is to give you a way to
show partial results without having to break the pipeline into pieces
and create unnecessary throw-away variables that will add bulk to your
code and clutter to your memory.
display
supports some options that you can use to modify the displayed
output:
head
rowsThese options do not affect the data being passed through the pipeline - only how the intermediate output is displayed.
mp_pivot.tee
behaves a little like the Linux "tee" command. It allows the
data to pass through unchanged but allows you to create a variable that
is a snapshot of the data at that point in the pipeline. It takes
a parameter var_name
and assigns the current DataFrame instance
to that name. So, when your pipeline has run you can access partial results (again,
without having to break up your pipeline to do so).
By default, it will not overwrite an existing variable of the same name
unless you specify clobber=True
in the call to tee
.
The example below shows the use of mp_pivot.run and mp_pivot.display.
This takes an existing DataFrame - suspcious_ips - and:
query
to filter only the high severity hitsjoin='left'
so our output will be all TI result data plus whois dataThe final step uses another MSTICPy pandas extension to plot the login attempts on a timeline chart.
suspicious_ips = [
"113.190.36.2",
"118.163.135.17",
"118.163.135.18",
"118.163.97.19",
"125.34.240.33",
"135.26.152.186",
"165.225.17.6",
"177.135.101.5",
"177.159.99.89",
"177.19.187.79",
"186.215.197.15",
"186.215.198.137",
"189.59.5.81",
]
(
suspicious_ips
.mp_pivot.display(title=f"Initial IPs {len(suspicious_ips)}", head=5)
# Lookup IPs at VT
.mp_pivot.run(IpAddress.ti.lookup_ipv4_VirusTotal, column="IPAddress")
# Filter on high severity
.query("Severity == 'high'")
.mp_pivot.run(IpAddress.util.whois, column="Ioc", join="left")
.mp_pivot.display(title="TI High Severity IPs", head=5)
# Query IPs that have login attempts
.mp_pivot.run(IpAddress.AzureSentinel.list_aad_signins_for_ip, ip_address_list="Ioc")
# Send the output of this to a plot
.mp_timeline.plot(
title="High Severity IPs with Logon attempts",
source_columns=["UserPrincipalName", "IPAddress", "ResultType", "ClientAppUsed", "UserAgent", "Location"],
group_by="UserPrincipalName"
)
)
This is what the pipelined functions should output (although the results will obviously not be the same for your environment).
To do this you need the following information
Item | Description | Required |
---|---|---|
src_module | The src_module to containing the class or function | Yes |
class | The class containing function | No |
src_func_name | The name of the function to wrap | Yes |
func_new_name | Rename the function | No |
input type | The input type that the wrapped function expects (dataframe iterable value) | Yes |
entity_map | Mapping of entity and attribute used for function | Yes |
func_df_param_name | The param name that the function uses as input param for DataFrame | If DF input |
func_df_col_param_name | The param name that function uses to identify the input column name | If DF input |
func_out_column_name | Name of the column in the output DF to use as a key to join | If DF output |
func_static_params | dict of static name/value params always sent to the function | No |
func_input_value_arg | Name of the param that the wrapped function uses for its input value | No |
can_iterate | True if the function supports being called multiple times | No |
entity_container_name | The name of the container in the entity where the func will appear | No |
The entity_map controls where the pivot function will be added. Each entry
requires an Entity name (see msticpy.datamodel.entities) and an entity
attribute name. This is only used if an instance of the entity is used
as a parameter to the function. For IpAddress
in the example below,
the pivot function will try to extract the value of the Address
attribute
when an instance of IpAddress is used as a function parameter.
entity_map:
IpAddress: Address
Host: HostName
Account: Name
This means that you can specify different attributes of the same entity for different functions (or even for two instances of the same function)
The func_df_param_name
and func_df_col_param_name
are needed only if
the source function takes a dataframe and column name as input parameters.
func_out_column_name
is relevant if the source function returns a
dataframe. In order to join input data with output data this needs to
be the column in the output that has the same value as the function
input (e.g. if you are processing IP addresses and the column name
in the output DF containing the IP is named "ip_addr", put "ip_addr" here.)
When you have this information create or add this to a yaml file
with the top-level element pivot_providers
.
Example from the msticpy ip_utils who_is
function
pivot_providers:
...
who_is:
src_module: msticpy.sectools.ip_utils
src_func_name: get_whois_df
func_new_name: whois
input_type: dataframe
entity_map:
IpAddress: Address
func_df_param_name: data
func_df_col_param_name: ip_column
func_out_column_name: ip
func_static_params:
whois_col: whois_result
func_input_value_arg: ip_address
This doesn't currently support creating pivots from functions defined inline (in the notebook)
Once you have your yaml definition file you can call
Pivot.register_pivot_providers(
pivot_reg_path=path_to_your_yaml,
namespace=globals(),
def_container="my_container",
force_container=True
)
Note, this is not persistent. You will need to call this each time you start a new session.
Pivot.register_pivot_providers(
pivot_reg_path: str,
namespace: Dict[str, Any] = None,
def_container: str = 'custom',
force_container: bool = False,
)
Docstring:
Register pivot functions from configuration file.
Parameters
----------
file_path : str
Path to config yaml file
namespace : Dict[str, Any], optional
Namespace to search for existing instances of classes, by default None
container : str, optional
Container name to use for entity pivot functions, by default "other"
force_container : bool, optional
Force `container` value to be used even if entity definitions have
specific setting for a container name, by default False
Pivot.register_pivot_providers?
Signature: Pivot.register_pivot_providers( pivot_reg_path: str, namespace: Dict[str, Any] = None, def_container: str = 'custom', force_container: bool = False, ) Docstring: Register pivot functions from configuration file. Parameters ---------- file_path : str Path to config yaml file namespace : Dict[str, Any], optional Namespace to search for existing instances of classes, by default None container : str, optional Container name to use for entity pivot functions, by default "other" force_container : bool, optional Force `container` value to be used even if entity definitions have specific setting for a container name, by default False Raises ------ ValueError An entity specified in the config file is not recognized. File: e:\src\microsoft\msticpy\msticpy\datamodel\pivot.py Type: function