Aug 21 2019
06:44 AM
- last edited on
Apr 07 2022
06:03 PM
by
TechCommunityAP
Aug 21 2019
06:44 AM
- last edited on
Apr 07 2022
06:03 PM
by
TechCommunityAP
Aug 21 2019 07:41 AM
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 |
Aug 21 2019 07:51 AM
Aug 21 2019 08:13 AM
If you have less than 10,000 unique computers, then a summarize or pivot should be able to help:
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...
Aug 21 2019 08:24 AM
Aug 21 2019 08:45 AM
Solution
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.
Aug 22 2019 10:42 AM
Aug 22 2019 10:57 AM
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
// or with a fixed start and end
Aug 22 2019 11:10 AM
Aug 22 2019 11:19 AM
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
Aug 22 2019 11:39 AM
Aug 22 2019 12:11 PM
Aug 22 2019 12:12 PM
Aug 21 2019 08:45 AM
Solution
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.