SOLVED
Home

OMS Query for exporting results in excel more than 10000 results

%3CLINGO-SUB%20id%3D%22lingo-sub-306829%22%20slang%3D%22en-US%22%3EOMS%20Query%20for%20exporting%20results%20in%20excel%20more%20than%2010000%20results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-306829%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI%20have%20a%20simple%20query%20to%20fetch%20complete%20computer%20name%20based%20on%20AD%20Site%20code%20and%20tried%20running%20below%20queries.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EBut%20certain%20AD%20sites%20has%20more%20that%2010000%20computer%20and%20only%20results%20first%2010000%20computername%20in%20Advanced%20Analytics%20and%205000%20in%20Log%20Search.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can't%20figure%20out%20how%20to%20export%20next%2010%2C000%20computer%20name%20from%20Advanced%20Analytics%20in%20Azure%20Log%20Analytics%20-%20there%20must%20be%20a%20simple%20way%20-%20but%20how%3F%20Can%20we%20do%20this%20and%20advice%20me%20in%20the%20right%20direction%20for%20the%20query%20to%20run%20export%20fetch%20the%20next%2010%2C000%20computer%20name%20from%20Advanced%20Analytics%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3ESample%20Queries%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EUAApp%20%7C%20project%20Computer%2C%20ComputerID%2C%20AppName%2C%20AppCategory%20%2C%20AppLanguage%2C%20Importance%20%7C%20where%20Computer%20contains%20%22*LOCATIONCODE*%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUAApp%20%7C%20project%20Computer%2C%20ComputerID%2C%20AppName%2C%20AppCategory%20%2C%20AppLanguage%2C%20Importance%20%7C%20where%20Computer%20startswith%20%22*LOCATIONCODE*%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Esearch%20*%3CBR%20%2F%3E%7C%20where%20(%20Type%20%3D%3D%20%22UAApp%22%20)%20%7C%20project%20Computer%2C%20ComputerID%2C%20AppName%2C%20AppCategory%20%2C%20AppLanguage%2C%20Importance%20%7C%20where%20Computer%20contains%20%22*LOCATIONCODE*%22%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-306829%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-363862%22%20slang%3D%22en-US%22%3ERe%3A%20OMS%20Query%20for%20exporting%20results%20in%20excel%20more%20than%2010000%20results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-363862%22%20slang%3D%22en-US%22%3E%3CP%3ENoa%2C%20could%20you%20please%20share%20the%20array%20example%20here%20in%20the%20post%20%3F%20Access%20denied%20on%20your%20workspace.%3C%2FP%3E%3CP%3EThanks%20a%20lot.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-363861%22%20slang%3D%22en-US%22%3ERe%3A%20OMS%20Query%20for%20exporting%20results%20in%20excel%20more%20than%2010000%20results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-363861%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20is%20a%20general%20limitation%20%2C%20not%20only%20for%20export%20to%20excel%2C%20same%20for%20PowerBI.%26nbsp%3B%20For%20large%20tenants%20like%20more%20as%20100k%20spo%20sites%2Fo365%20groups%20or%20Onedrive%20user%20same%26nbsp%3B%20problem%20if%20you%20build%20runbooks%20or%20use%20out%20of%20the%20box%20modules%20like%20the%20o365%20report%20module.%3CBR%20%2F%3EWe%20need%20for%20example%20the%20possibility%20to%20export%20150k%20items%20in%20flat%20query%20view.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-307507%22%20slang%3D%22en-US%22%3ERe%3A%20OMS%20Query%20for%20exporting%20results%20in%20excel%20more%20than%2010000%20results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-307507%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20Rest%20API%20is%20documented%20%3CA%20href%3D%22https%3A%2F%2Fdev.loganalytics.io%2F%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehere%3C%2FA%3E.%20To%20try%20querying%20your%20WS%20with%20your%20account%20easily%2C%26nbsp%3Byou%20can%20also%20use%20this%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Frest%2Fapi%2Floganalytics%2Fquery%2Fexecute%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EAPI%20documentation%20site%3C%2FA%3E%26nbsp%3Bwhich%20has%20a%20%22Try%20it%22%20option%20next%20to%20each%20function.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-307068%22%20slang%3D%22en-US%22%3ERe%3A%20OMS%20Query%20for%20exporting%20results%20in%20excel%20more%20than%2010000%20results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-307068%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EThanks%20for%20the%20points%20and%20I%20tried%20querying%20now%20by%20using%20distinct%20command%20and%20getting%20somehow%20the%20expected%20result.%3C%2FP%3E%3CP%3Eex%3A%26nbsp%3B%20UAComputer%20%7C%20project%20Computer%20%7C%20where%20Computer%20startswith%20%22XXXX%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20advice%20on%20API%20as%20said%20below.%20Whether%20we%20have%20the%20API%20to%20get%20the%20full%20result%20set%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-307050%22%20slang%3D%22en-US%22%3ERe%3A%20OMS%20Query%20for%20exporting%20results%20in%20excel%20more%20than%2010000%20results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-307050%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3EIndeed%20there%20is%20a%2010K%20cap%20on%20the%20result%20set%20size%20in%20the%20UI%2C%20but%20there%20are%20a%20number%20of%20ways%20to%20handle%20larger%20result%20sets.%3C%2FP%3E%0A%3CP%3E1.%20You%20can%20transform%20your%20results%20into%20an%20array%2C%20which%20can%20hold%20much%20more%20than%2010K%20values.%20See%20%3CA%20href%3D%22https%3A%2F%2Fms.portal.azure.com%23%4072f988bf-86f1-41af-91ab-2d7cd011db47%2Fblade%2FMicrosoft_OperationsManagementSuite_Workspace%2FAnalyticsBlade%2Finitiator%2FAnalyticsShareLinkToQuery%2FisQueryEditorVisible%2Ftrue%2Fscope%2F%257B%2522resources%2522%253A%255B%257B%2522resourceId%2522%253A%2522%252Fsubscriptions%252Fe4272367-5645-4c4e-9c67-3b74b59a6982%252Fresourcegroups%252Fcontosoazurehq%252Fproviders%252Fmicrosoft.operationalinsights%252Fworkspaces%252Fcontosoretail-it%2522%257D%255D%257D%2Fquery%2FH4sIAAAAAAAAA12MPQrCQBCF%252B4B3GFIlkEIPkMpGGxU8gIzLQ1ez2TAziyh7eBdFi7zq4%252F1twGJnsFGmxxUCWscwJYOQi6OxH5XqQnWmSeINzor%252FLZwEmgbr1cSxNb9d93%252FYHgqz4RLl2dH%252BuOOAlhZVJk0hsPgX5mfaB75j8GrNLOlotfyord5c8%252FF5tAAAAA%253D%253D%2FisQueryBase64Compressed%2Ftrue%2FtimespanInIsoFormat%2FP1D%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ethis%3C%2FA%3E%20example%2C%20where%20over%2040K%20values%20are%20put%20into%20a%20single%20array%2C%20that%20you%20can%20later%20export%20to%20excel.%20That%20would%20of%20mean%20you%20need%20to%20use%20excel%20formulas%20if%20you%20want%20to%20return%20to%20a%20tabular%20structure.%3C%2FP%3E%0A%3CP%3E2.%20Reduce%20the%20size%20of%20your%20results%20-%20you%20can%20use%20%22distinct%20Computer%22%2C%20%22summarize%20by%20Computer%22%20or%20%22summarize%20makeset%22%20to%20remove%20duplicate%20values%20from%20your%20results%20(Also%2C%20if%20all%20you%20need%20is%20that%20computer's%20name%2C%20project%20only%20that%20column).%3C%2FP%3E%0A%3CP%3E3.%20Use%20the%20API%20-%20the%2010K%20results%20limit%20is%20only%20a%20UI%20limitation%2C%20so%20if%20you%20use%20the%20API%20you'll%20always%20get%20the%20full%20result%20set.%20In%20your%20script%2C%20you%20can%20process%20the%20result%20set%20to%20create%20a%20CSV%20structure%20which%20excel%20can%20load.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHTH%2C%3C%2FP%3E%0A%3CP%3ENoa%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-888558%22%20slang%3D%22en-US%22%3ERe%3A%20OMS%20Query%20for%20exporting%20results%20in%20excel%20more%20than%2010000%20results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-888558%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F51645%22%20target%3D%22_blank%22%3E%40Heiko%20Fuhrmann%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%0A%3CDIV%3E%3CSPAN%3EHeartbeat%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20Computer%20%3C%2FSPAN%3E%3CSPAN%3Econtains%3C%2FSPAN%3E%20%3CSPAN%3E%22con%22%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eproject%3C%2FSPAN%3E%3CSPAN%3E%20computer_result%3Dstrcat(Computer%2C%20ComputerIP%2C%20Category%2C%20OSName)%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Esummarize%3C%2FSPAN%3E%3CSPAN%3E%20computer_results%3Dmakelist(computer_result%2C%20%3C%2FSPAN%3E%3CSPAN%3E1000000%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-934831%22%20slang%3D%22en-US%22%3ERe%3A%20OMS%20Query%20for%20exporting%20results%20in%20excel%20more%20than%2010000%20results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-934831%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F54923%22%20target%3D%22_blank%22%3E%40Noa%20Kuperberg%3C%2FA%3E%26nbsp%3BHow%20do%20we%20convert%20into%20array%20if%20what%20we%20have%20is%20a%20pivot%20table%20(after%20using%20evaluate%20pivot())%20instead%20normal%20table%20with%20normal%20column%20names%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Balasubramanian Murugesan
Contributor

