{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Azure Sentinel Query Creator Notebook\r\n", "\r\n", "This utility notebook assists in the creation of Azure Sentinel analytics and hunting queries by providing an interactive method for completing the required elements of the query templates and then formating this data in to the YAML format required by the Azure Sentinel GitHub repo.\r\n", "\r\n", " - [Query Style Guide](https://github.com/Azure/Azure-Sentinel/wiki/Query-Style-Guide )\r\n", " - [Azure Sentinel Contribution Guidance](https://github.com/Azure/Azure-Sentinel/wiki/Contribute-to-Sentinel-GitHub-Community-of-Queries )\r\n", "\r\n", "From time to time this notebook may fall out of sync with Azure Sentinel data connectors and Mitre ATT&CK elements. This this is the case you can manually adjust created queries with the required elements but please also raise an issue or PR on [GitHub](https://github.com/Azure/Azure-Sentinel-Notebooks) so that we can get this notebook updated." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# Setup\r\n", "\r\n", "Run these setup cells before creating either an analytics or hunting query." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "import ipywidgets as widgets\r\n", "import uuid\r\n", "import re\r\n", "from ipywidgets import interact\r\n", "\r\n", "# Set connector options\r\n", "connectors = {\r\n", " \"AWS\":[\"AWSCloudTrail\"],\r\n", " \"AzureActiveDirectory\":[\"SigninLogs\", \"AuditLogs\"],\r\n", " \"AzureActiveDirectoryIdentityProtection\":[\"SecurityAlert (IPC)\"],\r\n", " \"AzureActivity\":[\"AzureActivity\"],\r\n", " \"AzureAdvancedThreatProtection\":[\"SecurityAlert (AATP)\"],\r\n", " \"AzureInformationProtection\":[\"InformationProtectionLogs_CL\", \"SecurityAlert (AIP)\"],\r\n", " \"AzureMonitor(IIS)\":[\"W3CIISLog\"],\r\n", " \"AzureMonitor(VMInsights)\":[\"VMConnection\"],\r\n", " \"AzureMonitor(WireData)\":[\"WireData\"],\r\n", " \"AzureSecurityCenter\":[\"SecurityAlert (ASC)\"],\r\n", " \"IoT\":[\"SecurityAlert (ASC for IoT)\"],\r\n", " \"BarracudaCloudFirewall\":[\"Syslog(Barracuda)\"],\r\n", " \"Barracuda\":[\"CommonSecurityLog (Barracuda)\", \"Barracuda_CL\"],\r\n", " \"CheckPoint\":[\"CommonSecurityLog (CheckPoint)\"],\r\n", " \"CiscoASA\":[\"CommonSecurityLog (Cisco)\"],\r\n", " \"Citrix\":[\"CitrixAnalytics_SAlerts_CL\"],\r\n", " \"CEF\":[\"CommonSecurityLog\"],\r\n", " \"CyberArk\":[\"CyberArk\"],\r\n", " \"DNS\":[\"DnsEvents\", \"DnsInventory\"],\r\n", " \"ExtraHopNetworks\":[\"CommonSecurityLog ('ExtraHop')\"],\r\n", " \"F5BigIp\":[\"F5Telemetry_LTM_CL\",\"F5Telemetry_system_CL\", \"F5Telemetry_ASM_CL\"],\r\n", " \"F5\":[\"CommonSecurityLog (F5)\"],\r\n", " \"Fortinet\":[\"CommonSecurityLog (Fortinet)\"],\r\n", " \"MicrosoftCloudAppSecurity\":[\"SecurityAlert (MCAS)\", \"McasShadowItReporting\"],\r\n", " \"MicrosoftDefenderAdvancedThreatProtection\":[\"SecurityAlert (MDATP)\"],\r\n", " \"WAF\":[\"AzureDiagnostics (Application Gateways)\"],\r\n", " \"Office365\":[\"OfficeActivity (SharePoint)\",\"OfficeActivity (Exchange)\", \"OfficeActivity (Teams)\"],\r\n", " \"OfficeATP\":[\"SecurityAlert (Office 365 Security & Compliance)\"],\r\n", " \"OneIdentity\":[\"CommonSecurityLog (OneIdentity)\"],\r\n", " \"PaloAltoNetworks\":[\"CommonSecurityLog (PaloAlto)\"],\r\n", " \"SecurityEvents\":[\"SecurityEvents\"],\r\n", " \"Symantec\":[\"SymantecICDx_CL\"],\r\n", " \"Syslog\":[\"Syslog\"],\r\n", " \"ThreatIntelligenceTaxii\":[\"ThreatIntelligenceIndicator\"],\r\n", " \"ThreatIntelligence\":[\"ThreatIntelligenceIndicator\"],\r\n", " \"TrendMicro\":[\"CommonSecurityLog (TrendMicroDeepSecurity)\"],\r\n", " \"WindowsEventForwarding\":[\"WindowsEvent\"],\r\n", " \"WindowsFireWall\":[\"WindowsFirewall\"],\r\n", " \"Zscaler\":[\"CommonSecurityLog (Zscaler)\"],\r\n", "}\r\n", "\r\n", "# Set Mitre ATT&CK categories\r\n", "mitre = [\"Reconnaissance\", \"ResourceDevelopment\", \"InitialAccess\", \"Execution\", \"Persistence\", \"PrivilegeEscalation\", \"DefenseEvasion\", \"CredentialAccess\", \"Discovery\", \"LateralMovement\", \"Collection\", \"CommandAndControl\",\"Exfiltration\", \"Impact\"]\r\n", "\r\n", "# Set entity identifiers\r\n", "entity_identifiers = {\"Account\": [\"Name\", \"FullName\", \"NTDomain\", \"DnsDomain\", \"UPNSuffix\", \"Sid\", \"AadTenantId\", \"AadUserId\", \"PUID\", \"IsDomainJoined\", \"DisplayName\", \"ObjectGuid\"],\r\n", " \"Host\"\t:[\"DnsDomain\", \"NTDomain\", \"HostName\", \"FullName\", \"NetBiosName\", 'AzureID', \"OMSAgentID\", \"OSFamily\", \"OSVersion\", 'IsDomainJoined'],\r\n", " 'IP'\t:['Address'],\r\n", " 'Malware':\t['Name', 'Category'],\r\n", " 'File':['Directory', 'Name'],\r\n", " 'Process'\t:['ProcessId', 'CommandLine', 'ElevationToken', 'CreationTimeUtc'],\r\n", " 'CloudApplication'\t:['AppId', 'Name', 'InstanceName'],\r\n", " 'DNS':\t[\"DomainName\"],\r\n", " 'AzureResource'\t:['ResourceId'],\r\n", " 'FileHash':\t['Algorithm', 'Value'],\r\n", " 'RegistryKey':\t['Hive', 'Key'],\r\n", " 'RegistryValue'\t: ['Name', 'Value', 'ValueType'],\r\n", " 'SecurityGroup'\t: ['DistinguishedName', 'SID', 'ObjectGuid'],\r\n", " 'URL'\t:['Url'],\r\n", " 'Mailbox': ['MailboxPrimaryAddress', 'DisplayName', 'Upn', 'ExternalDirectoryObjectId', 'RiskLevel'],\r\n", " 'MailCluster'\t: ['NetworkMessageIds', 'CountByDeliveryStatus', 'CountByThreatType', 'CountByProtectionStatus', 'Threats', 'Query', 'QueryTime', 'MailCount', 'IsVolumeAnomaly', 'Source', 'ClusterSourceIdentifier', 'ClusterSourceType', 'ClusterQueryStartTime', 'ClusterQueryEndTime', 'ClusterGroup'],\r\n", " 'MailMessage' :\t['Recipient', 'Urls', 'Threats', 'Sender', 'P1Sender', 'P1SenderDisplayName', 'P1SenderDomain', 'SenderIP', 'P2Sender', 'P2SenderDisplayName', 'P2SenderDomain', 'ReceivedDate', 'NetworkMessageId', 'InternetMessageId', 'Subject', 'BodyFingerprintBin1', 'BodyFingerprintBin2', 'BodyFingerprintBin3', 'BodyFingerprintBin4', 'BodyFingerprintBin5', 'AntispamDirection', 'DeliveryAction', 'DeliveryLocation', 'Language', 'ThreatDetectionMethods'],\r\n", " 'SubmissionMail':\t['NetworkMessageId', 'Timestamp', 'Recipient', 'Sender', 'SenderIp', 'Subject', 'ReportType', 'SubmissionId', 'SubmissionDate', 'Submitter']}" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "name_layout = widgets.Layout(width='150px')\r\n", "widget_layout = widgets.Layout(width='75%')\r\n", "\r\n", "# Select datatype from connector ID\r\n", "def create_dts():\r\n", " datatype = []\r\n", " for connector in RequiredDataConnectors.value:\r\n", " datatype.extend(connectors[connector])\r\n", " return datatype\r\n", "\r\n", "# Create widgets to collect query elements\r\n", "QueryName = widgets.HBox([widgets.Label('Query Name:' , layout=name_layout), \r\n", " widgets.Text(\r\n", " placeholder='The name of the query',\r\n", " disabled=False,\r\n", " layout=widget_layout\r\n", " )], \r\n", " layout=widget_layout)\r\n", "\r\n", "QueryDescription = widgets.HBox([widgets.Label('Query Description:' , layout=name_layout), \r\n", " widgets.Textarea(\r\n", " placeholder='A description of the query',\r\n", " disabled=False,\r\n", " layout=widget_layout,\r\n", " rows=10\r\n", " )], layout=widget_layout)\r\n", "\r\n", "Severity = widgets.HBox([widgets.Label('Severity:' , layout=name_layout), \r\n", " widgets.Dropdown(\r\n", " options=['Low', 'Medium', 'High'],\r\n", " value='Medium',\r\n", " disabled=False,\r\n", " layout=widget_layout\r\n", " )], \r\n", " layout=widget_layout)\r\n", "\r\n", "QueryPeriod = widgets.HBox([widgets.Label('Query Period (hours):' , layout=name_layout),\r\n", " widgets.IntSlider(\r\n", " value=168,\r\n", " min=0,\r\n", " max=336,\r\n", " step=1,\r\n", " disabled=False,\r\n", " orientation='horizontal',\r\n", " readout=True,\r\n", " layout=widget_layout\r\n", " )], \r\n", " layout=widget_layout)\r\n", "\r\n", "QueryFrequency = widgets.HBox([widgets.Label('Query Frequency (hours):', layout=name_layout),\r\n", " widgets.IntSlider(\r\n", " value=12,\r\n", " min=0,\r\n", " max=168,\r\n", " step=1,\r\n", " disabled=False,\r\n", " orientation='horizontal',\r\n", " readout=True,\r\n", " layout=widget_layout\r\n", " )], \r\n", " layout=widget_layout)\r\n", "\r\n", "TriggerOperator = widgets.HBox([widgets.Label('Trigger:', layout=name_layout),\r\n", " widgets.Dropdown(\r\n", " options=['gt', 'lt', 'eq'],\r\n", " value='gt',\r\n", " disabled=False,\r\n", " layout=widget_layout\r\n", " )], \r\n", " layout=widget_layout)\r\n", "\r\n", "TriggerThreshold = widgets.HBox([widgets.Label('Trigger Threshold:', layout=name_layout),\r\n", " widgets.IntSlider(\r\n", " value=1,\r\n", " min=0,\r\n", " max=10,\r\n", " step=1,\r\n", " disabled=False,\r\n", " orientation='horizontal',\r\n", " readout=True,\r\n", " layout=widget_layout\r\n", " )], \r\n", " layout=widget_layout)\r\n", "\r\n", "Tactics = widgets.HBox([widgets.Label('ATT&CK Tactics:', layout=name_layout),\r\n", " widgets.SelectMultiple(\r\n", " options=mitre,\r\n", " value=[mitre[0]],\r\n", " disabled=False,\r\n", " layout=widget_layout\r\n", " )], \r\n", " layout=widget_layout)\r\n", "\r\n", "Techniques = widgets.HBox([widgets.Label('ATT&CK Techniques:', layout=name_layout),\r\n", " widgets.Text(\r\n", " placeholder='T001, T001.1',\r\n", " disabled=False,\r\n", " layout=widget_layout\r\n", " )], \r\n", " layout=widget_layout)\r\n", "\r\n", "Query = widgets.HBox([widgets.Label('Query:', layout=name_layout),\r\n", " widgets.Textarea(\r\n", " placeholder='Table | take 10',\r\n", " disabled=False,\r\n", " layout=widget_layout,\r\n", " rows=20\r\n", " )], \r\n", " layout=widget_layout)\r\n", "\r\n", "EntityCount = widgets.HBox([widgets.Label('Number of Entities:', layout=name_layout),\r\n", " widgets.IntSlider(\r\n", " value=1,\r\n", " min=0,\r\n", " max=5,\r\n", " step=1,\r\n", " disabled=False,\r\n", " continuous_update=False,\r\n", " orientation='horizontal',\r\n", " layout=widget_layout\r\n", " )], \r\n", " layout=widget_layout)\r\n", "\r\n", "RequiredDataConnectors = widgets.SelectMultiple(\r\n", " options=list(connectors.keys()),\r\n", " value=[list(connectors.keys())[0]],\r\n", " disabled=False,\r\n", " descriptoin = \"Data Connectors\",\r\n", " layout=widget_layout\r\n", " )\r\n", "\r\n", "DataTypes = widgets.SelectMultiple(\r\n", " options=create_dts(),\r\n", " description='DataType',\r\n", " disabled=False,\r\n", " layout=widget_layout\r\n", " )" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# Create an Analytics Query\r\n", "\r\n", "Use the following cells to create an Azure Sentinel Analytics Query. Complete the widgets in the next cells with details of your analytics query and then run the cells below to create an analytics query in the required template format and then write it to disk.\r\n", "\r\n", "For hunting queries use the `Create a Hunting Query` section of this notebook." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Display widgets used to enter analytic query details\r\n", "print(\"Complete your query details:\")\r\n", "display(QueryName)\r\n", "display(QueryDescription)\r\n", "display(Severity)\r\n", "@interact(DataConnector = RequiredDataConnectors, dtype = DataTypes)\r\n", "def select_datatypes(DataConnector, dtype):\r\n", " DataTypes.options = create_dts()\r\n", "display(QueryPeriod)\r\n", "display(QueryFrequency)\r\n", "display(TriggerOperator)\r\n", "display(TriggerThreshold)\r\n", "display(Tactics)\r\n", "display(Techniques)\r\n", "display(Query)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "print(\"Select values to extract as Entities (max of 5): \")\r\n", "\r\n", "# Parse out any extended columns that might be entities that a user would want to extract\r\n", "lines = Query.children[1].value.split(\"|\")\r\n", "items = []\r\n", "for line in lines:\r\n", " if \"extend\" in line:\r\n", " items.extend(re.split(\" |=|,\", line))\r\n", "items = [i for i in items if i not in [None, \"extend\", \"timestamp\", \"\"]]\r\n", "set(items)\r\n", "\r\n", "# Let the user select columns they want to extract as entities\r\n", "ents = widgets.HBox([widgets.Label('Number of Entities:', layout=name_layout), \r\n", " widgets.SelectMultiple(\r\n", " options=list(items),\r\n", " disabled=False,\r\n", " layout=widget_layout\r\n", " )],\r\n", " layout=widget_layout)\r\n", "display(ents)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Have user select the entity type of each value selected for extraction\r\n", "for ent in ents.children[1].value:\r\n", " print(f\"Select the entity type of {ent}:\")\r\n", " globals()[ent+\"widget\"] = widgets.Dropdown(options=entity_identifiers.keys())\r\n", " display(globals()[ent+\"widget\"])" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Based on selected entities allow user to select the identifier type\r\n", "ent_mapping = []\r\n", "for ent in ents.children[1].value:\r\n", " ent_mapping.append({\"columnName\" : ent, \"entityType\" :globals()[ent+\"widget\"].value})\r\n", "\r\n", "for item in ent_mapping:\r\n", " ent=item['columnName']\r\n", " print(f\"Select identifier type for {item['columnName']} of type {item['entityType']}:\")\r\n", " globals()[ent+\"identwidget\"] = widgets.Dropdown(options=entity_identifiers[item['entityType']])\r\n", " display(globals()[ent+\"identwidget\"])" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Generate unique GUID for the template\r\n", "q_guid = str(uuid.uuid4())\r\n", "# Get and format required data connectors and associated data types\r\n", "connector_id = \"\"\r\n", "if len(RequiredDataConnectors.value) == 0:\r\n", " connector_id += \"[]\"\r\n", "elif len(RequiredDataConnectors.value) == 1:\r\n", " connector_id += f\"- connectorId: {RequiredDataConnectors.value[0]}\"\r\n", " connector_id += \"\"\"\r\n", " dataTypes:\"\"\"\r\n", " for dtype in DataTypes.value:\r\n", " connector_id += f\"\"\"\r\n", " - {dtype}\"\"\"\r\n", "else:\r\n", " i = 0\r\n", " for conn in RequiredDataConnectors.value:\r\n", " if i == 0:\r\n", " connector_id += f\"- connectorId: {conn}\"\r\n", " else:\r\n", " connector_id += f\"\"\"\r\n", " - connectorId: {conn}\"\"\"\r\n", " connector_id += \"\"\"\r\n", " dataTypes:\"\"\"\r\n", " i+=1\r\n", " for dtype in DataTypes.value:\r\n", " if dtype in connectors[conn]:\r\n", " connector_id += f\"\"\"\r\n", " - {dtype}\"\"\"\r\n", "\r\n", "# Based on query period determine whether days or hours are best used \r\n", "if int(QueryFrequency.children[1].value/24) > 0:\r\n", " qfreq_val = f\"{int(QueryFrequency.children[1].value/24)}d\"\r\n", "else:\r\n", " qfreq_val = f\"{QueryFrequency.children[1].value}h\"\r\n", "if int(QueryPeriod.children[1].value/24) > 0:\r\n", " qperiod_val = f\"{int(QueryPeriod.children[1].value/24)}d\"\r\n", "else:\r\n", " qperiod_val = f\"{QueryPeriod.children[1].value}h\"\r\n", "\r\n", "# Get and format Mitre ATT&CK tactics and techniques\r\n", "att_tactics = \"tactics:\"\r\n", "for tact in Tactics.children[1].value:\r\n", " att_tactics += f\"\"\"\r\n", " - {tact}\"\"\"\r\n", "technique_ids = \"relevantTechniques:\"\r\n", "for tech in Techniques.children[1].value.split(\",\"):\r\n", " technique_ids += f\"\"\"\r\n", " - {tech.strip()}\"\"\"\r\n", "\r\n", "#Get and format entity mapping\r\n", "for ent in ents.children[1].value:\r\n", " for entm in ent_mapping:\r\n", " if entm['columnName'] == ent:\r\n", " entm.update({\"identifier\" :globals()[ent+\"identwidget\"].value})\r\n", "\r\n", "ent_ids = \"entityMappings:\"\r\n", "for ent in ent_mapping:\r\n", " ent_ids += f\"\"\"\r\n", " - entityType: {ent['entityType']}\r\n", " fieldMappings:\r\n", " - identifier: {ent['identifier']}\r\n", " columnName: {ent['columnName']}\"\"\"\r\n", " \r\n", "analytic_body = f\"\"\"id: {q_guid}\r\n", "name: {QueryName.children[1].value}\r\n", "description: |\r\n", " '{QueryDescription.children[1].value}'\r\n", "severity: {Severity.children[1].value}\r\n", "requiredDataConnectors:\r\n", " {connector_id}\r\n", "queryFrequency: {qfreq_val}\r\n", "queryPeriod: {qperiod_val}\r\n", "triggerOperator: {TriggerOperator.children[1].value}\r\n", "triggerThreshold: {TriggerThreshold.children[1].value}\r\n", "{att_tactics}\r\n", "{technique_ids}\r\n", "query: |\r\n", " {Query.children[1].value}\r\n", "{ent_ids}\r\n", "\"\"\"\r\n", "print(\"Your analytics query:\\n\")\r\n", "print(analytic_body)\r\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Write file to disk with the name of the query\r\n", "file_name = QueryName.children[1].value.replace(\" \",'') \r\n", "with open(f\"{file_name}.yaml\", \"x\") as qfile:\r\n", " qfile.write(analytic_body)\r\n", " print(f\"Query written to {file_name}.yaml\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# Create a Hunting Query\r\n", "\r\n", "Use the following cells to create an Azure Sentinel Hunting Query. Complete the widgets in the next cells with details of your analytics query and then run the cells below to create an analytics query in the required template format and then write it to disk.\r\n", "\r\n", "For analytics queries use the `Create an Analytics Query` section of this notebook." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Display widgets for the elements required in the Hunting Query template\r\n", "print(\"Complete your query details:\")\r\n", "display(QueryName)\r\n", "display(QueryDescription)\r\n", "@interact(dconnector = RequiredDataConnectors, dtype = DataTypes)\r\n", "def select_datatypes(dconnector, dtype):\r\n", " DataTypes.options = create_dts()\r\n", "display(Tactics)\r\n", "display(Techniques)\r\n", "display(Query)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "print(\"Select values to extract as Entities (max of 5): \")\r\n", "\r\n", "# Parse out any extended columns that might be entities that a user would want to extract\r\n", "lines = Query.children[1].value.split(\"|\")\r\n", "items = []\r\n", "for line in lines:\r\n", " if \"extend\" in line:\r\n", " items.extend(re.split(\" |=|,\", line))\r\n", "items = [i for i in items if i not in [None, \"extend\", \"timestamp\", \"\"]]\r\n", "set(items)\r\n", "\r\n", "# Let the user select columns they want to extract as entities\r\n", "ents = widgets.HBox([widgets.Label('Number of Entities:', layout=name_layout), \r\n", " widgets.SelectMultiple(\r\n", " options=list(items),\r\n", " disabled=False,\r\n", " layout=widget_layout\r\n", " )],\r\n", " layout=widget_layout)\r\n", "display(ents)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Have user select the entity type of each value selected for extraction\r\n", "for ent in ents.children[1].value:\r\n", " print(f\"Select the entity type of {ent}:\")\r\n", " globals()[ent+\"widget\"] = widgets.Dropdown(options=entity_identifiers.keys())\r\n", " display(globals()[ent+\"widget\"])" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Based on selected entities allow user to select the identifier type\r\n", "ent_mapping = []\r\n", "for ent in ents.children[1].value:\r\n", " ent_mapping.append({\"columnName\" : ent, \"entityType\" :globals()[ent+\"widget\"].value})\r\n", "\r\n", "for item in ent_mapping:\r\n", " ent=item['columnName']\r\n", " print(f\"Select identifier type for {item['columnName']} of type {item['entityType']}:\")\r\n", " globals()[ent+\"identwidget\"] = widgets.Dropdown(options=entity_identifiers[item['entityType']])\r\n", " display(globals()[ent+\"identwidget\"])" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Generate unique GUID for the template\r\n", "q_guid = str(uuid.uuid4())\r\n", "# Get and format Data Connector and Data Type elemtns\r\n", "connector_id = \"\"\r\n", "if len(RequiredDataConnectors.value) == 0:\r\n", " connector_id += \"[]\"\r\n", "elif len(RequiredDataConnectors.value) == 1:\r\n", " connector_id += f\"- connectorId: {RequiredDataConnectors.value[0]}\"\r\n", " connector_id += \"\"\"\r\n", " dataTypes:\"\"\"\r\n", " for dtype in DataTypes.value:\r\n", " connector_id += f\"\"\"\r\n", " - {dtype}\"\"\"\r\n", "else:\r\n", " i = 0\r\n", " for conn in RequiredDataConnectors.children[1].value:\r\n", " if i == 0:\r\n", " connector_id += f\"- connectorId: {conn}\"\r\n", " else:\r\n", " connector_id += f\"\"\"\r\n", " - connectorId: {conn}\"\"\"\r\n", " connector_id += \"\"\"\r\n", " dataTypes:\"\"\"\r\n", " i+=1\r\n", " for dtype in DataTypes.children[1].value:\r\n", " if dtype in connectors[conn]:\r\n", " connector_id += f\"\"\"\r\n", " - {dtype}\"\"\"\r\n", "\r\n", "# Get and format Mitre ATT&CK tactics and techniques\r\n", "att_tactics = \"tactics:\"\r\n", "for tact in Tactics.children[1].value:\r\n", " att_tactics += f\"\"\"\r\n", " - {tact}\"\"\"\r\n", "technique_ids = \"relevantTechniques:\"\r\n", "for tech in Techniques.children[1].value.split(\",\"):\r\n", " technique_ids += f\"\"\"\r\n", " - {tech.strip()}\"\"\"\r\n", "\r\n", "# Build out entity mapps \r\n", "for ent in ents.children[1].value:\r\n", " for entm in ent_mapping:\r\n", " if entm['columnName'] == ent:\r\n", " entm.update({\"identifier\" :globals()[ent+\"identwidget\"].value})\r\n", "\r\n", "ent_ids = \"entityMappings:\"\r\n", "for ent in ent_mapping:\r\n", " ent_ids += f\"\"\"\r\n", " - entityType: {ent['entityType']}\r\n", " fieldMappings:\r\n", " - identifier: {ent['identifier']}\r\n", " columnName: {ent['columnName']}\"\"\"\r\n", " \r\n", "# Write out populated template body\r\n", "analytic_body = f\"\"\"id: {q_guid}\r\n", "name: {QueryName.children[1].value}\r\n", "description: |\r\n", " '{QueryDescription.children[1].value}'\r\n", "requiredDataConnectors:\r\n", " {connector_id}\r\n", "{att_tactics}\r\n", "{technique_ids}\r\n", "query: |\r\n", " {Query.children[1].value}\r\n", "{ent_ids}\r\n", "\"\"\"\r\n", "print(\"Your hunting query:\\n\")\r\n", "print(analytic_body)\r\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Write the query file to disk with using the query title as the file name\r\n", "file_name = QueryName.children[1].value.replace(\" \",'') \r\n", "with open(f\"{file_name}.yaml\", \"x\") as qfile:\r\n", " qfile.write(analytic_body)\r\n", " print(f\"Query written to {file_name}.yaml\")" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3.8 - AzureML", "language": "python", "name": "python38-azureml" }, "language_info": { "name": "python", "version": "" }, "widgets": { "application/vnd.jupyter.widget-state+json": { "state": { "04a7427f23114b718c417fa9abf3f1ff": { "model_module": "@jupyter-widgets/base", "model_module_version": "1.2.0", "model_name": "LayoutModel", "state": {} }, "0564b9fab7ef49f58fdf8221fa1a90ef": { "model_module": "@jupyter-widgets/controls", "model_module_version": "1.5.0", "model_name": "SliderStyleModel", "state": { "description_width": "" } }, "0918038e90c746b7bb22f5358c8d00ce": { "model_module": "@jupyter-widgets/base", "model_module_version": "1.2.0", "model_name": "LayoutModel", "state": { "width": "75%" } }, "1cc4cb95749747fab73a8abb6cdbd1e1": { "model_module": "@jupyter-widgets/controls", "model_module_version": "1.5.0", "model_name": "DescriptionStyleModel", "state": { "description_width": "" } }, "2462306127924c7f8b492d5ed5c5e489": { "model_module": "@jupyter-widgets/controls", "model_module_version": "1.5.0", "model_name": "IntSliderModel", "state": { "description": "Query Frequency (hours):", "layout": "IPY_MODEL_7f2e46df02b947e98227918915fcc2ce", "max": 168, "style": "IPY_MODEL_5aa88a89fff3429197a6290e3baaefd9", "value": 1 } }, "28ca1cff49af4f1984742f30e8c79421": { "model_module": "@jupyter-widgets/controls", "model_module_version": "1.5.0", "model_name": "TextareaModel", "state": { "description": "String:", "layout": "IPY_MODEL_c96a323de09c4e9eb8bbf68e47534d56", "placeholder": "A description of the query", "rows": 10, "style": "IPY_MODEL_3523d282c3d7439192a9248309c86bd0", "value": "This query uses Windows Event ID 5136 in order to detect potential webshell deployment by exploitation of CVE-2021-27065. This query looks for changes to the InternalHostName or ExternalHostName properties of Exchange OAB Virtual Directory objects in AD Directory Services where the new objects contain potential webshell objects. Ref: https://aka.ms/ExchangeVulns" } }, "296621ed5e154f8b8aeb496d29d6fab7": { "model_module": "@jupyter-widgets/controls", "model_module_version": "1.5.0", "model_name": "VBoxModel", "state": { "_dom_classes": [ "widget-interact" ], "children": [ "IPY_MODEL_75c8374c27914d75a196d41505c6d037", "IPY_MODEL_c649a988e4be40f9815e69d05265dde4", "IPY_MODEL_ba968bf4310741fbaa4714c3c89c68a7" ], "layout": "IPY_MODEL_3a5f668a428a4455b1babe96bf791bf8" } }, "2c97f734ebe14588bd8c42bbc3c2232f": { "model_module": "@jupyter-widgets/base", "model_module_version": "1.2.0", "model_name": "LayoutModel", "state": { "width": "75%" } }, "2d62aedcd2a34bceb0d0ce3e9b3b0215": { "model_module": "@jupyter-widgets/base", "model_module_version": "1.2.0", "model_name": "LayoutModel", "state": {} }, "334852e3f33a490abd483cc56ed48956": { "model_module": "@jupyter-widgets/base", "model_module_version": "1.2.0", "model_name": "LayoutModel", "state": { "width": "75%" } }, "3497245005a242c0866a0709d3b76ea6": { "model_module": "@jupyter-widgets/base", "model_module_version": "1.2.0", "model_name": "LayoutModel", "state": {} }, "3523d282c3d7439192a9248309c86bd0": { "model_module": "@jupyter-widgets/controls", "model_module_version": "1.5.0", "model_name": "DescriptionStyleModel", "state": { "description_width": "" } }, "3a5f668a428a4455b1babe96bf791bf8": { "model_module": "@jupyter-widgets/base", "model_module_version": "1.2.0", "model_name": "LayoutModel", "state": {} }, "3c2aa87efd8a494a80808b828be32f72": { "model_module": "@jupyter-widgets/controls", "model_module_version": "1.5.0", "model_name": "DescriptionStyleModel", "state": { "description_width": "" } }, "3f383d51014e49649159ecaa40230340": { "model_module": "@jupyter-widgets/base", "model_module_version": "1.2.0", "model_name": "LayoutModel", "state": { "width": "75%" } }, "44f9260ada7f41babd2ade7d5a32774b": { "model_module": "@jupyter-widgets/controls", "model_module_version": "1.5.0", "model_name": "DropdownModel", "state": { "_options_labels": [ "gt", "lt", "eq" ], "description": "Trigger:", "index": 0, "layout": "IPY_MODEL_dd3d3deced4c42e9af3c0865f22d2b73", "style": "IPY_MODEL_3c2aa87efd8a494a80808b828be32f72" } }, "486ff814a28c43b5973b78f21bdb1786": { "model_module": "@jupyter-widgets/controls", "model_module_version": "1.5.0", "model_name": "DescriptionStyleModel", "state": { "description_width": "" } }, "4b6763e33aef463e955a61473d4d537e": { "model_module": "@jupyter-widgets/controls", "model_module_version": "1.5.0", "model_name": "DescriptionStyleModel", "state": { "description_width": "" } }, "5aa88a89fff3429197a6290e3baaefd9": { "model_module": "@jupyter-widgets/controls", "model_module_version": "1.5.0", "model_name": "SliderStyleModel", "state": { "description_width": "" } }, "5c78b12becf74402b11c8eebf8a36d77": { "model_module": "@jupyter-widgets/controls", "model_module_version": "1.5.0", "model_name": "DescriptionStyleModel", "state": { "description_width": "" } }, "5d616c17ce584cbd87878625405ce437": { "model_module": "@jupyter-widgets/controls", "model_module_version": "1.5.0", "model_name": "DescriptionStyleModel", "state": { "description_width": "" } }, "5e1ca083875346b1ad9e02539b3c08bf": { "model_module": "@jupyter-widgets/controls", "model_module_version": "1.5.0", "model_name": "DescriptionStyleModel", "state": { "description_width": "" } }, "604f0b9f32d64c7ab1bf6136c7fa6aac": { "model_module": "@jupyter-widgets/controls", "model_module_version": "1.5.0", "model_name": "DescriptionStyleModel", "state": { "description_width": "" } }, "666fd04b648540e7a66963da45d962fa": { "model_module": "@jupyter-widgets/controls", "model_module_version": "1.5.0", "model_name": "DropdownModel", "state": { "_options_labels": [ "Account", "Host", "IP", "Malware", "File", "Process", "CloudApplication", "DNS", "AzureResource", "FileHash", "RegistryKey", "RegistryValue", "SecurityGroup", "URL", "Mailbox", "MailCluster", "MailMessage", "SubmissionMail" ], "index": 1, "layout": "IPY_MODEL_3497245005a242c0866a0709d3b76ea6", "style": "IPY_MODEL_604f0b9f32d64c7ab1bf6136c7fa6aac" } }, "69969a22c00146219e337375518eccd2": { "model_module": "@jupyter-widgets/controls", "model_module_version": "1.5.0", "model_name": "SelectMultipleModel", "state": { "_options_labels": [ "Reconnaissance", "ResourceDevelopment", "InitialAccess", "Execution", "Persistence", "PrivilegeEscalation", "DefenseEvasion", "CredentialAccess", "Discovery", "LateralMovement", "Collection", "CommandAndControl", "Exfiltration", "Impact" ], "description": "ATT&CK Tactics:", "index": [ 2 ], "layout": "IPY_MODEL_0918038e90c746b7bb22f5358c8d00ce", "rows": 5, "style": "IPY_MODEL_6a583b0e608b4e5389df88d0270a541f" } }, "6a583b0e608b4e5389df88d0270a541f": { "model_module": "@jupyter-widgets/controls", "model_module_version": "1.5.0", "model_name": "DescriptionStyleModel", "state": { "description_width": "" } }, "75c8374c27914d75a196d41505c6d037": { "model_module": "@jupyter-widgets/controls", "model_module_version": "1.5.0", "model_name": "SelectMultipleModel", "state": { "_options_labels": [ "AWS", "AzureActiveDirectory", "AzureActiveDirectoryIdentityProtection", "AzureActivity", "AzureAdvancedThreatProtection", "AzureInformationProtection", "AzureMonitor(IIS)", "AzureMonitor(VMInsights)", "AzureMonitor(WireData)", "AzureSecurityCenter", "IoT", "BarracudaCloudFirewall", "Barracuda", "CheckPoint", "CiscoASA", "Citrix", "CEF", "CyberArk", "DNS", "ExtraHopNetworks", "F5BigIp", "F5", "Fortinet", "MicrosoftCloudAppSecurity", "MicrosoftDefenderAdvancedThreatProtection", "WAF", "Office365", "OfficeATP", "OneIdentity", "PaloAltoNetworks", "SecurityEvents", "Symantec", "Syslog", "ThreatIntelligenceTaxii", "ThreatIntelligence", "TrendMicro", "WindowsEventForwarding", "WindowsFireWall", "Zscaler" ], "description": "Data Connectors", "index": [ 30 ], "layout": "IPY_MODEL_3f383d51014e49649159ecaa40230340", "rows": 5, "style": "IPY_MODEL_5d616c17ce584cbd87878625405ce437" } }, "7cfacb66f52748f5a4eb803f1a0e70c5": { "model_module": "@jupyter-widgets/controls", "model_module_version": "1.5.0", "model_name": "DescriptionStyleModel", "state": { "description_width": "" } }, "7f2e46df02b947e98227918915fcc2ce": { "model_module": "@jupyter-widgets/base", "model_module_version": "1.2.0", "model_name": "LayoutModel", "state": { "width": "75%" } }, "81f7830e6763427499ddee2242a6a48d": { "model_module": "@jupyter-widgets/base", "model_module_version": "1.2.0", "model_name": "LayoutModel", "state": { "width": "75%" } }, "854f9bcbe6ba4c21a265434512c816b9": { "model_module": "@jupyter-widgets/controls", "model_module_version": "1.5.0", "model_name": "TextModel", "state": { "description": "String:", "layout": "IPY_MODEL_f2bfc1f2bb3345799a7f31e070cce144", "placeholder": "The name of the query", "style": "IPY_MODEL_4b6763e33aef463e955a61473d4d537e", "value": "Exchange OAB Virtual Directory Attribute Containing Potential Webshell" } }, "8e22d0195c66445d88d15827aa4a53bf": { "model_module": "@jupyter-widgets/base", "model_module_version": "1.2.0", "model_name": "LayoutModel", "state": {} }, "90005b0bd56b4cea8dab78f431d3b3f8": { "model_module": "@jupyter-widgets/controls", "model_module_version": "1.5.0", "model_name": "DropdownModel", "state": { "_options_labels": [ "DnsDomain", "NTDomain", "HostName", "FullName", "NetBiosName", "AzureID", "OMSAgentID", "OSFamily", "OSVersion", "IsDomainJoined" ], "index": 3, "layout": "IPY_MODEL_d17f193a3111499b98a082527d5ff207", "style": "IPY_MODEL_abe626e325c04fb992ca1bf8f21643f3" } }, "9d34a4a6ecfe49a5a4b1e4e0aa85bae1": { "model_module": "@jupyter-widgets/controls", "model_module_version": "1.5.0", "model_name": "DropdownModel", "state": { "_options_labels": [ "Name", "FullName", "NTDomain", "DnsDomain", "UPNSuffix", "Sid", "AadTenantId", "AadUserId", "PUID", "IsDomainJoined", "DisplayName", "ObjectGuid" ], "index": 1, "layout": "IPY_MODEL_2d62aedcd2a34bceb0d0ce3e9b3b0215", "style": "IPY_MODEL_486ff814a28c43b5973b78f21bdb1786" } }, "a08f8d47a195494abcb70ece0b6e930d": { "model_module": "@jupyter-widgets/controls", "model_module_version": "1.5.0", "model_name": "TextareaModel", "state": { "description": "Query:", "layout": "IPY_MODEL_334852e3f33a490abd483cc56ed48956", "placeholder": "Table | take 10", "rows": 20, "style": "IPY_MODEL_5e1ca083875346b1ad9e02539b3c08bf", "value": "SecurityEvent\n// Look for specific Directory Service Changes\n| where EventID == 5136\n| extend Data = parse_xml(EventData)\n| extend ObjectClass = tostring( Data.EventData.Data[10].[\"#text\"])\n// Where changes relate to Exchange OAB\n| where ObjectClass =~ \"msExchOABVirtualDirectory\"\n| extend AttributeLDAPDisplayName = tostring( Data.EventData.Data[11].[\"#text\"])\n// Look for InternalHostName or ExternalHostName properties being changed\n| where AttributeLDAPDisplayName in (\"msExchExternalHostName\", \"msExchInternalHostName\")\n| extend DN = tostring(Data.EventData.Data[8].[\"#text\"])\n| extend AttributeValue = tostring(Data.EventData.Data[13].[\"#text\"])\n// Look for suspected webshell activity\n| where AttributeValue has \"script\"\n| project-reorder TimeGenerated, Computer, Account, DN, AttributeLDAPDisplayName, AttributeValue\n| extend timestamp = StartTime, AccountCustomEntity = Account, HostCustomEntity = Computer" } }, "a6697c75d3ab4163a2c24c1394519c82": { "model_module": "@jupyter-widgets/controls", "model_module_version": "1.5.0", "model_name": "DescriptionStyleModel", "state": { "description_width": "" } }, "abe626e325c04fb992ca1bf8f21643f3": { "model_module": "@jupyter-widgets/controls", "model_module_version": "1.5.0", "model_name": "DescriptionStyleModel", "state": { "description_width": "" } }, "af414585afc24bc6b5f0394353260b76": { "model_module": "@jupyter-widgets/base", "model_module_version": "1.2.0", "model_name": "LayoutModel", "state": { "width": "75%" } }, "b67ff40ec8464dde91cb242421ff2647": { "model_module": "@jupyter-widgets/controls", "model_module_version": "1.5.0", "model_name": "IntSliderModel", "state": { "description": "Trigger Threshold:", "layout": "IPY_MODEL_f51b4246ed1b4fa7b370cff443e306a4", "max": 10, "style": "IPY_MODEL_fe6724c0fecd4dd7a2e20f10bd939fcd" } }, "b7fc41432f3045cca3be355f453911af": { "model_module": "@jupyter-widgets/base", "model_module_version": "1.2.0", "model_name": "LayoutModel", "state": { "width": "75%" } }, "ba968bf4310741fbaa4714c3c89c68a7": { "model_module": "@jupyter-widgets/output", "model_module_version": "1.0.0", "model_name": "OutputModel", "state": { "layout": "IPY_MODEL_04a7427f23114b718c417fa9abf3f1ff" } }, "bc13534bb8354e3faebdfbccf4c906b2": { "model_module": "@jupyter-widgets/controls", "model_module_version": "1.5.0", "model_name": "IntSliderModel", "state": { "continuous_update": false, "description": "Number of Entities:", "layout": "IPY_MODEL_81f7830e6763427499ddee2242a6a48d", "max": 5, "style": "IPY_MODEL_e4f0358e51dd4342b80821b17144e0c6", "value": 1 } }, "c649a988e4be40f9815e69d05265dde4": { "model_module": "@jupyter-widgets/controls", "model_module_version": "1.5.0", "model_name": "SelectMultipleModel", "state": { "_options_labels": [ "SecurityEvents" ], "description": "DataType", "index": [ 0 ], "layout": "IPY_MODEL_b7fc41432f3045cca3be355f453911af", "rows": 5, "style": "IPY_MODEL_5c78b12becf74402b11c8eebf8a36d77" } }, "c96a323de09c4e9eb8bbf68e47534d56": { "model_module": "@jupyter-widgets/base", "model_module_version": "1.2.0", "model_name": "LayoutModel", "state": { "width": "75%" } }, "cf2b046de2f34aabb883f99cb04ec86b": { "model_module": "@jupyter-widgets/controls", "model_module_version": "1.5.0", "model_name": "DropdownModel", "state": { "_options_labels": [ "Low", "Medium", "High" ], "description": "Severity:", "index": 2, "layout": "IPY_MODEL_2c97f734ebe14588bd8c42bbc3c2232f", "style": "IPY_MODEL_7cfacb66f52748f5a4eb803f1a0e70c5" } }, "cf79c03005764aadae91d90d861b3d18": { "model_module": "@jupyter-widgets/base", "model_module_version": "1.2.0", "model_name": "LayoutModel", "state": { "width": "75%" } }, "d17f193a3111499b98a082527d5ff207": { "model_module": "@jupyter-widgets/base", "model_module_version": "1.2.0", "model_name": "LayoutModel", "state": {} }, "d5238f1ce17242528c2e3748cc9010f6": { "model_module": "@jupyter-widgets/controls", "model_module_version": "1.5.0", "model_name": "SelectMultipleModel", "state": { "_options_labels": [ "extend", "Data", "parse_xml(EventData)\n", "extend", "ObjectClass", "tostring(", "Data.EventData.Data[10].[\"#text\"])\n//", "Where", "changes", "relate", "to", "Exchange", "OAB\n", "extend", "AttributeLDAPDisplayName", "tostring(", "Data.EventData.Data[11].[\"#text\"])\n//", "Look", "for", "InternalHostName", "or", "ExternalHostName", "properties", "being", "changed\n", "extend", "DN", "tostring(Data.EventData.Data[8].[\"#text\"])\n", "extend", "AttributeValue", "tostring(Data.EventData.Data[13].[\"#text\"])\n//", "Look", "for", "suspected", "webshell", "activity\n", "extend", "timestamp", "StartTime", "AccountCustomEntity", "Account", "HostCustomEntity", "Computer" ], "description": "Select Entities", "index": [ 39, 41 ], "layout": "IPY_MODEL_e7d6f520cc7a46d4af02bc0bf8938936", "rows": 5, "style": "IPY_MODEL_1cc4cb95749747fab73a8abb6cdbd1e1" } }, "dd3d3deced4c42e9af3c0865f22d2b73": { "model_module": "@jupyter-widgets/base", "model_module_version": "1.2.0", "model_name": "LayoutModel", "state": { "width": "75%" } }, "dfdc7fda0a27437ca57bacdea945b985": { "model_module": "@jupyter-widgets/controls", "model_module_version": "1.5.0", "model_name": "DescriptionStyleModel", "state": { "description_width": "" } }, "e4f0358e51dd4342b80821b17144e0c6": { "model_module": "@jupyter-widgets/controls", "model_module_version": "1.5.0", "model_name": "SliderStyleModel", "state": { "description_width": "" } }, "e7d6f520cc7a46d4af02bc0bf8938936": { "model_module": "@jupyter-widgets/base", "model_module_version": "1.2.0", "model_name": "LayoutModel", "state": {} }, "ee472307b150430ca05f374164c682d2": { "model_module": "@jupyter-widgets/controls", "model_module_version": "1.5.0", "model_name": "IntSliderModel", "state": { "description": "Query Period (hours):", "layout": "IPY_MODEL_cf79c03005764aadae91d90d861b3d18", "max": 336, "style": "IPY_MODEL_0564b9fab7ef49f58fdf8221fa1a90ef", "value": 1 } }, "f2bfc1f2bb3345799a7f31e070cce144": { "model_module": "@jupyter-widgets/base", "model_module_version": "1.2.0", "model_name": "LayoutModel", "state": { "width": "75%" } }, "f3effbb2b3194e75a3bed73e0dcd6b84": { "model_module": "@jupyter-widgets/controls", "model_module_version": "1.5.0", "model_name": "DropdownModel", "state": { "_options_labels": [ "Account", "Host", "IP", "Malware", "File", "Process", "CloudApplication", "DNS", "AzureResource", "FileHash", "RegistryKey", "RegistryValue", "SecurityGroup", "URL", "Mailbox", "MailCluster", "MailMessage", "SubmissionMail" ], "index": 0, "layout": "IPY_MODEL_8e22d0195c66445d88d15827aa4a53bf", "style": "IPY_MODEL_a6697c75d3ab4163a2c24c1394519c82" } }, "f40a6a190a5a4d03bb91cbee609e17f7": { "model_module": "@jupyter-widgets/controls", "model_module_version": "1.5.0", "model_name": "TextModel", "state": { "description": "ATT&CK Techniques:", "layout": "IPY_MODEL_af414585afc24bc6b5f0394353260b76", "placeholder": "T001, T001.1", "style": "IPY_MODEL_dfdc7fda0a27437ca57bacdea945b985", "value": "T1190" } }, "f51b4246ed1b4fa7b370cff443e306a4": { "model_module": "@jupyter-widgets/base", "model_module_version": "1.2.0", "model_name": "LayoutModel", "state": { "width": "75%" } }, "fe6724c0fecd4dd7a2e20f10bd939fcd": { "model_module": "@jupyter-widgets/controls", "model_module_version": "1.5.0", "model_name": "SliderStyleModel", "state": { "description_width": "" } } }, "version_major": 2, "version_minor": 0 } } }, "nbformat": 4, "nbformat_minor": 4 }