Article • 13 minutes to read • contributors
Applies to: ✅ADAssessmentRecommendation ✅ADSecurityAssessmentRecommendation ✅SQLAssessmentRecommendation ✅SCCMAssessmentRecommendation ✅SCOMAssessmentRecommendation ✅WindowsServerAssessmentRecommendation ✅WindowsClientAssessmentRecommendation
Subject to validation: 🔸AzureAssessmentRecommendation 🔸ExchangeAssessmentRecommendation 🔸SfBAssessmentRecommendation 🔸SfBOnlineAssessmentRecommendation 🔸SharePointOnlineAssessmentRecommendation 🔸SPAssessmentRecommendation
In this tutorial, you will export and transform Focus Area as well as findings and recommendations from Log Analytics workspace for On-Demand Assessment and import them as Epics and Product backlog items in Azure DevOps.
As an Administrator, I want to have the findings and recommendations of an On-Demand Assessment in Azure DevOps Work Items so that I can work with On-Demand Assessment remediation as with any other Work Item in my organization.
The result of this tutorial allows for easier tracking of remediation progress of On-Demand Assessment conform the Agile methodology in your organization. Tracking progress on what a team is working on then also conforms to your Agile working method.
The tutorial does not go further into concepts of Scrum and Agile methodologies nor how to apply the Scrum Framework with Boards in Azure DevOps. The focus is on doing more with less, on gaining automation savings and keeping developers and administrators focused on high-impact and not administrative work. To empower every developer and every organization to achieve more with their data.
You will learn how to:
✅Export Focus Area and findings and recommendations from Log Analytics workspace for On-Demand Assessment.
✅Use relevant queries in Kusto Query Language to perform export and transformation.
✅Bulk import Epics and Product backlog items in Azure DevOps.
Prerequisites to complete this tutorial.
- Browser like Microsoft Edge with Internet Access.
- Azure DevOps Organization. If there is no organization available to you now you can start with a free organization for Azure DevOps Services. Going through the steps in Sign up for Azure DevOps there is a Basic license where the first five users are free.
- Microsoft Office 2016 or higher (optional)
- Discuss with your Azure DevOps project owner so they are aware of this import. Work carefully because your Azure DevOps project may have content already.
- Log Analytics Reader role on Log Analytics workspace to export data.
- Azure DevOps permissions for importing Work Items.
ToDo --> Validate permissions with principle of least privilege.
If you do not have an existing Azure DevOps project in your organization, you can create one. Create a project in Azure DevOps and Choose a process for your Azure DevOps project that suits your needs.
Depending on which process was chosen on creation of your Azure DevOps project Work Item Type for backlog levels is different.
- Agile : User Story, Feature, and Epic
- Basic : Issue and Epic
- Scrum : Product Backlog Item, Feature, and Epic
- CMMI : Requirement, Feature, and Epic
Note
Examples throughout this document are from an Azure DevOps Scrum project (and an On-Demand Assessment for SQL). Go to your Azure DevOps project and confirm the Process in Project details. If the process is other than Scrum, you need to change the query parameter for Work Item Type later in the KQL query (Kusto Query Language).
Note
This tutorial is using Azure DevOps. However, there may be possibilities for similar bulk imports, for example into Jira. Adjust the export from Log Analytics workspace to match the import expected by Jira or other tools supporting Agile methodologies.
How to bulk import Components into JIRA Issues | Jira | Atlassian Documentation
We start by exporting On-Demand Assessment findings and recommendations from the Log Analytics workspace.
-
In the Azure portal select the specific Log Analytics workspace from which you want to export.
-
Select Logs in the workspace menu.
-
Close the Queries window.
On-Demand Assessment names differ per product. To export from the assessment you are interested in, you need the name.
-
Copy the KQL query (Kusto Query Language) to Log Analytics workspace > Logs.
//Assessment search * | where $table contains "Assessment" | summarize by $table
-
Select Run to get the assessment name. If there’s no immediate result, change the Time range; the default schedule to run On-Demand Assessments is weekly, but the default time range for KQL is the last 24 hours.
This example results to SQLAssessmentRecommendation.
Start by exporting the Focus Areas.
-
Copy below KQL query to Log Analytics workspace > Logs.
-
Select Ctrl+H to search for
<MyAssessmentRecommendation>and replace this with the result from the Assessment Name query. -
Adjust query parameter qpAssignTo and replace
<MyFullName>by your full name as shown in Azure DevOps.
-
Adjust the Time range if you are on the default schedule to run On-Demand Assessments weekly.
//Focus Area declare query_parameters ( qpAssignTo: string = "<MyFullName>" //your full name as shown in Azure DevOps ); // <MyAssessmentRecommendation> //replace with result from query Assessment Name | where (RecommendationResult == "Failed") | summarize by AssessmentName, FocusArea | project ID = ' ' , ['Work Item Type'] = 'Epic' , ['Assigned To'] = qpAssignTo , ['Title 1'] = strcat('On-Demand Assessment for ', AssessmentName, ' | ', FocusArea) , ['Title 2'] = '' , Description = strcat('Epic imported from "On-Demand Assessment for ', AssessmentName, '" and Focus Area "', FocusArea , '" in Log Analytics workspace.') , Priority = 2; //Export | Export to CSV - displayed columns. //Rename query_data.csv to 1_Epic.csv
-
Select Run the query to get the Focus Area that will transform to Epics.
-
Export the results to csv through Export | Export to CSV - displayed columns.
-
Rename query_data.csv to 1_Epic.csv.
-
If you reorder columns or change the sort order in the csv files, the later import in Azure DevOps may break or have unexpected results.
This example results from items found in the On-Demand Assessment for SQL.
Like the export of Focus Area, we export findings and recommendations.
-
Copy below KQL query to Log Analytics workspace > Logs.
-
Use Ctrl+H to search for
<MyAssessmentRecommendation>and replace this with the result from the Assessment Name query. -
Adjust query parameter qpAssignTo and replace
<MyFullName>by your full name as shown in Azure DevOps. -
If needed adjust query parameter qpWorkItemType and replace Product Backlog Item by your Work Item Type as confirmed in Azure DevOps project process.
-
Adjust the Time range if you are on the default schedule to run On-Demand Assessments weekly.
//Findings and recommendations declare query_parameters ( qpAssignTo: string = "<MyFullName>" //your full name as shown in Azure DevOps. , qpWorkItemType: string = "Product Backlog Item" //Work Item Type as in your Azure DevOps project process. ); // <MyAssessmentRecommendation> //replace with result from query Assessment Name | where (RecommendationResult == "Failed") | summarize by AssessmentName, FocusArea | project ID = '<EpicID>' , ['Work Item Type'] = 'Epic' , ['Assigned To'] = qpAssignTo , ['Title 1'] = strcat('On-Demand Assessment for ', AssessmentName, ' | ', FocusArea) , ['Title 2'] = '' , Description = strcat('Epic imported from "On-Demand Assessment for ', AssessmentName, '" and Focus Area "', FocusArea, '" in Log Analytics workspace.') , Priority = 2 , tmpFocusArea = FocusArea | union ( <MyAssessmentRecommendation> //replace with result from query Assessment Name | where (RecommendationResult == "Failed") | summarize by AssessmentName, FocusArea, Recommendation, Description, RecommendationScore | sort by FocusArea, Recommendation | project ID = ' ' , ['Work Item Type'] = qpWorkItemType , ['Assigned To'] = qpAssignTo , ['Title 1'] = '' , ['Title 2'] = Recommendation , Description , RecommendationScore , tmpFocusArea = FocusArea | extend Priority = //convert RecommendationScore in On-Demand Assessment to Priority in Azure DevOps iif(RecommendationScore >= 30, 1, iif(RecommendationScore >= 20, 2, iif(RecommendationScore >= 10, 3, iif(RecommendationScore >= 0, 4, 4)))) | project-away RecommendationScore ) | sort by tmpFocusArea asc, ['Work Item Type'] asc | project-away tmpFocusArea; //Export | Export to CSV - displayed columns. //Rename query_data.csv to 2_ProductBacklogItem.csv
-
Select Run the query to get the findings and recommendations that will transform to product backlog items.
-
Export the results to csv through Export | Export to CSV - displayed columns.
-
Rename query_data.csv to 2_ProductBacklogItems.csv.
-
If you reorder columns or change the sort order in the csv files, the later import in Azure DevOps may break or have unexpected results.
This example results from items found in the On-Demand Assessment for SQL.
Now the export is complete, we can import Azure DevOps Epics and Product Backlog Items.
-
Go to your Azure DevOps project and select the Queries menu item on the left.
-
In the top menu select Import Work Items.
-
On the right select Choose File. Navigate to 1_Epic.csv and select Import.
-
Review the imported items to see if there are no data issues highlighted that you need to resolve before you can select Save items.
-
Keep the screen open because you need the Epic IDs in a minute.
-
If you don’t have the screen open with the Epics you just created, go to your Azure DevOps project and Recently created in Work items.
-
First, you need to prepare file 2_ProductBacklogItems.csv for import in Azure DevOps.
-
Open file 2_ProductBacklogItems.csv.
-
For each row in file 2_ProductBacklogItems.csv where Work Item Type equals Epic, replace the placeholder text
<EpicID>by the ID from the Azure DevOps Epics we just imported and paste it in the row with the same Epic title in 2_ProductBacklogItems.csv.
-
Save and close file 2_ProductBacklogItems.csv.
-
Return to Import Work Items in the Queries menu item in the left in Azure DevOps.
-
Select Import Work Items.
-
On the right Choose File. Navigate to file 2_ProductBacklogItems.csv and select Import.
-
Review the imported items to see if there are no data issues highlighted that you need to resolve before you can continue. Depending on the number of error messages either remediate manually in Azure DevOps or change in file 2_ProductBacklogItems.csv and restart the import.
Warning
Example with import errors where the name in the csv file does not match an identity in Azure DevOps to assign Work Items to. For a successful import, you need to resolve errors and warnings.
-
Select Save Items when all is ready for import.
-
The import process has populated the backlog with all the findings and recommendations from your assessment.
When no longer needed, delete the csv files generated earlier.
Next you can work on On-Demand Assessment remediation in Azure DevOps as you would with any other Work Item in your Agile working method.