I am running the below search with table command. The data which I am searching is very huge and it exceeds more than 600,000 rows. But the query got stuck at 500,000 rows. When I stop the query, it failed as it got expired.
What is the maximum number of rows this query can result? Or is there any limitation on disk quota for the search (for admin role)?
The table command is a finalizing command which means that it stops the map-reduce work on the Indexers causes all remaining partial results and commands to be pulled to the Search Head. This is why it should always be the VERY LAST command and fields should be used in the middle (if anything at all). It has no maximum but it is easy to use up all the RAM available to your Search Head in your browser which will then cause it to truncate. Check the search.log in the Job inspector.
I don't believe this is exactly correct. I just ran a large table query on a machine with plenty of RAM and it "seemed to cap" at exactly 500k rows also. Then, a bit after the query itself completed, the table suddenly updated to 4M rows. That cannot be a coincidence, I think there must be a "first pass" size or something in the backend jobs.
For sure, Splunk Cloud should have setup some configuration to avoid long running searches. It is required to keep a good performance on Splunk Cloud. I suggest to open a case with splunk support to report the issue you are having and also request for them about the parameters configuration for your environment.
I also suggest a change to your report in order to get only the required fields for this report using the fields command. Please tweak your report to see if there is any other improvement you can apply.
It can be something like this: