1. Before posting any MODs, please contact us at support@portalarium.com to ensure your MOD is acceptable to the Terms of Service and EULA of the game. Portalarium is not responsible for use of MODs and possible adverse effects on your client or computer, and we may take action on accounts using MODs that are deemed illegal. Whether submitted here or elsewhere in the community, use MODs at your own risk!
    Dismiss Notice

Powershell Chatlog to combat CSV converter

Discussion in 'Player Created Resources' started by Spungwa, Mar 9, 2018.

  1. Spungwa

    Spungwa Avatar

    Messages:
    170
    Likes Received:
    476
    Trophy Points:
    18
    I have been writing a powershell script (windows scripting language) to input a chat log and it creates a structured CSV file of all the combat lines.
    You can then do whatever analysis you want to the data in excel or other tool that can import CSVs.

    @Chris It is noticeable that all buffs and debuffs are not in the log, it gives the words of power, but I think only for yourself and does not tell you who it was cast against (self or enemy)

    In doing this i have the made RegExs for each of the types of line (i think i have them all now, but then i don't necessary have examples of them all)

    I can send anyone the script if they want a copy, hit me up on discord, same name as here.

    But otherwise you can copy the below post and save it into a file call CreateCombatCSV.ps1

    Once you have done that you need to run it giving a chatlog file as input parameter. For those that know more about powershell you can also use the -verbose and see the lines for debugging. The output is put into the output pipeline so you can pipe it into other scripts if you like, but if that makes no sense to you it does not matter.

    To run it you need start a powershell session in administrator mode.
    -search cmd and right click command prompt and select run as administrator

    Go to where you stored the script.
    run powershell in the command prompt
    - type powershell and press enter

    Check you have rights to run the script, if not set the execution policy so you can
    - type Get-ExecutionPolicy
    if it says anything other than unrestrict you need to set to unrestricted, if it say unrestricted you can skip the next step.

    You may want to remember the setting to put it back afterwards.
    set policy to unrestricted
    - type Set-ExecutionPolicy unrestricted

    To get the chat log data into a CSV you invoke the script and give it the path and filename then pipe the output pipe into a file.
    Replace .\ChatLogTest.txt with the path and filename of the chatlog you want to process.
    Replace outputfilename.csv with whatever filename you want to call it. If no path is put it will be put the directory you are in in the powershell prompt.
    - type .\CreateCombatCSV.ps1 .\ChatLogTesting.txt | Out-File outputfilename.csv

    To put your execution policy back to what it was
    - type Set-ExecutionPolicy <old value probably Restricted>

    To exit type exit then type exit again, the first exit the powershell prompt the second exits the command prompt.

    If you find lines that my script misses, could you send me a copy of the chat line, either reply here, or send it in discord. This is still a work in progress.

    Note this ignores crafting lines on purpose, it would be better to create another script, as i think you would want different heading for your CSV for this.
     
    Last edited: Mar 9, 2018
    Adam Crow, Ben_Hroth, Numa and 5 others like this.
  2. Spungwa

    Spungwa Avatar

    Messages:
    170
    Likes Received:
    476
    Trophy Points:
    18
    Code:
    [CmdletBinding()]
    Param(
        [Parameter(Mandatory=$True,Position=1)]
        [string]$FilePath
    )
    function ReplaceCommas ($text) {
        if ($text -ne $null) {
            return $text.Replace(",","-");
        }
        return $null;
    }
    #RegEx for entries
    $IsDropItem = "Item \((?<item>.*)\) removed from inventory.";
    $IsFizzle   = 'Your attempt to use "(?<verb>.*)\" has fizzled.';
    $IsAttack   = ".*\]+ (?<subject>.*) attacks (?<object>.*) and (?<modifier>.*), dealing (?<damage>.*) points of (?<damageType>.*) from (?<verb>.*)\.";
    $IsAttackNPC = ".*\]+ (?<subject>.*) attacks (?<object>.*) and (?<modifier>.*), dealing (?<damage>.*) points of (?<damageType>.*)\.";
    $IsHarvestItem = ".*\]+ (?<subject>.*) harvested an Item \((?<item>.*)\)"; 
    $IsLootedItem = ".*\]+ (?<subject>.*) looted an item \((?<item>.*)\)";
    $IsLottedGoldITem = ".*\]+ (?<subject>.*) picked up (?<gold>.*) gold.";
    $IsLootedSharedGoldItem = "gold from the (?<gold>.*) gold that (?<temp>.*) picked up\."; 
    $IsSlainItem = ".*\]+ (?<object>.*) has been slain by (?<subject>.*)'s (?<verb>.*)!";
    $IsDodgeItem = ".*\]+ (?<subject>.*) tried to attack (?<object>.*) but (?<temp>.*) dodges\.";
    $IsCollectedItem = ".*\]+ (?<subject>.*) received: (?<item>.*).";
    $IsHealOtherCritItem = ".*\]+ (?<object>.*) is healed for (?<damage>.*) points of health \(critical\) by (?<subject>.*)\."
    $IsHealOtherItem = ".*\]+ (?<object>.*) is healed for (?<damage>.*) points of health by (?<subject>.*)\."
    $IsHealSelfItem = ".*\]+ (?<subject>.*) is healed for (?<damage>.*) points of health\."
    $IsHealSelfCritItem = ".*\]+ (?<subject>.*) is healed for (?<damage>.*) points of health \(critical\)\."
    $IsOutOfRange = ".*\]+ (?<subject>.*) used (?<verb>.*) but hit nothing!"
    $inputFileName = Split-Path $FilePath -leaf;
    function getTimeStamp ([string] $line) {
        return $line.Substring(1,$line.IndexOf("]")-1);
       
    }
    #Write CSV headings
    Write-Output "Timestamp,Subject,Object,Verb,Damage/Heal,IsCritical,IsParried,IsBlocked,IsGlancing,IsFizzled,ItemGained,ItemGainedQuantity,ItemLost,ItemLostQuantity";
    foreach($entry in Get-Content $FilePath) {
        Write-Verbose $entry
        if ($entry -match $IsDropItem) {
            Write-Verbose "***Above Drop Item Entry";
            $timeStamp = getTimeStamp $entry;
            $itemLost = ReplaceCommas $matches['item'];
            $itemLostQuantity = 1;
            #check if multiple items
            if ($itemLost -like "* x*") {
                $itemLostQuantity = $itemLost.SubString($itemLost.LastIndexOf("x")+1,$itemLost.Length - $itemLost.LastIndexOf("x")-1);
                $itemLost = $itemLost.SubString(0,$itemLost.LastIndexOf("x")-1);
            }
            $entryLine = "$timestamp,You,You,Dropped,0,0,0,0,0,0,,0,$itemLost,$itemLostQuantity";
            Write-Output $entryLine;
        } elseif ($entry -match $IsFizzle) {
            Write-Verbose "***Above Fizzle Item Entry";
            $timeStamp = getTimeStamp $entry;
            $verb = ReplaceCommas $matches['verb']
            $entryLine = "$timestamp,YOU,YOU,$verb,0,0,0,0,0,1,,0,,0";
            Write-Output $entryLine;
        } elseif ($entry -match $IsHarvestItem) {
            Write-Verbose "***Above Harvest Item Entry";
            $timeStamp = getTimeStamp $entry;
            $subject = ReplaceCommas $matches['subject'];
            $itemGained = ReplaceCommas $matches['item'];
            $itemGainedQuantity = 1;
            #check if multiple items
            if ($itemGained -like "* x*") {
                $itemGainedQuantity = $itemGained.SubString($itemGained.LastIndexOf("x")+1,$itemGained.Length - $itemGained.LastIndexOf("x")-1);
                $itemGained = $itemGained.SubString(0,$itemGained.LastIndexOf("x")-1);
            }
            $entryLine = "$timestamp,$subject,$subject,Harvested,0,0,0,0,0,0,$itemGained,$itemGainedQuantity,0,0";
            Write-Output $entryLine;
        } elseif ($entry -match $IsLootedItem) {
            Write-Verbose "***Above Looted Item Entry";
            $timeStamp = getTimeStamp $entry;
            $subject = ReplaceCommas $matches['subject'];
            $itemGained = ReplaceCommas $matches['item'];
            $itemGainedQuantity = 1;
            #check if multiple items
            if ($itemGained -like "* x*") {
                $itemGainedQuantity = $itemGained.SubString($itemGained.LastIndexOf("x")+1,$itemGained.Length - $itemGained.LastIndexOf("x")-1);
                $itemGained = $itemGained.SubString(0,$itemGained.LastIndexOf("x")-1);
            }
            $entryLine = "$timestamp,$subject,$subject,Looted,0,0,0,0,0,0,$itemGained,$itemGainedQuantity,0,0";
            Write-Output $entryLine;
           
        } elseif ($entry -match $IsLottedGoldITem) {
            Write-Verbose "***Above Looted Gold Entry";
            $subject = ReplaceCommas $matches['subject'];
            $goldGained = ReplaceCommas $matches['gold'];
            $entryLine = "$timestamp,$subject,$subject,Looted,0,0,0,0,0,0,Gold,$goldGained,0,0";
            Write-Output $entryLine;
        } elseif ($entry -match $IsLootedSharedGoldItem) {
            Write-Verbose "***Above Looted Item Entry";
            $timeStamp = getTimeStamp $entry;
            $goldGained = ReplaceCommas $matches['gold'];
            $entryLine = "$timestamp,Party,Party,Looted,0,0,0,0,0,0,Gold,$goldGained,0,0";
            Write-Output $entryLine;
        } elseif ($entry -match $IsDodgeItem) {
            Write-Verbose "***Above Dodge Item Entry";
            $timeStamp = getTimeStamp $entry;
            $subject = ReplaceCommas $matches['subject'];
            $object = ReplaceCommas $matches['object'];
            $entryLine = "$timestamp,$subject,$object,Dodges,0,0,0,0,0,0,,0,,0";
            Write-Output $entryLine;
        # Order of Attack check is significant, need to most specific to least specific to work
        } elseif ($entry -match $IsAttack) {
            Write-Verbose "***Above Attack Item Entry";
            $timeStamp = getTimeStamp $entry;
            $subject = ReplaceCommas $matches['subject'];
            $object = ReplaceCommas $matches['object'];
            $modifier = ReplaceCommas $matches['modifier'];
            $damage = ReplaceCommas $matches['damage'];
            $damageType = ReplaceCommas $matches['damageType'];
            $verb = ReplaceCommas $matches['verb'];
            $isCritical = 0;
            $IsGlancing = 0;
            if ($damageType -match "critical") {
                $isCritical = 1;
            }
            if ($damageType -match "glancing") {
                $IsGlancing = 1;
            }
            $isParried = 0;
            $isBlocked = 0;
            if ($modifier -match "parried") {
                $isParried = 1;
            }
            if ($modifier -match "blocked") {
                $isBlocked = 1;
            }
            $entryLine = "$timestamp,$subject,$object,$verb,$damage,$isCritical,$isParried,$isBlocked,$IsGlancing,0,,0,,0";
            Write-Output $entryLine;
        } elseif ($entry -match $IsAttackNPC) {
            Write-Verbose "***Above Attack Item Entry";
            $timeStamp = getTimeStamp $entry;
            $subject = ReplaceCommas $matches['subject'];
            $object = ReplaceCommas $matches['object'];
            $modifier = ReplaceCommas $matches['modifier'];
            $damage = ReplaceCommas $matches['damage'];
            $damageType = ReplaceCommas $matches['damageType'];
            $verb = ReplaceCommas $matches['verb'];
            $isCritical = 0;
            $IsGlancing = 0;
            if ($damageType -match "critical") {
                $isCritical = 1;
            }
            if ($damageType -match "glancing") {
                $IsGlancing = 1;
            }
            $isParried = 0;
            $isBlocked = 0;
            if ($modifier -match "parried") {
                $isParried = 1;
            }
            if ($modifier -match "blocked") {
                $isBlocked = 1;
            }
            $entryLine = "$timestamp,$subject,$object,$verb,$damage,$isCritical,$isParried,$isBlocked,$IsGlancing,0,,0,,0";
            Write-Output $entryLine;
        } elseif ($entry -match $IsSlainItem) {
            Write-Verbose "***Above Slain Item Entry";
            $timeStamp = getTimeStamp $entry;
            $subject = ReplaceCommas $matches['subject'];
            $object = ReplaceCommas $matches['object'];
            $verb = ReplaceCommas $matches['verb'];
            $entryLine = "$timestamp,$subject,$object,Slain,0,0,0,0,0,0,,0,,0";
            Write-Output $entryLine;
        } elseif ($entry -match $IsCollectedItem) {
            Write-Verbose "***Above You received Item Entry";
            $timeStamp = getTimeStamp $entry;
            $itemGained = ReplaceCommas $matches['item'];
            $subject = ReplaceCommas $matches['subject'];
            $entryLine = "$timestamp,$subject,$subject,Receive,0,0,0,0,0,0,$itemGained,1,0,0";
            Write-Output $entryLine;
            # Order of Heal check is significant, need to most specific to least specific to work
        } elseif ($entry -match $IsHealOtherCritItem) {
            Write-Verbose "***Above You heal other crit Item Entry";
            $timeStamp = getTimeStamp $entry;
            $damage = ReplaceCommas $matches['damage'];
            $subject = ReplaceCommas $matches['subject'];
            $object = ReplaceCommas $matches['object'];
            $entryLine = "$timestamp,$subject,$object,Heal,$damage,1,0,0,0,0,,0,,0";
            Write-Output $entryLine;
        } elseif ($entry -match $IsHealOtherItem) {
            Write-Verbose "***Above You heal other Item Entry";
            $timeStamp = getTimeStamp $entry;
            $damage = ReplaceCommas $matches['damage'];
            $subject = ReplaceCommas $matches['subject'];
            $object = ReplaceCommas $matches['object'];
            $entryLine = "$timestamp,$subject,$object,Heal,$damage,0,0,0,0,0,,0,,0";
            Write-Output $entryLine;
        } elseif ($entry -match $IsHealSelfCritItem) {
            Write-Verbose "***Above You crit heal Item Entry";
            $timeStamp = getTimeStamp $entry;
            $damage = ReplaceCommas $matches['damage'];
            $subject = ReplaceCommas $matches['subject'];
            $entryLine = "$timestamp,$subject,$subject,Heal,$damage,1,0,0,0,0,,0,,0";
            Write-Output $entryLine;
        } elseif ($entry -match $IsHealSelfItem) {
            Write-Verbose "***Above You heal Item Entry";
            $timeStamp = getTimeStamp $entry;
            $damage = ReplaceCommas $matches['damage'];
            $subject = ReplaceCommas $matches['subject'];
            $entryLine = "$timestamp,$subject,$subject,Heal,$damage,0,0,0,0,0,,0,,0";
            Write-Output $entryLine;
        } elseif ($entry -match $IsOutOfRange) {
            Write-Verbose "***Above You out of range Entry";
            $timeStamp = getTimeStamp $entry;
            $verb = ReplaceCommas $matches['verb'];
            $subject = $matches['subject'];
            $entryLine = "$timestamp,$subject,OutOfRange,$verb,0,0,0,0,0,0,,0,,0";
            Write-Output $entryLine;
        }
    }
    
    
    
     
    Last edited: Aug 20, 2018
  3. Tad Murakami

    Tad Murakami Avatar

    Messages:
    36
    Likes Received:
    116
    Trophy Points:
    20
    Gender:
    Male
    Location:
    Nested in Brittany Fort, Novia
    Hi Spungwa,
    This is good initiative on making script as i can easily connect up to Access DB.

    1 typo error found so far.
    $entryLine = "$timestamp,$subject,$subject,Harvested,0,0,0,0,0.0,$itemGained,$itemGainedQuantity,0,0";

    . is faulty so needs to be

    $entryLine = "$timestamp,$subject,$subject,Harvested,0,0,0,0,0,0,$itemGained,$itemGainedQuantity,0,0";
     
    Last edited: Mar 9, 2018
  4. Tad Murakami

    Tad Murakami Avatar

    Messages:
    36
    Likes Received:
    116
    Trophy Points:
    20
    Gender:
    Male
    Location:
    Nested in Brittany Fort, Novia
    some more findings.

    • For below 3 line I add "\] to rip off extra timestamp getting in as part of <subject> like other part.

    $IsCollectedItem = "(?<subject>.*) received: (?<item>.*).";
    $IsHealSelfCritItem = "(?<subject>.*) is healed for (?<damage>.*) points of health \(critical\)\."
    $IsOutOfRange = "(?<subject>.*) used (?<verb>.*) but hit nothing!"
    so i used below
    $IsCollectedItem = "\] (?<subject>.*) received: (?<item>.*).";

    $IsHealSelfCritItem = "\] (?<subject>.*) is healed for (?<damage>.*) points of health \(critical\)\."
    $IsOutOfRange = "\] (?<subject>.*) used (?<verb>.*) but hit nothing!"​

    with above all data seems to be correct.

    Below is more or less cosmetic changes I made for myself only. But sharing as reference.
    • Potion item like "Potion of Focus, Greater" actually include "," in name causing CSV to separate column. This affected bit as issue importing data into my Access DB.
    So for myself wherever
    $itemGained = $matches['item'];
    exists, i replaced with
    $itemGained = $matches['item'].Replace(', ', '-');​

    So name of potion is changed to "Potion of Focus-Greater" instead to avoid this.
    I know its not clean and there must be better way exists than this but for me this was good enough.​

    • for purely cosmetic reason I removed 0 from below to align with data model of "item gained" where it only contain text and blank if none.
    $entryLine = "$timestamp,$subject,$subject,Looted,0,0,0,0,0,0,$itemGained,$itemGainedQuantity,0,0";

    changed to below.
    $entryLine = "$timestamp,$subject,$subject,Looted,0,0,0,0,0,0,$itemGained,$itemGainedQuantity,,0";​

    Hope this helps!
     
    Last edited: Mar 9, 2018
  5. Spungwa

    Spungwa Avatar

    Messages:
    170
    Likes Received:
    476
    Trophy Points:
    18


    I would not do what you did above. I originally did this for the Verb one. If fails the whole script if $matches['item#] is null.

    More defensive to do it how i did it for the verb

    Code:
    $verb = $matches['verb']
           if ($verb -ne $null) {
               $verb = $verb.replace(",","");
           }
    
    But thanks, i never use potions, so had no example of a potion line.
     
    Tad Murakami likes this.
  6. Tad Murakami

    Tad Murakami Avatar

    Messages:
    36
    Likes Received:
    116
    Trophy Points:
    20
    Gender:
    Male
    Location:
    Nested in Brittany Fort, Novia
    cool! I took your one since its way better :) Thanks all good for me now.
     
  7. Spungwa

    Spungwa Avatar

    Messages:
    170
    Likes Received:
    476
    Trophy Points:
    18
    Ok, i actually updated the script with what you said about the RegEx, that is correct, copy and paste error on my part, every time the RegEx variable is first it needs that.

    I also went very defensive and put a function for changing commas to -. Then put everything through it.
     
    Tad Murakami likes this.
  8. Drake Aedus

    Drake Aedus Avatar

    Messages:
    465
    Likes Received:
    772
    Trophy Points:
    55
    Gender:
    Male
    Awesome work, I knew we'd eventually get a few combat parsers and this is a great prep tool for that.

    Thanks for sharing!
     
  9. Spungwa

    Spungwa Avatar

    Messages:
    170
    Likes Received:
    476
    Trophy Points:
    18
    Updated this (in the second post), not used it in a while, but it was noted that if you turn timestamps on in your chat then the chat log has the time twice, causing the subject to have a timestamp and the person.

    The new regular expression will look for any number of character then do a greedy look for ]. greedy meaning it will continue consuming ] until the last match.

    Spung