Showing posts with label Azure Log Analytics. Show all posts
Showing posts with label Azure Log Analytics. Show all posts

Tuesday, October 20, 2020

Azure Data Explorer - Kusto Query - Get data for last 20 days

In continuation to my previous post on Get Min/Max from each category, this time let’s do one of the most demanded queries with filter criteria on date time field.

For the purpose of simplicity and keep this article more focused, I have removed data from all the additional columns as shown below:

GenerationDate

DescriptionTitle

DescriptionDetail

FeedKey

2020-10-02 00:00:00:0000000

2020-10-21 00:00:00:0000000

2020-10-21 00:00:00:0000000

2020-10-21 00:00:00:0000000

2020-10-21 00:00:00:0000000

2020-10-22 00:00:00:0000000

2020-10-22 00:00:00:0000000

Query description

The idea is to fetch all the records, which occurred in past 20 days of GenerationDate.

Approaches

Now to achieve our expected result, there could be more than a way.  One way could be:

Approach 1

Find out the date which fall exactly twenty days back using ago(…) and then use conditional operator (<= and >=) to achieve this result. 

Above approach would work perfectly but the problem with this approach is lot many lines of code and calculation.

Approach 2

The other approach having very less lines of code could be using the between(…) as shown below:

  • DemoData   
  • where (todatetime(GenerationDate) – now()) between(0d…20d);     
  • NOTE: Make sure to do the proper datetime cast else you may end up getting an error ‘Arithmetic expression cannot be carried-out between StringBuffer and DateTime’.

    Happy kustoing!

    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!