Website: Kill Log Queries

Step-by-step guide for web developers - query the PvP Kill Log table for recent-kill feeds, top fights, guild and map stats, build an all-time PvP ladder (kills, deaths, assists, damage), and decode f

The Kill Log writes one row per PvP death into dbo.PvPKillLog. This guide shows how to use that table on a website: a live kill feed, top fights, guild and map stats, an all-time PvP ladder with kills, deaths, assists and damage, and a fight recap page.

Read this first: the table is a rolling window

The table does not keep every kill forever. Each character keeps its last KeepPerChar rows (default 50) as victim and as killer. A row is deleted once both players have more than 50 newer fights.

What that means:

Use	Query the table directly?
Last kills feed, top fights this week, a character's recent fights	Yes - see Recent queries
All-time kills / deaths / damage ladder	No - counts drop as old rows are deleted. Use the ladder table

The table

Column	Type	Meaning
Id	int, identity	Grows by 1 per row. Never reused
KillTime	datetime	Time of death (SQL Server's clock)
KillType	tinyint	0 world, 1 duel, 2 siege, 3 event, 4 gens, 5 guild war
Victim / Killer	varchar(10)	Character names. A kill by a summon is credited to its owner
VictimGuild / KillerGuild	varchar(8)	Guild at the time, '' = none
VictimClass / KillerClass	tinyint	Class code, same values as Character.Class
VictimLevel / KillerLevel	smallint	Level (not master level)
MapNumber, MapX, MapY	tinyint	Where the victim died
FightMs	bigint	Fight length in milliseconds
TotalDmg	bigint	Damage the victim took in the fight, HP + SD, from all attackers
TopSkill	int	Skill that did the most damage (from any attacker). 0 = basic attack, 65535 = several merged, 65534 = poison / bleed, 65533 = reflect
AssistCount	tinyint	Attackers other than the killer
VictimKeep / KillerKeep	bit	Internal window flags. Ignore
Detail	varbinary	Per-attacker and per-skill totals, last 32 hits. See Fight details

Indexes exist on Victim and Killer. For time-based pages add one more (safe, the game does not care):

CREATE NONCLUSTERED INDEX IX_PvPKillLog_KillTime ON dbo.PvPKillLog (KillTime DESC) INCLUDE (KillType, Killer, Victim, TotalDmg);

Skill names come from Skill.txt (skill number → name). Class codes are the same as in Character.Class, which your website already shows.

Recent queries

Run these straight on PvPKillLog. Use parameters for names.

Kill feed (last 20):

SELECT TOP 20 Id, KillTime, KillType, Killer, KillerGuild, KillerClass, Victim, VictimGuild, VictimClass,
       MapNumber, TotalDmg, TopSkill, AssistCount, FightMs
FROM dbo.PvPKillLog
ORDER BY Id DESC;

Top killers, last 7 days (no duels):

SELECT TOP 20 Killer, COUNT(*) AS Kills, SUM(TotalDmg) AS DamageInKills
FROM dbo.PvPKillLog
WHERE KillTime >= DATEADD(day, -7, GETDATE()) AND KillType <> 1
GROUP BY Killer
ORDER BY Kills DESC;

Short periods (day, week) are accurate unless a player fights more than KeepPerChar times in that period. For longer periods use the ladder table.

Biggest fights (most damage taken before dying):

SELECT TOP 10 KillTime, Victim, Killer, TotalDmg, FightMs, AssistCount, MapNumber
FROM dbo.PvPKillLog
WHERE KillTime >= DATEADD(day, -7, GETDATE())
ORDER BY TotalDmg DESC;

Longest fights: same query, ORDER BY FightMs DESC.

Character page (last fights of one character):

SELECT TOP 20 Id, KillTime, KillType,
       CASE WHEN Killer = @name THEN 'Kill' ELSE 'Death' END AS Result,
       CASE WHEN Killer = @name THEN Victim ELSE Killer END AS Opponent,
       MapNumber, TotalDmg, FightMs
FROM dbo.PvPKillLog
WHERE Killer = @name OR Victim = @name
ORDER BY Id DESC;

Nemesis (who killed this character the most):

SELECT TOP 5 Killer, COUNT(*) AS Times
FROM dbo.PvPKillLog
WHERE Victim = @name
GROUP BY Killer
ORDER BY Times DESC;

Guild war / siege table:

SELECT KillerGuild AS Guild, COUNT(*) AS Kills
FROM dbo.PvPKillLog
WHERE KillType IN (2, 5) AND KillerGuild <> '' AND KillTime >= DATEADD(day, -7, GETDATE())
GROUP BY KillerGuild
ORDER BY Kills DESC;

PvP hot spots (deaths per map):

SELECT MapNumber, COUNT(*) AS Deaths
FROM dbo.PvPKillLog
WHERE KillTime >= DATEADD(day, -1, GETDATE())
GROUP BY MapNumber
ORDER BY Deaths DESC;

Most deadly skills:

SELECT TOP 10 TopSkill, COUNT(*) AS Fights
FROM dbo.PvPKillLog
WHERE KillTime >= DATEADD(day, -7, GETDATE()) AND TopSkill < 65533
GROUP BY TopSkill
ORDER BY Fights DESC;

All-time ladder

Because old rows are deleted, an all-time ladder needs its own table. A stored procedure reads every new row since its last run and adds it to the totals. It also reads the Detail column, so the ladder gets damage dealt and assists, which are not in the plain columns.

Ladder column	Comes from
Kills	Rows where the character is Killer
Deaths	Rows where the character is Victim
Assists	Fights where the character hit the victim but did not land the kill
DamageDone	HP + SD damage the character dealt to players who then died
DamageTaken	TotalDmg of the character's deaths
BiggestHit	Biggest single hit (HP + SD) in any counted fight
LastFight	Time of the last counted fight

Damage in fights the target survived is not recorded by the game, so it cannot be counted.

Install (once)

Run this against the muonline database in SQL Server Management Studio. It only adds new objects (WEB_ prefix); it changes nothing the game uses.

Before running, edit the KillType IN (...) line to choose what counts. The default leaves out duels (1), because friends can farm duel kills.

CREATE TABLE dbo.WEB_PvPLadder
(
    Name        varchar(10) NOT NULL PRIMARY KEY,
    Kills       int         NOT NULL DEFAULT 0,
    Deaths      int         NOT NULL DEFAULT 0,
    Assists     int         NOT NULL DEFAULT 0,
    DamageDone  bigint      NOT NULL DEFAULT 0,
    DamageTaken bigint      NOT NULL DEFAULT 0,
    BiggestHit  bigint      NOT NULL DEFAULT 0,
    LastFight   datetime    NULL
)
GO
CREATE TABLE dbo.WEB_PvPLadderState (LastId int NOT NULL)
INSERT INTO dbo.WEB_PvPLadderState (LastId) VALUES (0)
GO
CREATE PROCEDURE dbo.WEB_PvPLadderUpdate
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    BEGIN TRAN;

    DECLARE @From int, @To int;
    SELECT @From = LastId FROM dbo.WEB_PvPLadderState WITH (UPDLOCK, HOLDLOCK);
    SELECT @To = ISNULL(MAX(Id), @From) FROM dbo.PvPKillLog;

    -- New rows to count. Edit the KillType list to choose what counts:
    -- 0 world, 1 duel, 2 siege, 3 event, 4 gens, 5 guild war
    SELECT Id, KillTime, Victim, Killer, TotalDmg, Detail
    INTO #rows
    FROM dbo.PvPKillLog
    WHERE Id > @From AND Id <= @To
      AND KillType IN (0, 2, 3, 4, 5);

    -- Read the Detail blob (format version 1). Numbers in it are little-endian.
    -- Attacker i starts at byte 13 + i*32, skill row j at 13 + AttackerCount*32 + j*21.
    SELECT r.Id, n.v AS Idx, 13 + n.v * 32 AS P, r.Detail
    INTO #a
    FROM #rows r
    JOIN (VALUES (0),(1),(2),(3),(4),(5),(6),(7)) n(v)
      ON n.v < CAST(SUBSTRING(r.Detail, 2, 1) AS int)              -- AttackerCount
    WHERE SUBSTRING(r.Detail, 1, 1) = 0x01;                         -- Version 1

    SELECT r.Id,
           CAST(SUBSTRING(r.Detail, s.P, 1) AS int) AS Idx,
           CAST(CAST(SUBSTRING(r.Detail, s.P + 20, 1) + SUBSTRING(r.Detail, s.P + 19, 1)
                   + SUBSTRING(r.Detail, s.P + 18, 1) + SUBSTRING(r.Detail, s.P + 17, 1) AS binary(4)) AS bigint) AS Biggest
    INTO #s
    FROM #rows r
    CROSS APPLY (SELECT 13 + CAST(SUBSTRING(r.Detail, 2, 1) AS int) * 32 + k.v * 21 AS P
                   FROM (SELECT TOP (48) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS v FROM sys.all_objects) k
                  WHERE k.v < CAST(SUBSTRING(r.Detail, 3, 1) AS int)) s   -- SkillCount
    WHERE SUBSTRING(r.Detail, 1, 1) = 0x01;

    SELECT a.Id,
           LEFT(x.RawName, CHARINDEX(CHAR(0) COLLATE Latin1_General_BIN, x.RawName COLLATE Latin1_General_BIN + CHAR(0)) - 1) AS Name,
           CAST(CAST(SUBSTRING(a.Detail, a.P + 24, 1) + SUBSTRING(a.Detail, a.P + 23, 1)
                   + SUBSTRING(a.Detail, a.P + 22, 1) + SUBSTRING(a.Detail, a.P + 21, 1) AS binary(4)) AS bigint)     -- HP damage
         + CAST(CAST(SUBSTRING(a.Detail, a.P + 28, 1) + SUBSTRING(a.Detail, a.P + 27, 1)
                   + SUBSTRING(a.Detail, a.P + 26, 1) + SUBSTRING(a.Detail, a.P + 25, 1) AS binary(4)) AS bigint) AS Damage,  -- + SD damage
           CAST(SUBSTRING(a.Detail, a.P + 31, 1) AS int) AS Flags,       -- 1 = killer, 2 = merged "others"
           (SELECT MAX(s.Biggest) FROM #s s WHERE s.Id = a.Id AND s.Idx = a.Idx) AS Biggest
    INTO #att
    FROM #a a
    CROSS APPLY (SELECT CAST(SUBSTRING(a.Detail, a.P, 10) AS varchar(10)) AS RawName) x;

    -- Totals per character
    SELECT Name,
           SUM(Kills) AS Kills, SUM(Deaths) AS Deaths, SUM(Assists) AS Assists,
           SUM(DamageDone) AS DamageDone, SUM(DamageTaken) AS DamageTaken,
           MAX(BiggestHit) AS BiggestHit, MAX(LastFight) AS LastFight
    INTO #sum
    FROM
    (
        SELECT Killer AS Name, 1 AS Kills, 0 AS Deaths, 0 AS Assists, CAST(0 AS bigint) AS DamageDone, CAST(0 AS bigint) AS DamageTaken, CAST(0 AS bigint) AS BiggestHit, KillTime AS LastFight FROM #rows
        UNION ALL
        SELECT Victim, 0, 1, 0, 0, TotalDmg, 0, KillTime FROM #rows
        UNION ALL
        SELECT a.Name, 0, 0, CASE WHEN a.Flags & 3 = 0 THEN 1 ELSE 0 END, a.Damage, 0, ISNULL(a.Biggest, 0), r.KillTime
        FROM #att a JOIN #rows r ON r.Id = a.Id
        WHERE a.Flags & 2 = 0 AND a.Name <> ''
    ) x
    GROUP BY Name;

    UPDATE l SET
        Kills       = l.Kills + s.Kills,
        Deaths      = l.Deaths + s.Deaths,
        Assists     = l.Assists + s.Assists,
        DamageDone  = l.DamageDone + s.DamageDone,
        DamageTaken = l.DamageTaken + s.DamageTaken,
        BiggestHit  = CASE WHEN s.BiggestHit > l.BiggestHit THEN s.BiggestHit ELSE l.BiggestHit END,
        LastFight   = CASE WHEN l.LastFight IS NULL OR s.LastFight > l.LastFight THEN s.LastFight ELSE l.LastFight END
    FROM dbo.WEB_PvPLadder l
    JOIN #sum s ON s.Name = l.Name;

    INSERT INTO dbo.WEB_PvPLadder (Name, Kills, Deaths, Assists, DamageDone, DamageTaken, BiggestHit, LastFight)
    SELECT s.Name, s.Kills, s.Deaths, s.Assists, s.DamageDone, s.DamageTaken, s.BiggestHit, s.LastFight
    FROM #sum s
    WHERE NOT EXISTS (SELECT 1 FROM dbo.WEB_PvPLadder l WHERE l.Name = s.Name);

    UPDATE dbo.WEB_PvPLadderState SET LastId = @To;

    COMMIT;
END
GO

Run it once by hand to count the rows already in the table:

EXEC dbo.WEB_PvPLadderUpdate;

Keep it updated

Run EXEC dbo.WEB_PvPLadderUpdate every 1-5 minutes. Each run only reads new rows, so it is cheap. Running it twice in a row counts nothing twice.

Pick one way:

Way	How
SQL Server Agent (not in Express)	New job, one step EXEC dbo.WEB_PvPLadderUpdate, schedule every 1 minute
Windows Task Scheduler	Every 1 minute: sqlcmd -S . -E -d muonline -Q "EXEC dbo.WEB_PvPLadderUpdate"
Website cron	Your site's scheduler runs the EXEC

Run it at least every few minutes. A row the ladder has not read yet is lost if it gets deleted first (only happens when both players have KeepPerChar newer fights in between, so every 1-5 minutes is safe).

Ladder queries

Top killers:

SELECT TOP 100 l.Name, c.Class, c.cLevel, l.Kills, l.Deaths, l.Assists,
       CAST(l.Kills AS float) / NULLIF(l.Deaths, 0) AS KD
FROM dbo.WEB_PvPLadder l
JOIN dbo.Character c ON c.Name = l.Name          -- hides deleted characters
ORDER BY l.Kills DESC, l.Deaths ASC;

Damage ladder: ORDER BY l.DamageDone DESC. Biggest hit: ORDER BY l.BiggestHit DESC. Best K/D (minimum 20 kills, so one lucky kill does not top it):

SELECT TOP 100 l.Name, l.Kills, l.Deaths, CAST(l.Kills AS float) / NULLIF(l.Deaths, 0) AS KD
FROM dbo.WEB_PvPLadder l
JOIN dbo.Character c ON c.Name = l.Name
WHERE l.Kills >= 20
ORDER BY CASE WHEN l.Deaths = 0 THEN 1 ELSE 0 END DESC, KD DESC;

Season reset: TRUNCATE TABLE dbo.WEB_PvPLadder. Do not reset WEB_PvPLadderState, or old kills are counted again.

Fight details

The Detail column holds the full recap the game shows in the Kill Log window. All numbers are little-endian. Use it for a fight recap page.

Part	Size	Count
Head	12	1
Attacker	32	AttackerCount (max 8)
Skill row	21	SkillCount (max 48)
Hit	14	HitCount (max 32), oldest first

Head:

Offset	Size	Field
0	1	Version. Only 1 exists
1	1	AttackerCount
2	1	SkillCount
3	1	HitCount
4	4	Victim max HP
8	4	Victim max SD

Attacker (32 bytes):

Offset	Size	Field
0	10	Name, zero-padded (no zero when 10 characters long)
10	8	Guild, zero-padded
18	1	Class code
19	2	Level
21	4	HP damage dealt
25	4	SD damage dealt
29	2	Hits
31	1	Flags: 1 = landed the kill, 2 = "others" (more than 8 attackers merged into one entry)

Skill row (21 bytes):

Offset	Size	Field
0	1	Attacker (index into the attacker list, 0 = first)
1	2	Skill. 0 basic attack, 65535 other skills merged, 65534 poison / bleed, 65533 reflect
3	4	HP damage
7	4	SD damage
11	2	Hits
13	2	Critical hits
15	2	Excellent hits
17	4	Biggest single hit (HP + SD)

Hit (14 bytes):

Offset	Size	Field
0	1	Attacker index
1	2	Skill
3	4	HP damage
7	4	SD damage
11	1	Flags: 1 critical, 2 excellent, 4 ignore defense, 8 double, 16 triple, 32 combo, 64 reflect, 128 poison / bleed
12	2	Time before the death, in 1/100 seconds

Node.js / TypeScript

// fightDetail.ts
const cstr = (b: Buffer) => { const z = b.indexOf(0); return b.subarray(0, z < 0 ? b.length : z).toString("latin1"); };

export function readFightDetail(d: Buffer) {
  if (!d || d.length < 12 || d[0] !== 1) return null;
  const head = { attackerCount: d[1], skillCount: d[2], hitCount: d[3], victimMaxHp: d.readUInt32LE(4), victimMaxSd: d.readUInt32LE(8) };
  let p = 12;
  if (d.length < p + head.attackerCount * 32 + head.skillCount * 21 + head.hitCount * 14) return null;

  const attackers = [];
  for (let i = 0; i < head.attackerCount; i++, p += 32) {
    attackers.push({
      name: cstr(d.subarray(p, p + 10)), guild: cstr(d.subarray(p + 10, p + 18)), dbClass: d[p + 18],
      level: d.readUInt16LE(p + 19), hp: d.readUInt32LE(p + 21), sd: d.readUInt32LE(p + 25), hits: d.readUInt16LE(p + 29),
      killer: (d[p + 31] & 1) !== 0, others: (d[p + 31] & 2) !== 0,
    });
  }
  const skills = [];
  for (let i = 0; i < head.skillCount; i++, p += 21) {
    skills.push({
      attacker: d[p], skill: d.readUInt16LE(p + 1), hp: d.readUInt32LE(p + 3), sd: d.readUInt32LE(p + 7),
      hits: d.readUInt16LE(p + 11), crits: d.readUInt16LE(p + 13), excs: d.readUInt16LE(p + 15), biggest: d.readUInt32LE(p + 17),
    });
  }
  const hits = [];
  for (let i = 0; i < head.hitCount; i++, p += 14) {
    hits.push({
      attacker: d[p], skill: d.readUInt16LE(p + 1), hp: d.readUInt32LE(p + 3), sd: d.readUInt32LE(p + 7),
      flags: d[p + 11], secondsBeforeDeath: d.readUInt16LE(p + 12) / 100,
    });
  }
  return { ...head, attackers, skills, hits };
}

PHP

<?php
// fight_detail.php
function klStr(string $b): string { $z = strpos($b, "\0"); return $z === false ? $b : substr($b, 0, $z); }

function readFightDetail(string $d): ?array {
    if (strlen($d) < 12 || ord($d[0]) !== 1) return null;
    $h = unpack('Cver/Cattackers/Cskills/Chits/VmaxHp/VmaxSd', $d);
    $p = 12;
    if (strlen($d) < $p + $h['attackers'] * 32 + $h['skills'] * 21 + $h['hits'] * 14) return null;

    $attackers = [];
    for ($i = 0; $i < $h['attackers']; $i++, $p += 32) {
        $a = unpack('Cclass/vlevel/Vhp/Vsd/vhits/Cflags', $d, $p + 18);
        $attackers[] = ['name' => klStr(substr($d, $p, 10)), 'guild' => klStr(substr($d, $p + 10, 8)),
                        'class' => $a['class'], 'level' => $a['level'], 'hp' => $a['hp'], 'sd' => $a['sd'],
                        'hits' => $a['hits'], 'killer' => ($a['flags'] & 1) !== 0, 'others' => ($a['flags'] & 2) !== 0];
    }
    $skills = [];
    for ($i = 0; $i < $h['skills']; $i++, $p += 21) {
        $skills[] = unpack('Cattacker/vskill/Vhp/Vsd/vhits/vcrits/vexcs/Vbiggest', $d, $p);
    }
    $hits = [];
    for ($i = 0; $i < $h['hits']; $i++, $p += 14) {
        $x = unpack('Cattacker/vskill/Vhp/Vsd/Cflags/vago', $d, $p);
        $x['secondsBeforeDeath'] = $x['ago'] / 100;
        $hits[] = $x;
    }
    return ['victimMaxHp' => $h['maxHp'], 'victimMaxSd' => $h['maxSd'],
            'attackers' => $attackers, 'skills' => $skills, 'hits' => $hits];
}

unpack with an offset needs PHP 7.1+. Read Detail with PDO::SQLSRV_ENCODING_BINARY so it comes back as raw bytes.

Useful numbers for a recap page:

• Each attacker's share: (hp + sd) / sum of all attackers' (hp + sd).

• Skill share: (skill.hp + skill.sd) / TotalDmg.

• "Killing blow": the last entry of hits.

In game, only the killer and the victim can open a fight's details. Showing them publicly is your choice. A safe default: public pages show the plain columns only, and the full recap only to the logged-in owner of one of the two characters.

Important Behavior

• Only player-kills-player deaths are recorded. No monster kills, no suicides.

• What gets recorded depends on KillLog.xml: disabled <Types> and fights under MinTotalDamage never reach the table. The ladder can only count what is there.

• Duel farming. The ladder default skips duels. Watch for pairs that kill each other again and again:

  SELECT TOP 20 Killer, Victim, COUNT(*) AS Times
  FROM dbo.PvPKillLog
  WHERE KillTime >= DATEADD(day, -1, GETDATE())
  GROUP BY Killer, Victim
  HAVING COUNT(*) >= 10
  ORDER BY Times DESC;

• Character rename. Rows keep the old name. The ladder starts a new line for the new name.

• Time. KillTime is the SQL Server machine's local time.

• Free plugin, no license check: if Enabled="1" in KillLog.xml, rows are written.

Common Issues

Table is empty. The Kill Log is off (Enabled="0"), or dbo.PvPKillLog was never created. See Kill Log.

All-time counts go down over time. You are counting PvPKillLog directly. Use the ladder table.

Ladder stopped growing. The scheduled EXEC is not running. Compare SELECT LastId FROM WEB_PvPLadderState with SELECT MAX(Id) FROM PvPKillLog.

DamageDone is always 0. Rows have an unexpected Detail version, or Detail was cut by a changed column type. It must stay varbinary(2048).

Do not use the stock KillLog table (columns Name1, Name2, KD1...). The Kill Log plugin never writes to it.

Related pages

• Kill Log - settings, types, KeepPerChar