SOLVED

Azure Log analytics

Brass Contributor
I am looking to get a list of computer names and all the add-ins on that computer. The canned query for this returns an aggregated list of add-ins which is helpful at viewing the complexity of this task. Beyond that I need to see which PC's have which add-ins. I was able to get close with a query, but it did not list all computers in the environment and all of each computers add-ins. I suspect this is not terribly complicated for someone advanced with this query language. he canned query search in (UAOfficeAddIn) OfficeAddInName != "" | summarize AggregatedValue = count(Computer) by OfficeAddInName | sort by AggregatedValue
12 Replies

Hi @Robert James Reber 

 

Something like this?

UAOfficeAddIn
| where OfficeAddInName != ""
| summarize AddIn = make_list(OfficeAddInName), count() by Computer

Go to Log Analytics and Run Query

 

 

Computer AddIn count_
GaryRa-Dsktp.Contoso ["Microsoft SharePoint Server Colleague Import Add-in","Microsoft VBA for Outlook Addin","OneNote Notes about Outlook Items","Microsoft Data Streamer for Excel","Inquire","Windows Search Email Indexer","OneNote Notes about PowerPoint Presentations","OneNote Notes about Word Documents","OneNote Linked Notes Add-In","Microsoft Exchange Add-in","Microsoft Power Map for Excel","Microsoft Power Pivot for Excel","Conversation History Add-in for Microsoft Office 2016","OneNote Linked Notes Add-In","Microsoft Power View for Excel","Microsoft Access Outlook Add-in for Data Collection and Publishing","Outlook Social Connector 2016","Skype Meeting Add-in for Microsoft Office"] 18
BaFrank-Corp.Contoso ["Windows Search Email Indexer","QBExcelReportUpdater","QBExcelReportUpdater","Avast Add-in"] 4
PatriciaSa-Book.Contoso ["Microsoft SharePoint Workspace Proxy for Outlook Add-in","Microsoft Forefront Identity Manager Add-in","Sharing Add-in for Microsoft Lync 2010","Business Connectivity Services Add-In","Windows Search Email Indexer","OneNote Notes about Outlook Items","Microsoft Exchange Add-in","Microsoft VBA for Outlook Addin","Microsoft Visual Studio 2008 Tools for Office Design-Time Adaptor for Excel 2007","Microsoft Visual Studio 2008 Tools for Office Design-Time Adaptor for Excel 2003","Microsoft Visual Studio 2008 Tools for Office Design-Time Adaptor for Word 2003","Microsoft Outlook Social Connector","Online Meeting Add-in for Microsoft Lync 2010","Microsoft Visual Studio 2008 Tools for Office Design-Time Adaptor for Word 2007"] 14
SavannaRh-Dsk.Contoso ["Microsoft Azure Information Protection","Microsoft Azure Information Protection","Skype Meeting Add-in for Microsoft Office 2013","Microsoft Azure Information Protection","Microsoft Azure Information Protection","Visual Studio Tools for Office Design-Time Adaptor for Word","Team Foundation Add-in","Windows Search Email Indexer","Microsoft Power Query for Excel","Visual Studio Tools for Office Design-Time Adaptor for Excel"] 10
RobertHi-Corp.Contoso ["Load Test Report Addin","Visual Studio Tools for Office Design-Time Adaptor for Excel","Skype Meeting Add-in for Microsoft Office 2013","Windows Search Email Indexer","Load Test Report Addin","Team Foundation Add-in","Visual Studio Tools for Office Design-Time Adaptor for Word"] 7

 

I think I have a query now that does work, but I've blasted through the rafters. The query is capable of returning 10,000 records, and I have surely exceeded that with this query. So now I am off to experiment with GET https://api.loganalytics.io/v1/workspaces/{workspaceId}/query?query={query}&timespan={timespan} which can handle 500,000 records. This is the basic query I can use via the portal, which I hope transfers to the API call. search in (UAOfficeAddIn) OfficeAddInName != "" | where OfficeAddInName != "" It does list every detail in that OfficeAddIn, which provides each individual PC and their associated add-ins. Thanks for your reply Clive

@Robert James Reber 

 

If you have less than 10,000 unique computers, then a summarize or pivot should be able to help:

  • reduce time the query takes 
  • reduce complexity
  • get all the details about a computer on a single row

 

 UAOfficeAddIn
| where OfficeAddInName != ""
| evaluate pivot(OfficeAddInName, count(ComputerID), Computer)
| sort by Computer asc

 

or 

 

UAOfficeAddIn
| where OfficeAddInName != ""
| summarize AddIn = make_set(OfficeAddInName), version = make_set(OfficeProductVersion) , count() by Computer

 

 

If you have more than 10,000 computers - well done :)  You could divide the query by Computer start letter, and get A-G, then H-P etc...

Clive, I have 15,000 PC's. I tried this because I have 441 or so add-ins. UAOfficeAddIn |where OfficeAddInName != " " |summarize AddIn = makelist(Computer), count(computer) by OfficeAddInName This return AddInName, then all PC's with that add-in 8,900 The aggregate I took a few weeks ago shows ~11,000 Pc's as having Microsoft Exchange add-in. While this query shows 8,900 for the same add-in. I of course do not want to miss any PC's in my query. Do you think I captured everything with this query? Or have I been bitten by the 10,000 record limit just the same? Thanks again for your suggestions. I think I am pretty close.
best response confirmed by Robert James Reber (Brass Contributor)
Solution

