#Check we are running Python 3.6
import sys
MIN_REQ_PYTHON = (3,6)
if sys.version_info < MIN_REQ_PYTHON:
print('Check the Kernel->Change Kernel menu and ensure that Python 3.6')
print('or later is selected as the active kernel.')
sys.exit("Python %s.%s or later is required.\n" % MIN_REQ_PYTHON)
#imports
import json
import yaml
import msticpy.nbtools as nbtools
#data library imports
from msticpy.data.data_providers import QueryProvider
import msticpy.nbtools as mas
print('Imports Complete')
Imports Complete
# directory with osqueryd.results.log or other *.log files
# Tested with single file (osqueryd.results.log) and double (osqueryd.results.log + osqueryd.snapshots.log)
datadir = "/path/to/var/log/osquery"
# directory with queries yaml file
query_path = "/path/to"
# Specify path to look for data files
data_path = datadir
qry_prov = QueryProvider("LocalOsquery",
data_paths=[data_path],
query_paths=[query_path]
)
%%time
# Show the schema of the data files read in
# Slow for log file ~1MB
print(qry_prov.schema)
{'pack_osquery-custom-pack2_processes': {'name': 'object', 'hostIdentifier': 'object', 'calendarTime': 'object', 'unixTime': 'datetime64[ns]', 'epoch': 'int64', 'counter': 'int64', 'numerics': 'bool', 'action': 'object', 'decorations_host_uuid': 'object', 'decorations_username': 'object', 'columns_cmdline': 'object', 'columns_euid': 'object', 'columns_name': 'object', 'columns_parent': 'object', 'columns_path': 'object', 'columns_pcmdline': 'object', 'columns_pid': 'object', 'columns_uid': 'object', 'columns_username': 'object'}, 'pack_osquery-custom-pack2_process_binding_to_ports': {'name': 'object', 'hostIdentifier': 'object', 'calendarTime': 'object', 'unixTime': 'datetime64[ns]', 'epoch': 'int64', 'counter': 'int64', 'numerics': 'bool', 'action': 'object', 'decorations_host_uuid': 'object', 'decorations_username': 'object', 'columns_name': 'object', 'columns_pid': 'object', 'columns_port': 'object', 'columns_protocol': 'object'}, 'pack_osquery-monitoring_osquery_info': {'name': 'object', 'hostIdentifier': 'object', 'calendarTime': 'object', 'unixTime': 'datetime64[ns]', 'epoch': 'int64', 'counter': 'int64', 'numerics': 'bool', 'action': 'object', 'decorations_host_uuid': 'object', 'decorations_username': 'object', 'columns_pid': 'object', 'columns_build_distro': 'object', 'columns_build_platform': 'object', 'columns_config_hash': 'object', 'columns_config_valid': 'object', 'columns_counter': 'object', 'columns_extensions': 'object', 'columns_instance_id': 'object', 'columns_platform_mask': 'object', 'columns_resident_size': 'object', 'columns_start_time': 'object', 'columns_system_time': 'object', 'columns_user_time': 'object', 'columns_uuid': 'object', 'columns_version': 'object', 'columns_watcher': 'object'}, 'pack_osquery-custom-pack2_outbound_connections': {'name': 'object', 'hostIdentifier': 'object', 'calendarTime': 'object', 'unixTime': 'datetime64[ns]', 'epoch': 'int64', 'counter': 'int64', 'numerics': 'bool', 'action': 'object', 'decorations_host_uuid': 'object', 'decorations_username': 'object', 'columns_cmdline': 'object', 'columns_name': 'object', 'columns_path': 'object', 'columns_pcmdline': 'object', 'columns_pid': 'object', 'columns_username': 'object', 'columns_local_port': 'object', 'columns_md5': 'object', 'columns_remote_address': 'object', 'columns_remote_port': 'object'}, 'pack_incident-response_mounts': {'name': 'object', 'hostIdentifier': 'object', 'calendarTime': 'object', 'unixTime': 'datetime64[ns]', 'epoch': 'int64', 'counter': 'int64', 'numerics': 'bool', 'action': 'object', 'decorations_host_uuid': 'object', 'decorations_username': 'object', 'columns_path': 'object', 'columns_blocks': 'object', 'columns_blocks_available': 'object', 'columns_blocks_free': 'object', 'columns_blocks_size': 'object', 'columns_device': 'object', 'columns_device_alias': 'object', 'columns_flags': 'object', 'columns_inodes': 'object', 'columns_inodes_free': 'object', 'columns_type': 'object'}, 'pack_osquery-custom-pack2_process_env': {'name': 'object', 'hostIdentifier': 'object', 'calendarTime': 'object', 'unixTime': 'datetime64[ns]', 'epoch': 'int64', 'counter': 'int64', 'numerics': 'bool', 'action': 'object', 'decorations_host_uuid': 'object', 'decorations_username': 'object', 'columns_cmdline': 'object', 'columns_pid': 'object', 'columns_key': 'object', 'columns_value': 'object'}, 'pack_incident-response_listening_ports': {'name': 'object', 'hostIdentifier': 'object', 'calendarTime': 'object', 'unixTime': 'datetime64[ns]', 'epoch': 'int64', 'counter': 'int64', 'numerics': 'bool', 'action': 'object', 'decorations_host_uuid': 'object', 'decorations_username': 'object', 'columns_path': 'object', 'columns_pid': 'object', 'columns_port': 'object', 'columns_protocol': 'object', 'columns_address': 'object', 'columns_family': 'object', 'columns_fd': 'object', 'columns_net_namespace': 'object', 'columns_socket': 'object'}, 'pack_osquery-monitoring_schedule': {'name': 'object', 'hostIdentifier': 'object', 'calendarTime': 'object', 'unixTime': 'datetime64[ns]', 'epoch': 'int64', 'counter': 'int64', 'numerics': 'bool', 'action': 'object', 'decorations_host_uuid': 'object', 'decorations_username': 'object', 'columns_name': 'object', 'columns_average_memory': 'object', 'columns_avg_system_time': 'object', 'columns_avg_user_time': 'object', 'columns_denylisted': 'object', 'columns_executions': 'object', 'columns_interval': 'object', 'columns_last_executed': 'object', 'columns_output_size': 'object', 'columns_wall_time': 'object'}, 'pack_incident-response_process_env': {'name': 'object', 'hostIdentifier': 'object', 'calendarTime': 'object', 'unixTime': 'datetime64[ns]', 'epoch': 'int64', 'counter': 'int64', 'numerics': 'bool', 'action': 'object', 'decorations_host_uuid': 'object', 'decorations_username': 'object', 'columns_pid': 'object', 'columns_key': 'object', 'columns_value': 'object'}, 'fim': {'name': 'object', 'hostIdentifier': 'object', 'calendarTime': 'object', 'unixTime': 'datetime64[ns]', 'epoch': 'int64', 'counter': 'int64', 'numerics': 'bool', 'action': 'object', 'decorations_host_uuid': 'object', 'decorations_username': 'object', 'columns_uid': 'object', 'columns_username': 'object', 'columns_md5': 'object', 'columns_action': 'object', 'columns_atime': 'datetime64[ns]', 'columns_category': 'object', 'columns_ctime': 'datetime64[ns]', 'columns_mode': 'object', 'columns_mtime': 'datetime64[ns]', 'columns_sha256': 'object', 'columns_size': 'object', 'columns_target_path': 'object', 'columns_time': 'datetime64[ns]'}, 'pack_incident-response_open_files': {'name': 'object', 'hostIdentifier': 'object', 'calendarTime': 'object', 'unixTime': 'datetime64[ns]', 'epoch': 'int64', 'counter': 'int64', 'numerics': 'bool', 'action': 'object', 'decorations_host_uuid': 'object', 'decorations_username': 'object', 'columns_path': 'object', 'columns_pid': 'object'}, 'pack_incident-response_last': {'name': 'object', 'hostIdentifier': 'object', 'calendarTime': 'object', 'unixTime': 'datetime64[ns]', 'epoch': 'int64', 'counter': 'int64', 'numerics': 'bool', 'action': 'object', 'decorations_host_uuid': 'object', 'decorations_username': 'object', 'columns_pid': 'object', 'columns_username': 'object', 'columns_type': 'object', 'columns_time': 'datetime64[ns]', 'columns_host': 'object', 'columns_tty': 'object', 'columns_type_name': 'object'}, 'pack_incident-response_logged_in_users': {'name': 'object', 'hostIdentifier': 'object', 'calendarTime': 'object', 'unixTime': 'datetime64[ns]', 'epoch': 'int64', 'counter': 'int64', 'numerics': 'bool', 'action': 'object', 'decorations_host_uuid': 'object', 'decorations_username': 'object', 'columns_cmdline': 'object', 'columns_name': 'object', 'columns_pid': 'object', 'columns_type': 'object', 'columns_time': 'datetime64[ns]', 'columns_host': 'object', 'columns_tty': 'object', 'columns_cwd': 'object', 'columns_root': 'object', 'columns_user': 'object'}, 'pack_osquery-custom-pack2_known_hosts': {'name': 'object', 'hostIdentifier': 'object', 'calendarTime': 'object', 'unixTime': 'datetime64[ns]', 'epoch': 'int64', 'counter': 'int64', 'numerics': 'bool', 'action': 'object', 'decorations_host_uuid': 'object', 'decorations_username': 'object', 'columns_uid': 'object', 'columns_key': 'object', 'columns_key_file': 'object'}, 'pack_incident-response_process_memory': {'name': 'object', 'hostIdentifier': 'object', 'calendarTime': 'object', 'unixTime': 'datetime64[ns]', 'epoch': 'int64', 'counter': 'int64', 'numerics': 'bool', 'action': 'object', 'decorations_host_uuid': 'object', 'decorations_username': 'object', 'columns_path': 'object', 'columns_pid': 'object', 'columns_device': 'object', 'columns_end': 'object', 'columns_inode': 'object', 'columns_offset': 'object', 'columns_permissions': 'object', 'columns_pseudo': 'object', 'columns_start': 'object'}, 'pack_vuln-management_deb_packages': {'name': 'object', 'hostIdentifier': 'object', 'calendarTime': 'object', 'unixTime': 'datetime64[ns]', 'epoch': 'int64', 'counter': 'int64', 'numerics': 'bool', 'action': 'object', 'decorations_host_uuid': 'object', 'decorations_username': 'object', 'columns_name': 'object', 'columns_version': 'object', 'columns_size': 'object', 'columns_admindir': 'object', 'columns_arch': 'object', 'columns_maintainer': 'object', 'columns_priority': 'object', 'columns_revision': 'object', 'columns_section': 'object', 'columns_source': 'object', 'columns_status': 'object'}, 'pack_incident-response_shell_history': {'name': 'object', 'hostIdentifier': 'object', 'calendarTime': 'object', 'unixTime': 'datetime64[ns]', 'epoch': 'int64', 'counter': 'int64', 'numerics': 'bool', 'action': 'object', 'decorations_host_uuid': 'object', 'decorations_username': 'object', 'columns_uid': 'object', 'columns_username': 'object', 'columns_uuid': 'object', 'columns_time': 'datetime64[ns]', 'columns_command': 'object', 'columns_description': 'object', 'columns_directory': 'object', 'columns_gid': 'object', 'columns_gid_signed': 'object', 'columns_history_file': 'object', 'columns_shell': 'object', 'columns_uid_signed': 'object'}} CPU times: user 2min 45s, sys: 602 ms, total: 2min 46s Wall time: 2min 49s
print(json.dumps(qry_prov.schema, indent=2))
{ "pack_osquery-custom-pack2_processes": { "name": "object", "hostIdentifier": "object", "calendarTime": "object", "unixTime": "datetime64[ns]", "epoch": "int64", "counter": "int64", "numerics": "bool", "action": "object", "decorations_host_uuid": "object", "decorations_username": "object", "columns_cmdline": "object", "columns_euid": "object", "columns_name": "object", "columns_parent": "object", "columns_path": "object", "columns_pcmdline": "object", "columns_pid": "object", "columns_uid": "object", "columns_username": "object" }, "pack_osquery-custom-pack2_process_binding_to_ports": { "name": "object", "hostIdentifier": "object", "calendarTime": "object", "unixTime": "datetime64[ns]", "epoch": "int64", "counter": "int64", "numerics": "bool", "action": "object", "decorations_host_uuid": "object", "decorations_username": "object", "columns_name": "object", "columns_pid": "object", "columns_port": "object", "columns_protocol": "object" }, "pack_osquery-monitoring_osquery_info": { "name": "object", "hostIdentifier": "object", "calendarTime": "object", "unixTime": "datetime64[ns]", "epoch": "int64", "counter": "int64", "numerics": "bool", "action": "object", "decorations_host_uuid": "object", "decorations_username": "object", "columns_pid": "object", "columns_build_distro": "object", "columns_build_platform": "object", "columns_config_hash": "object", "columns_config_valid": "object", "columns_counter": "object", "columns_extensions": "object", "columns_instance_id": "object", "columns_platform_mask": "object", "columns_resident_size": "object", "columns_start_time": "object", "columns_system_time": "object", "columns_user_time": "object", "columns_uuid": "object", "columns_version": "object", "columns_watcher": "object" }, "pack_osquery-custom-pack2_outbound_connections": { "name": "object", "hostIdentifier": "object", "calendarTime": "object", "unixTime": "datetime64[ns]", "epoch": "int64", "counter": "int64", "numerics": "bool", "action": "object", "decorations_host_uuid": "object", "decorations_username": "object", "columns_cmdline": "object", "columns_name": "object", "columns_path": "object", "columns_pcmdline": "object", "columns_pid": "object", "columns_username": "object", "columns_local_port": "object", "columns_md5": "object", "columns_remote_address": "object", "columns_remote_port": "object" }, "pack_incident-response_mounts": { "name": "object", "hostIdentifier": "object", "calendarTime": "object", "unixTime": "datetime64[ns]", "epoch": "int64", "counter": "int64", "numerics": "bool", "action": "object", "decorations_host_uuid": "object", "decorations_username": "object", "columns_path": "object", "columns_blocks": "object", "columns_blocks_available": "object", "columns_blocks_free": "object", "columns_blocks_size": "object", "columns_device": "object", "columns_device_alias": "object", "columns_flags": "object", "columns_inodes": "object", "columns_inodes_free": "object", "columns_type": "object" }, "pack_osquery-custom-pack2_process_env": { "name": "object", "hostIdentifier": "object", "calendarTime": "object", "unixTime": "datetime64[ns]", "epoch": "int64", "counter": "int64", "numerics": "bool", "action": "object", "decorations_host_uuid": "object", "decorations_username": "object", "columns_cmdline": "object", "columns_pid": "object", "columns_key": "object", "columns_value": "object" }, "pack_incident-response_listening_ports": { "name": "object", "hostIdentifier": "object", "calendarTime": "object", "unixTime": "datetime64[ns]", "epoch": "int64", "counter": "int64", "numerics": "bool", "action": "object", "decorations_host_uuid": "object", "decorations_username": "object", "columns_path": "object", "columns_pid": "object", "columns_port": "object", "columns_protocol": "object", "columns_address": "object", "columns_family": "object", "columns_fd": "object", "columns_net_namespace": "object", "columns_socket": "object" }, "pack_osquery-monitoring_schedule": { "name": "object", "hostIdentifier": "object", "calendarTime": "object", "unixTime": "datetime64[ns]", "epoch": "int64", "counter": "int64", "numerics": "bool", "action": "object", "decorations_host_uuid": "object", "decorations_username": "object", "columns_name": "object", "columns_average_memory": "object", "columns_avg_system_time": "object", "columns_avg_user_time": "object", "columns_denylisted": "object", "columns_executions": "object", "columns_interval": "object", "columns_last_executed": "object", "columns_output_size": "object", "columns_wall_time": "object" }, "pack_incident-response_process_env": { "name": "object", "hostIdentifier": "object", "calendarTime": "object", "unixTime": "datetime64[ns]", "epoch": "int64", "counter": "int64", "numerics": "bool", "action": "object", "decorations_host_uuid": "object", "decorations_username": "object", "columns_pid": "object", "columns_key": "object", "columns_value": "object" }, "fim": { "name": "object", "hostIdentifier": "object", "calendarTime": "object", "unixTime": "datetime64[ns]", "epoch": "int64", "counter": "int64", "numerics": "bool", "action": "object", "decorations_host_uuid": "object", "decorations_username": "object", "columns_uid": "object", "columns_username": "object", "columns_md5": "object", "columns_action": "object", "columns_atime": "datetime64[ns]", "columns_category": "object", "columns_ctime": "datetime64[ns]", "columns_mode": "object", "columns_mtime": "datetime64[ns]", "columns_sha256": "object", "columns_size": "object", "columns_target_path": "object", "columns_time": "datetime64[ns]" }, "pack_incident-response_open_files": { "name": "object", "hostIdentifier": "object", "calendarTime": "object", "unixTime": "datetime64[ns]", "epoch": "int64", "counter": "int64", "numerics": "bool", "action": "object", "decorations_host_uuid": "object", "decorations_username": "object", "columns_path": "object", "columns_pid": "object" }, "pack_incident-response_last": { "name": "object", "hostIdentifier": "object", "calendarTime": "object", "unixTime": "datetime64[ns]", "epoch": "int64", "counter": "int64", "numerics": "bool", "action": "object", "decorations_host_uuid": "object", "decorations_username": "object", "columns_pid": "object", "columns_username": "object", "columns_type": "object", "columns_time": "datetime64[ns]", "columns_host": "object", "columns_tty": "object", "columns_type_name": "object" }, "pack_incident-response_logged_in_users": { "name": "object", "hostIdentifier": "object", "calendarTime": "object", "unixTime": "datetime64[ns]", "epoch": "int64", "counter": "int64", "numerics": "bool", "action": "object", "decorations_host_uuid": "object", "decorations_username": "object", "columns_cmdline": "object", "columns_name": "object", "columns_pid": "object", "columns_type": "object", "columns_time": "datetime64[ns]", "columns_host": "object", "columns_tty": "object", "columns_cwd": "object", "columns_root": "object", "columns_user": "object" }, "pack_osquery-custom-pack2_known_hosts": { "name": "object", "hostIdentifier": "object", "calendarTime": "object", "unixTime": "datetime64[ns]", "epoch": "int64", "counter": "int64", "numerics": "bool", "action": "object", "decorations_host_uuid": "object", "decorations_username": "object", "columns_uid": "object", "columns_key": "object", "columns_key_file": "object" }, "pack_incident-response_process_memory": { "name": "object", "hostIdentifier": "object", "calendarTime": "object", "unixTime": "datetime64[ns]", "epoch": "int64", "counter": "int64", "numerics": "bool", "action": "object", "decorations_host_uuid": "object", "decorations_username": "object", "columns_path": "object", "columns_pid": "object", "columns_device": "object", "columns_end": "object", "columns_inode": "object", "columns_offset": "object", "columns_permissions": "object", "columns_pseudo": "object", "columns_start": "object" }, "pack_vuln-management_deb_packages": { "name": "object", "hostIdentifier": "object", "calendarTime": "object", "unixTime": "datetime64[ns]", "epoch": "int64", "counter": "int64", "numerics": "bool", "action": "object", "decorations_host_uuid": "object", "decorations_username": "object", "columns_name": "object", "columns_version": "object", "columns_size": "object", "columns_admindir": "object", "columns_arch": "object", "columns_maintainer": "object", "columns_priority": "object", "columns_revision": "object", "columns_section": "object", "columns_source": "object", "columns_status": "object" }, "pack_incident-response_shell_history": { "name": "object", "hostIdentifier": "object", "calendarTime": "object", "unixTime": "datetime64[ns]", "epoch": "int64", "counter": "int64", "numerics": "bool", "action": "object", "decorations_host_uuid": "object", "decorations_username": "object", "columns_uid": "object", "columns_username": "object", "columns_uuid": "object", "columns_time": "datetime64[ns]", "columns_command": "object", "columns_description": "object", "columns_directory": "object", "columns_gid": "object", "columns_gid_signed": "object", "columns_history_file": "object", "columns_shell": "object", "columns_uid_signed": "object" } }
qry_prov.list_queries()
['file.deb_packages', 'file.fim', 'linux.deb_packages', 'linux.fim', 'linux.osquery_info', 'linux.outbound_connections', 'linux.process_binding_to_ports', 'linux.processes', 'linux.shell_history', 'network.outbound_connections', 'network.process_binding_to_ports', 'process.process_binding_to_ports', 'process.processes', 'shell.shell_history']
%%time
df_fim = qry_prov.linux.fim()
df_fim.head(1)
CPU times: user 2min 44s, sys: 26.8 ms, total: 2min 44s Wall time: 2min 45s
name | hostIdentifier | calendarTime | unixTime | epoch | counter | numerics | action | decorations_host_uuid | decorations_username | ... | columns_action | columns_atime | columns_category | columns_ctime | columns_mode | columns_mtime | columns_sha256 | columns_size | columns_target_path | columns_time | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
793 | fim | HOSTNAME | Fri Feb 3 11:52:32 2023 UTC | 1675425152 | 0 | 8 | False | added | F7E6787D-B2D8-4830-854E-33AF0A1338B8 | ... | DELETED | 1675425150 | roothome | 1675425150 | 0600 | 1675425150 | 30306 | /root/.viminfo | 1675425150 |
1 rows × 23 columns
%%time
df_process = qry_prov.linux.processes()
df_process.head(1)
CPU times: user 2min 46s, sys: 30.1 ms, total: 2min 46s Wall time: 2min 48s
name | hostIdentifier | calendarTime | unixTime | epoch | counter | numerics | action | decorations_host_uuid | decorations_username | columns_cmdline | columns_euid | columns_name | columns_parent | columns_path | columns_pcmdline | columns_pid | columns_uid | columns_username | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | pack_osquery-custom-pack2_processes | HOSTNAME | Fri Feb 3 06:28:25 2023 UTC | 1675405705 | 0 | 876 | False | removed | F7E6787D-B2D8-4830-854E-33AF0A1338B8 | /bin/sh /usr/local/scripts/audispd_report.sh | 102 | sudo | 54935 | sudo -u syslog /usr/local/scripts/audispd_repo... | 54940 | 102 | syslog |
%%time
df_outbound_conn = qry_prov.linux.outbound_connections()
df_outbound_conn.head(1)
CPU times: user 2min 43s, sys: 27.6 ms, total: 2min 43s Wall time: 2min 46s
name | hostIdentifier | calendarTime | unixTime | epoch | counter | numerics | action | decorations_host_uuid | decorations_username | columns_cmdline | columns_name | columns_path | columns_pcmdline | columns_pid | columns_username | columns_local_port | columns_md5 | columns_remote_address | columns_remote_port | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
90 | pack_osquery-custom-pack2_outbound_connections | HOSTNAME | Fri Feb 3 07:00:47 2023 UTC | 1675407647 | 0 | 59 | False | removed | F7E6787D-B2D8-4830-854E-33AF0A1338B8 | /usr/local/bin/prometheus --storage.tsdb.path=... | prometheus | /usr/local/bin/prometheus | /sbin/init | 1510 | prometheus | 34404 | 10.8.0.77 | 9100 |
# https://msticpy.readthedocs.io/en/latest/visualization/ProcessTree.html
from msticpy.vis import process_tree
from msticpy.transform.proc_tree_builder import OSQUERY_EVENT_SCH
p_tree_lx = process_tree.build_process_tree(df_process, schema=OSQUERY_EVENT_SCH)
# partial tree - 10 processes only
process_tree.plot_process_tree(data=df_process[50:60], legend_col="columns_name")
# FIXME! schema correct above but not here. time columns not datetime64
df_fim.dtypes
name object hostIdentifier object calendarTime object unixTime int64 epoch int64 counter int64 numerics bool action object decorations_host_uuid object decorations_username object columns_uid object columns_username object columns_md5 object columns_action object columns_atime object columns_category object columns_ctime object columns_mode object columns_mtime object columns_sha256 object columns_size object columns_target_path object columns_time object dtype: object
df_fim.mp_plot.timeline(
title="FIM by action",
# group_by="columns.action",
# group_by="columns.username",
group_by="columns_target_path",
source_columns=["columns_username", "columns_action", "columns_category", "columns_target_path"],
time_column="columns_time",
legend="left",
height=200,
)
df_outbound_conn.mp_plot.matrix(x="columns_name", y="columns_remote_address", title="Process name vs remote address Interaction")