Big thank you to @Jing Nghik, for coauthoring this solution.
To keep the learning going, the Intro to KQL interactive workbook has been updated with more content. This content introduces 15 new operators for:
- Strings
- Anomalies
- Miscellaneous Uses
Additionally, this update introduces ways for users to add their own exercises and data for learning and testing.
New Content
Strings
- Strcat
- Extract
- Split
- Substring
- Parse
Anomaly
- Materialize
- Basket
- Diffpatterns
- Autocluster
- Make-series
Miscellaneous
- Getschema
- isBillable
- BilledSize
- isEmpty
- User Function
Inserting Content and Questions
The content for the workbook is contained within the Microsoft Sentinel GitHub repository. For a user to add their own content and questions, it will require 2 actions.
- Clone the repository.
- Redirect the Intro to KQL workbook to the cloned repository.
Cloning the Repository
The main Azure/Azure-Sentinel repository will need to be cloned as a local repository for customization of the content. To clone the repository, please follow steps on the official GitHub documentation.
Redirecting the Workbook
Redirecting the workbook is a simple process. The workbook has a parameter that points to a GitHub repository that all of the parameters will utilize to pull data. The parameter is called ‘GitHub Repository’. This is the only parameter that needs to be swapped. To do so:
- Go into the Intro to KQL workbook.
- Go into edit mode.
- Within the GitHub Repository parameter, enter the GitHub repository path that leads to the content needed for the Intro to KQL workbook. The current value is Azure/Azure-Sentinel/master/Tools/IntrotoKQL.
- Once the repository path has been changed, click the refresh button within the workbook.
Note: If the repository that the workbook points to does not have the same path structure as the original Azure/Azure-Sentinel repository, the path will need to be updated to reflect the correct path for the new repository.
The Content
The content is what makes this solution tick. All of the content for the workbook is hosted within GitHub in a variety of files. This section will cover how the content is laid out and how users can add their own content.
Content Structure
The content for the workbook is broken into a few different files:
- All datasets (all_datasets.json)
- All exercises (all_exercises.json)
- Data files (each json file under Datasets)
Adding Data
The query space within the workbook can query data from both the workspace that the workbook is connected to as well as the data from GitHub. If using data from GitHub, sample files and a KQL reference will need to be provided. The file will need to be in JSON format. It is recommended that you edit the file in Notepad++ or another text editor and upload the file to GitHub. From experience, modifying the file via Visual Studio Code and uploading the file to GitHub has caused complications in the past.
Once the file is uploaded, the all_datasets file will need to be updated. It is as simple as listing the table name for the data that has been added and a KQL reference that is encoded in base64. The KQL reference will be a KQL externaldata query that will be used to query the data. An example looks like:
externaldata (TimeGenerated:datetime, Low:real, High:real, Rain:real, Location:string) [h'https://raw.githubusercontent.com/Azure/Azure-Sentinel/master/Tools/IntrotoKQL/Datasets/Weather.json']
with (format="multijson");
It is recommended that the query is tested in an Azure Log Analytics workspace to ensure that it is able to pull the data. Once it is ready to go, use a base64 encoder and paste the string in the all_datasets file.
{
"name": "Syslogs",
"kql_reference": "ZXh0ZXJuYWxkYXRhICh0YWJsZTpkeW5hbWljKSBbaCdodHRwczovL3Jhdy5naXRodWJ1c2VyY29udGVudC5jb20vbWFsb3dlMTAxL1NlbnRpbmVsLVByb2plY3RzL21hc3Rlci9JbnRyb1RvS1FML0RhdGFzZXRzL1N5c2xvZ3MuanNvbiddIHdpdGggKGZvcm1hdD0ibXVsdGlqc29uIikKfCBtdmV4cGFuZCB0YWJsZQp8IGV2YWx1YXRlIGJhZ191bnBhY2sodGFibGUp"
}
Adding Content
The all_exercises file contains all of the information that is tied to an operator. This includes:
- Markdown: The content written for the operator. This is written in markdown and needs to be encoded in base64.
- Tab: Required for mapping the operator to the appropriate tab within the workbook. In the example below, the tab is mapped to the strings tab in the workbook.
- Section: These should match the name of the operator for ease of use.
- Exercises:
- Name: Each question will need a name, in this case the content has been built using the structure of 'operator nameEx1.
- Question: Question for the user to perform. Can be written in plain text and does not need to be encoded.
- Dataset: Name of the table that is used for the question. This helps the workbook know which table should be checked for the answer. This is also the table used the answer. If no table is being used, it can be left blank.
- Answer: The answer expected for the question. This is a KQL query that provides the results that should be seen by the user. The answer is encoded in base64.
- Count: Number that reflects the number of exercises for the operator.
{
"markdown": "IVtEb2N1bWVudGF0aW9uXShodHRwczovL3NoaWVsZHMuaW8vYmFkZ2UvLURvY3VtZW50YXRpb24taW5mb3JtYXRpb25hbCkKPHA+Cmh0dHBzOi8vZG9jcy5taWNyb3NvZnQuY29tL2F6dXJlL2RhdGEtZXhwbG9yZXIva3VzdG8vcXVlcnkvc3RyY2F0ZnVuY3Rpb24KPC9wPgoKICoqU3VtbWFyeToqKgo8cD4KVGhlIGNvbmNhdCBvcGVyYXRvciBjb21iaW5lcyBtdWx0aXBsZSBzcGVjaWZpZWQgdmFsdWVzIHRvZ2V0aGVyIHRvIGZvcm0gb25lIHN0cmluZy4gCjwvcD4KCiAqKkV4YW1wbGU6KioKPHA+ClNlY3VyaXR5QWxlcnQgfCBleHRlbmQgQ29tcHJvbWlzZWRFbnRpdGllcyA9IHN0cmNhdChEaXNwbGF5TmFtZSwgJy8nLCBFbnRpdGllcywnLycsIFByb3ZpZGVyTmFtZSkgIDwvYnI+ClNlY3VyaXR5RXZlbnQgfCBleHRlbmQgRXZlbnQgPSBzdHJjYXQoRXZlbnRJRCwgJy8nLCBQcm9jZXNzLCAnLycsIENvbW1hbmRMaW5lKSB8IHByb2plY3QgRXZlbnQgPC9icj4KU2lnbmluTG9ncyB8IGV4dGVuZCBFdmVudCA9IHN0cmNhdCgnVXNlciBsb2dnaW5nIGluIGlzJywgVXNlckRpc3BsYXlOYW1lLCAnLycsIFJlc3VsdFR5cGUsICcvJywgTG9jYXRpb24sICcvJywgU3RhdHVzKSB8IHByb2plY3QgRXZlbnQgIDxici8+CjwvcD4KCiAqKldoZW4gdG8gdXNlOioqCjxwPgpTdHJjYXQgaXMgZ3JlYXQgZm9yIGNyZWF0aW5nIG5ldyB2YXJpYWJsZXMgb3IgZm9yIHN1bW1hcml6aW5nIGV4aXN0aW5nIGRhdGEgaW50byBvbmUgc3RyaW5nLiBJZiBzdW1tYXJpemluZyBvciBleHBvcnRpbmcgZGF0YSBpbiBhIHN1bW1hcnksIHN0cmNhdCBjYW4gYmUgdXNlZCB0byBjb21waWxlIGtleSBkZXRhaWxzIGludG8gb25lIHN0cmluZyBmb3IgdXNhZ2UuCjwvcD4=",
"tab": "String",
"section": "Strcat",
"exercises": {
"value": [
{
"name": "StrcatEx1",
"question": "Create a variable called Report that references the columns, with the value of 'The weather on DATE for LOCATION was a high of HIGH, a low of LOW, and had a rainfall of RAIN.'",
"dataset": "Weather",
"answer": "V2VhdGhlciB8IGV4dGVuZCBSZXBvcnQgPSBzdHJjYXQoJ1RoZSB3ZWF0aGVyIG9uICcsIFRpbWVHZW5lcmF0ZWQsICcgd2FzIGEgaGlnaCBvZiAnLCBIaWdoLCAnIGFuZCBhIGxvdyBvZiAnLCBMb3csICcgd2l0aCBhIHJhaW5mYWxsIG9mICcsIFJhaW4pIHwgcHJvamVjdCBSZXBvcnQ="
}
],
"Count": 1
}
},
And just like that, custom content can be added to the Intro to KQL solution! It is recommended that this is done for new hires, new users, or for users that need to learn queries that are consistently used within the environment. Following this process allows for the solution to be futureproof and can be changed to be the solution that is needed. The workbook will be updated over the next week. If you would like access early to the workbook, it can be found in the Microsoft Sentinel GitHub repository. The content will need to be applied to the existing workbook.