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;