Hi,

I have a simple query to fetch complete computer name based on AD Site code and tried running below queries.


But certain AD sites has more that 10000 computer and only results first 10000 computername in Advanced Analytics and 5000 in Log Search.

 

I can't figure out how to export next 10,000 computer name from Advanced Analytics in Azure Log Analytics - there must be a simple way - but how? Can we do this and advice me in the right direction for the query to run export fetch the next 10,000 computer name from Advanced Analytics?

 

Sample Queries:

UAApp | project Computer, ComputerID, AppName, AppCategory , AppLanguage, Importance | where Computer contains "*LOCATIONCODE*"

 

UAApp | project Computer, ComputerID, AppName, AppCategory , AppLanguage, Importance | where Computer startswith "*LOCATIONCODE*"

 

search *
| where ( Type == "UAApp" ) | project Computer, ComputerID, AppName, AppCategory , AppLanguage, Importance | where Computer contains "*LOCATIONCODE*"

7 Replies
Solution

Hi,

Indeed there is a 10K cap on the result set size in the UI, but there are a number of ways to handle larger result sets.

1. You can transform your results into an array, which can hold much more than 10K values. See this example, where over 40K values are put into a single array, that you can later export to excel. That would of mean you need to use excel formulas if you want to return to a tabular structure.

2. Reduce the size of your results - you can use "distinct Computer", "summarize by Computer" or "summarize makeset" to remove duplicate values from your results (Also, if all you need is that computer's name, project only that column).

3. Use the API - the 10K results limit is only a UI limitation, so if you use the API you'll always get the full result set. In your script, you can process the result set to create a CSV structure which excel can load.

 

HTH,

Noa

Highlighted

Hi,

Thanks for the points and I tried querying now by using distinct command and getting somehow the expected result.

ex:  UAComputer | project Computer | where Computer startswith "XXXX"

 

Can you advice on API as said below. Whether we have the API to get the full result set?

Hi,

 

The Rest API is documented here. To try querying your WS with your account easily, you can also use this API documentation site which has a "Try it" option next to each function.

This is a general limitation , not only for export to excel, same for PowerBI.  For large tenants like more as 100k spo sites/o365 groups or Onedrive user same  problem if you build runbooks or use out of the box modules like the o365 report module.
We need for example the possibility to export 150k items in flat query view.

Noa, could you please share the array example here in the post ? Access denied on your workspace.

Thanks a lot.

@Heiko Fuhrmann 

Heartbeat
| where Computer contains "con"
| project computer_result=strcat(Computer, ComputerIP, Category, OSName)
| summarize computer_results=makelist(computer_result, 1000000)
 
 

@Noa Kuperberg How do we convert into array if what we have is a pivot table (after using evaluate pivot()) instead normal table with normal column names?

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies