How many Reassigns have we done? A quick dive into the LRS Edit Log!

759
3
04-22-2021 01:18 PM
Labels (3)
SamuelColdiron
New Contributor III

Hello All,

We recently got asked how many times we used the Reassign tool and what were the RouteIDs that got reassigned. Well, it's been discussed on here before how to access the LRS Edit Log and use the Activity Types to pull the total number, but not really how to pull which RouteIDs got changed and what to. After some digging online, I figured out the RouteID information is stored in XML format in the 'EDITDATA' BLOB in that table. So we created the below Python Script to pull that information out and put it in a CSV file. 

I will preface by saying that we are on ArcMap and Enterprise 10.8.1, so this is in the ArcMap Roads & Highways Environment and using Python 2.x

from arcpy import da
import os
import io
import xml.etree.ElementTree as ET
import csv


# Path to the LRS Edit Log in the RH Database
editLog = r"[Your Database]/Lrs_Edit_Log"
# Path to the output csv
outputFile = r"[Your output file path and name].csv"

# Dictionary to convert database User IDs to real names
user_dict = {"[Your database user name]": "[Your name]",
             "[Your database user name]": "[Your name]"
             }

# Columns to pull from the LRS Edit Log
editLog_columns = ["TRANSACTIONID", "TRANSACTIONDATE", "USERNAME", "EDITDATA"]
# The query to pull Reassigns and a date range. Activity Type 6 = Reassign
editLog_query = "ACTIVITYTYPE = 6 AND (TRANSACTIONDATE >= TO_DATE('03/21/2020', 'MM/DD/YYYY') AND TRANSACTIONDATE <= TO_DATE('03/06/2021', 'MM/DD/YYYY'))"
# SQL Clause to sort by Transaction Date
editLog_clause = (None, "ORDER BY TRANSACTIONDATE")

# Open the output file to write to
with open(outputFile, 'wb') as file:
    # Create the file writer
    writer = csv.writer(file)
    # Fill in the column names
    writer.writerow(["TransactionID", "Transaction Date", "User Name", "Old RouteID", "New RouteID"])

    # Create the Search Cursor for the LRS Edit Log
    with da.SearchCursor(editLog, editLog_columns, editLog_query, sql_clause=editLog_clause) as cursor:
        for row in cursor:
            # Convert the USERNAME column to real names using the above created dictionary
            user_name = user_dict[row[2]]

            # Use the XML Element Tree Library to read the XML packed in the EDITDATA BLOB.
            # Use io.BytesIO to convert the BLOB to bytes that can then be read by ET
            root = ET.fromstring(io.BytesIO(row[3].tobytes()).getvalue())
            # Iterate over the child elements within the XML
            for child in root:
                # Pull out the RouteID child attribute. 
                routeid = child.attrib['RouteId']
                # Pull out the NewRouteID child attribute
                new_routeid = child.attrib['NewRouteId']

            # Write the data to the output csv. 
            # Convert the TransactionID from unicode to String and the Transaction Date from datetime to a String.
            writer.writerow([str(row[0]), row[1].strftime("%m/%d/%Y %H:%M:%S"), user_name, routeid, new_routeid])
            # Print the output to follow along at home.
            print(str(row[0]), row[1].strftime("%m/%d/%Y %H:%M:%S"), user_name, routeid, new_routeid)

# Any good script has a good ending!
print("\n**END**")

 

And it works great! For our example, it pulled ~3,100 records and completed in ~10 seconds. I wanted to share with everyone if you need to do this or as ground work to adapt this to any of the other Activity Types in the LRS Edit Log.

Thanks,

Sam Coldiron - Oklahoma DOT

3 Replies
RyanKoschatzky
Occasional Contributor III

Nice. The from is easy to find but the to route at least for us is never populated. Not a huge issues as I am looking at the time slices in milepoint at the same time but not great if doing a report. 👍

0 Kudos
SamuelColdiron
New Contributor III

Yeah the ToRoute is not populated in ours either, that's why we had to figure out how to dive into the EDITDATA BLOB.

RyanKoschatzky
Occasional Contributor III

In case you or others didn't know Phil Hardy showed me that looking at the LRS EditLog record, you can right click on a record and select Show XML Data to see the measure values and from/to routes ids.