Forum Discussion

GouravIN's avatar
GouravIN
Brass Contributor
Jul 19, 2019
Solved

Render Piechart

Hi All,

 

I want to create a pie chart that can populate the count of two types VMs that has either security or critical patch missing.

Like : - I have 20 VM and 15 have Critical patch missing whereas 5 have security patch matching. So it will generate a pie chart with count of VM.

 

I have scratched my head and tried to write Kusto for the same. But not sure how to count "true" in extended column. Or how to summarize it.

 

Update
| where TimeGenerated >= ago(1d)
| where (Classification == "Security Updates" or Classification == "Critical Updates")
| extend sec_server = (Classification == "Security Updates")
| extend cri_server = (Classification == "Critical Updates")
 
//| summarize count(Title) by Computer
//| project TimeGenerated, Title, Classification, Computer, Resource, UpdateState, Product, KBID, RebootBehavior, ResourceGroup
//| sort by count_Title desc
//| take 10
//| render piechart
 
Clarifying it more: -
 
  1. The number of servers that have one or more critical patches pending.  So if there are 100 servers that each have at least one critical patch pending, I would like to see the number 100 represented in a pie chart or bar graph format.  The actual number of critical patches pending is not the important number, the number of servers that are pending critical patches is the important number.

 

    2. The same representation as above for pending security patches 
 
Thanks in advance for the help 🙂
Azure Log Analytics
azure monitor
  • CliveWatson's avatar
    CliveWatson
    Jul 22, 2019

    GouravIN 

     

    Update 
| where TimeGenerated >= ago(1d) 
| where (Classification == "Security Updates" or Classification == "Critical Updates") 
| where UpdateState == "Needed"
| extend su = iif(Classification=="Security Updates", 1,0) 
| extend cu = iif(Classification=="Critical Updates", 1,0) 
| summarize dcount(su), dcount(cu) by Computer
| where dcount_cu > 1 and dcount_su > 1
| count

6 Replies

  • CliveWatson's avatar
    CliveWatson
    Former Employee
    Jul 21, 2019

    GouravIN 

     

    Update
| where TimeGenerated >= ago(1d)
| where (Classification == "Security Updates" or Classification == "Critical Updates")
| summarize count(Classification) by Computer
| sort by count_Classification desc 
    // render piechart

    or

    Update
| where TimeGenerated >= ago(1d)
| where (Classification == "Security Updates" or Classification == "Critical Updates")
| extend sec_server = (Classification == "Security Updates")
| extend cri_server = (Classification == "Critical Updates")
| summarize SecurityUpdate = count(sec_server), CriticalUpdate = count(cri_server) by Computer
| sort by CriticalUpdate desc , SecurityUpdate desc
    // render barchart
     
    or
     
    Update
| where TimeGenerated >= ago(1d)
| where (Classification == "Security Updates" or Classification == "Critical Updates")
| summarize ServerThatMatchCriteria=dcount(Computer)

     

    • GouravIN's avatar
      GouravIN
      Brass Contributor
      Jul 22, 2019

      CliveWatson Thanks a lot sir,

       

      Now I am bit stuck to represent this through a pie chart. As i want servers that have missing security update >1 also critical update > 1. But when i thought about query found below hurdles:- 

       

      If i will go by classification then this field either have Security Updates or Critical Updates. And if i will use below line in query then i will nothing in result since since field cannot hold both at a time.

       
      Wrong One: - | where (Classification == "Security Updates" and Classification == "Critical Updates")
      Right One: -| where (Classification == "Security Updates" or Classification == "Critical Updates")
       
      But this contains both due to its or condition.
      I am using below query as of now
       
      Update
      | where TimeGenerated >= ago(1d)
      | where (Classification == "Security Updates" or Classification == "Critical Updates")
      | where UpdateState == "Needed"
      | summarize dcount(Computer) by Classification
      | render piechart
       
      This is generating similar output like as beneath: -
       
      This is good but I want server that has Security Update missing and (here i want and not or condition) Critical Update missing.
       
      Thanks in advance :)
      • CliveWatson's avatar
        CliveWatson
        Former Employee
        Jul 22, 2019

        GouravIN 

         

        In a table, this would work (not Pie chart)

         

        Update
| where TimeGenerated >= ago(1d) 
| where (Classification == "Security Updates" or Classification == "Critical Updates") 
| where UpdateState == "Needed"
| summarize  by Classification, Computer
| evaluate pivot(Classification)

        or , this that allows you to see when > 1 for the two columns?

         

        Update
| where TimeGenerated >= ago(1d) 
| where (Classification == "Security Updates" or Classification == "Critical Updates") 
| where UpdateState == "Needed"
| extend su = iif(Classification=="Security Updates", 1,0) 
| extend cu = iif(Classification=="Critical Updates", 1,0) 
| summarize dcount(su), dcount(cu) by Computer
| where dcount_cu > 1 and dcount_su > 1

         

         

Resources