Tip you can also use the queries to form an Alert in Azure Monitor or Azure Sentinel to detect when a IP address is made public.

Demo: Demo Gif file

Installation instructions: https://github.com/CliveW-MSFT/KQLpublic/blob/master/README.md

Download: https://github.com/CliveW-MSFT/KQLpublic/blob/master/KQL/Workbooks/PublicIP/PublicIP%20v0.1.workbook

Overview

Use this Workbook to compare any Public IP address (PIP) in Azure Montor Logs and Azure Resource Graph (ARG). ARG may have more data that is useful to compare logged data against.

- e.g. If you create a Resource but never start it, ARG will have data, whereas Log Analytics wont have a log entry.  
- Also Log Analytics has data retention, so the data you seek may have been removed if the retention period has passed.

Data Source required:

AzureActivity
| where ResourceProvider == "Microsoft.Network"

Permission:

Access to ARG

The post Azure Workbook: This will show Public IP Address that you have appeared first on Microsoft Industry Blogs - United Kingdom.

Please see part1: http://approjects.co.za/?big=en-gb/industry/blog/cross-industry/2020/06/18/log-analytics-kql-saved-queries-how-to-find-and-run-them-in-a-workbook/

For future versions please look here: https://github.com/CliveW-MSFT/KQLpublic/tree/master/KQL/Workbooks/findMySynatx

Summary

So why do I have a Azure Monitor Workbook to find Workbooks, two main reasons:

1. In Shared Workbooks, I can again search within the code for a keyword – highly useful for finding specific syntax.  Shared Workbooks are those other people are granted access to view.
2. You can filter by Time Modified – again useful if you have a lot of Workbooks to search through.  This is also true for Private Workbooks (only the ones the author can see).  

I have 100s of Workbooks from various projects, so a search by date is extremely useful.   Unfortunately you cant do a keyword search within these, private workbooks.

Example:

I also created a similar Tab for Azure Resource Graph saved queries (saved Queries only), again the main benefit is a Time and Keyword search.

Please see the latest file in my Github:  https://github.com/CliveW-MSFT/KQLpublic/tree/master/KQL/Workbooks/findMySynatx

If you’d like to give it a try please read how to Import a Workbook from here: https://github.com/CliveW-MSFT/KQLpublic/blob/master/README.md

Special thanks to Gary Bushey for testing some of this, sorry Gary, but not all the bugs I’ve fixed yet!

Thanks Clive

The post Log Analytics: Queries, how to find and run them in a Workbook – part 2 appeared first on Microsoft Industry Blogs - United Kingdom.

Azure Monitor workbooks are a fantastic way to visualise data within a Log Analytics workspace and there are a number available in the Azure Portal.

Useful references

• Azure Monitor Workbook documentation
• Azure Log Analytics REST API reference
• Setting Data Retention by data type
• Azure Resource Graph

The workbook uses a combination of Azure Resource Graph (ARG) and the Log Analytics REST API to collect the required pieces of information that are needed, which is available from my repository on GitHub, where you will also find instructions on how to download and import it.

On importing the workbook, you will notice two dropdown pickers (as shown below), one which allows you to select the subscription where the Log Analytics workspace(s) are and the other is for what I refer to as the Report Option.

Use the Subscription dropdown picker to select the appropriate subscription, which will use Azure Resource Graph (ARG) to retrieve all the workspaces that exist in that subscription and the results are presented in a table as shown below:

The Report Option picker gives you two choices:

1. Full List – where the REST API call returns the Data Retention settings for all tables
2. View by Table – where you choose or search for a particular table and its associated Data Retention setting

NOTE: The Report Option is dependent on you having selected a particular workspace which is displayed in a table above as this exports some values into parameters that are used by the API queries.

So, here are some screenshots showing the results of both of those options:

Full List view

You will note that I have highlighted a couple of tables in the Full List report option that I changed as part of my previous post.

View by Table

You see the picker allows you to scroll through the list of available tables or you can do a text search. Once you have chosen a table the result will be presented to the right of the dropdown.

I will look at making some enhancements to this workbook in the future.

Thanks Paul

The post Log Analytics Workspace Retention Reporting Options (Part 2) appeared first on Microsoft Industry Blogs - United Kingdom.

