{ "version": "Notebook/1.0", "items": [ { "type": 9, "content": { "version": "KqlParameterItem/1.0", "parameters": [ { "id": "71eb8ef5-996b-44ca-a342-5c8ab713584f", "version": "KqlParameterItem/1.0", "name": "CustomerNameURL", "type": 1, "description": "Variable to find the appropriate images for banners", "value": "nwdemo", "timeContext": { "durationMs": 86400000 } } ], "style": "pills", "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces" }, "name": "parameters - 15" }, { "type": 1, "content": { "json": "\r\n![MS Logo](https://arcimages.blob.core.windows.net/{CustomerNameURL}/arc-cockpit.jpg \"Welcome\") \r\n\r\n ## Welcome \r\n---\r\nThis advanced workbook will show you how to leverage data collected by Azure Arc & services and create your own report (workbook). This is very convenient as 1) you can have a single page vision for all the data 2) it prevents going in multiple pages of the Azure Portal 3) you can get inspired, and can create multiple workbooks fine tuned for different teams.\r\n\r\nThis workbook is devided by chapters, meaning \"angle of analysis\" of your internal IT, you can minimize/maximize them. At the end of each chapter you have a Text zone where you can add your comments for \"this\" chapter (I discovered this, I should do that, etc).\r\n\r\n... then you can print as PDF so get a copy of your health for this specific date and see weeks after weeks the progress.\r\n\r\nInteresting readings regarding workbooks : \r\nhttps://docs.microsoft.com/fr-fr/azure/azure-monitor/visualize/workbooks-overview\r\n\r\nIn this example we can also leverage variables to cunstomize banners. Then, your logos will be located here here : {CustomerNameURL}, default are in \"data\" Directory. Check with fesnouf@microsoft.com to add your banners during POC then for production, just replace the URLs.\r\n\r\nCurrent URL (fesnouf@microsoft.com personal web site) : https://arcimages.blob.core.windows.net/{CustomerNameURL}/arc-cockpit.jpg (this location is owned by fesnouf@microsoft.com).\r\n\r\nWorkbook Version 3.0 (any question please contact fesnouf@microsoft.com)\r\n\r\n" }, "name": "texte - 2" }, { "type": 12, "content": { "version": "NotebookGroup/1.0", "groupType": "editable", "title": "Overview", "expandable": true, "expanded": true, "items": [ { "type": 1, "content": { "json": "![MS Logo](https://arcimages.blob.core.windows.net/{CustomerNameURL}/arc-overview.jpg \"Microsoft\")\r\n\r\nBelow a list of diagrams that will give you a high level vision of your infrastructure.\r\n" }, "name": "text - 8" }, { "type": 1, "content": { "json": "![MS Logo](https://arcimages.blob.core.windows.net/nwdemo/ceeimage.jpg \"cee\")" }, "customWidth": "50", "name": "text - 18" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "Resources\r\n|where type ==\"microsoft.hybridcompute/machines\"\r\n//| extend DC=parse_json (tags.['Datacenter'])\r\n| extend country=parse_json (tags.['CountryOrRegion'])\r\n| extend city=parse_json (tags.['City'])\r\n| project id, country, city", "showQuery": true, "size": 2, "title": "Demo environment based on Tags :", "showRefreshButton": true, "queryType": 1, "resourceType": "microsoft.resourcegraph/resources", "visualization": "map", "mapSettings": { "locInfo": "CountryRegion", "locInfoColumn": "country", "sizeSettings": "country", "sizeAggregation": "Count", "legendMetric": "country", "legendAggregation": "Count", "itemColorSettings": null } }, "customWidth": "50", "name": "Serverlocation" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "Resources\r\n|where type ==\"microsoft.hybridcompute/machines\"\r\n| extend DC=parse_json (tags.['Datacenter'])\r\n| extend city=parse_json (tags.['City'])\r\n| extend country=parse_json (tags.['CountryOrRegion'])\r\n| extend osType=parse_json (properties.['osType'])\r\n| extend osver=parse_json (properties.['osVersion'])\r\n| extend issql=parse_json (properties.['mssqldiscovered'])\r\n| extend issql=parse_json (properties.['mssqldiscovered'])\r\n| extend oss=parse_json (properties.['osSku'])\r\n| project oss, id, DC, country,city, osType, osver, issql, properties,tags", "size": 0, "title": "List of VMs in the lab, with Arc agent :", "showRefreshButton": true, "queryType": 1, "resourceType": "microsoft.resourcegraph/resources", "visualization": "table", "mapSettings": { "locInfo": "AzureResource", "locInfoColumn": "id", "sizeAggregation": "Sum", "legendMetric": "country", "legendAggregation": "Count", "itemColorSettings": null } }, "customWidth": "50", "name": "query - 18 - Copy" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "search * | distinct Type |order by Type asc", "showQuery": true, "size": 0, "title": "List of database available in log analytics (means solutions deployed) :", "timeContext": { "durationMs": 86400000 }, "showRefreshButton": true, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces" }, "customWidth": "30", "name": "query - 8", "styleSettings": { "showBorder": true } }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "securityresources\r\n|where type==\"microsoft.security/pricings\" \r\n|project id, name, properties, tenantId\r\n|order by name\r\n\r\n", "showQuery": true, "size": 0, "title": "Save money : List of products with 30 days eval (Free) and status : ", "noDataMessageStyle": 2, "queryType": 1, "resourceType": "microsoft.resourcegraph/resources", "gridSettings": { "sortBy": [ { "itemKey": "$gen_link_id_0", "sortOrder": 1 } ] }, "sortBy": [ { "itemKey": "$gen_link_id_0", "sortOrder": 1 } ] }, "customWidth": "50", "name": "query - 16" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "\r\nPerf\r\n| where TimeGenerated > ago(1d) and CounterValue<99\r\n| where ObjectName == \"LogicalDisk\" and CounterName == \"% Free Space\"\r\n| summarize (TimeGenerated, Free_Space_Percent)=arg_max(TimeGenerated, CounterValue) by Computer, InstanceName\r\n| where strlen(InstanceName) ==2 and InstanceName contains \":\"\r\n|order by Computer\r\n", "showQuery": true, "size": 4, "title": "Example of performance counter usage. \"Machines with less than 20% disk space\"", "noDataMessageStyle": 4, "timeContext": { "durationMs": 86400000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces", "visualization": "tiles", "tileSettings": { "titleContent": { "columnMatch": "Computer", "formatter": 1 }, "subtitleContent": { "columnMatch": "InstanceName", "formatter": 12, "formatOptions": { "min": 10, "palette": "red" } }, "leftContent": { "columnMatch": "Free_Space_Percent", "formatter": 12, "formatOptions": { "min": 10, "palette": "red" } }, "showBorder": true, "sortCriteriaField": "Free_Space_Percent", "sortOrderField": 1 } }, "name": "query - 8" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "Heartbeat\r\n| summarize AggregatedValue = dcount(Computer) by RemoteIPCountry", "size": 0, "title": "Example of server location (the Lab) in the world, based on public IP :", "timeContext": { "durationMs": 2592000000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces", "visualization": "map", "mapSettings": { "locInfo": "CountryRegion", "locInfoColumn": "RemoteIPCountry", "sizeSettings": "AggregatedValue", "sizeAggregation": "Sum", "legendMetric": "AggregatedValue", "legendAggregation": "Sum", "itemColorSettings": { "nodeColorField": "AggregatedValue", "colorAggregation": "Sum", "type": "heatmap", "heatmapPalette": "greenRed" } } }, "customWidth": "50", "name": "query - 7" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "Update \r\n| where OSType == \"Linux\"\r\n// and Optional == false \r\n| summarize hint.strategy=partitioned arg_max(TimeGenerated, *) by Computer, UpdateID\r\n//| where UpdateState =~ \"Needed\" and Approved != false\r\n| summarize Updates_Count=count() by Computer, MSRCSeverity, Classification, Product, PackageRepository\r\n| order by Updates_Count desc", "showQuery": true, "size": 0, "title": "Example of Linux machines which require updates :", "noDataMessageStyle": 4, "timeContext": { "durationMs": 604800000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces", "visualization": "table" }, "customWidth": "50", "name": "query - 9 - Copy" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "Event\r\n | where TimeGenerated >= ago(1d)\r\n | summarize NbOfTime=count() by RenderedDescription, Computer, EventLevelName\r\n | order by NbOfTime desc \r\n ", "showQuery": true, "size": 0, "title": "Example of Windows Event log. Identify Top 10 Errors :", "timeContext": { "durationMs": 86400000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces", "visualization": "table" }, "customWidth": "50", "name": "query - 13 - Copy - Copy" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "Event\r\n | where TimeGenerated >= ago(1d)\r\n | summarize NbDeFois=count() by RenderedDescription, Computer, EventLevelName\r\n | order by NbDeFois desc", "size": 2, "title": "Errors.. split (graphical vision) :", "timeContext": { "durationMs": 86400000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces", "visualization": "piechart" }, "customWidth": "50", "name": "query - 13 - Copy - Copy - Copy" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "Event\r\n| where TimeGenerated >= ago(1d) \r\n| summarize ELN=count() by Computer\r\n| where ELN>10\r\n| order by ELN", "size": 0, "title": "Example of number of \"errors\" (event) per machine in the past 24 hours ", "timeContext": { "durationMs": 86400000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces", "visualization": "tiles", "tileSettings": { "showBorder": false, "titleContent": { "columnMatch": "Computer", "formatter": 1 }, "leftContent": { "columnMatch": "ELN", "formatter": 12, "formatOptions": { "palette": "auto" }, "numberFormat": { "unit": 17, "options": { "maximumSignificantDigits": 3, "maximumFractionDigits": 2 } } } } }, "name": "query - 10" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "ServiceHealthResources\r\n| where type =~ 'Microsoft.ResourceHealth/events'\r\n| extend eventType = properties.EventType, status = properties.Status, description = properties.Title, trackingId = properties.TrackingId, summary = properties.Summary, priority = properties.Priority, impactStartTime = properties.ImpactStartTime, impactMitigationTime = todatetime(tolong(properties.ImpactMitigationTime))\r\n| where properties.Status == 'Active' and tolong(impactStartTime) > 1 and eventType == 'PlannedMaintenance'\r\n|project id, location,eventType,description, summary, properties", "size": 0, "title": "Azure planned maintenance :", "queryType": 1, "resourceType": "microsoft.resourcegraph/resources" }, "name": "query - 13" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "ServiceHealthResources\r\n| where type =~ 'Microsoft.ResourceHealth/events'\r\n| extend eventType = properties.EventType, status = properties.Status, description = properties.Title, trackingId = properties.TrackingId, summary = properties.Summary, priority = properties.Priority, impactStartTime = properties.ImpactStartTime, impactMitigationTime = todatetime(tolong(properties.ImpactMitigationTime))\r\n| where properties.Status == 'Active' and tolong(impactStartTime) > 1 and eventType == 'ServiceIssue'", "size": 0, "title": "Current Azure Outages :", "queryType": 1, "resourceType": "microsoft.resourcegraph/resources" }, "name": "query - 14" }, { "type": 1, "content": { "json": "## Overview Section\r\n---\r\n\r\nyou can add here your notes, commentes, advice for this section", "style": "error" }, "name": "text - analyse - Generalites", "styleSettings": { "showBorder": true } } ] }, "name": "group - introduction", "styleSettings": { "showBorder": true } }, { "type": 12, "content": { "version": "NotebookGroup/1.0", "groupType": "editable", "title": "Cost analysis (Log Analytics Only) :", "expandable": true, "items": [ { "type": 1, "content": { "json": "![MS Logo](https://arcimages.blob.core.windows.net/{CustomerNameURL}/arc-la-costs.jpg \"Portal\") \r\n## Costs related to ingestion of Log Analytics\r\nLog analytics workspace is used by many components to gather data.\r\nThe cost below represent the analysis of these costs.\r\nImportant : part of your project cost may come from other components. For a 360 vision please refer to Cost Management, or Powerbi connected to your Billing APIs.", "style": "success" }, "name": "texte - 2 - Copier - Copier - Copier - Copy" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "Usage \r\n| where StartTime >= startofday(ago(7d)) and EndTime < startofday(now())\r\n| where IsBillable == true\r\n| summarize BillableDataGB = sum(Quantity) / 1000. by bin(StartTime, 1d), Solution | render barchart", "size": 0, "title": "Quantity per solution", "timeContext": { "durationMs": 604800000 }, "showRefreshButton": true, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces", "visualization": "barchart", "tileSettings": { "showBorder": false, "titleContent": { "columnMatch": "Solution", "formatter": 1 }, "leftContent": { "columnMatch": "BillableDataGB", "formatter": 12, "formatOptions": { "palette": "auto" }, "numberFormat": { "unit": 17, "options": { "maximumSignificantDigits": 3, "maximumFractionDigits": 2 } } } }, "graphSettings": { "type": 0, "topContent": { "columnMatch": "Solution", "formatter": 1 }, "centerContent": { "columnMatch": "BillableDataGB", "formatter": 1, "numberFormat": { "unit": 17, "options": { "maximumSignificantDigits": 3, "maximumFractionDigits": 2 } } } }, "mapSettings": { "locInfo": "LatLong", "sizeSettings": "BillableDataGB", "sizeAggregation": "Sum", "legendMetric": "BillableDataGB", "legendAggregation": "Sum", "itemColorSettings": { "type": "heatmap", "colorAggregation": "Sum", "nodeColorField": "BillableDataGB", "heatmapPalette": "greenRed" } } }, "customWidth": "30", "name": "query - 18" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "Usage \r\n//| where StartTime >= startofday(ago(31d)) and EndTime < startofday(now())\r\n| where IsBillable == true\r\n| summarize BillableDataGB = sum(Quantity) / 1000. by bin(StartTime, 1m) | render barchart\r\n\r\n//, DataType", "size": 0, "title": "Quantity thoughout the day", "timeContext": { "durationMs": 86400000 }, "showRefreshButton": true, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces", "visualization": "linechart" }, "customWidth": "30", "name": "query - 8 - Copy" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "Usage \r\n| where TimeGenerated > ago(10d)\r\n| where StartTime >= startofday(ago(10d)) and EndTime < startofday(now())\r\n| where IsBillable == true\r\n| summarize 30Days= sum(Quantity) / 1000. *2.522 *3 \r\n\r\n//https://azure.microsoft.com/fr-fr/pricing/details/monitor/\r\n", "size": 0, "title": "Cost simulation of YOUR CONFIG based on last days :", "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces", "visualization": "table", "tileSettings": { "showBorder": false, "titleContent": { "columnMatch": "Solution", "formatter": 1 }, "leftContent": { "columnMatch": "BillableDataGB", "formatter": 12, "formatOptions": { "palette": "auto" }, "numberFormat": { "unit": 17, "options": { "maximumSignificantDigits": 3, "maximumFractionDigits": 2 } } } }, "graphSettings": { "type": 0, "topContent": { "columnMatch": "Solution", "formatter": 1 }, "centerContent": { "columnMatch": "BillableDataGB", "formatter": 1, "numberFormat": { "unit": 17, "options": { "maximumSignificantDigits": 3, "maximumFractionDigits": 2 } } } }, "chartSettings": { "showLegend": true }, "mapSettings": { "locInfo": "LatLong", "sizeSettings": "BillableDataGB", "sizeAggregation": "Sum", "legendMetric": "BillableDataGB", "legendAggregation": "Sum", "itemColorSettings": { "type": "heatmap", "colorAggregation": "Sum", "nodeColorField": "BillableDataGB", "heatmapPalette": "greenRed" } } }, "customWidth": "30", "name": "query - 18 - Copy" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "Usage \r\n//| where StartTime >= startofday(ago(31d)) and EndTime < startofday(now())\r\n| where IsBillable == true\r\n| summarize BillableDataGB = sum(Quantity) / 1000. by bin(StartTime, 1d), DataType | render barchart", "size": 0, "title": "Quantity per Data Type", "timeContext": { "durationMs": 604800000 }, "showRefreshButton": true, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces" }, "customWidth": "30", "name": "query - 8" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "find where TimeGenerated > ago(24h) project _IsBillable, Computer\r\n| where _IsBillable == true \r\n| extend computerName = tolower(tostring(split(Computer, '.')[0]))\r\n| summarize eventCount = count() by computerName \r\n| sort by eventCount nulls last", "size": 2, "title": "Quantity of data per machine : ", "timeContext": { "durationMs": 86400000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces", "visualization": "piechart" }, "customWidth": "30", "name": "query - 17" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "Usage \r\n//|where Solution==\"LogManagement\"\r\n| where TimeGenerated > ago(5d)\r\n| where StartTime >= startofday(ago(5d)) and EndTime < startofday(now())\r\n| where IsBillable == true\r\n| summarize BillableDataGB = sum(Quantity) / 1000. by bin(StartTime, 5d), DataType ", "size": 0, "title": "Cost per solution, based on 5 days consumption :", "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces", "visualization": "piechart" }, "customWidth": "30", "name": "query - 7" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "Usage \r\n| where Solution has \"Security\"\r\n| where TimeGenerated > ago(32d)\r\n| where StartTime >= startofday(ago(32d)) and EndTime < startofday(now())\r\n| where IsBillable == true\r\n| summarize BillableDataGB = sum(Quantity) / 1000. by bin(StartTime, 32d), DataType | render barchart\r\n\r\n//|distinct Solution\r\n", "size": 0, "title": "Data generated by Security Solution : ", "timeContext": { "durationMs": 2592000000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces", "visualization": "piechart" }, "customWidth": "30", "name": "query - 7 - Copy" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "Usage \r\n| where Solution has \"Security\"\r\n//| where DataType has \"SecurityEvent\"\r\n| where TimeGenerated > ago(32d)\r\n| where StartTime >= startofday(ago(32d)) and EndTime < startofday(now())\r\n| where IsBillable == true\r\n| summarize BillableDataGB = sum(Quantity) / 1000. by bin(StartTime, 32d), DataType, Solution\r\n| render barchart\r\n\r\n//|distinct Solution\r\n", "size": 0, "title": "Quantity of event per source :", "timeContext": { "durationMs": 2592000000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces", "visualization": "table" }, "customWidth": "50", "name": "query - 7 - Copy - Copy" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "SecurityEvent \r\n//Event \r\n| summarize nbevent = count() by Computer\r\n| order by nbevent\r\n//|distinct Solution\r\n", "size": 0, "title": "Number of event per machine on Security : ", "timeContext": { "durationMs": 2592000000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces", "visualization": "table" }, "name": "query - 7 - Copy - Copy - Copy" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "SecurityEvent \r\n//Event \r\n| summarize nbevent = count() by EventID, Activity\r\n| order by nbevent\r\n//|distinct Solution\r\n", "size": 0, "title": "Zoom on event logs data :", "timeContext": { "durationMs": 2592000000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces", "visualization": "table" }, "customWidth": "50", "name": "query - 7 - Copy - Copy - Copy - Copy" }, { "type": 1, "content": { "json": "## Cost Analysis Section :\r\n---\r\n\r\nUseful links for this chapter :\r\n\r\n* [Log Analytics cost section](https://ms.portal.azure.com/#@microsoft.onmicrosoft.com/resource/subscriptions/770207bd-ebc7-4cda-ad79-03507721ea17/resourceGroups/MyWorkstations/providers/Microsoft.OperationalInsights/workspaces/MyWorkstations/usageAndCosts)\r\n\r\n* [All Azure Costs via Cost Management](https://ms.portal.azure.com/#view/Microsoft_Azure_CostManagement/Menu/~/costanalysisv3)\r\n\r\n\r\n", "style": "error" }, "name": "text - analyse - Monitoring Costs", "styleSettings": { "showBorder": true } } ], "exportParameters": true }, "name": "group - Monitoring Costs ", "styleSettings": { "showBorder": true } }, { "type": 12, "content": { "version": "NotebookGroup/1.0", "groupType": "editable", "title": "Network MAP analysis", "expandable": true, "expanded": true, "items": [ { "type": 1, "content": { "json": "![MS Logo](https://arcimages.blob.core.windows.net/{CustomerNameURL}/arc-network-traffic.jpg \"Microsoft\")\r\n## Let's discover network traffic and server/server communications\r\n---\r\nMAP Network service will detect process/process traffic, and data can be used to create a \"network map\" of your infrastructure. Both Raw data and graphical representation is available for customer.", "style": "success" }, "name": "texte - 2 - Copier" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "ServiceMapComputer_CL | summarize arg_max(TimeGenerated, *) by ResourceId | project ComputerName_s, VirtualMachineType_s, VirtualizationState_s, OperatingSystemFullName_s, DnsNames_s, Ipv4Addresses_s, CpuSpeed_d, Cpus_d, PhysicalMemory_d", "size": 0, "title": "List of Machines analyzed by MAP : ", "timeContext": { "durationMs": 604800000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces" }, "name": "requête - 3" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "VMProcess \r\n//| where CompanyName contains \"Chro\"\r\n|distinct DisplayName, ProductVersion, Description, Group, CompanyName, InternalName, CommandLine, UserName, UserDomain\r\n|order by DisplayName asc", "size": 0, "title": "List of process detected on servers : ", "timeContext": { "durationMs": 14400000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces" }, "customWidth": "50", "name": "requête - 4" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "VMProcess \r\n|summarize total=count() by CompanyName\r\n|order by total", "size": 0, "title": "List of vendor and # (trend) of software installed : ", "timeContext": { "durationMs": 172800000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces", "visualization": "table" }, "customWidth": "40", "name": "requête - 4 - Copy" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "VMConnection | summarize sum(BytesSent), sum(BytesReceived) by bin(TimeGenerated,1m), Computer | order by Computer desc ", "size": 0, "title": "Trend. Traffic in and Out (all machines) :", "timeContext": { "durationMs": 86400000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces", "visualization": "linechart" }, "name": "requête - 5" }, { "type": 1, "content": { "json": "\r\n## Let's focus on one specific machine for deeper analysis\r\nPick the machine in the list box at your right =>" }, "customWidth": "50", "name": "text - 11" }, { "type": 9, "content": { "version": "KqlParameterItem/1.0", "parameters": [ { "id": "368512fb-7517-400a-a518-54c71e6277f8", "version": "KqlParameterItem/1.0", "name": "Server2Analyze", "type": 2, "query": "ServiceMapComputer_CL \r\n | distinct Computer\r\n | order by Computer", "value": null, "typeSettings": { "additionalResourceOptions": [] }, "timeContext": { "durationMs": 86400000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces" } ], "style": "pills", "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces" }, "customWidth": "50", "name": "parameters - 9" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "\r\nVMConnection \r\n|where Computer == \"{Server2Analyze}\"\r\n|where SourceIp!=\"127.0.0.1\" or DestinationIp!=\"127.0.0.1\"\r\n|summarize sum(BytesSent), sum(BytesReceived) by bin(TimeGenerated,1s), Computer \r\n//| order by Computer desc \r\n", "size": 0, "title": "All data captured by MAP bytes sent and deceived \"{Server2Analyze}\" :", "timeContext": { "durationMs": 3600000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces", "visualization": "timechart" }, "name": "requête - 6" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "VMBoundPort\r\n| where TimeGenerated >= ago(24hr)\r\n| where Computer == \"{Server2Analyze}\"\r\n| distinct Port, ProcessName\r\n| order by Port asc", "size": 0, "title": "Services and ports for \"{Server2Analyze}\" : ", "timeContext": { "durationMs": 86400000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces" }, "customWidth": "30", "name": "requête - 7" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "VMConnection \r\n| where Computer==\"{Server2Analyze}\" and DestinationIp !contains \"127\" and RemoteDnsQuestions !contains \"azure\" and DestinationIp !contains \"255\" \r\n| where RemoteDnsQuestions !contains \"microsoft\" and RemoteDnsQuestions !contains \"windows\" and RemoteDnsQuestions !contains \"studio\"\r\n| summarize count(BytesSent) by Computer,SourceIp, ProcessName, RemoteCountry, Direction, DestinationIp, DestinationPort, RemoteDnsQuestions, MaliciousIp \r\n|sort by DestinationIp desc\r\n//| join kind=fullouter(VMComputer) on $left.Computer == $right.Computer ", "size": 0, "title": "Data Sent per machine per services for \"{Server2Analyze}\" : please update query for filtering criterias : ", "timeContext": { "durationMs": 172800000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces" }, "customWidth": "70", "name": "requête - 10" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "VMProcess\r\n|where CommandLine contains \"/\"\r\n//|where DisplayName==\"msedge\"\r\n| project Computer, DisplayName, StartTime, UserName, UserDomain, Description, CommandLine, FirstPid\r\n//where DisplayName contains \"chrome\"", "size": 0, "title": "List details about a running Service (update the query with the service name), VMProcess database (with filters)", "timeContext": { "durationMs": 86400000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces", "gridSettings": { "sortBy": [ { "itemKey": "UserName", "sortOrder": 1 } ] }, "sortBy": [ { "itemKey": "UserName", "sortOrder": 1 } ] }, "name": "query - 12" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "VMConnection \r\n//|where ProcessName contains \"chrome\"\r\n|where SourceIp contains \"8.8.8.8\"\r\n//|where DestinationIp contains \"10.87\" //.5.89\"\r\n|project Computer, Direction, ProcessName, SourceIp, DestinationIp, ipv4_is_private(DestinationIp), DestinationPort, RemoteDnsQuestions, RemoteDnsCanonicalNames", "showQuery": true, "size": 0, "title": "Look at connections with criterias such as source IP, etc :", "timeContext": { "durationMs": 604800000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces" }, "name": "query - 13" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "// ip to lookup\r\nlet ipAddress = '82.65.97.28';\r\n// get data from here \r\nlet IP_Data = external_data(network:string,geoname_id:long,continent_code:string,continent_name:string ,country_iso_code:string,country_name:string,is_anonymous_proxy:bool,is_satellite_provider:bool)\r\n ['https://raw.githubusercontent.com/datasets/geoip2-ipv4/master/data/geoip2-ipv4.csv']\r\n with (ignoreFirstRecord=true, format=\"csv\");\r\nIP_Data\r\n| evaluate ipv4_lookup(IP_Data, ipAddress, network)\r\n| summarize arg_max(network,*) by ipAddress\r\n| extend IPaddress = ipAddress\r\n| project-away *1\r\n| project-reorder IPaddress", "size": 0, "title": "Find the country of a public IP :", "timeContext": { "durationMs": 86400000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces" }, "name": "query - 14" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "VMConnection \r\n| where RemoteLatitude >0 and isempty(RemoteDnsQuestions)\r\n| summarize count(SourceIp) by ProcessName", "size": 0, "title": "Communication outside with no DNS : Stat per service", "timeContext": { "durationMs": 2592000000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces", "visualization": "piechart", "graphSettings": { "type": 0, "topContent": { "columnMatch": "RemoteCountry", "formatter": 1 }, "centerContent": { "columnMatch": "count_SourceIp", "formatter": 1, "numberFormat": { "unit": 17, "options": { "maximumSignificantDigits": 3, "maximumFractionDigits": 2 } } } }, "mapSettings": { "locInfo": "LatLong", "latitude": "RemoteLatitude", "longitude": "RemoteLongitude", "sizeSettings": "BytesSent", "sizeAggregation": "Sum", "maxSize": 99, "labelSettings": "RemoteCountry", "legendMetric": "BytesSent", "legendAggregation": "Count", "itemColorSettings": { "nodeColorField": "BytesSent", "colorAggregation": "Sum", "type": "heatmap", "heatmapPalette": "greenRed" } } }, "customWidth": "33", "name": "query - 12 - Copy - Copy - Copy - Copy" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "VMConnection \r\n| where RemoteLatitude >0\r\n| where RemoteDnsQuestions !contains \"azure\" and RemoteDnsQuestions !contains \"windows.net\" and RemoteDnsQuestions !contains \"microsoft.com\" and RemoteDnsQuestions !contains \"windows.com\" and RemoteDnsQuestions !contains \"visualstudio.com\" and RemoteDnsQuestions !contains \"msn.com\" and RemoteDnsQuestions !contains \"live.com\" and RemoteDnsQuestions !contains \"office.com\"\r\n| extend Host = tostring(parse_url(RemoteDnsQuestions).Host)\r\n| summarize mytotal=count(SourceIp) by RemoteDnsQuestions, Host\r\n| order by mytotal", "size": 0, "title": "Communication outside with DNS : per DNS name : ", "timeContext": { "durationMs": 2592000000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces", "visualization": "table", "graphSettings": { "type": 0, "topContent": { "columnMatch": "RemoteCountry", "formatter": 1 }, "centerContent": { "columnMatch": "count_SourceIp", "formatter": 1, "numberFormat": { "unit": 17, "options": { "maximumSignificantDigits": 3, "maximumFractionDigits": 2 } } } }, "mapSettings": { "locInfo": "LatLong", "latitude": "RemoteLatitude", "longitude": "RemoteLongitude", "sizeSettings": "BytesSent", "sizeAggregation": "Sum", "maxSize": 99, "labelSettings": "RemoteCountry", "legendMetric": "BytesSent", "legendAggregation": "Count", "itemColorSettings": { "nodeColorField": "BytesSent", "colorAggregation": "Sum", "type": "heatmap", "heatmapPalette": "greenRed" } } }, "customWidth": "66", "name": "query - 12 - Copy - Copy" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "VMConnection \r\n| project RemoteCountry, RemoteLatitude, RemoteLongitude, BytesSent, SourceIp, DestinationIp, RemoteDnsQuestions\r\n| order by RemoteCountry\r\n//| where RemoteLatitude >0\r\n\r\n// https://support.google.com/maps/answer/18539?hl=fr_ch&ref_topic=3092444", "size": 0, "title": "Corresponding Raw data of network connectivity : ", "timeContext": { "durationMs": 2592000000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces", "visualization": "table", "mapSettings": { "locInfo": "LatLong", "latitude": "RemoteLatitude", "longitude": "RemoteLongitude", "sizeSettings": "BytesSent", "sizeAggregation": "Sum", "maxSize": 99, "labelSettings": "RemoteCountry", "legendMetric": "BytesSent", "legendAggregation": "Count", "itemColorSettings": { "nodeColorField": "BytesSent", "colorAggregation": "Sum", "type": "heatmap", "heatmapPalette": "greenRed" } } }, "customWidth": "50", "name": "query - 12" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "VMConnection \r\n| where RemoteLatitude >0\r\n| summarize count(SourceIp) by RemoteCountry ", "size": 0, "title": "Communication outside with no DNS : per Country :", "timeContext": { "durationMs": 2592000000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces", "visualization": "piechart", "graphSettings": { "type": 0, "topContent": { "columnMatch": "RemoteCountry", "formatter": 1 }, "centerContent": { "columnMatch": "count_SourceIp", "formatter": 1, "numberFormat": { "unit": 17, "options": { "maximumSignificantDigits": 3, "maximumFractionDigits": 2 } } } }, "mapSettings": { "locInfo": "LatLong", "latitude": "RemoteLatitude", "longitude": "RemoteLongitude", "sizeSettings": "BytesSent", "sizeAggregation": "Sum", "maxSize": 99, "labelSettings": "RemoteCountry", "legendMetric": "BytesSent", "legendAggregation": "Count", "itemColorSettings": { "nodeColorField": "BytesSent", "colorAggregation": "Sum", "type": "heatmap", "heatmapPalette": "greenRed" } } }, "customWidth": "50", "name": "query - 12 - Copy" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "VMConnection \r\n| where RemoteLatitude >0\r\n| project RemoteCountry, RemoteLatitude, RemoteLongitude, BytesSent ", "showQuery": true, "size": 0, "title": "Example of Network MAP. My internal machines talk to these public external machines : ", "timeContext": { "durationMs": 2592000000 }, "showRefreshButton": true, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces", "visualization": "map", "mapSettings": { "locInfo": "LatLong", "latitude": "RemoteLatitude", "longitude": "RemoteLongitude", "sizeSettings": "BytesSent", "sizeAggregation": "Sum", "maxSize": 99, "labelSettings": "RemoteCountry", "legendMetric": "BytesSent", "legendAggregation": "Count", "itemColorSettings": { "nodeColorField": "BytesSent", "colorAggregation": "Sum", "type": "heatmap", "heatmapPalette": "greenRed" } } }, "customWidth": "50", "name": "query - 12 - Copy" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "Heartbeat\r\n| summarize AggregatedValue = dcount(Computer) by RemoteIPCountry", "showQuery": true, "size": 0, "title": "Example of server location (the Lab) in the world, based on public IP :", "timeContext": { "durationMs": 2592000000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces", "visualization": "map", "mapSettings": { "locInfo": "CountryRegion", "locInfoColumn": "RemoteIPCountry", "sizeSettings": "AggregatedValue", "sizeAggregation": "Sum", "legendMetric": "AggregatedValue", "legendAggregation": "Sum", "itemColorSettings": { "nodeColorField": "AggregatedValue", "colorAggregation": "Sum", "type": "heatmap", "heatmapPalette": "greenRed" } } }, "customWidth": "50", "name": "query - 7 - Copy" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "VMConnection \r\n| where RemoteLatitude >0\r\n|where Computer contains \"LAPTOP\"\r\n|where RemoteDnsQuestions !contains \"Microsoft\"\r\n|where RemoteCountry contains \"Sin\"\r\n|distinct Computer, ProcessName, RemoteCountry, RemoteLatitude, RemoteLongitude, DestinationIp, DestinationPort, RemoteDnsQuestions, RemoteDnsCanonicalNames, MaliciousIp", "size": 0, "title": "Example of Why Singapore , and not Microsoft , for a dedicated machine (laptop) :", "timeContext": { "durationMs": 2592000000 }, "showRefreshButton": true, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces", "visualization": "table", "gridSettings": { "sortBy": [ { "itemKey": "Computer", "sortOrder": 1 } ] }, "sortBy": [ { "itemKey": "Computer", "sortOrder": 1 } ], "mapSettings": { "locInfo": "LatLong", "latitude": "RemoteLatitude", "longitude": "RemoteLongitude", "sizeSettings": "BytesSent", "sizeAggregation": "Sum", "maxSize": 99, "labelSettings": "RemoteCountry", "legendMetric": "BytesSent", "legendAggregation": "Count", "itemColorSettings": { "nodeColorField": "BytesSent", "colorAggregation": "Sum", "type": "heatmap", "heatmapPalette": "greenRed" } } }, "name": "query - 12 - Copy - Copy" }, { "type": 1, "content": { "json": "## Leverage Data in Power BI\r\n---\r\nYou can generate a \"MAP\" of your machines leveraging products such as Power BI and use Graph vizualisation.\r\nAs a result, you will know \"clusters\" of machines talking to each other, for example if you plan a Move2Cloud.\r\nThe Graph vision in workbooks would require too much browser cycles, this is why we need to switch PowerBI.\r\n\r\n![MS Logo](https://blog.esnouf.net/wp-content/uploads/2022/05/051722_0829_BoostValueA8.png \"Microsoft\")\r\n\r\n\r\n\r\n* [How to do it ](https://blog.esnouf.net/2022/05/10/boostvalue-azure-service-map-network/)\r\n\r\n", "style": "info" }, "name": "text - analyse - Cartographie", "styleSettings": { "showBorder": true } }, { "type": 1, "content": { "json": "## Network MAP traffic\r\n---\r\n\r\nUseful links for this chapter :\r\n\r\n* [Historical MAP portal](https://ms.portal.azure.com/#blade/Microsoft_Azure_AdvisorPortalExtension/WorkspaceOverviewBlade/id/%2Fsubscriptions%2F770207bd-ebc7-4cda-ad79-03507721ea17%2Fresourcegroups%2Fmyworkstations%2Fproviders%2Fmicrosoft.operationalinsights%2Fworkspaces%2Fmyworkstations/parameters/%7B%7D)\r\n\r\n* [MAP Focus on One Machine](https://ms.portal.azure.com/#@microsoft.onmicrosoft.com/resource/subscriptions/770207bd-ebc7-4cda-ad79-03507721ea17/resourceGroups/Camera/providers/Microsoft.HybridCompute/machines/greecewin2/insights)\r\n\r\n* [GPS Coordinates captured by MAP ](https://www.google.fr/maps/place/37%C2%B025'12.0%22N+122%C2%B004'48.0%22W/@37.4200042,-122.0821887,17z/data=!3m1!4b1!4m5!3m4!1s0x0:0x98542fe8b47e77d2!8m2!3d37.42!4d-122.08)\r\n\r\n* [URLSCAN](http://www.urlscan.io)\r\n\r\n\r\n\r\n", "style": "error" }, "name": "text - analyse - Cartographie - Copy", "styleSettings": { "showBorder": true } } ], "exportParameters": true }, "name": "group - network map", "styleSettings": { "showBorder": true } }, { "type": 12, "content": { "version": "NotebookGroup/1.0", "groupType": "editable", "title": "SQL (Performance and security)", "expandable": true, "items": [ { "type": 1, "content": { "json": "![MS Logo](https://arcimages.blob.core.windows.net/{CustomerNameURL}/arc-sql.jpg \"Microsoft\")\r\n\r\n## SQL related information\r\nLet's see in details.\r\n", "style": "success" }, "name": "texte - 2 - Copier - Copier - Copier" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "SQLAssessmentRecommendation \r\n|where RecommendationResult!=\"Passed\"\r\n|project Computer, FocusArea, Description, Recommendation, RecommendationResult, AffectedObjectType\r\n", "size": 0, "title": "Recommandations SQL, base SQLAssessmentRecommendation ", "timeContext": { "durationMs": 2592000000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces", "gridSettings": { "sortBy": [ { "itemKey": "RecommendationResult", "sortOrder": 1 } ] }, "sortBy": [ { "itemKey": "RecommendationResult", "sortOrder": 1 } ] }, "name": "query - 8" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "VMConnection \r\n| where Computer==\"LAPTOP-DFSU93JS\"\r\n//| where DestinationIp !=\"127.0.0.1\" or SourceIp !=\"127.0.0.1\"\r\n| project Computer, ProcessName, DestinationIp, DestinationPort, RemoteIp, RemoteCountry", "size": 0, "title": "My SQL server is talking to these guys :", "timeContext": { "durationMs": 2592000000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces", "visualization": "map", "mapSettings": { "locInfo": "CountryRegion", "locInfoColumn": "RemoteCountry", "sizeSettings": "DestinationPort", "sizeAggregation": "Sum", "legendMetric": "DestinationPort", "legendAggregation": "Sum", "itemColorSettings": { "nodeColorField": "DestinationPort", "colorAggregation": "Sum", "type": "heatmap", "heatmapPalette": "greenRed" } } }, "name": "query - 12" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "SQLAssessmentRecommendation\r\n| where FocusAreaId == \"d617a649-5756-40a5-ac41-481e66e6200b\"\r\n| summarize arg_max(TimeGenerated, *) by RecommendationId, AffectedObjectName, AffectedObjectUniqueName\r\n| where RecommendationResult == \"Failed\" and (Technology has \"\" or Technology == \"\")\r\n| sort by RecommendationWeight desc, TimeGenerated desc\r\n| project AffectedObjectName, AffectedObjectUniqueName, Recommendation, Description, RecommendationScore,RecommendationResult, FocusArea, ActionArea , RecommendationWeight, AffectedObjectDetails\r\n| order by RecommendationScore desc", "size": 0, "title": "AVAILABILITY AND BUSINESS CONTINUITY :", "timeContext": { "durationMs": 2592000000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces", "visualization": "table", "gridSettings": { "sortBy": [ { "itemKey": "AffectedObjectDetails", "sortOrder": 1 } ] }, "sortBy": [ { "itemKey": "AffectedObjectDetails", "sortOrder": 1 } ], "chartSettings": { "xAxis": "TimeGenerated" } }, "name": "query - 8 - Copy - Copy" }, { "type": 1, "content": { "json": "## SQL Section\r\n---\r\n\r\nUseful links for this chapter :\r\n\r\n\r\n* [New assessment](https://ms.portal.azure.com/#view/Microsoft_Azure_HybridCompute/AzureArcCenterBlade/~/sqlServers)\r\n\r\n\r\n\r\n\r\n\r\n", "style": "error" }, "name": "text - analyse - SQL", "styleSettings": { "showBorder": true } } ] }, "name": "group - SQL", "styleSettings": { "showBorder": true } }, { "type": 12, "content": { "version": "NotebookGroup/1.0", "groupType": "editable", "title": "Security Focus - General", "expandable": true, "items": [ { "type": 1, "content": { "json": "![MS Logo](https://arcimages.blob.core.windows.net/{CustomerNameURL}/arc-inventory.jpg \"Microsoft\")\r\n## Let's focus on security\r\nSecurity is a wide range of discussions. Below we will combine different Azure technology to give you the right vision and take actions. Have zero trust in mind as security is a multilayer approach :\r\n\r\n![MS Logo](https://blog.esnouf.net/wp-content/uploads/2022/05/052522_0814_ArcforMove27.png \"Microsoft\")\r\n", "style": "success" }, "name": "text - 7" }, { "type": 1, "content": { "json": "[Security Workbook](https://ms.portal.azure.com/#view/Microsoft_Azure_Security/SecurityMenuBlade/~/28)" }, "name": "text - 12" }, { "type": 12, "content": { "version": "NotebookGroup/1.0", "groupType": "editable", "title": "Security Assessment", "expandable": true, "items": [ { "type": 3, "content": { "version": "KqlItem/1.0", "query": "Update\r\n| where TimeGenerated > now(-1d)\r\n| where UpdateState == \"Needed\"\r\n| distinct TimeGenerated, Computer, Title, Classification, PublishedDate, Product, UpdateState, RebootBehavior,MSRCBulletinID\r\n| sort by Computer\r\n", "size": 0, "title": "Manque de correctifs sur serveurs", "timeContext": { "durationMs": 604800000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces" }, "name": "query - 0" } ] }, "name": "group - 23 sec assessment ", "styleSettings": { "showBorder": true } }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "securityresources\r\n | where type == \"microsoft.security/assessments\"\r\n| extend DisplayName=parse_json (properties.['displayName'])\r\n| extend description=parse_json (properties.metadata.['description'])\r\n| extend AssessType=parse_json (properties.metadata.['assessmentType'])\r\n| extend Severity=parse_json (properties.metadata.['severity'])\r\n| extend RemedDescr=parse_json (properties.metadata.['remediationDescription'])\r\n| extend effort=parse_json (properties.metadata.['remediationEffort'])\r\n| extend techniques=parse_json (properties.metadata.['techniques'][3])\r\n| extend links=parse_json (properties.['links'])\r\n| project type, DisplayName, description, AssessType, Severity, RemedDescr,effort,techniques,links\r\n", "size": 0, "title": "Security events in details :", "queryType": 1, "resourceType": "microsoft.resourcegraph/resources", "crossComponentResources": [ "/subscriptions/770207bd-ebc7-4cda-ad79-03507721ea17/resourceGroups/MyWorkstations/providers/Microsoft.OperationalInsights/workspaces/MyWorkstations" ] }, "name": "query - 12 - Copy" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "securityresources\r\n| where type == \"microsoft.security/assessments/subassessments\"\r\n| extend rsdetails=split(properties.resourceDetails,\"/\")\r\n|extend size=array_length(rsdetails)\r\n| project rsdetails[size-1],rsdetails, properties.displayName, properties.remediation, properties.status.severity, properties.status.code, properties\r\n", "size": 0, "title": "Security Resources details :", "queryType": 1, "resourceType": "microsoft.resourcegraph/resources" }, "name": "query - 27" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "WindowsFirewall \r\n| project TimeGenerated, Computer, CommunicationDirection, FirewallAction, Protocol, SourceIP, DestinationIP, RemoteIP, SourcePort, DestinationPort\r\n| limit 100", "size": 0, "title": "Windows Firewall (sorry no firewall in my demo) ", "timeContext": { "durationMs": 2592000000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces" }, "name": "query - Security and Audit - Windows Firewall" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "SecurityEvent\r\n| where EventID == 4624 and AccountType == \"User\"\r\n| summarize count() by Account, Computer, IpAddress, AuthenticationPackageName\r\n| sort by count_, AuthenticationPackageName", "size": 0, "title": "Security events : example of authentication error (Defender or Sentinel)", "timeContext": { "durationMs": 2592000000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces" }, "name": "query - 10" }, { "type": 1, "content": { "json": "## Security section\r\n---\r\n\r\n[* Defender recommendations : ](https://ms.portal.azure.com/#view/Microsoft_Azure_Security/SecurityMenuBlade/~/5)\r\n\r\n[* Defender Security Alerts : ](https://ms.portal.azure.com/#view/Microsoft_Azure_Security/SecurityMenuBlade/~/7)\r\n\r\n\r\n[* Defender Inventory : ](https://ms.portal.azure.com/#view/Microsoft_Azure_Security/SecurityMenuBlade/~/25)\r\n\r\n", "style": "error" }, "name": "text - analyse - Network - Copy - Copy - Copy", "styleSettings": { "showBorder": true } } ], "exportParameters": true }, "name": "group - security - general", "styleSettings": { "showBorder": true } }, { "type": 12, "content": { "version": "NotebookGroup/1.0", "groupType": "editable", "title": "Security Focus - Compliance", "expandable": true, "items": [ { "type": 1, "content": { "json": "![MS Logo](https://arcimages.blob.core.windows.net/{CustomerNameURL}/arc-inventory.jpg \"Microsoft\")\r\n## Let's focus on security : Compliance (Policies)\r\nSecurity is a wide range of discussions. Let's focus on policy compliance\r\n", "style": "success" }, "name": "text - 7" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "securityresources\r\n| where type == \"microsoft.security/regulatorycompliancestandards\"\r\n| extend \r\n\tpassedControls = trim (' ', tostring(properties.passedControls)), \r\n\tfailedControls = trim(' ',tostring(properties.failedControls)), \r\n\tstate \t\t = trim(' ', tostring(properties.state)), \r\n\tunsupportedControls = trim(' ', tostring(properties.unsupportedControls)), \r\n\tskippedControls = trim(' ', tostring(properties.skippedControls))\r\n| project name, passedControls, failedControls, unsupportedControls, skippedControls , subscriptionId\r\n| order by passedControls desc", "size": 0, "title": "Certification level of the Azure Plateform (From Security) :", "queryType": 1, "resourceType": "microsoft.resourcegraph/resources", "crossComponentResources": [ "/subscriptions/770207bd-ebc7-4cda-ad79-03507721ea17/resourceGroups/MyWorkstations/providers/Microsoft.OperationalInsights/workspaces/MyWorkstations" ], "gridSettings": { "formatters": [ { "columnMatch": "passedControls", "formatter": 3, "formatOptions": { "palette": "coldHot" }, "numberFormat": { "unit": 0, "options": { "style": "decimal", "useGrouping": false } } }, { "columnMatch": "failedControls", "formatter": 3, "formatOptions": { "palette": "redDark" }, "numberFormat": { "unit": 0, "options": { "style": "decimal" } } }, { "columnMatch": "unsupportedControls", "formatter": 3, "formatOptions": { "palette": "coldHot" }, "numberFormat": { "unit": 0, "options": { "style": "decimal" } } }, { "columnMatch": "skippedControls", "formatter": 3, "formatOptions": { "palette": "coldHot" }, "numberFormat": { "unit": 0, "options": { "style": "decimal" } } } ] } }, "name": "query - 12" }, { "type": 9, "content": { "version": "KqlParameterItem/1.0", "crossComponentResources": [ "/subscriptions/770207bd-ebc7-4cda-ad79-03507721ea17/resourceGroups/MyWorkstations/providers/Microsoft.OperationalInsights/workspaces/MyWorkstations" ], "parameters": [ { "id": "0f8bcb5b-e96b-4982-9961-1b39314729f4", "version": "KqlParameterItem/1.0", "name": "SelectCompliance", "type": 7, "query": "securityresources\r\n| where type == \"microsoft.security/regulatorycompliancestandards\"\r\n| project name\r\n", "crossComponentResources": [ "/subscriptions/770207bd-ebc7-4cda-ad79-03507721ea17/resourceGroups/MyWorkstations/providers/Microsoft.OperationalInsights/workspaces/MyWorkstations" ], "value": "Azure-Security-Benchmark", "typeSettings": { "additionalResourceOptions": [], "showDefault": false }, "timeContext": { "durationMs": 86400000 }, "queryType": 1, "resourceType": "microsoft.resourcegraph/resources" }, { "id": "34ee6922-b54d-4d39-98b7-d7a4f2b935e4", "version": "KqlParameterItem/1.0", "name": "selectState", "type": 6, "query": "securityresources\r\n| where type == \"microsoft.security/regulatorycompliancestandards/regulatorycompliancecontrols\"\r\n | extend state \t\t = trim(' ', tostring(properties.state))\r\n| summarize by state", "crossComponentResources": [ "/subscriptions/770207bd-ebc7-4cda-ad79-03507721ea17/resourceGroups/MyWorkstations/providers/Microsoft.OperationalInsights/workspaces/MyWorkstations" ], "value": "/subscriptions/Passed", "typeSettings": { "additionalResourceOptions": [], "showDefault": false }, "timeContext": { "durationMs": 86400000 }, "queryType": 1, "resourceType": "microsoft.resourcegraph/resources" } ], "style": "above", "queryType": 1, "resourceType": "microsoft.resourcegraph/resources" }, "name": "parameters - 14" }, { "type": 9, "content": { "version": "KqlParameterItem/1.0", "crossComponentResources": [ "/subscriptions/770207bd-ebc7-4cda-ad79-03507721ea17/resourceGroups/MyWorkstations/providers/Microsoft.OperationalInsights/workspaces/MyWorkstations" ], "parameters": [ { "id": "e5b9ba7b-2bb6-4f34-8544-3ce3d2dcf3ad", "version": "KqlParameterItem/1.0", "name": "Impact", "type": 5, "isRequired": true, "multiSelect": true, "quote": "'", "delimiter": ",", "query": "securityresources\r\n| where type == \"microsoft.security/assessments\"\r\n| extend impact = tostring(properties.metadata.severity)\r\n| summarize by impact\r\n", "crossComponentResources": [ "/subscriptions/770207bd-ebc7-4cda-ad79-03507721ea17/resourceGroups/MyWorkstations/providers/Microsoft.OperationalInsights/workspaces/MyWorkstations" ], "value": [ "High" ], "typeSettings": { "additionalResourceOptions": [], "showDefault": false }, "timeContext": { "durationMs": 86400000 }, "queryType": 1, "resourceType": "microsoft.resourcegraph/resources" } ], "style": "above", "queryType": 1, "resourceType": "microsoft.resourcegraph/resources" }, "name": "parameters - 13" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "securityresources\r\n| where type == \"microsoft.security/regulatorycompliancestandards/regulatorycompliancecontrols/regulatorycomplianceassessments\"\r\n| parse id with *\"/regulatoryComplianceStandards/\" strControlName \"/regulatory\"*\r\n| extend \r\n\t state \t\t = trim(' ', tostring(properties.state))\r\n\t,description = trim(' ', tostring(properties.description))\r\n\t,assessType = trim(' ', tostring(properties.assessmentType))\r\n\t,passedResources = trim (' ', tostring(properties.passedResources))\r\n\t,failedResources = trim(' ',tostring(properties.failedResources))\r\n\t,skippedResources = trim(' ', tostring(properties.skippedResources))\r\n | where strControlName startswith '{SelectCompliance}'\r\n| extend isState = iif(isempty('{selectState}'),\"All states\",'{selectState}')\r\n| summarize by ControlName = strControlName, description,Status = isState, passedResources, failedResources, skippedResources \r\n|order by failedResources", "size": 0, "title": "Complinance and security", "queryType": 1, "resourceType": "microsoft.resourcegraph/resources", "crossComponentResources": [ "/subscriptions/770207bd-ebc7-4cda-ad79-03507721ea17/resourceGroups/MyWorkstations/providers/Microsoft.OperationalInsights/workspaces/MyWorkstations" ] }, "name": "query - 13" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "PolicyResources\r\n| where type =~ 'Microsoft.PolicyInsights/PolicyStates'\r\n| extend complianceState = tostring(properties.complianceState)\r\n| extend\r\n\tresourceId = tostring(properties.resourceId),\r\n\tpolicyAssignmentId = tostring(properties.policyAssignmentId),\r\n\tpolicyAssignmentScope = tostring(properties.policyAssignmentScope),\r\n\tpolicyAssignmentName = tostring(properties.policyAssignmentName),\r\n\tpolicyDefinitionId = tostring(properties.policyDefinitionId),\r\n\tpolicyDefinitionReferenceId = tostring(properties.policyDefinitionReferenceId),\r\n\tstateWeight = iff(complianceState == 'NonCompliant', int(300), iff(complianceState == 'Compliant', int(200), iff(complianceState == 'Conflict', int(100), iff(complianceState == 'Exempt', int(50), int(0)))))\r\n| summarize max(stateWeight) by resourceId, policyAssignmentId, policyAssignmentScope, policyAssignmentName\r\n| summarize counts = count() by policyAssignmentId, policyAssignmentScope, max_stateWeight, policyAssignmentName\r\n| summarize overallStateWeight = max(max_stateWeight),\r\nnonCompliantCount = sumif(counts, max_stateWeight == 300),\r\ncompliantCount = sumif(counts, max_stateWeight == 200),\r\nconflictCount = sumif(counts, max_stateWeight == 100),\r\nexemptCount = sumif(counts, max_stateWeight == 50) by policyAssignmentId, policyAssignmentScope, policyAssignmentName\r\n| extend totalResources = todouble(nonCompliantCount + compliantCount + conflictCount + exemptCount)\r\n| extend compliancePercentage = iff(totalResources == 0, todouble(100), 100 * todouble(compliantCount + exemptCount) / totalResources)\r\n| project policyAssignmentName, scope = policyAssignmentScope,\r\ncomplianceState = iff(overallStateWeight == 300, 'noncompliant', iff(overallStateWeight == 200, 'compliant', iff(overallStateWeight == 100, 'conflict', iff(overallStateWeight == 50, 'exempt', 'notstarted')))),\r\ncompliancePercentage,\r\ncompliantCount,\r\nnonCompliantCount,\r\nconflictCount,\r\nexemptCount", "size": 3, "queryType": 1, "resourceType": "microsoft.resourcegraph/resources", "visualization": "table", "tileSettings": { "showBorder": false, "titleContent": { "columnMatch": "policyAssignmentName", "formatter": 1 }, "leftContent": { "columnMatch": "compliancePercentage", "formatter": 12, "formatOptions": { "palette": "auto" }, "numberFormat": { "unit": 17, "options": { "maximumSignificantDigits": 3, "maximumFractionDigits": 2 } } } } }, "name": "query - 24" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "policyResources\r\n| where type =~'Microsoft.Authorization/PolicyAssignments'\r\n| project policyAssignmentId = tolower(tostring(id)), policyAssignmentName = name, policyAssignmentDisplayName = tostring(properties.displayName), policyAssignmentScope = tostring(properties.scope), policyAssignmentDefinitionId = tolower(properties.policyDefinitionId), policyAssignmentNotScopes = tolower(properties.notScopes)\r\n| join kind=leftouter(\r\n policyResources\r\n | where type =~'Microsoft.Authorization/PolicySetDefinitions' or type =~'Microsoft.Authorization/PolicyDefinitions'\r\n | project definitionId = tolower(id), type, numberOfPolicies = array_length(properties.policyDefinitions), category = tostring(properties.metadata.category), numberOfGroups= array_length(properties.policyDefinitionGroups), mode = tostring(properties.mode)\r\n | extend isRegulatoryInitiative = iff(category =~ 'Regulatory Compliance', true, false)\r\n | extend definitionType = iff(type =~ 'Microsoft.Authorization/PolicysetDefinitions', 'initiative', 'policy')\r\n | extend isRPMode = iff(mode startswith 'Microsoft.', true, false)\r\n | project definitionId, numberOfPolicies, category, numberOfGroups, isRegulatoryInitiative, definitionType, isRPMode\r\n) on $left.policyAssignmentDefinitionId == $right.definitionId\r\n| join kind=leftouter(\r\n policyResources\r\n | where type =~ 'Microsoft.PolicyInsights/PolicyStates'\r\n | extend complianceState = tostring(properties.complianceState)\r\n | extend policyStateResourceId =id, resourceId = tostring(properties.resourceId), policyAssignmentId = tostring(properties.policyAssignmentId), policyDefinitionId = tostring(properties.policyDefinitionId), policySetDefinitionId = tostring(properties.policySetDefinitionId), policyDefinitionReferenceId = tostring(properties.policyDefinitionReferenceId), policyDefinitionAction = tostring(properties.policyDefinitionAction), policyDefinitionGroupNames = iff(isnotnull(properties.policyDefinitionGroupNames), properties.policyDefinitionGroupNames, dynamic([''])), stateWeight = toint(properties.stateWeight)\r\n | summarize max(stateWeight) by resourceId, policyAssignmentId, policySetDefinitionId\r\n | summarize resourceCounts = count() by policyAssignmentId, policySetDefinitionId, max_stateWeight\r\n| extend complianceState = case(\r\nmax_stateWeight == 300, 'noncompliant',\r\nmax_stateWeight == 200, 'compliant',\r\nmax_stateWeight == 100, 'conflict',\r\nmax_stateWeight == 50, 'exempt',\r\nmax_stateWeight == 10, 'unknown',\r\n'notapplicable')\r\n | extend pack = pack('complianceState', complianceState, 'resourceCounts', resourceCounts), numberOfNonCompliantResources = toint(iff(complianceState =~ 'NonCompliant', resourceCounts,0))\r\n | summarize numberOfNonCompliantResources = max(numberOfNonCompliantResources), details = makelist(pack) by policyAssignmentId, policySetDefinitionId\r\n | limit 5000\r\n) on $left.policyAssignmentId == $right.policyAssignmentId\r\n| sort by numberOfNonCompliantResources desc\r\n| project-away policyAssignmentId1", "size": 0, "title": "Policies overall satistics : ", "queryType": 1, "resourceType": "microsoft.resourcegraph/resources", "gridSettings": { "sortBy": [ { "itemKey": "policyAssignmentName", "sortOrder": 1 } ] }, "sortBy": [ { "itemKey": "policyAssignmentName", "sortOrder": 1 } ] }, "name": "query - 25" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "GuestConfigurationResources\r\n| where type =~ 'microsoft.guestconfiguration/guestconfigurationassignments'\r\n| where properties.complianceStatus == 'NonCompliant'\r\n| project id, name, resources = properties.latestAssignmentReport.resources, machine = split(properties.targetResourceId,'/')[(-1)], status = tostring(properties.complianceStatus)\r\n| extend resources = iff(isnull(resources[0]), dynamic([{}]), resources)\r\n| mvexpand resources\r\n| extend reasons = resources.reasons\r\n| extend reasons = iff(isnull(reasons[0]), dynamic([{}]), reasons)\r\n| mvexpand reasons\r\n| where machine == 'hyperv2'\r\n| project id, machine, name, status, resource = resources.resourceId, reason = reasons.phrase", "size": 0, "title": "Zoom on 1 machine : ", "queryType": 1, "resourceType": "microsoft.resourcegraph/resources" }, "name": "query - 26" }, { "type": 1, "content": { "json": "## Security Compliance (Policy) section\r\n---\r\n\r\nAdd all your notes regarding security", "style": "error" }, "name": "text - analyse - Network - Copy - Copy - Copy", "styleSettings": { "showBorder": true } } ], "exportParameters": true }, "name": "group - security - Compliance", "styleSettings": { "showBorder": true } }, { "type": 12, "content": { "version": "NotebookGroup/1.0", "groupType": "editable", "title": "Events Logs", "expandable": true, "items": [ { "type": 1, "content": { "json": "\r\n![MS Logo](https://arcimages.blob.core.windows.net/{CustomerNameURL}/arc-event-log.jpg \"Gameloft\")\r\n### Event log related data\r\n\r\nLet's see in details", "style": "info" }, "name": "texte - 2 - Copy" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "Event\r\n| project Computer, EventLevelName, Source, RenderedDescription", "size": 0, "title": "Events detected in the passed 24 hours :", "timeContext": { "durationMs": 86400000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces" }, "name": "query - 8" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "Event\r\n | where TimeGenerated >= ago(1d)\r\n | summarize EL=count() by Source\r\n\r\n", "size": 0, "title": "Number of event per source, 24 hours", "timeContext": { "durationMs": 86400000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces", "visualization": "piechart" }, "customWidth": "50", "name": "query - 13" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "\r\nEvent\r\n| where TimeGenerated >= ago(1d)\r\n| summarize ELN=count() by Computer\r\n| order by ELN", "size": 0, "title": "Number of errors per machine in 24 hours", "timeContext": { "durationMs": 86400000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces", "visualization": "tiles", "tileSettings": { "showBorder": false, "titleContent": { "columnMatch": "Computer", "formatter": 1 }, "leftContent": { "columnMatch": "ELN", "formatter": 12, "formatOptions": { "palette": "auto" }, "numberFormat": { "unit": 17, "options": { "maximumSignificantDigits": 3, "maximumFractionDigits": 2 } } } } }, "customWidth": "50", "name": "query - 13 - Copy" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "let starttime = 30d;\r\n let endtime = 30d;\r\n SecurityEvent\r\n | where TimeGenerated >= ago(endtime) \r\n | where EventID == 4624 and LogonType == 10\r\n | summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated), ConnectionCount = count() \r\n by Account = tolower(Account), Computer = toupper(Computer), IpAddress, AccountType, Activity, LogonTypeName, ProcessName\r\n // use left anti to exclude anything from the previous 14 days that is not rare\r\n | join kind=leftanti (\r\n SecurityEvent\r\n | where TimeGenerated between (ago(starttime) .. ago(endtime))\r\n | where EventID == 4624\r\n | summarize by Computer = toupper(Computer), IpAddress, Account = tolower(Account)\r\n ) on Account, Computer\r\n | summarize StartTime = min(StartTime), EndTime = max(EndTime), ConnectionCount = sum(ConnectionCount) \r\n by Account, Computer, IpAddress, AccountType, Activity, LogonTypeName, ProcessName", "size": 0, "title": "RDP authentication (an exemple of ID filtering) :", "timeContext": { "durationMs": 2592000000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces", "visualization": "table" }, "name": "query - 13 - Copy - Copy - Copy" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "SecurityEvent\r\n | where EventID == 4624 //and LogonType == 8\r\n // | summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated), ConnectionCount = count() \r\n|summarize nb=count() by AuthenticationPackageName\r\n|order by nb\r\n//2 \tInteractive (logon at keyboard and screen of system)\r\n//3\tNetwork (i.e. connection to shared folder on this computer from elsewhere on network)\r\n//4\tBatch (i.e. scheduled task)\r\n//5\tService (Service startup)\r\n//7\tUnlock (i.e. unnattended workstation with password protected screen saver)\r\n//8\tNetworkCleartext (Logon with credentials sent in the clear text. Most often indicates a logon to IIS with \"basic authentication\") See this article for more information.\r\n//9\tNewCredentials such as with RunAs or mapping a network drive with alternate credentials. This logon type does not seem to show up in any events. If you want to track users attempting to logon with alternate credentials see 4648. MS says \"A caller cloned its current token and specified new credentials for outbound connections. The new logon session has the same local identity, but uses different credentials for other network connections.\"\r\n//10\tRemoteInteractive (Terminal Services, Remote Desktop or Remote Assistance)\r\n//11\tCachedInteractive (logon with cached domain credentials such as when logging on to a laptop when away from the network)", "size": 0, "title": "Authentication events (can filter more) :", "timeContext": { "durationMs": 2592000000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces", "visualization": "table" }, "name": "query - 13 - Copy - Copy - Copy - Copy" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "Event\r\n| summarize total=count() by Source\r\n| order by total", "size": 0, "title": "Number of event per \"Source\" : ", "timeContext": { "durationMs": 2592000000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces" }, "name": "query - 11" }, { "type": 9, "content": { "version": "KqlParameterItem/1.0", "parameters": [ { "id": "5bf2327b-ba36-4734-94ef-873076e3f75e", "version": "KqlParameterItem/1.0", "name": "ServerEvent", "type": 2, "query": "Event\r\n| distinct Computer", "value": "LAPTOP-DFSU93JS", "typeSettings": { "additionalResourceOptions": [] }, "timeContext": { "durationMs": 172800000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces" } ], "style": "pills", "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces" }, "name": "parameters - 9" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "Event\r\n| where Computer ==\"{ServerEvent}\"\r\n| distinct Source, EventLog, Computer, EventLevelName, RenderedDescription\r\n\r\n", "size": 0, "title": "Events for \"{ServerEvent}\" :", "timeContext": { "durationMs": 86400000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces" }, "name": "query - 11 - Copy" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "Event\r\n| where Computer ==\"{ServerEvent}\"\r\n | summarize EL=count() by RenderedDescription, Source, EventLog\r\n|order by EL desc\r\n", "size": 0, "title": "Count per event \"{ServerEvent}\" :", "timeContext": { "durationMs": 86400000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces" }, "name": "query - 11 - Copy - Copy" }, { "type": 1, "content": { "json": "## Event related analysis\r\n---\r\n\r\nDetails", "style": "error" }, "name": "text - analyse - Evenements OS", "styleSettings": { "showBorder": true } } ] }, "name": "group - event log", "styleSettings": { "showBorder": true } }, { "type": 12, "content": { "version": "NotebookGroup/1.0", "groupType": "editable", "title": "Inventory", "expandable": true, "items": [ { "type": 1, "content": { "json": "\r\n![MS Logo](https://arcimages.blob.core.windows.net/{CustomerNameURL}/arc-Software-inventory.jpg \"Gameloft\")\r\n### Event log related data\r\n\r\nLet's see in details" }, "name": "text - 4" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "ConfigurationData\r\n| distinct SvcName,SvcDisplayName, SvcPath, SvcDescription, ConfigDataType\r\n| order by SvcName asc", "size": 0, "title": "List all the services and details found on all machines :", "timeContext": { "durationMs": 86400000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces" }, "name": "query - 0" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "ConfigurationData\r\n| where SvcState == \"Stopped\"\r\n| project Computer, SvcName, SvcDisplayName, SvcState, TimeGenerated\r\n| order by Computer\r\n", "size": 0, "title": "List of Services \"not running\" per machine", "timeContext": { "durationMs": 86400000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces" }, "name": "query - 1" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "ConfigurationData\r\n| where Publisher !=\"Microsoft Corporation\"\r\n| project Publisher, SoftwareName, CurrentVersion\r\n| order by Publisher\r\n", "size": 0, "title": "List of product for vendor not Microsoft : ", "timeContext": { "durationMs": 86400000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces" }, "name": "query - 3" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "ConfigurationData\r\n|where Publisher !=\"Microsoft Corporation\"\r\n| summarize total=count() by ConfigDataType", "size": 0, "title": "Split of sofware per type : ", "timeContext": { "durationMs": 86400000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces", "visualization": "piechart", "chartSettings": { "showLegend": true } }, "customWidth": "50", "name": "query - 3 - Copy" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "ConfigurationData\r\n|where Publisher !=\"Microsoft Corporation\" and Publisher !=\"\"\r\n| summarize total=count() by SvcName", "size": 0, "title": "List of Services where Publisher not Microsoft : ", "timeContext": { "durationMs": 86400000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces", "visualization": "table", "chartSettings": { "showLegend": true } }, "customWidth": "50", "name": "query - 3 - Copy - Copy" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "ConfigurationData\r\n|where SvcName==\"\"\r\n|project SoftwareName, SoftwareType, Publisher\r\n//, CurrentVersion", "size": 0, "title": "List of software", "timeContext": { "durationMs": 86400000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces", "visualization": "table", "chartSettings": { "showLegend": true } }, "customWidth": "50", "name": "query - 3 - Copy - Copy - Copy" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "ConfigurationData\r\n|where SvcName==\"\"\r\n|summarize tot=count() by Publisher", "size": 0, "title": "List of software", "timeContext": { "durationMs": 86400000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces", "visualization": "piechart", "chartSettings": { "showLegend": true } }, "customWidth": "50", "name": "query - 3 - Copy - Copy - Copy - Copy" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "ConfigurationData\r\n|where SvcName==\"\" and Publisher==\"\"\r\n|summarize tot=count() by SoftwareName", "size": 0, "title": "List of software with no publisher name : ", "timeContext": { "durationMs": 86400000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces", "visualization": "tiles", "tileSettings": { "titleContent": { "columnMatch": "SoftwareName", "formatter": 1 }, "leftContent": { "columnMatch": "tot", "formatter": 12, "formatOptions": { "palette": "auto" }, "numberFormat": { "unit": 17, "options": { "maximumSignificantDigits": 3, "maximumFractionDigits": 2 } } }, "showBorder": false, "size": "auto" }, "chartSettings": { "showLegend": true } }, "customWidth": "50", "name": "query - 3 - Copy - Copy - Copy - Copy - Copy" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "ConfigurationData\r\n| where SoftwareName ==\"Microsoft SQL Server 2016 (64-bit)\"\r\n| project Computer\r\n\r\n", "size": 0, "title": "List of software with no publisher name : ", "timeContext": { "durationMs": 86400000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces", "visualization": "table", "tileSettings": { "titleContent": { "columnMatch": "SoftwareName", "formatter": 1 }, "leftContent": { "columnMatch": "tot", "formatter": 12, "formatOptions": { "palette": "auto" }, "numberFormat": { "unit": 17, "options": { "maximumSignificantDigits": 3, "maximumFractionDigits": 2 } } }, "showBorder": false, "size": "auto" }, "chartSettings": { "showLegend": true } }, "customWidth": "50", "name": "query - 3 - Copy - Copy - Copy - Copy - Copy - Copy" } ] }, "name": "group - software inventory", "styleSettings": { "showBorder": true } }, { "type": 12, "content": { "version": "NotebookGroup/1.0", "groupType": "editable", "title": "Azure Stack HCI", "expandable": true, "items": [ { "type": 1, "content": { "json": "![MS Logo](https://arcimages.blob.core.windows.net/{CustomerNameURL}/arc-hci.jpg \"Microsoft\")\r\n## Azure Stack HCI\r\nAll details regarding running HCI on prem, and managing it from Azure.", "style": "success" }, "name": "texte - 2 - Copier - Copier - Copier - Copy - Copy" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "resources\r\n| where type==\"microsoft.azurestackhci/clusters\"\r\n| extend Properties = parse_json (properties)\r\n| extend RProperties=parse_json (Properties.['reportedProperties'])\r\n| extend Node0=parse_json (RProperties.['nodes'][0])\r\n| extend Node1=parse_json (RProperties.['nodes'][1])\r\n| extend Node2=parse_json (RProperties.['nodes'][2])\r\n| extend Node3=parse_json (RProperties.['nodes'][3])\r\n| extend Node4=parse_json (RProperties.['nodes'][4])\r\n| extend Node5=parse_json (RProperties.['nodes'][5])\r\n| extend Node6=parse_json (RProperties.['nodes'][6])\r\n| extend Node7=parse_json (RProperties.['nodes'][7])\r\n| extend Node8=parse_json (RProperties.['nodes'][8])\r\n| extend Node9=parse_json (RProperties.['nodes'][9])\r\n|project name, Properties.status, RProperties.clusterVersion, Properties.trialDaysRemaining, Node0,Node1,properties,location, RProperties.clusterName, type, tags, Properties.provisioningState\r\n\r\n ", "size": 0, "title": "List of HCI Cluster and details :", "queryType": 1, "resourceType": "microsoft.resourcegraph/resources", "visualization": "table", "tileSettings": { "showBorder": false } }, "name": "query - 17" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "Resources\r\n| where type==\"microsoft.azurestackhci/clusters\"\r\n| extend ClusterProperties = parse_json (properties)\r\n| extend RClusterProperties=parse_json (ClusterProperties.['reportedProperties'])\r\n| extend TheNode=parse_json (RClusterProperties.['nodes'][0])\r\n|project name, ClusterProperties.status, ClusterProperties.lastSyncTimestamp, TheNode, TheNode.id -1, TheNode.name, TheNode.osName, TheNode.manufacturer,TheNode.model, TheNode.serialNumber, TheNode.coreCount, TheNode.memoryInGiB, TheNode.windowsServerSubscription\r\n\r\n ", "size": 0, "title": "Node 1 :", "queryType": 1, "resourceType": "microsoft.resourcegraph/resources" }, "name": "query - 17 - Copy", "styleSettings": { "showBorder": true } }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "Resources\r\n| where type==\"microsoft.azurestackhci/clusters\"\r\n| extend ClusterProperties = parse_json (properties)\r\n| extend RClusterProperties=parse_json (ClusterProperties.['reportedProperties'])\r\n| extend TheNode=parse_json (RClusterProperties.['nodes'][1])\r\n|project name, ClusterProperties.status, ClusterProperties.lastSyncTimestamp, TheNode, TheNode.id -1, TheNode.name, TheNode.osName, TheNode.manufacturer,TheNode.model, TheNode.serialNumber, TheNode.coreCount, TheNode.memoryInGiB, TheNode.windowsServerSubscription\r\n\r\n ", "size": 0, "title": "Node 2 :", "queryType": 1, "resourceType": "microsoft.resourcegraph/resources" }, "name": "query - 17 - Copy - Copy" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "Event\r\n////ID 3000 for servers\r\n| where EventLog==\"Microsoft-Windows-Health/Operational\" or EventLog==\"Microsoft-Windows-SDDC-Management/Operational\"\r\n| where EventID==\"3000\" // Servers\r\n//| where EventID==\"3001\" // Drives\r\n//| where EventID==\"3002\" // Volumes\r\n//| where EventID==\"3003\" // VMs\r\n//| where EventID==\"3004\" // Clusters\r\n| project Computer, EventID,Source, EventLog, EventLevelName, ParameterXml, EventData, RenderedDescription\r\n\r\n// https://docs.microsoft.com/fr-fr/windows-server/manage/windows-admin-center/use/manage-hyper-converged\r\n\r\n\r\n", "size": 0, "title": "Events related to HCI, 300 Event which means servers (nodes) :", "timeContext": { "durationMs": 604800000 }, "showExportToExcel": true, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces" }, "name": "query - 5" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "Perf\r\n|where CounterName==\"% Processor Time\" and Computer==\"AzSHOST2.contoso.com\"\r\n\r\n//| where ObjectName == \"Physical Disk\" \r\n//.| summarize avg(CounterValue) by bin(TimeGenerated, 1sec), CounterName, Computer\r\n// we use variables\r\n", "size": 0, "title": "Processor usage for a node :", "timeContext": { "durationMs": 86400000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces", "visualization": "timechart" }, "name": "query - 6" }, { "type": 1, "content": { "json": "## HCI\r\n---\r\n\r\nAs I am using a demo environment hosted on virtual machines. As a result, some features are not visible on this lab (resource bridge, etc). Also, on real harware, you should get more data from the \"hardware vendor\", that will be uploaded in Azure Log Analytics workspace. ", "style": "error" }, "name": "text - analyse - AD", "styleSettings": { "showBorder": true } } ], "exportParameters": true }, "name": "group - HCI", "styleSettings": { "showBorder": true } }, { "type": 12, "content": { "version": "NotebookGroup/1.0", "groupType": "editable", "title": "Machines Linux", "expandable": true, "items": [ { "type": 1, "content": { "json": "\r\n![MS Logo](https://arcimages.blob.core.windows.net/{CustomerNameURL}/arc-linux.jpg \"Gameloft\")\r\n### Linux related data\r\nBelow all the data gathered by our solutions for the Linux machines", "style": "info" }, "name": "texte - 2 - Copy - Copy - Copy" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "Heartbeat | where OSType == \"Linux\" | summarize AggregatedValue = dcount(Computer) by Computer // Oql: Type=Heartbeat OSType == \"Linux\" | measure countdistinct(Computer) by Computer // Settings: {NAV: True} // WorkspaceId: {00000000-0000-0000-0000-000000000000} // Version: 0.1.91", "size": 0, "title": "Linux machines : ", "timeContext": { "durationMs": 604800000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces" }, "name": "query - 15" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "\r\nPerf\r\n| where Computer==\"u1\" and CounterName ==\"% Processor Time\"\r\n| project TimeGenerated, CounterName, CounterValue\r\n| summarize avg(CounterValue) by CounterName, bin(TimeGenerated, 1m)\r\n// Computer==\"northwindriskserver1\"", "size": 0, "title": "CPU of a Linux machine (Computer==\"northwindriskserver1\") : ", "timeContext": { "durationMs": 604800000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces", "visualization": "linechart" }, "name": "query - 3" }, { "type": 1, "content": { "json": "## Notes from Linux\r\n---\r\n\r\nAdd your comments..", "style": "error" }, "name": "text - analyse - Linux", "styleSettings": { "showBorder": true } } ] }, "name": "group - Linux", "styleSettings": { "showBorder": true } }, { "type": 12, "content": { "version": "NotebookGroup/1.0", "groupType": "editable", "title": "Active Directory", "expandable": true, "items": [ { "type": 1, "content": { "json": "![MS Logo](https://arcimages.blob.core.windows.net/{CustomerNameURL}/arc-ad.jpg \"Microsoft\")\r\n## Active Directory\t\r\nAll details regarding configuration and replication", "style": "success" }, "name": "texte - 2 - Copier - Copier - Copier - Copy - Copy" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "ADAssessmentRecommendation | summarize arg_max(TimeGenerated, *) by RecommendationId, Computer, AssessmentName, Recommendation, Description, FocusArea, ActionArea | where RecommendationResult == \"Failed\" and (Technology has \"\" or Technology == \"\") | sort by RecommendationWeight desc, TimeGenerated desc", "size": 0, "title": "Recommendations last 30 days :", "timeContext": { "durationMs": 2592000000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces", "visualization": "table" }, "name": "query - 21" }, { "type": 1, "content": { "json": "## AD section\r\n---\r\n\r\n[AD Assessment Portal](https://ms.portal.azure.com/#blade/Microsoft_Azure_AdvisorPortalExtension/WorkspaceOverviewBlade/id/%2Fsubscriptions%2F770207bd-ebc7-4cda-ad79-03507721ea17%2Fresourcegroups%2Fmyworkstations%2Fproviders%2Fmicrosoft.operationalinsights%2Fworkspaces%2Fmyworkstations/parameters/%7B%7D)]", "style": "error" }, "name": "text - analyse - AD", "styleSettings": { "showBorder": true } } ], "exportParameters": true }, "name": "group - AD", "styleSettings": { "showBorder": true } }, { "type": 9, "content": { "version": "KqlParameterItem/1.0", "parameters": [ { "id": "6bbd632a-43ad-4e8e-b9a7-337a90b6ca8b", "version": "KqlParameterItem/1.0", "name": "DateTimeRange", "type": 4, "description": "Select the data/time range for the reports below", "isRequired": true, "value": { "durationMs": 15540000, "endTime": "2021-12-21T16:19:00.000Z" }, "typeSettings": { "selectableValues": [ { "durationMs": 300000 }, { "durationMs": 900000 }, { "durationMs": 1800000 }, { "durationMs": 3600000 }, { "durationMs": 14400000 }, { "durationMs": 43200000 }, { "durationMs": 86400000 }, { "durationMs": 172800000 }, { "durationMs": 259200000 }, { "durationMs": 604800000 }, { "durationMs": 1209600000 }, { "durationMs": 2419200000 }, { "durationMs": 2592000000 } ], "allowCustom": true }, "timeContext": { "durationMs": 86400000 } }, { "id": "5a74d5c4-87e1-444c-92bb-ceea60b2dc92", "version": "KqlParameterItem/1.0", "name": "MachineToInvestigate", "type": 2, "description": "Select the machine to investigate", "isRequired": true, "query": "Heartbeat \n| project Computer \n| distinct Computer\n| order by Computer asc\n", "value": "AzSHOST2.contoso.com", "typeSettings": { "additionalResourceOptions": [], "showDefault": false }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces" }, { "id": "7adafbf0-aeec-4954-b967-f41a8b7d773c", "version": "KqlParameterItem/1.0", "name": "MachineToInvestigateSecond", "label": "Second machine to compare", "type": 2, "description": "Second machine to compare", "query": "Heartbeat | where TimeGenerated > ago(200h)\r\n| project Computer | distinct Computer\r\n", "value": "AzSHOST1.contoso.com", "typeSettings": { "additionalResourceOptions": [], "showDefault": false }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces" }, { "id": "5302f3ed-eb79-4b2b-ba08-7b2223e8e09c", "version": "KqlParameterItem/1.0", "name": "t1", "label": "Type of machine : max MB/s TEMP", "type": 2, "description": "Threshold1", "value": "500000000", "typeSettings": { "additionalResourceOptions": [], "showDefault": false }, "jsonData": " [{ \"value\": \"125000000\", \"label\": \"2 CPU\" },\r\n { \"value\": \"250000000\", \"label\": \"4 CPU\"},\r\n { \"value\": \"500000000\", \"label\": \"8 CPU\"},\r\n { \"value\": \"1000000000\", \"label\": \"16 CPU\" }]" }, { "id": "fd9436bf-b71e-4b81-8916-4e6b3a1c99c3", "version": "KqlParameterItem/1.0", "name": "t2", "label": "Type of attached disk : Max MB/S ", "type": 2, "description": "Max MB/S Attached ", "value": "60000000", "typeSettings": { "additionalResourceOptions": [], "showDefault": false }, "jsonData": " [{ \"value\": \"500000\", \"label\": \"HDD\" },\r\n { \"value\": \"750000\", \"label\": \"SSD Std\"},\r\n { \"value\": \"900000\", \"label\": \"SSD Prem\"},\r\n { \"value\": \"4000000000\", \"label\": \"Ultra\" },\r\n { \"value\": \"60000000\", \"label\": \"E10 Standard SSD\"} ]\r\n" } ], "style": "pills", "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces" }, "name": "parameters - 13" }, { "type": 12, "content": { "version": "NotebookGroup/1.0", "groupType": "editable", "title": "Performance Counters", "expandable": true, "items": [ { "type": 1, "content": { "json": "\r\n![MS Logo](https://arcimages.blob.core.windows.net/{CustomerNameURL}/arc-perfc.jpg \"Microsoft\")\r\n## Performance Counters\r\nPerformance Counters", "style": "success" }, "name": "texte - 2 - Copier - Copier" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "// What data is being collected? \r\n// List the collected performance counters and object types (Process, Memory, Processor…) \r\nPerf\r\n| where TimeGenerated {DateTimeRange}\r\n| summarize by ObjectName, CounterName", "size": 0, "title": "Liste des compteurs disponibles : adapter la configuration sur le Worskspace log analytics pour plus d'informations", "timeContext": { "durationMs": 259200000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces" }, "name": "query - 1" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "Perf \r\n| where ObjectName == \"Logical Disk\" and CounterName == \"Disk Writes/sec\" and InstanceName!=\"_Total\"\r\n| summarize avg(CounterValue), min(CounterValue), max(CounterValue) by Computer, bin(TimeGenerated, 3d), InstanceName\r\n//end CPU", "size": 0, "title": "Disques : moyenne, max et min srur 30 jours", "timeContext": { "durationMs": 2592000000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces", "visualization": "table", "gridSettings": { "sortBy": [ { "itemKey": "avg_CounterValue", "sortOrder": 1 } ] }, "sortBy": [ { "itemKey": "avg_CounterValue", "sortOrder": 1 } ], "tileSettings": { "showBorder": false, "titleContent": { "columnMatch": "Computer", "formatter": 1 }, "leftContent": { "columnMatch": "avg_CounterValue", "formatter": 12, "formatOptions": { "palette": "auto" }, "numberFormat": { "unit": 17, "options": { "maximumSignificantDigits": 3, "maximumFractionDigits": 2 } } } } }, "name": "query - 1 - Copy" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "Perf\r\n| where ObjectName == \"Logical Disk\" and CounterName != \"Free Megabytes\" and CounterName != \"% Used Space\" and CounterName != \"% Used Inodes\" and TimeGenerated {DateTimeRange} and Computer ==\"{MachineToInvestigate}\"\r\n| summarize max(CounterValue) by bin(TimeGenerated, 1sec), CounterName\r\n// we use variables\r\n\r\n", "showQuery": true, "size": 2, "showAnnotations": true, "title": "SCOMPARE 1 - Suivi des disques logiques pour : \"{MachineToInvestigate}\", {DateTimeRange}, {t1}", "timeContext": { "durationMs": 14400000, "endTime": "2021-12-14T17:40:00.000Z" }, "showRefreshButton": true, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces", "visualization": "table", "chartSettings": { "showLegend": true, "customThresholdLine": "{t1}", "customThresholdLineStyle": 0, "showDataPoints": true } }, "name": "query - 0" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "Perf\r\n| where ObjectName == \"Logical Disk\" and CounterName != \"Free Megabytes\" and CounterName != \"% Used Space\" and CounterName != \"% Used Inodes\" and TimeGenerated {DateTimeRange} and Computer ==\"{MachineToInvestigateSecond}\"\r\n| summarize avg(CounterValue) by bin(TimeGenerated, 1sec), CounterName, Computer\r\n// we use variables\r\n\r\n", "showQuery": true, "size": 2, "showAnnotations": true, "title": "SCOMPARE 2 - Suivi des disques logiques pour : \"{MachineToInvestigateSeconde}\", {DateTimeRange}, {t2}", "noDataMessageStyle": 3, "timeContext": { "durationMs": 2592000000 }, "showRefreshButton": true, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces", "visualization": "linechart", "chartSettings": { "customThresholdLine": "{t}", "customThresholdLineStyle": 1, "showDataPoints": true } }, "name": "query - 0 - Copy" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "Perf\r\n| where ObjectName == \"Physical Disk\" and TimeGenerated {DateTimeRange} and Computer ==\"{MachineToInvestigate}\"\r\n| summarize avg(CounterValue) by bin(TimeGenerated, 1sec), CounterName, Computer\r\n// we use variables\r\n\r\n", "showQuery": true, "size": 2, "showAnnotations": true, "title": "COMPARE 1 : Suivi des disques Physiques : \"{MachineToInvestigate}\", {DateTimeRange}. Threshold : {t1}", "noDataMessageStyle": 3, "timeContext": { "durationMs": 2592000000 }, "showRefreshButton": true, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces", "visualization": "linechart", "chartSettings": { "showLegend": true, "customThresholdLine": "{t1}", "customThresholdLineStyle": 3 } }, "name": "query - 0 - Copy - Copy" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "Perf\r\n| where ObjectName == \"Physical Disk\" and TimeGenerated {DateTimeRange} and Computer ==\"{MachineToInvestigateSecond}\"\r\n| summarize avg(CounterValue) by bin(TimeGenerated, 1sec), CounterName, Computer\r\n// we use variables\r\n\r\n", "showQuery": true, "size": 2, "showAnnotations": true, "title": "COMPARE 2 : Suivi des disques Physique : \"{MachineToInvestigateSecond}\", {DateTimeRange}. Threshold : {t2}", "noDataMessageStyle": 3, "timeContext": { "durationMs": 2592000000 }, "showRefreshButton": true, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces", "visualization": "linechart", "chartSettings": { "showLegend": true, "customThresholdLine": "{t2}", "customThresholdLineStyle": 3 } }, "name": "query - 0 - Copy" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "Perf\r\n| where ObjectName == \"Logical Disk\" and CounterName != \"Free Megabytes\" and CounterName != \"% Used Space\" and CounterName != \"% Used Inodes\" and TimeGenerated {DateTimeRange} and Computer ==\"{MachineToInvestigate}\" and InstanceName!=\"_Total\"\r\n| summarize avg(CounterValue) by bin(TimeGenerated, 1sec), InstanceName\r\n// we use variables\r\n\r\n", "showQuery": true, "size": 2, "showAnnotations": true, "title": "Répartition des accès disque pour \"{MachineToInvestigate}\", {DateTimeRange}", "timeContext": { "durationMs": 2592000000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces", "visualization": "piechart" }, "name": "query - 0 - Copy - Copy" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "Perf\r\n| where ObjectName == \"Container\" and CounterName != \"Free Megabytes\" and CounterName != \"% Used Space\" and CounterName != \"% Used Inodes\" and TimeGenerated {DateTimeRange} and Computer ==\"{MachineToInvestigate}\"\r\n| summarize avg(CounterValue) by bin(TimeGenerated, 1sec),CounterName, InstanceName\r\n// we use variables\r\n\r\n", "showQuery": true, "size": 2, "showAnnotations": true, "title": "Suivi des conteneurs pour : \"{MachineToInvestigate}\", {DateTimeRange}", "timeContext": { "durationMs": 2592000000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces", "visualization": "areachart", "tileSettings": { "showBorder": false, "titleContent": { "columnMatch": "InstanceName", "formatter": 1 }, "leftContent": { "columnMatch": "avg_CounterValue", "formatter": 12, "formatOptions": { "palette": "auto" }, "numberFormat": { "unit": 17, "options": { "maximumSignificantDigits": 3, "maximumFractionDigits": 2 } } } }, "graphSettings": { "type": 0, "topContent": { "columnMatch": "InstanceName", "formatter": 1 }, "centerContent": { "columnMatch": "avg_CounterValue", "formatter": 1, "numberFormat": { "unit": 17, "options": { "maximumSignificantDigits": 3, "maximumFractionDigits": 2 } } } } }, "name": "query - 0 - Copy - Copy" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "Perf\r\n| where ObjectName == \"Container\" and CounterName == \"Disk Writes MB\" and TimeGenerated {DateTimeRange} and Computer ==\"{MachineToInvestigate}\"\r\n| summarize avg(CounterValue) by bin(TimeGenerated, 1sec), InstanceName\r\n// we use variables\r\n\r\n", "showQuery": true, "size": 2, "showAnnotations": true, "title": "Suivi des conteneurs pour : \"{MachineToInvestigate}\", {DateTimeRange}", "timeContext": { "durationMs": 2592000000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces", "visualization": "areachart", "tileSettings": { "showBorder": false, "titleContent": { "columnMatch": "InstanceName", "formatter": 1 }, "leftContent": { "columnMatch": "avg_CounterValue", "formatter": 12, "formatOptions": { "palette": "auto" }, "numberFormat": { "unit": 17, "options": { "maximumSignificantDigits": 3, "maximumFractionDigits": 2 } } } }, "graphSettings": { "type": 0, "topContent": { "columnMatch": "InstanceName", "formatter": 1 }, "centerContent": { "columnMatch": "avg_CounterValue", "formatter": 1, "numberFormat": { "unit": 17, "options": { "maximumSignificantDigits": 3, "maximumFractionDigits": 2 } } } } }, "name": "query - 0 - Copy - Copy - Copy" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "ContainerProcess_CL \r\n| where TimeGenerated {DateTimeRange} and Computer ==\"{MachineToInvestigate}\"\r\n// we use variables\r\n\r\n", "showQuery": true, "size": 2, "showAnnotations": true, "title": "Suivi des conteneurs pour : \"{MachineToInvestigate}\", {DateTimeRange}", "timeContext": { "durationMs": 2592000000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces", "visualization": "table", "tileSettings": { "showBorder": false, "titleContent": { "columnMatch": "InstanceName", "formatter": 1 }, "leftContent": { "columnMatch": "avg_CounterValue", "formatter": 12, "formatOptions": { "palette": "auto" }, "numberFormat": { "unit": 17, "options": { "maximumSignificantDigits": 3, "maximumFractionDigits": 2 } } } }, "graphSettings": { "type": 0, "topContent": { "columnMatch": "InstanceName", "formatter": 1 }, "centerContent": { "columnMatch": "avg_CounterValue", "formatter": 1, "numberFormat": { "unit": 17, "options": { "maximumSignificantDigits": 3, "maximumFractionDigits": 2 } } } } }, "name": "query - 0 - Copy - Copy - Copy - Copy" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "Perf \r\n| where TimeGenerated > ago(7d)\r\n| where Computer == \"AIR-DC01.gameloft.org\" \r\n| where CounterName == \"% Processor Time\" \r\n| summarize avg(CounterValue) by TimeGenerated", "size": 0, "title": "Zoom : Suivi du % de processeur pour 1 machine spécifique (voir la requete)", "timeContext": { "durationMs": 3600000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces", "visualization": "linechart", "graphSettings": { "type": 0, "topContent": { "columnMatch": "Computer", "formatter": 1 }, "centerContent": { "columnMatch": "AggregatedValue", "formatter": 1, "numberFormat": { "unit": 17, "options": { "maximumSignificantDigits": 3, "maximumFractionDigits": 2 } } } } }, "name": "requête - 10" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "Perf\r\n| where TimeGenerated > now(-60m)\r\n and ObjectName == \"Process\"\r\n and CounterName == \"% Processor Time\"\r\n and InstanceName != \"_Total\"\r\n and InstanceName != \"Idle\"\r\n and CounterValue > 50\r\n| project Computer, InstanceName, CounterValue, TimeGenerated\r\n| order by CounterValue \r\n\r\n //After some noodling on this, the CounterValue for Process is actually the total of all CPUs together. For instances if your server has 16 CPUs, then 100% is 1600. So, how can we dynamically adjust this to a percentage? Using the query above that gave us the CPU count, we can calculate the percentages after we do some joins.", "size": 0, "title": "CPU par process dernière heure pour tous les serveurs", "timeContext": { "durationMs": 43200000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces", "visualization": "table" }, "name": "query - 2" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "//Find Top processes utilizing CPU\r\n// by finding the machine(s) using over 90% of CPU\r\n// then finding the processes using the CPU\r\n// also finding CPU count of the machines to find the actual percentage of CPU being used\r\n// After some noodling on this, the CounterValue for Process is actually the total of all CPUs together. For instances if your server has 16 CPUs, then 100% is 1600. So, how can we dynamically adjust this to a percentage? Using the query above that gave us the CPU count, we can calculate the percentages after we do some joins.\r\n \r\n//defining our CPU threshold\r\nlet CPUThreshold = 30;\r\n \r\n//define time sample rate\r\nlet Time =3d;\r\n \r\n//define Count of processes to return\r\nlet Count = 5;\r\n \r\n//Find instances of total cpu being used above 90% over the last 10 minutes\r\nlet TopCPU = Perf\r\n| where TimeGenerated > now(-Time)\r\n and ObjectName == \"Processor\"\r\n and CounterName == \"% Processor Time\"\r\n and InstanceName == \"_Total\"\r\n and CounterValue > CPUThreshold\r\n| project Computer, ObjectName\r\n , CounterName, CounterValue\r\n , TimeGenerated;\r\n//end query\r\n \r\n// find top Processes, excluding _Total and Idle instances, there may be other instances you want to exclude as well\r\nlet TopProcess = Perf\r\n| where TimeGenerated > now(-Time)\r\n and CounterName == \"% Processor Time\"\r\n and InstanceName != \"_Total\"\r\n and InstanceName != \"Idle\"\r\n| project Computer, ObjectName\r\n , CounterName, InstanceName\r\n , CounterValue, TimeGenerated;\r\n// end query\r\n \r\n// find CPU count for servers(s)\r\nlet FindCPU = Perf\r\n| where TimeGenerated >= ago(24h)\r\n| where ObjectName == \"Processor\"\r\n and CounterName == \"% Processor Time\"\r\n and InstanceName!=\"_Total\"\r\n| sort by InstanceName asc nulls first\r\n| summarize CPUCount = dcount(InstanceName) by Computer;\r\n// end query\r\n \r\n//Join all 3 datasets together\r\nFindCPU | join(TopCPU) on Computer \r\n| join(TopProcess)on Computer\r\n| extend PercentProcessorUsed = CounterValue1 / CPUCount\r\n| summarize avg(PercentProcessorUsed) by Computer, ObjectName\r\n , CounterName, CPUCount \r\n , TotalCPU=CounterValue //rename CounterValue to TotalCPU \r\n , Process=ObjectName1 //rename ObjectName1 to Process \r\n , ProcessTime=CounterName1 //rename CounterName1 to ProcessTime \r\n , ProcessName=InstanceName //rename InstanceName to ProcessName \r\n , TimeGenerated\r\n| where Process == \"Process\"\r\nand avg_PercentProcessorUsed > 10 // only return processes that are using more than X %\r\n| top Count by avg_PercentProcessorUsed desc\r\n| project Computer, CPUCount\r\n , ProcessName , avg_PercentProcessorUsed\r\n , TotalCPU, Process\r\n , ProcessTime, TimeGenerated", "size": 0, "title": "Zoom : Machine et process a haute utilisation de CPU", "timeContext": { "durationMs": 86400000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces" }, "name": "query - 8" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "//Find Top processes utilizing CPU\r\n// by finding the machine(s) using over 90% of CPU\r\n// then finding the processes using the CPU\r\n// also finding CPU count of the machines to find the actual percentage of CPU being used\r\n// After some noodling on this, the CounterValue for Process is actually the total of all CPUs together. For instances if your server has 16 CPUs, then 100% is 1600. So, how can we dynamically adjust this to a percentage? Using the query above that gave us the CPU count, we can calculate the percentages after we do some joins.\r\n \r\n//defining our CPU threshold\r\nlet CPUThreshold = 0;\r\n \r\n//define time sample rate\r\nlet Time =2d;\r\n \r\n//define Count of processes to return\r\nlet Count = 30;\r\n \r\n//Find instances of total cpu being used above 90% over the last 10 minutes\r\nlet TopCPU = Perf\r\n| where TimeGenerated > now(-Time)\r\n and ObjectName == \"Processor\"\r\n and CounterName == \"% Processor Time\"\r\n and InstanceName == \"_Total\"\r\n and Computer==\"AIR-DC01.gameloft.org\"\r\n and CounterValue > CPUThreshold\r\n| project Computer, ObjectName\r\n , CounterName, CounterValue\r\n , TimeGenerated;\r\n//end query\r\n \r\n// find top Processes, excluding _Total and Idle instances, there may be other instances you want to exclude as well\r\nlet TopProcess = Perf\r\n| where TimeGenerated > now(-Time)\r\n and CounterName == \"% Processor Time\"\r\n and InstanceName != \"_Total\"\r\n and InstanceName != \"Idle\"\r\n| project Computer, ObjectName\r\n , CounterName, InstanceName\r\n , CounterValue, TimeGenerated;\r\n// end query\r\n \r\n// find CPU count for servers(s)\r\nlet FindCPU = Perf\r\n| where TimeGenerated >= ago(48h)\r\n| where ObjectName == \"Processor\"\r\n and CounterName == \"% Processor Time\"\r\n and InstanceName!=\"_Total\"\r\n| sort by InstanceName asc nulls first\r\n| summarize CPUCount = dcount(InstanceName) by Computer;\r\n// end query\r\n \r\n//Join all 3 datasets together\r\nFindCPU | join(TopCPU) on Computer \r\n| join(TopProcess)on Computer\r\n| extend PercentProcessorUsed = CounterValue1 / CPUCount\r\n| summarize avg(PercentProcessorUsed) by Computer, ObjectName\r\n , CounterName, CPUCount \r\n , TotalCPU=CounterValue //rename CounterValue to TotalCPU \r\n , Process=ObjectName1 //rename ObjectName1 to Process \r\n , ProcessTime=CounterName1 //rename CounterName1 to ProcessTime \r\n , ProcessName=InstanceName //rename InstanceName to ProcessName \r\n , TimeGenerated\r\n| where Process == \"Process\"\r\nand avg_PercentProcessorUsed > 0 // only return processes that are using more than X %\r\n| top Count by avg_PercentProcessorUsed desc\r\n| project ProcessName , avg_PercentProcessorUsed, TimeGenerated", "size": 0, "title": "Zoom : Machine et process a haute utilisation de CPU", "timeContext": { "durationMs": 86400000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces", "visualization": "barchart", "chartSettings": { "xAxis": "TimeGenerated" } }, "name": "query - 8 - Copy" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "ConfigurationData \r\n|where Computer==\"AIR-DC01.gameloft.org\"\r\n|project TimeGenerated, Computer, SvcDisplayName, SvcName, SvcState, SvcStartupType, SvcPath, SvcDescription\r\n|order by TimeGenerated desc\r\n", "size": 0, "title": "Liste des services et état sur une machine (voir la requete)", "timeContext": { "durationMs": 86400000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces" }, "name": "query - 8" }, { "type": 10, "content": { "chartId": "workbook1737664b-f988-4076-b24b-72d509cef327", "version": "MetricsItem/2.0", "size": 0, "chartType": 2, "resourceType": "microsoft.operationalinsights/workspaces", "metricScope": 0, "resourceIds": [ "/subscriptions/2bccd012-d697-4d40-925e-4739dc325f67/resourceGroups/slg-infra-trs-rgp-cicd-002/providers/Microsoft.OperationalInsights/workspaces/testPerfsGael" ], "timeContext": { "durationMs": 259200000 }, "metrics": [ { "namespace": "microsoft.operationalinsights/workspaces", "metric": "microsoft.operationalinsights/workspaces--Average_Avg. Disk sec/Read", "aggregation": 4, "splitBy": null }, { "namespace": "microsoft.operationalinsights/workspaces", "metric": "microsoft.operationalinsights/workspaces--Average_Avg. Disk sec/Write", "aggregation": 4 }, { "namespace": "microsoft.operationalinsights/workspaces", "metric": "microsoft.operationalinsights/workspaces--Average_Avg. Disk sec/Transfer", "aggregation": 4 } ], "gridSettings": { "rowLimit": 10000 } }, "name": "metric - 13" }, { "type": 1, "content": { "json": "## Performance Counters\r\n---\r\n\r\nadd Details", "style": "error" }, "name": "text - analyse - Compteurs de performance", "styleSettings": { "showBorder": true } } ], "exportParameters": true }, "name": "group - Advanced performance counter", "styleSettings": { "showBorder": true } }, { "type": 12, "content": { "version": "NotebookGroup/1.0", "groupType": "editable", "title": "My blog, web app data :", "expandable": true, "items": [ { "type": 3, "content": { "version": "KqlItem/1.0", "query": "AppServiceHTTPLogs \r\n| where CIp!=\"82.65.97.28\"\r\n| project Category, TimeGenerated, CsMethod, CsUriStem, CIp, UserAgent\r\n\r\n// https://tools.keycdn.com/geo?host=66.249.65.109", "size": 0, "title": "Monitoring a Web App, example of my blog :", "timeContext": { "durationMs": 86400000 }, "queryType": 0, "resourceType": "microsoft.web/sites", "crossComponentResources": [ "/subscriptions/770207bd-ebc7-4cda-ad79-03507721ea17/resourceGroups/camera/providers/Microsoft.Web/sites/wordpress1fesnouf" ], "gridSettings": { "sortBy": [ { "itemKey": "CsUriStem", "sortOrder": 1 } ] }, "sortBy": [ { "itemKey": "CsUriStem", "sortOrder": 1 } ] }, "name": "query - 20" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "let IP_Data = \r\n external_data(network:string,geoname_id:long,continent_code:string,continent_name:string ,country_iso_code:string, country_name:string,is_anonymous_proxy:bool,is_satellite_provider:bool)\r\n ['https://raw.githubusercontent.com/datasets/geoip2-ipv4/master/data/geoip2-ipv4.csv']\r\n with (ignoreFirstRecord=true, format=\"csv\");\r\nAppServiceHTTPLogs\r\n| extend IPAddress = tostring(CIp)\r\n| where CIp!=\"82.65.97.28\" \r\n| where CsUriStem!=\"/\"\r\n| summarize ipCount=count() by IPAddress, CsUriStem\r\n| where isnotempty(IPAddress)\r\n| evaluate ipv4_lookup(IP_Data, IPAddress, network)\r\n//| where country_name != \"United States\"", "size": 0, "title": "Check Web App Connectivity, extract IP, locate country... and URL targeted :", "timeContext": { "durationMs": 2592000000 }, "queryType": 0, "resourceType": "microsoft.web/sites", "crossComponentResources": [ "/subscriptions/770207bd-ebc7-4cda-ad79-03507721ea17/resourceGroups/camera/providers/Microsoft.Web/sites/wordpress1fesnouf" ], "gridSettings": { "sortBy": [ { "itemKey": "country_iso_code", "sortOrder": 1 } ] }, "sortBy": [ { "itemKey": "country_iso_code", "sortOrder": 1 } ] }, "customWidth": "50", "name": "query - 22" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "let IP_Data = \r\n external_data(network:string,geoname_id:long,continent_code:string,continent_name:string ,country_iso_code:string, country_name:string,is_anonymous_proxy:bool,is_satellite_provider:bool)\r\n ['https://raw.githubusercontent.com/datasets/geoip2-ipv4/master/data/geoip2-ipv4.csv']\r\n with (ignoreFirstRecord=true, format=\"csv\");\r\nAppServiceHTTPLogs\r\n| extend IPAddress = tostring(CIp)\r\n| summarize ipCount=count() by IPAddress\r\n| where isnotempty(IPAddress)\r\n| evaluate ipv4_lookup(IP_Data, IPAddress, network)\r\n//| where country_name != \"United States\"", "size": 0, "title": "Check Web App Connectivity, extract IP, locate country", "timeContext": { "durationMs": 2592000000 }, "showRefreshButton": true, "queryType": 0, "resourceType": "microsoft.web/sites", "crossComponentResources": [ "/subscriptions/770207bd-ebc7-4cda-ad79-03507721ea17/resourceGroups/camera/providers/Microsoft.Web/sites/wordpress1fesnouf" ], "visualization": "map", "mapSettings": { "locInfo": "CountryRegion", "locInfoColumn": "country_name", "sizeSettings": "ipCount", "sizeAggregation": "Sum", "legendMetric": "ipCount", "legendAggregation": "Sum", "itemColorSettings": { "nodeColorField": "ipCount", "colorAggregation": "Sum", "type": "heatmap", "heatmapPalette": "greenRed" } } }, "customWidth": "50", "name": "query - 22 - Copy" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "let IP_Data = \r\n external_data(network:string,geoname_id:long,continent_code:string,continent_name:string ,country_iso_code:string, country_name:string,is_anonymous_proxy:bool,is_satellite_provider:bool)\r\n ['https://raw.githubusercontent.com/datasets/geoip2-ipv4/master/data/geoip2-ipv4.csv']\r\n with (ignoreFirstRecord=true, format=\"csv\");\r\nAppServiceHTTPLogs\r\n| extend IPAddress = tostring(CIp)\r\n| where isnotempty(IPAddress)\r\n| where CIp!=\"82.65.97.28\"\r\n| evaluate ipv4_lookup(IP_Data, IPAddress, network)\r\n| project TimeGenerated, country_iso_code, CsMethod, CsUriStem, CIp, UserAgent, CsHost, Referer\r\n//| where country_name != \"United States\"", "size": 0, "title": "Check Web App Connectivity, extract IP, locate country", "timeContext": { "durationMs": 604800000 }, "queryType": 0, "resourceType": "microsoft.web/sites", "crossComponentResources": [ "/subscriptions/770207bd-ebc7-4cda-ad79-03507721ea17/resourceGroups/camera/providers/Microsoft.Web/sites/wordpress1fesnouf" ] }, "name": "query - 22 - Copy" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "AppServiceHTTPLogs \r\n//| where CIp!=\"82.65.97.28\"\r\n|where CsUriStem contains \"wp-admin\"\r\n| project Category, TimeGenerated, CsUriStem, CIp\r\n// https://tools.keycdn.com/geo?host=66.249.65.109", "size": 0, "title": "App service Log Quer. Looking for Admin Page connection : ", "timeContext": { "durationMs": 2592000000 }, "queryType": 0, "resourceType": "microsoft.web/sites", "crossComponentResources": [ "/subscriptions/770207bd-ebc7-4cda-ad79-03507721ea17/resourceGroups/camera/providers/Microsoft.Web/sites/wordpress1fesnouf" ], "gridSettings": { "sortBy": [ { "itemKey": "CsUriStem", "sortOrder": 1 } ] }, "sortBy": [ { "itemKey": "CsUriStem", "sortOrder": 1 } ] }, "name": "query - 20 - Copy" } ] }, "name": "group - 28 - webapp" }, { "type": 1, "content": { "json": "## General conclusion\r\n---\r\n\r\nAdd your comments here", "style": "error" }, "name": "text - analyse - AD - Copy", "styleSettings": { "showBorder": true } }, { "type": 12, "content": { "version": "NotebookGroup/1.0", "groupType": "editable", "title": "KUBERNETES", "expandable": true, "items": [ { "type": 1, "content": { "json": "\r\n![MS Logo](https://arcimages.blob.core.windows.net/{CustomerNameURL}/arc-k8s.jpg \"Microsoft\")\r\n## Kubernetes\t\r\nMultiple axis" }, "name": "text - 17" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "ContainerInventory\r\n| project Computer, Name, Image, ImageTag, ContainerState, CreatedTime, StartedTime, FinishedTime\r\n| render table", "size": 0, "title": "Liste de toutes les informations sur le cycle de vie d’un conteneur", "timeContext": { "durationMs": 86400000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces" }, "name": "query - 0" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "KubeEvents\r\n| where not(isempty(Namespace))\r\n| sort by TimeGenerated desc\r\n| render table", "size": 0, "title": "Événements Kubernetes", "timeContext": { "durationMs": 86400000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces" }, "name": "query - 1" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "Perf\r\n| where ObjectName == \"K8SContainer\" and CounterName == \"cpuUsageNanoCores\" \r\n| summarize AvgCPUUsageNanoCores = avg(CounterValue) by bin(TimeGenerated, 30m), InstanceName", "size": 0, "title": "Processeur du conteneur", "timeContext": { "durationMs": 86400000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces" }, "name": "query - 2" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "Perf\r\n| where ObjectName == \"K8SContainer\" and CounterName == \"memoryRssBytes\"\r\n| summarize AvgUsedRssMemoryBytes = avg(CounterValue) by bin(TimeGenerated, 30m), InstanceName", "size": 0, "title": "Mémoire du conteneur", "timeContext": { "durationMs": 86400000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces" }, "name": "query - 3" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "InsightsMetrics\r\n| where Name == \"requests_count\"\r\n| summarize Val=any(Val) by TimeGenerated=bin(TimeGenerated, 1m)\r\n| sort by TimeGenerated asc\r\n| project RequestsPerMinute = Val - prev(Val), TimeGenerated\r\n| render barchart ", "size": 0, "title": "Demandes par minute avec des métriques personnalisées", "timeContext": { "durationMs": 86400000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces" }, "name": "query - 4" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "let startTimestamp = ago(1h);\r\nKubePodInventory\r\n| where TimeGenerated > startTimestamp\r\n| project ContainerID, PodName=Name, Namespace\r\n| where PodName contains \"name\" and Namespace startswith \"namespace\"\r\n| distinct ContainerID, PodName\r\n| join\r\n(\r\n ContainerLog\r\n | where TimeGenerated > startTimestamp\r\n)\r\non ContainerID\r\n// at this point before the next pipe, columns from both tables are available to be \"projected\". Due to both\r\n// tables having a \"Name\" column, we assign an alias as PodName to one column which we actually want\r\n| project TimeGenerated, PodName, LogEntry, LogEntrySource\r\n| summarize by TimeGenerated, LogEntry\r\n| order by TimeGenerated desc", "size": 0, "title": "Pods par nom et espace de noms", "timeContext": { "durationMs": 86400000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces" }, "name": "query - 5" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "let _minthreshold = 70; // minimum threshold goes here if you want to setup as an alert\r\nlet _maxthreshold = 90; // maximum threshold goes here if you want to setup as an alert\r\nlet startDateTime = ago(60m);\r\nKubePodInventory\r\n| where TimeGenerated >= startDateTime \r\n| where Namespace !in('default', 'kube-system') // List of non system namespace filter goes here.\r\n| extend labels = todynamic(PodLabel)\r\n| extend deployment_hpa = reverse(substring(reverse(ControllerName), indexof(reverse(ControllerName), \"-\") + 1))\r\n| distinct tostring(deployment_hpa)\r\n| join kind=inner (InsightsMetrics \r\n | where TimeGenerated > startDateTime \r\n | where Name == 'kube_hpa_status_current_replicas'\r\n | extend pTags = todynamic(Tags) //parse the tags for values\r\n | extend ns = todynamic(pTags.k8sNamespace) //parse namespace value from tags\r\n | extend deployment_hpa = todynamic(pTags.targetName) //parse HPA target name from tags\r\n | extend max_reps = todynamic(pTags.spec_max_replicas) // Parse maximum replica settings from HPA deployment\r\n | extend desired_reps = todynamic(pTags.status_desired_replicas) // Parse desired replica settings from HPA deployment\r\n | summarize arg_max(TimeGenerated, *) by tostring(ns), tostring(deployment_hpa), Cluster=toupper(tostring(split(_ResourceId, '/')[8])), toint(desired_reps), toint(max_reps), scale_out_percentage=(desired_reps * 100 / max_reps)\r\n //| where scale_out_percentage > _minthreshold and scale_out_percentage <= _maxthreshold\r\n )\r\n on deployment_hpa", "size": 0, "title": "Scale-out des pods (HPA)", "timeContext": { "durationMs": 86400000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces" }, "name": "query - 6" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "let nodepoolMaxnodeCount = 10; // the maximum number of nodes in your auto scale setting goes here.\r\nlet _minthreshold = 20;\r\nlet _maxthreshold = 90;\r\nlet startDateTime = 60m;\r\nKubeNodeInventory\r\n| where TimeGenerated >= ago(startDateTime)\r\n| extend nodepoolType = todynamic(Labels) //Parse the labels to get the list of node pool types\r\n| extend nodepoolName = todynamic(nodepoolType[0].agentpool) // parse the label to get the nodepool name or set the specific nodepool name (like nodepoolName = 'agentpool)'\r\n| summarize nodeCount = count(Computer) by ClusterName, tostring(nodepoolName), TimeGenerated\r\n//(Uncomment the below two lines to set this as an log search alert)\r\n//| extend scaledpercent = iff(((nodeCount * 100 / nodepoolMaxnodeCount) >= _minthreshold and (nodeCount * 100 / nodepoolMaxnodeCount) < _maxthreshold), \"warn\", \"normal\")\r\n//| where scaledpercent == 'warn'\r\n| summarize arg_max(TimeGenerated, *) by nodeCount, ClusterName, tostring(nodepoolName)\r\n| project ClusterName, \r\n TotalNodeCount= strcat(\"Total Node Count: \", nodeCount),\r\n ScaledOutPercentage = (nodeCount * 100 / nodepoolMaxnodeCount), \r\n TimeGenerated, \r\n nodepoolName", "size": 0, "title": "Scale-out de pool de nœud", "timeContext": { "durationMs": 86400000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces" }, "name": "query - 7" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "let startDateTime = 5m; // the minimum time interval goes here\r\nlet _minalertThreshold = 50; //Threshold for minimum and maximum unavailable or not running containers\r\nlet _maxalertThreshold = 70;\r\nKubePodInventory\r\n| where TimeGenerated >= ago(startDateTime)\r\n| distinct ClusterName, TimeGenerated\r\n| summarize Clustersnapshot = count() by ClusterName\r\n| join kind=inner (\r\n KubePodInventory\r\n | where TimeGenerated >= ago(startDateTime)\r\n | where Namespace in('default', 'kube-system') and ControllerKind == 'ReplicaSet' // the system namespace filter goes here\r\n | distinct ClusterName, Computer, PodUid, TimeGenerated, PodStatus, ServiceName, PodLabel, Namespace, ContainerStatus\r\n | summarize arg_max(TimeGenerated, *), TotalPODCount = count(), podCount = sumif(1, PodStatus == 'Running' or PodStatus != 'Running'), containerNotrunning = sumif(1, ContainerStatus != 'running')\r\n by ClusterName, TimeGenerated, ServiceName, PodLabel, Namespace\r\n )\r\n on ClusterName\r\n| project ClusterName, ServiceName, podCount, containerNotrunning, containerNotrunningPercent = (containerNotrunning * 100 / podCount), TimeGenerated, PodStatus, PodLabel, Namespace, Environment = tostring(split(ClusterName, '-')[3]), Location = tostring(split(ClusterName, '-')[4]), ContainerStatus\r\n//Uncomment the below line to set for automated alert\r\n//| where PodStatus == \"Running\" and containerNotrunningPercent > _minalertThreshold and containerNotrunningPercent < _maxalertThreshold\r\n| summarize arg_max(TimeGenerated, *), c_entry=count() by PodLabel, ServiceName, ClusterName\r\n//Below lines are to parse the labels to identify the impacted service/component name\r\n| extend parseLabel = replace(@'k8s-app', @'k8sapp', PodLabel)\r\n| extend parseLabel = replace(@'app.kubernetes.io/component', @'appkubernetesiocomponent', parseLabel)\r\n| extend parseLabel = replace(@'app.kubernetes.io/instance', @'appkubernetesioinstance', parseLabel)\r\n| extend tags = todynamic(parseLabel)\r\n| extend tag01 = todynamic(tags[0].app)\r\n| extend tag02 = todynamic(tags[0].k8sapp)\r\n| extend tag03 = todynamic(tags[0].appkubernetesiocomponent)\r\n| extend tag04 = todynamic(tags[0].aadpodidbinding)\r\n| extend tag05 = todynamic(tags[0].appkubernetesioinstance)\r\n| extend tag06 = todynamic(tags[0].component)\r\n| project ClusterName, TimeGenerated,\r\n ServiceName = strcat( ServiceName, tag01, tag02, tag03, tag04, tag05, tag06),\r\n ContainerUnavailable = strcat(\"Unavailable Percentage: \", containerNotrunningPercent),\r\n PodStatus = strcat(\"PodStatus: \", PodStatus), \r\n ContainerStatus = strcat(\"Container Status: \", ContainerStatus)", "size": 0, "title": "Disponibilité des conteneurs système (replicatset)", "timeContext": { "durationMs": 86400000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces" }, "name": "query - 8" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "let startDateTime = 5m; // the minimum time interval goes here\r\nlet _minalertThreshold = 50; //Threshold for minimum and maximum unavailable or not running containers\r\nlet _maxalertThreshold = 70;\r\nKubePodInventory\r\n| where TimeGenerated >= ago(startDateTime)\r\n| distinct ClusterName, TimeGenerated\r\n| summarize Clustersnapshot = count() by ClusterName\r\n| join kind=inner (\r\n KubePodInventory\r\n | where TimeGenerated >= ago(startDateTime)\r\n | where Namespace in('default', 'kube-system') and ControllerKind == 'DaemonSet' // the system namespace filter goes here\r\n | distinct ClusterName, Computer, PodUid, TimeGenerated, PodStatus, ServiceName, PodLabel, Namespace, ContainerStatus\r\n | summarize arg_max(TimeGenerated, *), TotalPODCount = count(), podCount = sumif(1, PodStatus == 'Running' or PodStatus != 'Running'), containerNotrunning = sumif(1, ContainerStatus != 'running')\r\n by ClusterName, TimeGenerated, ServiceName, PodLabel, Namespace\r\n )\r\n on ClusterName\r\n| project ClusterName, ServiceName, podCount, containerNotrunning, containerNotrunningPercent = (containerNotrunning * 100 / podCount), TimeGenerated, PodStatus, PodLabel, Namespace, Environment = tostring(split(ClusterName, '-')[3]), Location = tostring(split(ClusterName, '-')[4]), ContainerStatus\r\n//Uncomment the below line to set for automated alert\r\n//| where PodStatus == \"Running\" and containerNotrunningPercent > _minalertThreshold and containerNotrunningPercent < _maxalertThreshold\r\n| summarize arg_max(TimeGenerated, *), c_entry=count() by PodLabel, ServiceName, ClusterName\r\n//Below lines are to parse the labels to identify the impacted service/component name\r\n| extend parseLabel = replace(@'k8s-app', @'k8sapp', PodLabel)\r\n| extend parseLabel = replace(@'app.kubernetes.io/component', @'appkubernetesiocomponent', parseLabel)\r\n| extend parseLabel = replace(@'app.kubernetes.io/instance', @'appkubernetesioinstance', parseLabel)\r\n| extend tags = todynamic(parseLabel)\r\n| extend tag01 = todynamic(tags[0].app)\r\n| extend tag02 = todynamic(tags[0].k8sapp)\r\n| extend tag03 = todynamic(tags[0].appkubernetesiocomponent)\r\n| extend tag04 = todynamic(tags[0].aadpodidbinding)\r\n| extend tag05 = todynamic(tags[0].appkubernetesioinstance)\r\n| extend tag06 = todynamic(tags[0].component)\r\n| project ClusterName, TimeGenerated,\r\n ServiceName = strcat( ServiceName, tag01, tag02, tag03, tag04, tag05, tag06),\r\n ContainerUnavailable = strcat(\"Unavailable Percentage: \", containerNotrunningPercent),\r\n PodStatus = strcat(\"PodStatus: \", PodStatus), \r\n ContainerStatus = strcat(\"Container Status: \", ContainerStatus)", "size": 0, "title": "Disponibilité des conteneurs système (daemonsets)", "timeContext": { "durationMs": 86400000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces" }, "name": "query - 9" } ] }, "name": "group - kubernetes" }, { "type": 12, "content": { "version": "NotebookGroup/1.0", "groupType": "editable", "title": "Misc", "expandable": true, "items": [ { "type": 12, "content": { "version": "NotebookGroup/1.0", "groupType": "editable", "title": "Evenements de la plateforme", "items": [ { "type": 3, "content": { "version": "KqlItem/1.0", "query": "Operation \r\n| project SourceSystem, OperationStatus, Computer, Detail, OperationCategory, Solution, OperationKey", "size": 0, "timeContext": { "durationMs": 86400000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces" }, "name": "query - 0" } ] }, "name": "group - 12" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "SecurityEvent\r\n| where TimeGenerated >= ago(1d)\r\n| project Account\r\n , Computer\r\n , Activity\r\n , LogonTypeName\r\n| evaluate basket(0.03)\r\n\r\n//https://www.ciraltos.com/azure-machine-learning-in-log-analytics/", "size": 0, "title": "ML sur le sevent securité", "timeContext": { "durationMs": 86400000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces" }, "name": "query - 13" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "Update\r\n| where TimeGenerated >= ago(1d)\r\n| project Computer\r\n , UpdateState\r\n , Product\r\n , OSType\r\n| evaluate autocluster(0.01)", "size": 0, "timeContext": { "durationMs": 86400000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces" }, "name": "query - 14" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "Event\r\n| where TimeGenerated >= ago(7d)\r\nand EventLevelName == \"Warning\" or EventLevelName == \"Error\"\r\n| project EventLevelName\r\n , Computer\r\n| evaluate diffpatterns(EventLevelName, \"Warning\", \"Error\")", "size": 0, "timeContext": { "durationMs": 86400000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces" }, "name": "query - 15" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "SecurityEvent\r\n| where TimeGenerated >= ago(1d)\r\nand EventID == 4624 or EventID == 4625\r\n| project Account\r\n , Computer\r\n , EventID\r\n , Activity\r\n| evaluate diffpatterns(EventID, \"4624\", \"4625\", \"~\", 0.02)", "size": 0, "timeContext": { "durationMs": 259200000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces" }, "name": "query - 16" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "ComputerGroup \r\n| distinct Group, GroupSource, GroupFullName", "size": 0, "title": "Example to list computer groups", "timeContext": { "durationMs": 86400000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces" }, "name": "query - 5" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "UpdateSummary | where Computer in (\"All Computers\")", "size": 0, "timeContext": { "durationMs": 86400000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces" }, "name": "query - 6" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "AdvisorResources\r\n| where type == 'microsoft.advisor/recommendations'\r\n| extend\r\n resources = tostring(properties.resourceMetadata.resourceId),\r\n savings = todouble(properties.extendedProperties.savingsAmount),\r\n probleme = tostring(properties.shortDescription.problem),\r\n solution = tostring(properties.shortDescription.solution),\r\n currency = tostring(properties.extendedProperties.savingsCurrency)\r\n| summarize\r\n dcount(resources),\r\n bin(sum(savings), 0.01)\r\n by solution, currency, probleme\r\n| project probleme, solution, dcount_resources, sum_savings, currency\r\n| order by sum_savings desc", "size": 0, "title": "Azure Advisor", "queryType": 1, "resourceType": "microsoft.resourcegraph/resources" }, "name": "query - 7" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "Resources\r\n| where type == 'microsoft.hybridcompute/machines'\r\n| project\r\n id,\r\n JoinID = toupper(id),\r\n ComputerName = tostring(properties.osProfile.computerName),\r\n OSName = tostring(properties.osName)\r\n| join kind=leftouter(\r\n Resources\r\n | where type == 'microsoft.hybridcompute/machines/extensions'\r\n | project\r\n MachineId = toupper(substring(id, 0, indexof(id, '/extensions'))),\r\n ExtensionName = name\r\n) on $left.JoinID == $right.MachineId\r\n| summarize Extensions = make_list(ExtensionName) by id, ComputerName, OSName\r\n| order by tolower(OSName) asc", "size": 0, "title": "Extentions installed on Arc", "queryType": 1, "resourceType": "microsoft.resourcegraph/resources" }, "name": "query - 8" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "PolicyResources\r\n| where type =~ 'Microsoft.PolicyInsights/PolicyStates'\r\n| extend complianceState = tostring(properties.complianceState)\r\n| extend\r\n resourceId = tostring(properties.resourceId),\r\n policyAssignmentId = tostring(properties.policyAssignmentId),\r\n policyAssignmentScope = tostring(properties.policyAssignmentScope),\r\n policyAssignmentName = tostring(properties.policyAssignmentName),\r\n policyDefinitionId = tostring(properties.policyDefinitionId),\r\n policyDefinitionReferenceId = tostring(properties.policyDefinitionReferenceId),\r\n stateWeight = iff(complianceState == 'NonCompliant', int(300), iff(complianceState == 'Compliant', int(200), iff(complianceState == 'Conflict', int(100), iff(complianceState == 'Exempt', int(50), int(0)))))\r\n| summarize max(stateWeight) by resourceId, policyAssignmentId, policyAssignmentScope, policyAssignmentName\r\n| summarize counts = count() by policyAssignmentId, policyAssignmentScope, max_stateWeight, policyAssignmentName\r\n| summarize overallStateWeight = max(max_stateWeight),\r\nnonCompliantCount = sumif(counts, max_stateWeight == 300),\r\ncompliantCount = sumif(counts, max_stateWeight == 200),\r\nconflictCount = sumif(counts, max_stateWeight == 100),\r\nexemptCount = sumif(counts, max_stateWeight == 50) by policyAssignmentId, policyAssignmentScope, policyAssignmentName\r\n| extend totalResources = todouble(nonCompliantCount + compliantCount + conflictCount + exemptCount)\r\n| extend compliancePercentage = iff(totalResources == 0, todouble(100), 100 * todouble(compliantCount + exemptCount) / totalResources)\r\n| project policyAssignmentName, scope = policyAssignmentScope,\r\ncomplianceState = iff(overallStateWeight == 300, 'noncompliant', iff(overallStateWeight == 200, 'compliant', iff(overallStateWeight == 100, 'conflict', iff(overallStateWeight == 50, 'exempt', 'notstarted')))),\r\ncompliancePercentage,\r\ncompliantCount,\r\nnonCompliantCount,\r\nconflictCount,\r\nexemptCount", "size": 0, "title": "policies compliance", "queryType": 1, "resourceType": "microsoft.resourcegraph/resources" }, "name": "query - 9" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "ServiceHealthResources\r\n| where type =~ 'Microsoft.ResourceHealth/events'\r\n| extend eventType = properties.EventType, status = properties.Status, description = properties.Title, trackingId = properties.TrackingId, summary = properties.Summary, priority = properties.Priority, impactStartTime = properties.ImpactStartTime, impactMitigationTime = properties.ImpactMitigationTime\r\n| where properties.Status == 'Active' and tolong(impactStartTime) > 1", "size": 0, "title": "Azure Health", "queryType": 1, "resourceType": "microsoft.resourcegraph/resources" }, "name": "query - 10" } ] }, "name": "group - 13 - misc", "styleSettings": { "showBorder": true } }, { "type": 12, "content": { "version": "NotebookGroup/1.0", "groupType": "editable", "title": "Network (TBD)", "expandable": true, "items": [ { "type": 1, "content": { "json": "![MS Logo](https://arcimages.blob.core.windows.net/{CustomerNameURL}/arc-network-traffic.jpg \"Microsoft\")\r\n###Network-related data\r\n\r\nAll the details related to network.", "style": "info" }, "name": "texte - 2 - Baniere network" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "NetworkMonitoring | where SubType == \"NetworkNodeLink\"\r\n| project SubType, SourceNetwork, DestinationNetwork, Loss, HighLatency, MedianLatency, LowLatency, LossHealthState, LatencyHealthState", "size": 0, "title": "NetworkNodeLink", "timeContext": { "durationMs": 86400000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces" }, "name": "query - 15" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "\r\nNetworkMonitoring | where SubType == \"SubNetwork\"\r\n| project SubType, SourceNetwork, DestinationNetwork, Loss, HighLatency, MedianLatency, LowLatency, LossHealthState, LatencyHealthState\r\n", "size": 0, "title": "Sub Network", "timeContext": { "durationMs": 86400000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces" }, "name": "query - 13 - Copy" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "NetworkMonitoring \r\n| where SubType == \"Network\"\r\n| project SubType, SourceNetwork, DestinationNetwork, Loss, HighLatency, MedianLatency, LowLatency, LossHealthState, LatencyHealthState", "size": 0, "title": "Network", "timeContext": { "durationMs": 86400000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces" }, "name": "query - 13" }, { "type": 3, "content": { "version": "KqlItem/1.0", "query": "NetworkMonitoring \r\n| where SubType ==\"NetworkAgentDiagnostics\"\r\n| project Computer, NotificationType", "size": 0, "title": "Health of machines", "timeContext": { "durationMs": 172800000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces" }, "name": "query - 12" }, { "type": 1, "content": { "json": "## Network Section\r\n---\r\n\r\n\r\nUseful links for this chapter :\r\n\r\n* [link1](https://ms.portal.azure.com/)\r\n\r\n* [Link2](https://ms.portal.azure.com/#s)\r\n\r\n\r\n\r\n\r\n", "style": "error" }, "name": "text - analyse - Network", "styleSettings": { "showBorder": true } } ] }, "name": "group - Network Monitoring - TBD", "styleSettings": { "showBorder": true } }, { "type": 12, "content": { "version": "NotebookGroup/1.0", "groupType": "editable", "title": "CyberWar", "expandable": true, "items": [ { "type": 12, "content": { "version": "NotebookGroup/1.0", "groupType": "editable", "title": "Cybersec WAR", "items": [ { "type": 3, "content": { "version": "KqlItem/1.0", "query": "let IPList = dynamic([\"154.223.45.38\",\"185.141.207.140\",\"185.234.73.19\",\"216.245.210.106\",\"51.91.48.210\",\"46.255.230.229\"]);\r\nVMConnection \r\n|where SourceIp in (IPList) or DestinationIp in (IPList)\r\n|project Computer, Direction, ProcessName, SourceIp, DestinationIp, ipv4_is_private(DestinationIp), DestinationPort, RemoteDnsQuestions, RemoteDnsCanonicalNames", "size": 0, "timeContext": { "durationMs": 86400000 }, "queryType": 0, "resourceType": "microsoft.operationalinsights/workspaces" }, "name": "query - 0" } ] }, "name": "group - 18" } ] }, "name": "group - 21 - cyberwar" } ], "fallbackResourceIds": [ "/subscriptions/770207bd-ebc7-4cda-ad79-03507721ea17/resourceGroups/myworkstations/providers/Microsoft.OperationalInsights/workspaces/MyWorkstations" ], "$schema": "https://github.com/Microsoft/Application-Insights-Workbooks/blob/master/schema/workbook.json" }