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 (root prims only, sorted by size):

SELECT p.Name, p.SceneGroupID,
       p.GroupPositionX, p.GroupPositionY, p.GroupPositionZ,
       COUNT(*) AS prim_count
FROM prims p
WHERE p.UUID = p.SceneGroupID
GROUP BY p.SceneGroupID, p.Name, p.GroupPositionX, p.GroupPositionY, p.GroupPositionZ
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;

Find objects owned by a specific user:

SELECT Name, UUID, GroupPositionX, GroupPositionY, GroupPositionZ
FROM prims
WHERE UUID = SceneGroupID
AND OwnerID = (
    SELECT PrincipalID FROM osimdev_robust.UserAccounts
    WHERE FirstName = 'Test' AND LastName = 'User'
)
ORDER BY Name;

Object Contents (Task Inventory)

Show contents of a named object:

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

Count task inventory items by type across all objects:

SELECT pi.assetType, COUNT(*) AS count
FROM primitems pi
GROUP BY pi.assetType
ORDER BY count DESC;

Show all objects that contain scripts:

SELECT DISTINCT p.Name, p.UUID, p.GroupPositionX, p.GroupPositionY, p.GroupPositionZ
FROM prims p
JOIN primitems pi ON pi.primID = p.UUID
WHERE p.UUID = p.SceneGroupID
AND pi.assetType = 10
ORDER BY p.Name;

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;

Persistence State

Find objects with pending changes (not yet persisted):

SELECT Name, UUID, GroupPositionX, GroupPositionY, GroupPositionZ
FROM prims
WHERE UUID = SceneGroupID
AND RezzerID IS NOT NULL
ORDER BY Name;

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;

Cross-Database Queries

Objects owned by a named user (cross-database):

SELECT p.Name, p.UUID, p.GroupPositionX, p.GroupPositionY, p.GroupPositionZ
FROM osimdev_t1.prims p
WHERE p.UUID = p.SceneGroupID
AND p.OwnerID = (
    SELECT PrincipalID FROM osimdev_robust.UserAccounts
    WHERE FirstName = 'Test' AND LastName = 'User'
)
ORDER BY p.Name;

Inventory items for an object currently in the region:

SELECT pi.name, pi.assetType, a.name AS asset_name
FROM osimdev_t1.primitems pi
JOIN osimdev_robust.assets a ON a.id = pi.assetID
WHERE pi.primID = (
    SELECT UUID FROM osimdev_t1.prims
    WHERE UUID = SceneGroupID AND Name LIKE '%Box%'
    LIMIT 1
);

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;

Asset type numbers:

0  = texture
2  = sound
3  = landmark
5  = clothing
6  = object
7  = notecard
10 = script (LSL)
13 = body part
20 = animation
24 = link (inventory link)
25 = mesh