OpenSimulator Internals/Useful Queries - Revision history

Jwbshaw: /* Regions */

2026-07-09T06:16:28Z

Regions

← Older revision		Revision as of 06:16, 9 July 2026
Line 168:		Line 168:

	=== Regions ===		=== Regions ===

			'''Note:''' In standalone deployments the <code>regions</code> table is not
			written. Region data exists only in memory for the duration of the process.
			These queries return no rows on a standalone.


	List all registered regions:		List all registered regions:

Jwbshaw at 06:12, 9 July 2026

2026-07-09T06:12:23Z

← Older revision		Revision as of 06:12, 9 July 2026
Line 161:		Line 161:
	WHERE FirstName = 'Test' AND LastName = 'User'		WHERE FirstName = 'Test' AND LastName = 'User'
	);		);

			=== Tokens ===

			The <code>tokens</code> table exists in the schema but is not used as of 0.9.x.
			Do not rely on its contents.

	=== Regions ===		=== Regions ===

Jwbshaw: /* Estate */

2026-07-09T06:01:00Z

Estate

@@ Line 303: / Line 303: @@
 === Estate ===
 Show estate settings:
-  SELECT EstateName, EstateOwner, PublicAccess, AllowVoice,
+  SELECT EstateID, EstateName, EstateOwner,
-         DenyMinors, AllowDirectTeleport, AllowSetHome
+        PublicAccess, AllowVoice, DenyMinors,
   FROM estate_settings;
-----
+Show which region belongs to which estate:
 == Cross-Database Queries ==

Jwbshaw: more material

2026-07-07T22:51:33Z

more material

@@ Line 1: / Line 1: @@
 = OpenSimulator Internals/Useful Queries =
@@ Line 190: / Line 191: @@
   ORDER BY Name;
-Count prims per object:
+Count prims per object (root prims only, sorted by size):
-  SELECT Name, SceneGroupID, GroupPositionX, GroupPositionY, GroupPositionZ,
+  SELECT p.Name, p.SceneGroupID,
-     (SELECT COUNT(*) FROM prims p2 WHERE p2.SceneGroupID = p.SceneGroupID) AS prim_count
+        p.GroupPositionX, p.GroupPositionY, p.GroupPositionZ,
   FROM prims p
-  WHERE UUID = SceneGroupID
+  WHERE p.UUID = p.SceneGroupID
   ORDER BY prim_count DESC;
@@ Line 216: / Line 219: @@
   ORDER BY LinkNumber;
-=== Object Contents ===
+Find objects owned by a specific user:
 Show contents of a named object:
-  SELECT name, assetType, assetID
+  SELECT pi.name, pi.assetType, pi.assetID
-  FROM primitems
+  FROM primitems pi
-  WHERE primID IN (
+  WHERE pi.primID IN (
       SELECT UUID FROM prims
       WHERE SceneGroupID = (
@@ Line 230: / Line 244: @@
+      )
   );
 === Prim Shape ===
@@ Line 246: / Line 276: @@
+  )
   ORDER BY p.LinkNumber;
 === Land ===
@@ Line 269: / Line 309: @@
          DenyMinors, AllowDirectTeleport, AllowSetHome
   FROM estate_settings;
 ----
@@ Line 281: / Line 347: @@
   SELECT locX/256 AS coord_x, locY/256 AS coord_y FROM regions;
 ----
@@ Line 287: / Line 367: @@
 * [[OpenSimulator Internals/Databases]]
-* [[OpenSimulator Internals/GridService]]
+* [[OpenSimulator Internals/Walkthroughs/Avatar Rezzes Box]]
 * [[OpenSimulator Internals/Data Dictionaries]]

Jwbshaw: first

2026-06-29T20:37:22Z

first

New page

= OpenSimulator Internals/Useful Queries =

== Overview ==

These queries are for grid administrators and developers working directly with MariaDB. Correlated subqueries are used to look up UUIDs by name so you don't have to remember them.

All queries assume osimdev_robust for grid data and osimdev_t1 for region data. Substitute your region database name as needed. Examples use "Test User" as the avatar name -- substitute as required.

----

== Connection ==

mysql -u opensim -p osimdev_robust
USE osimdev_t1;

----

== Grid Database (osimdev_robust) ==

=== Users ===

Find a user by first name:

SELECT PrincipalID, FirstName, LastName, Email, UserLevel,
FROM_UNIXTIME(Created) AS created
FROM UserAccounts
WHERE FirstName LIKE 'Test%';

Show login history by name:

SELECT FROM_UNIXTIME(Login) AS last_login,
FROM_UNIXTIME(Logout) AS last_logout,
Online
FROM GridUser
WHERE UserID = (
SELECT PrincipalID FROM UserAccounts
WHERE FirstName = 'Test' AND LastName = 'User'
);

Show home and last location by name:

SELECT HomePosition, HomeLookAt, LastPosition, LastLookAt,
HomeRegionID, LastRegionID
FROM GridUser
WHERE UserID = (
SELECT PrincipalID FROM UserAccounts
WHERE FirstName = 'Test' AND LastName = 'User'
);

Who is currently online:

SELECT UserID, RegionID, LastSeen FROM Presence;

=== Avatar Appearance ===

Show all appearance data for a user by name:

SELECT Name, Value
FROM Avatars
WHERE PrincipalID = (
SELECT PrincipalID FROM UserAccounts
WHERE FirstName = 'Test' AND LastName = 'User'
)
ORDER BY Name;

Show worn wearables only:

SELECT Name, Value
FROM Avatars
WHERE PrincipalID = (
SELECT PrincipalID FROM UserAccounts
WHERE FirstName = 'Test' AND LastName = 'User'
)
AND Name LIKE 'Wearable%';

Show attachment points only:

SELECT Name, Value
FROM Avatars
WHERE PrincipalID = (
SELECT PrincipalID FROM UserAccounts
WHERE FirstName = 'Test' AND LastName = 'User'
)
AND Name LIKE '_ap_%';

=== Inventory ===

Count inventory items by asset type for a user:

SELECT assetType, COUNT(*) AS count
FROM inventoryitems
WHERE avatarID = (
SELECT PrincipalID FROM UserAccounts
WHERE FirstName = 'Test' AND LastName = 'User'
)
GROUP BY assetType
ORDER BY count DESC;

Find an inventory item by name:

SELECT inventoryName, assetType, assetID, parentFolderID
FROM inventoryitems
WHERE avatarID = (
SELECT PrincipalID FROM UserAccounts
WHERE FirstName = 'Test' AND LastName = 'User'
)
AND inventoryName LIKE '%house%';

List top-level inventory folders for a user:

SELECT folderName, type, folderID
FROM inventoryfolders
WHERE agentID = (
SELECT PrincipalID FROM UserAccounts
WHERE FirstName = 'Test' AND LastName = 'User'
)
AND parentFolderID = (
SELECT folderID FROM inventoryfolders
WHERE agentID = (
SELECT PrincipalID FROM UserAccounts
WHERE FirstName = 'Test' AND LastName = 'User'
)
AND type = 8
)
ORDER BY folderName;

=== Assets ===

Count all assets by type:

SELECT assetType, COUNT(*) AS count
FROM assets
GROUP BY assetType
ORDER BY count DESC;

Find assets created by a user:

SELECT id, name, assetType, FROM_UNIXTIME(create_time) AS created
FROM assets
WHERE CreatorID = (
SELECT PrincipalID FROM UserAccounts
WHERE FirstName = 'Test' AND LastName = 'User'
)
ORDER BY create_time DESC;

Find an asset by name:

SELECT id, name, assetType, FROM_UNIXTIME(create_time) AS created
FROM assets
WHERE name LIKE '%note%';

=== Friends ===

Show friends for a user:

SELECT Friend, Flags
FROM Friends
WHERE PrincipalID = (
SELECT PrincipalID FROM UserAccounts
WHERE FirstName = 'Test' AND LastName = 'User'
);

=== Regions ===

List all registered regions:

SELECT regionName,
locX/256 AS coord_x, locY/256 AS coord_y,
sizeX, sizeY, flags,
FROM_UNIXTIME(last_seen) AS last_seen
FROM regions;

Check if a region is online:

SELECT regionName, flags & 4 AS online
FROM regions
WHERE regionName = 'T1';

----

== Region Database (osimdev_t1) ==

=== Objects ===

List all objects with world position (root prims only):

SELECT Name, UUID, GroupPositionX, GroupPositionY, GroupPositionZ
FROM prims
WHERE UUID = SceneGroupID
ORDER BY Name;

Count prims per object:

SELECT Name, SceneGroupID, GroupPositionX, GroupPositionY, GroupPositionZ,
(SELECT COUNT(*) FROM prims p2 WHERE p2.SceneGroupID = p.SceneGroupID) AS prim_count
FROM prims p
WHERE UUID = SceneGroupID
ORDER BY prim_count DESC;

Find an object by name:

SELECT UUID, GroupPositionX, GroupPositionY, GroupPositionZ
FROM prims
WHERE UUID = SceneGroupID
AND Name LIKE '%house%';

List all prims in a named object:

SELECT UUID, Name, LinkNumber, PositionX, PositionY, PositionZ
FROM prims
WHERE SceneGroupID = (
SELECT UUID FROM prims
WHERE UUID = SceneGroupID AND Name LIKE '%Brown House%'
LIMIT 1
)
ORDER BY LinkNumber;

=== Object Contents ===

Show contents of a named object:

SELECT name, assetType, assetID
FROM primitems
WHERE primID IN (
SELECT UUID FROM prims
WHERE SceneGroupID = (
SELECT UUID FROM prims
WHERE UUID = SceneGroupID AND Name LIKE '%Box%'
LIMIT 1
)
);

=== Prim Shape ===

Show shape for a named object:

SELECT p.Name, p.LinkNumber,
ps.ScaleX, ps.ScaleY, ps.ScaleZ,
ps.PCode, ps.Shape, ps.ProfileCurve, ps.PathCurve
FROM primshapes ps
JOIN prims p ON ps.UUID = p.UUID
WHERE p.SceneGroupID = (
SELECT UUID FROM prims
WHERE UUID = SceneGroupID AND Name = 'Object'
LIMIT 1
)
ORDER BY p.LinkNumber;

=== Land ===

Show all parcels:

SELECT LocalLandID, Name, OwnerUUID, Area, LandStatus,
UserLocationX, UserLocationY, UserLocationZ
FROM land;

=== Terrain ===

Check terrain revision and blob size:

SELECT RegionUUID, Revision, LENGTH(Heightfield) AS bytes
FROM terrain;

=== Estate ===

Show estate settings:

SELECT EstateName, EstateOwner, PublicAccess, AllowVoice,
DenyMinors, AllowDirectTeleport, AllowSetHome
FROM estate_settings;

----

== Conversions ==

Unix timestamp to datetime:

SELECT FROM_UNIXTIME(1782133981);

Region metres to region coordinates:

SELECT locX/256 AS coord_x, locY/256 AS coord_y FROM regions;

----

== See Also ==

* [[OpenSimulator Internals/Databases]]
* [[OpenSimulator Internals/GridService]]
* [[OpenSimulator Internals/Data Dictionaries]]