Expand all | Collapse all

Querying Entity History

  • 1.  Querying Entity History

    Posted 11-11-2019 20:19
    Hey all,

    Does anyone on here the Query the Entity history table/s? Just would like to get some feedback from the general community about your experience, good or bad?


    Carl Hanebuth
    City of Sydney Council

  • 2.  RE: Querying Entity History

    Posted 11-12-2019 00:50
    Hi Carl

    If I'm not mistaken, I believe the City of Salisbury have used Entity History tables in certain reports.  I'm not 100% certain if this is an on-going or ad hoc process, but it might be worth checking with them.

    Andrew McSeveney
    Principal Consultant
    Pitney Bowes

  • 3.  RE: Querying Entity History

    Posted 11-12-2019 05:19
    Hi Carl,

    We use the entity history to mostly check the defects, looking for where the priorities have been changed and also to monitor where the features have been changed - these feed through onto a dashboard. It is very basic and I think was fiddly to set up but does what we need to monitor changes.

    Thanks, Laura

    Confirm Support Ringway Jacobs
    Senior Technician
    Ringway Jacobs Ltd

  • 4.  RE: Querying Entity History

    Posted 11-13-2019 00:28
    Hi Carl,

    I use it to monitor user changes to assets and the most difficulty was catering to inconsistencies in entity keys with site code and plot numbers in the joins.


    Jason Hewett
    Business Systems Administrator
    City of Sydney Council

  • 5.  RE: Querying Entity History

    Posted 11-13-2019 04:14

    ​We use this function for a few reports if we just want to extract jobs or features that have changed recently.

    It can take a couple of exploratory queries to work out what format the entity key is to make your first join but we havent noticed any issues when using it.


    Andrew Stacey
    Ringway Specialist Services

  • 6.  RE: Querying Entity History

    Posted 11-13-2019 23:39
    Edited by Roger Ling 12-15-2019 16:39
    We regularly use the entity history tables, mainly as an auditing exercise and to check who, what and when made changes to assets.

    Having the primary key as an amalgam of several fields, becomes an issue when trying to parse the key or join it to other tables.

    We have also discovered the entity history table creates a number of unnecessary additional entries for a single login session, which adds to the complexity of the data.

    Roger Ling
    Senior GIS Officer
    Department of Primary Industries, Parks, Water and Environment

  • 7.  RE: Querying Entity History

    Posted 12-14-2019 20:30
    We also query the entity history table, as previously mentioned it can be difficult with the key as it stores the site code, plot number and additional info such as codes.

    From memory it also drops any zeros in the decimals for plot numbers (eg ".00")  so joining to the feature table needed some additional sql.
    Would be nice if feature key was stored in table to make it simpler.

    Mostly use it for checking where users are altering features such as changing values, making assets dead, creating new assets etc.

    James Corletto
    Team Leader Strategic Asset Management
    City of Salisbury