When you run the first 2 lines of the SPL, you will get quite a few results, as it queries the internal db for hosts and retrieves a count of their logs. After you add the outputlookup command, it removes all your results and will not add them to the kvstore.
As my coworker found, there is a way to write the results to the kvstore after all, however the SPL for that is quite cursed, as it involves joining the original search back in, but the new results will be written to the kvstore.
| tstats count by host
| rename host as hostname
| table hostname
| join hostname [ tstats count by host | rename host as hostname]
| outputlookup some_kvstore
As far as I aware, 9.1.2, 9.0.6, and latest verisions of cloud have this issue even as fresh installs of Splunk, however it does work on an 8.2.1 and 7.3.3 systems (dont ask). The Splunk user owns everything in the Splunkdir so there is no problem with writing to any files, the kvstore permissions are global, and any user can read or write to it.
So after several hours of troubleshooting, we are stumped here and not sure where we should look next. Changing to a csv is unfortunately not an option.
Things we have tried so far, that i can remember:
Completely fresh installs of Splunk
Cleaning the kvstore via `splunk clean kvstore -local`
Outputting to a csv (works)
Using makeresults to create the fields manually and add to the kvstore (works)
Using the noop command to disable all search optimization
Writing to the kvstore via API (works)
Reading data from the kvstore via inputlookup (works)
Modifying an entry in the kvstore via the lookup editor app (works)
Testing with all search modes (fast, smart, verbose)