Tuesday, October 13, 2020

Azure Data Explorer - Kusto Query - Get Min/Max Within Each Category Filter

In continuation to my previous post on Get Categorial Count, this time let’s get our hands dirty with one more query related to filter criteria for date time field.

Below is the sample data on which we are going to query:

GenerationDate

IngestionTime

DescriptionTitle

DescriptionDetail

FeedKey

2020-05-21 00:00:00:0000000

2020-05-25 02:00:00:0000000

Schedule Task

Read feed from server 1

acbf-uhef-4t5i-dfff

2020-05-21 00:00:00:0000000

2020-05-25 03:00:00:3000000

Schedule Task

Read feed from server 1

acbf-uhef-4t5i-dfff

2020-05-21 00:00:00:0000000

2020-05-25 03:00:00:3500000

Schedule Task

Read feed from server 1

acbf-uhef-4t5i-dfff

2020-05-21 00:00:00:0000000

2020-05-25 03:00:00:3000000

Monitoring Task

Monitoring failed for LOC

lcbf-u78f-4p5i-dfff

2020-05-21 00:00:00:0000000

2020-05-26 02:00:00:0000000

Schedule Task

Data missing for palto

acbf-uhef-4t5i-dfff

2020-05-22 00:00:00:0000000

2020-05-26 00:09:00:0000000

Schedule Task

Read feed from server 1

acbf-uhef-4t5i-dfff

2020-05-22 00:00:00:0000000

2020-05-27 00:04:00:0000000

Failover Handling

Disk fault occurred in region R

acbf-uhef-4t5i-dfff

Query description:

For each unique combination of FeedKey and Description, find the maximum and minimum Ingestion time

Kusto query:

  1. let fact = DemoData  
  2. where GenerationDate == datetime(2020-05-21)  
  3. | summarize dcount(FeedKey) by DescriptionTitle, DescriptionDetail, FeedKey, GenerationDate; 
  4.  
  5. let minIngestionTimes = fact | join kind=leftouter DemoData on FeedKey, DescriptionTitle, DescriptionDetail, GenerationDate  
  6. | project FeedKey, DescriptionTitle, DescriptionDetail, GenerationDate, IngestionTime 
  7. | summarize MinIngestTime = arg_min(IngestionTime,*) by FeedKey, DescriptionTitle, DescriptionDetail;  

  8. let maxIngestionTimes = fact | join kind=leftouter DemoData on FeedKey, DescriptionTitle, DescriptionDetail, GenerationDate  
  9. | project FeedKey, DescriptionTitle, DescriptionDetail, GenerationDate, IngestionTime
  10. | summarize MaxIngestTime = arg_max(IngestionTime,*) by FeedKey, DescriptionTitle, DescriptionDetail;  

  11. minIngestionTimes | join kind=innerunique maxIngestionTimes on FeedKey, DescriptionTitle, DescriptionDetail  
  12. | extend Description = strcat(DescriptionTitle," : ", DescriptionDetail)  
  13. | project FeedKey, Description, MinIngestTime, MaxIngestTime, GenerationDate,  
  14. | sort by FeedKey  

Expected output

FeedKey

Description

MinIngestTime

MaxIngestTime

GenerationDate

acbf-uhef-4t5i-dfff

Schedule Task : Read feed from server 1

2020-05-25 02:00:00:0000000

2020-05-25 03:00:00:3500000

2020-05-21 00:00:00:0000000

lcbf-u78f-4p5i-dfff

Monitoring Task : Monitoring failed for LOC

2020-05-25 03:00:00:3000000

2020-05-25 03:00:00:3000000

2020-05-21 00:00:00:0000000

acbf-uhef-4t5i-dfff

Schedule Task : Data missing for palto

2020-05-26 02:00:00:0000000

2020-05-26 02:00:00:0000000

2020-05-21 00:00:00:0000000

 Happy kustoing!

Friday, October 9, 2020

Azure Data Explorer - Kusto Query - Get Categorial Count

It’s been a while since I started working on data analysis part. When it comes to data analysis, it’s all about how efficiently one can filter and fetch the small set of useful data from humongous collection.

I used Kusto Query Language (KQL) for writing advanced queries for Azure Log Analytics. At first, when you will start writing queries, it would be very daunting and keeping that in mind, I thought, I should share a few of those queries which could save huge on the beginner’s time.

