Render Piechart

[GouravIN]

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 🙂

[#2]

@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) 

[#3]

@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 🙂

[#4]

@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

[#5]

@CliveWatson Thank you very much for the help.

Sir, Is it possible to count server numbers that has security and Critical patches missing.

Like, I have total 20 servers and 15 has security and 8 has critical patches missing. So i want server count that has both of patches missing.

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

| project Computer

| print Total_Server = count(project Computer)

when I used project Computer that populated all computer name. But I want total count of Computer, if somehow i can achieve this. That would be the best answer of my question.

Thanks in advance 🙂

[#6 · Accepted solution]

@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 

[#7]

Ahh.... I missed this simple thing.

@CliveWatson Thanks a lot Sir for helping me here 🙂