Forum Discussion
OMS Query for exporting results in excel more than 10000 results
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
MicrosoftHi,
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
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?
- Noa Kuperberg
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.