SOLVED

Advanced hunting API Limitations

%3CLINGO-SUB%20id%3D%22lingo-sub-1252300%22%20slang%3D%22en-US%22%3EAdvanced%20hunting%20API%20Limitations%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1252300%22%20slang%3D%22en-US%22%3E%3CP%3EThe%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fwindows%2Fsecurity%2Fthreat-protection%2Fmicrosoft-defender-atp%2Frun-advanced-query-api%23limitations%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Eofficial%20docs%3C%2FA%3E%20state%20there%20is%20a%26nbsp%3B%3CSPAN%3Emax%20result%20limit%20of%20100%2C000%20rows.%20Is%20there%20also%20a%20limit%20on%26nbsp%3B%3CSTRONG%3E%3CEM%3Etable_size%26nbsp%3B%3C%2FEM%3E%3C%2FSTRONG%3Ethat%20can%20be%20returned%20when%20querying%20via%20the%20advanced%20hunting%20API%3F%20If%20so%2C%20what%20is%20the%20limit%3F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EFor%20example%2C%20this%20query%20r%3C%2FSPAN%3E%3CSPAN%3Eeturns%20a%20generic%20'400%20Bad%20Request'%20response%3A%3C%2FSPAN%3E%3C%2FP%3E%3CDIV%3E%3CDIV%3EatpQuery%3D'DeviceEvents%20%7C%26nbsp%3Blimit%20100000'%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3EHowever%2C%20when%20limiting%20to%20a%20few%20columns%20(much%20smaller%20table%20size%2C%20same%20number%20of%20requested%20rows)%2C%20I'm%20able%20to%20retrieve%20all%20100K%20rows%20with%3A%3C%2FDIV%3E%3CDIV%3EatpQuery%3D'DeviceEvents%20%7C%20project%20Timestamp%2C%20DeviceId%2C%20DeviceName%20%7C%20limit%20100000'%3CBR%20%2F%3E%3CBR%20%2F%3EFor%20the%20successful%20query%2C%20the%20Stats%20returned%20are%20%5B%22dataset_statistics%22%5D%5B%7B'table_row_count'%3A%20100000%2C%20'table_size'%3A%207734181%7D%5D%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1258430%22%20slang%3D%22en-US%22%3ERe%3A%20Advanced%20hunting%20API%20Limitations%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1258430%22%20slang%3D%22en-US%22%3E%3CP%3EFor%20anyone%20that%20comes%20across%20this%20issue%2C%20the%20confirmed%20size%20limit%20on%20results%20returned%20by%20a%20hunting%20API%20query%20is%2050MB.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

The official docs state there is a max result limit of 100,000 rows. Is there also a limit on table_size that can be returned when querying via the advanced hunting API? If so, what is the limit?

 

For example, this query returns a generic '400 Bad Request' response:

atpQuery='DeviceEvents | limit 100000'
 
However, when limiting to a few columns (much smaller table size, same number of requested rows), I'm able to retrieve all 100K rows with:
atpQuery='DeviceEvents | project Timestamp, DeviceId, DeviceName | limit 100000'

For the successful query, the Stats returned are ["dataset_statistics"][{'table_row_count': 100000, 'table_size': 7734181}]
1 Reply
Highlighted
Best Response confirmed by StephenMcc (Occasional Contributor)
Solution

For anyone that comes across this issue, the confirmed size limit on results returned by a hunting API query is 50MB.