Hence, my next few posts would be mostly based on how to achieve expected output using KQL. So, let’s get started with a simple scenario first.

Below is the sample data on which we are going to query:

GenerationDate

IngestionTime

DescriptionTitle

DescriptionDetail

FeedKey

2020-05-21 00:00:00:0000000

2020-05-25 02:00:00:0000000

Schedule Task

Read feed from server 1

acbf-uhef-4t5i-dfff

2020-05-21 00:00:00:0000000

2020-05-25 03:00:00:3000000

Schedule Task

Read feed from server 1

acbf-uhef-4t5i-dfff

2020-05-21 00:00:00:0000000

2020-05-25 03:00:00:3000000

Monitoring Task

Monitoring failed for LOC

lcbf-u78f-4p5i-dfff

2020-05-22 00:00:00:0000000

2020-05-26 02:00:00:0000000

Schedule Task

Data missing for palto

acbf-uhef-4t5i-dfff

2020-05-22 00:00:00:0000000

2020-05-26 00:09:00:0000000

Schedule Task

Read feed from server 1

acbf-uhef-4t5i-dfff

2020-05-22 00:00:00:0000000

2020-05-27 00:04:00:0000000

Failover Handling

Disk fault occurred in region R

acbf-uhef-4t5i-dfff

Query description:

How to get the varied description count for each FeedKey.

Kusto: 


  1. DemoData   
  2. where GenerationDate >= datetime(2020-05-20) and GenerationDate <= datetime(2020-05-23)  
  3. | extend Descriptions = strcat(DescriptionTitle," : ",DescriptionDetail)  
  4. | summarize dcount(FeedKey) by Descriptions, FeedKey   
  5. | summarize DescriptionCount = count() by FeedKey | sort by DescriptionCount desc;

Expected output:


FeedKey

DescriptionCount

acbf-uhef-4t5i-dfff

3

lcbf-u78f-4p5i-dfff

1

 Happy kustoing!

Thursday, July 23, 2020

Excluding Teams from Office Deployment

Recently I received a request from one of my readers on how to tailor Office deployments and to be specific, how to exclude Teams from deployment configuration file.

Let’s navigate to  https://config.office.com/ from the browser. This is the place from where administrators can manage and deploy office products and subscriptions.

You can see that there are two options on the page: 
  • Create a new configuration 
  • Import your configuration

Here I am going to create a new configuration, but if you have existing configuration, you can import that too and update it based on your business needs.

Once you have entered the Deployment Settings page, there are many options which need to be configured.

Architecture Selection

Select the architecture for which we are creating a deployment:







Office Suite Selection

Next is to select the Office Suite:









Version Selection

Next is to select the version which we want to deploy:






App Exclusion

Now comes the most important part, wherein we are going the exclude the apps which we do not want to export as part of our deployment script:










Language Selection

Next mandatory parameter is the to select the primary language:




















File Format Selection

We are almost done. Final step is to export this newly created configuration, and that can be done by clicking on the Export button on top of the page.

As soon as Export button is clicked, another dialog will pop up asking for the file format:
















Accept Licensing Terms

Next is to accept the license agreement and provide a name for the configuration file:

















Export Configuration

Click on Export and deployment file will get downloaded to your machine. Lets open the file and have a look at the configuration settings:













In above image, you can see that Teams is excluded and will no more be part of our Office deployment. 

Happy Deployment.

Monday, July 20, 2020

Acessing Azure DevOps By Using PAT


You may have come across a requirement, wherein you needed to update Azure DevOps objects programmatically and it is obvious that there must be some authentication mechanism which has to be in place.

Now, there are many methods one can use to authenticate, but for this post, I’ve specifically chosen personal access token. PAT, which is short for Personal Access Token is a way to provide an alternate password to authenticate to Azure DevOps.

To understand more about how to generate this token and how to utilize this, let’s follow certain steps and make a successful REST API call. More...


Wednesday, July 15, 2020

Made debut as an International Speaker

Today, I've something good to share. Recently I was given an opportunity to talk in one of the technical conference named 'Lightup Virtual Conference', which was a fund raising event to support UNICEF in order to take a stand against COVID19 and it was organized by C# Corner and The Tech Platform. 

I spoke on a topic 'Azure Bot Services Utilizing LUIS Capabilities' which was a very wonderful experience. Hope recording of the same will be available soon.