Forum Discussion
Solved
Azure Log analytics
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
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.
12 Replies
Something like this?
UAOfficeAddIn | where OfficeAddInName != "" | summarize AddIn = make_list(OfficeAddInName), count() by ComputerGo 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}×pan={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
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 ascor
UAOfficeAddIn | where OfficeAddInName != "" | summarize AddIn = make_set(OfficeAddInName), version = make_set(OfficeProductVersion) , count() by ComputerIf 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...
