Have to run same queries against many servers (30+), use CURSOR or LOOP
Connecting to other instances without Linked Servers is not convenient unless you use Registered Servers or Central Management Server.
If you have registered servers or CMS, you can execute the same query against all the instances in a group (or all registered servers) at once via SSMS (see the links above). But I don't know if you can output the results to a single table in one instance; you may be stuck with copy &amp; paste to Excel.
If you're willing to go with Powershell, the dbatools module has a very convenient solution in Invoke-DbaQuery.
Invoke-DbaQuery -Query &quot;select fields from table&quot; -SqlInstance instance1,instance2,instance3 will return an object representing the results of the query plus a field with the corresponding instance name that each returned record came from.
From there, you can filter the results with Where-Object, or you can copy the results into a table in another instance with Write-DbaDbDataTable