@Robert James Reber 

 

Note: https://docs.microsoft.com/en-us/azure/kusto/query/makeset-aggfunction  you should always use make_set or make_list which have replaced makeset and makelist as the older functions have a 128 limit. 

Make_set is probably the one to use - as it "Returns a dynamic (JSON) array of the set of distinct values that Expr takes in the group."

 

The aim here is to get the row (record) count below 10,000, then you know you have all the data, if you have 441 then all should be ok.  Also look out for the 500 column limit, but I think you are a long way from that.  

 

Annotation 2019-08-21 164436.png

Clive, I wonder if you can help me with this question. When I do the below query I get strange results? In other words, if I set my timeframe for 24hrs, or 7 days, or custom (15 days) my numbers keep changing. I'm not sure I feel confident with any of these number right now. Am I looking at Log Analytics incorrectly? I want a list of all PC's that have this add-in, which will be in excess of 12,000, so I limit my scope to fewer geographic locations as you suggested the other day and I still get inconsistent numbers based on the Time Range I select? search in (UAOfficeAddIn) OfficeAddInName == "Microsoft VBA for Outlook Addin" | where Computer !contains "US" and Computer !contains "GB" and Computer !contains "SG" |order by Computer

@Robert James Reber 

 

Are the number massively out?  I suspect it maybe run to run differences, as when you ask to see the past 7days it does just that, from the moment you run the query to the same time 7days ago.  As the hours/minutes change so does the results. 

 

We can fix that with a fixed window of time, in the case below (line #2), I added a midnight to midnight clause using the startofday function, e.g. midnight 7days ago to midnight yesterday.

 

UAOfficeAddIn
| where TimeGenerated > startofday(ago(7d)) and TimeGenerated < startofday(now())
| where OfficeAddInName == "Microsoft VBA for Outlook Addin" 
| where Computer !contains "US" and Computer !contains "GB" and Computer !contains "SG" 
|order by Computer

 

Go to Log Analytics and Run Query 

 

Let me know if that helps?

 

If you look at lots of my queries on my blog, I often use startofday for this reason 

 

// with just a fixed startpoint 

| where TimeGenerated > startofday(ago(7d)) 
 

// or with a fixed start and end 

| where TimeGenerated > startofday(ago(7d)) // and TimeGenerated < startofday(now())

 

Thanks Clive, I tried that and I still get strange outputs. I do see 5 entries for one PC barcode that lists 2019-08-16T08.00.00.00 then 2019-08-15T08.00.00.00 then 2019-08-17T08.00.00.00 So The further back in time I go, the more redundancy I see for more and more PC's. if I use the 24hour default, I do not see an entire fleet, which should be around 15,000 or so. I instead get 1,594

@Robert James Reber 

 

What about when you go back to using make_set?  So you only get one row of data per computer.

 

UAOfficeAddIn
| where TimeGenerated > startofday(ago(7d))  and TimeGenerated < startofday(now())
| where OfficeAddInName == "Microsoft VBA for Outlook Addin" 
| where Computer !contains "US" and Computer !contains "GB" and Computer !contains "SG" 
| summarize AddIn = make_set(OfficeAddInName), count() by Computer

 

 

 

Make_set worked much better. That removed the redundancy and I can go back pretty far. So this leads me to believe I have a misconception on how these logs work. If my time duration is 24 hours, I do not see my entire workforce. If I go back in time say 7 days, I see more. So these logs must not be one huge blob, but more so smaller activity logs. I am trying to resolve them like an SCCM report that gathers data from every PC, then logs the add-in and the barcode for every PC. I can then report that every PC that checks in is accounted for. This seems to be more dynamic in nature and in order for me to see the entire fleet, I must go far enough into the past to see when they might have logged on or used that add-in to register in these logs. I think ????
Thanks Clive for all your help, I took all your suggestions and bundled them into something I think will get me to my goal. I can now go pretty far into the past and that make_set command removes the redundancy. I can also split my focus into two queries and marry them together in Excel. This has been a life saver for what I am preparing to do, Bob
Logs are created a row at a time based on timegernated...so on some hours or days you may get more or less logs. A machine that is off wont send any. Pivot on the computer name which is unique unlike time...hence the consistency with make-set
1 best response

Accepted Solutions
best response confirmed by Robert James Reber (Brass Contributor)
Solution

@Robert James Reber 

 

Note: https://docs.microsoft.com/en-us/azure/kusto/query/makeset-aggfunction  you should always use make_set or make_list which have replaced makeset and makelist as the older functions have a 128 limit. 

Make_set is probably the one to use - as it "Returns a dynamic (JSON) array of the set of distinct values that Expr takes in the group."

 

The aim here is to get the row (record) count below 10,000, then you know you have all the data, if you have 441 then all should be ok.  Also look out for the 500 column limit, but I think you are a long way from that.  

 

Annotation 2019-08-21 164436.png

View solution in original post