Log Analytics has a option called Query Explorer (note, this is due to be updated, so this example is applicable for a short period of time).  If like me you have 100’s of saved queries, managing them can be a challenge (my #1 challenge!), lets fix that with a Azure Monitor Workbook…

One of the ways Query Explorer is used, is to save your KQL queries in a Category, with a Name – to help you find them again.  So I may have saved a query in Category:Demo and with a  Name: “This is a demo query”.   If I wanted to use this query again, I’d open Query Explorer, search for the name and re-run it.  However the challenge is, the search only looks at the “name”.  So for example if I had some KQL using the “externaldata” operator, unless I had that in the name as well, I couldn’t find it (without opening all my files), which is only ok, if you have a few saves.  It’s a reason I started to store more in Github, as that has a keyword search.

John Gardner a Principal Software Engineer in the Azure Monitor Workbooks team recently shared an example of using an api within a Workbook, similar to what I did here: https://techcommunity.microsoft.com/t5/azure-sentinel/using-the-sentinel-api-to-view-data-in-a-workbook/ba-p/1386436   John has kindly let me share his example,  the workbook he produced retrieved the ‘Saved Searches’ from the Log Analytics api, displayed them, then if you clicked one, shows the KQL and tries to run it in a workspace.

Solution

This was great, but whilst having a conversation about this, I thought why can’t it be used to solve my #1 challenge; how to find a keyword or command within a saved KQL query.  A light bulb moment.  Fortunately it was easy to make a few simple changes to the code from John.  Now from the search control in this Workbook you can type and find any text.

You can see in the next screenshot, we can search on a string, that can be the category, name or content/key word in the code.  This is a great time saver for me – just today I wanted a “regex” example and had to open 10+files to find it, with this workbook, I only needed one go!

You can see here, I looked for the word ‘extend‘ which was found in the Demo category, in a file called services-running – prior to this Workbook…would I have remembered it was in a file with that name, probably not?

Demo

Please click here to see a recorded Demo stored as a GIF from my Github.

Download the example

If you’d like to give it a try please read how to Import a Workbook from here: https://github.com/CliveW-MSFT/KQLpublic/blob/master/README.md

Then download the Workbook here: https://github.com/CliveW-MSFT/KQLpublic/blob/master/KQL/Workbooks/findMySynatx/FindmySyntax%20v0.2.4.workbook (remember to use ‘RAW’ mode)

Thanks Clive

The post Log Analytics: KQL saved Queries, how to find and run them in a Workbook appeared first on Microsoft Industry Blogs - United Kingdom.

This is the first of two posts that I will be doing on how you can report on the Retention settings of an Azure Log Analytics workspace. In the second post I will provide a sample Workbook for displaying the settings.

It is often that during my conversations with customers about Azure Monitor, Azure Security Center and Azure Sentinel, the topic of data retention comes up. In most cases discussing the default global settings of:

• 31 days for Log Analytics
• 90 days for Application Insights
• 90 days for an Azure Sentinel linked workspace
• and the maximum retention time of 730 days

are sufficient but then there are those occasions when a customer wants to retain certain data types for either a longer or shorter period of time, because either the data becomes stale and therefore not of value or they are thinking about cost optimisation.

Note: the above global defaults do not apply to Free Pricing tier, which has a retention of 7 days.

The process to change the data retention period is part of the Azure Monitor documentation in the Usage and Cost section. Changing the data retention period using the Azure portal is a global change across all data types. The ability to set retention by data type has been available since October 2019 and can be changed by utilising the Azure Resource Manager REST API.

Using this method it is possible to set different retention settings for individual data types from 30 to 730 days. I should note that both Usage and AzureActivity data types are retained for a minimum of 90 days by default and these cannot be set any lower.

So now that we know that it is possible to set individual settings, how do we go about setting it? The documentation provides a link to an OSS tool – ARMClient but I decided to take a slightly different approach and used Postman as it allowed me to save individual requests and then come back to them at a later date.

After downloading and installing the Postman client, I used this great blog post from Jon Gallant to configure Postman to work with Azure AD. It also provides some examples to make sure that everything is working correctly.

One of the things that I like about using Postman is that I can set variables for an environment and then reuse them when constructing the REST API calls. Although not shown in the screenshot below, I created variables for each of my workspaces and then inter-changed the variable as I needed.

With that bit done, it was now a case of creating the API requests applicable to the Azure Monitor Log Analytics workspace. The Azure Monitor documentation provides some example code for a GET request to list the retention for all the tables in a workspace:

GET /subscriptions/00000000-0000-0000-0000-00000000000/resourceGroups/MyResourceGroupName/providers/Microsoft.OperationalInsights/workspaces/MyWorkspaceName/Tables?api-version=2017-04-26-preview

Getting the current settings

Rather than walk through the creation of a new request, below are some screenshots showing what a GET request looks like in Postman (note: I have utilised the variables in the construction of the GET request, so it now looks like this:

{{resource}}/subscriptions/{{subscriptionId}}/resourceGroups/{{ala-workspace}}/Tables?api-version=2017-04-26-preview

Params section:

Headers section:

Once these have been set, clicking the SEND button will connect to the workspace and return the current settings as shown below:

The screenshot above shows that I have already updated the retention settings for the ConfigurationData table to 30 days.

Setting the Retention

To change the setting(s) I created a new PUT request and the settings in the Params and Headers sections are the same as the GET request but now I needed to add the necessary code to the Body section to actually set the retention period – see below:

And like the GET request, clicking the SEND button connected to the workspace and updated the setting for the chosen table. In the screenshot below, I changed the setting for the ConfigurationData table to 60 days.

In summary, once you have Postman set up to work with Azure AD and you get your environment variables configured, it is a very simple and straightforward process to:

• check the existing settings across all tables or individual tables,
• but you can easily update the retention periods to suit your needs whether it is from a cost optimisation point of view or you simply don’t want to retain specific data types.

In the next post I will use the same REST API calls but will display the results in a workbook.

The post Log Analytics Workspace Retention Reporting Options (Part 1) appeared first on Microsoft Industry Blogs - United Kingdom.

https://techcommunity.microsoft.com/t5/azure-log-analytics/log-analytics-for-report-generation/m-p/1469610

Question: Can I schedule a query to run in Azure Monitor Logs / Log Analytics (or even for Azure Sentinel) and email the results?

Answer:  Yes, I think there are two ways.  The first which I don’t go into detail about here is to provide a Azure Monitor Workbook – that way anyone with access can see the data whenever they need (you can also enable a download control if required).

However if you do need automation, please use a Logic App (playbook).  These are great for running a Daily/Weekly/ Monthly report schedule.

This is one of mine as a example:

1. The Recurrence – sets the schedule, this one runs on Friday at 23:00 – you decide when.

2. We use the “Run query..” to send the KQL commands and create a output.  I actually run two queries, as I need a Capacity report (shown) and a Performance report.  By adding a parallel branch you can do more or less.

3. Use an email connector like “send an email…” – as I use O365, to send the output to the desired people/team.

Step 1: example

 Step 2

I used a time chart, you can see the other options here:

Step 3

I send a very simple email, with the output as an attachment.  You could also send via Microsoft Teams, or any other supported messaging or social platforms – Logic Apps has 100’s of 3rd party connectors?   You use Dynamic content (click from a list, to fill in the Attachment Content / Name field)

Please see more details: https://docs.microsoft.com/en-us/azure/logic-apps/tutorial-process-email-attachments-workflow

The post Log Analytics or Azure Sentinel – how schedule a report appeared first on Microsoft Industry Blogs - United Kingdom.

1. How is my Azure Security Center (ASC) licenced and configured?
2. And how many workspaces do I have, and what retention policy is set.

You can look in the portal, however to do this at scale, lets use Azure Resource graph:

I suggest you use Azure Resource Graph (ARG) for this (some of which my recent Workbook does as well, but for a quick check you can load ARG in the Azure Portal.  these are some basic query examples, but they could be the basis of more complex queries.

1. Azure Security Center:  free vs. Standard licence

securityresources 
| where type == “microsoft.security/pricings”
| extend tier = trim(‘ ‘,tostring(properties.pricingTier))
| summarize  resource = make_set(name), tier = make_set(tier) by  subscriptionId, tenantId

2. Workspace details

resources
| where type == “microsoft.operationalinsights/workspaces”
| extend sku = tostring(properties.sku.name), retention = tostring(properties.retentionInDays), created = tostring(properties.createdDate), modified = tostring(properties.modifiedDate)
| summarize by subscriptionId, name, sku, retention, created, modified, location
| order by sku asc

Example output from Query #2:  This shows that most of my workspaces are set for 30day retention but one is 90days (in this case that’s the one that supports my Azure Sentinel., so that is correctly set as 90days is part of the free retention for Azure Sentinel).

Query 3:  Much like Query2 but shows if its free or Standard per Subscription ID and Resource Name

The post Audit at scale. Workspaces and Azure Security Center appeared first on Microsoft Industry Blogs - United Kingdom.

I just found out today that the Render operator now supports more features in Log Analytics.

The post Log Analytics: Improved rendering of Charts appeared first on Microsoft Industry Blogs - United Kingdom.