Logging and change tracking for UniVerse files?

[Timothy Morris]

Hi all,

Does anyone know of any utilities or UniVerse functionality that can log/track changes in one or more UniVerse files for a limited period of time, and allow inspection of the changes?

An example use case is… say you have a program you don’t have the source code for that creates a customer in the CUSTOMER file and creates references in a few other files too e.g. ACCOUNT. Is there a way to inspect the changes the program made when it created a customer? I could use commonly available tools (e.g. a text editor or git) to compare the before and after versions of the files but I would like to use something that makes it easier to analyse changes in multivalued files.

I’ve had a look at Rocket’s transaction logging manual but my understanding is that is more for disaster recovery (rolling forward changes after restoring from a good state) rather than providing logging/change data for analysis.

Thanks,

Tim

[Richard Lewis]

Tim,

We would use file triggers for that type of logging on uniVerse.  You could then tie in your own audit routine to record exactly which fields were changed, and both the before and after versions.

Best Regards,

Richard Lewis

Senior Software Engineer II

Nu Skin Enterprises

--
You received this message because you are subscribed to
the "Pick and MultiValue Databases" group.
To post, email to: mvd...@googlegroups.com
To unsubscribe, email to: mvdbms+un...@googlegroups.com
For more options, visit http://groups.google.com/group/mvdbms
---
You received this message because you are subscribed to the Google Groups "Pick and MultiValue Databases" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mvdbms+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mvdbms/be57df18-35ef-47ba-abc7-85da4d2e921an%40googlegroups.com.

[Timothy Morris]

Hi Richard (and Bill Haskett who replied to me directly),

Wow, thank you both so much. This is EXACTLY what I needed! I’ll be able to create logging subroutines for use as triggers. This has been a great help.

Tim

To view this discussion on the web visit https://groups.google.com/d/msgid/mvdbms/CAC9MznKLebSNon_FWza%2BLRp5ZCaCJxNA4ZHfKSahmo6b%3DrBJVA%40mail.gmail.com.

[Joe G]

Triggers will work if you know what files the program is opening. If you don't have the source code you can examine the object code directly.  I have a program that opens these files:

      OPEN 'VOC' TO FV.VOC ELSE STOP 201,'VOC'
      OPEN 'BPCUST' TO FV.GUI.PROJECT.FILE ELSE STOP 201,'BPCUST'
      OPEN 'MENU.DFLTS' TO FV.MENU.DFLTS ELSE STOP 201,'MENU.DFLTS'
      OPEN 'CM' TO FV.CM ELSE STOP 201,'CM'
      OPEN 'EDI.CM' TO FV.EDI.CM ELSE STOP 201,'EDI.CM'
      OPEN 'OW' TO FV.OW ELSE STOP 201,'OW'
      OPEN 'ROW' TO FV.ROW ELSE STOP 201,'ROW'
      OPEN 'HOW' TO FV.HOW ELSE STOP 201,'HOW'
      OPEN 'SLSDIR' TO FV.SLSDIR ELSE STOP 201,'SLSDIR'

To make sure I didn't affect the object code directly, I used FT to transfer the object code to my temp folder. I opened it with Notepad++ to take a look.

When I examined the code I found this string

VOCBPCUSTMENU.DFLTSCMEDI.CMOWROWHOWSLSDIR

You can see that all the string literals all got put together. I assume when the program references them it uses a pointer into this string. I also found a line that had all my variables listed. Those were stored as null terminated strings instead of one big string like the file names.

Depending on how the program was compiled the file variables may not be there but any file name literals probably will. It would need those names to fine the files and open them.

Joe G.

[Jay LaBonte]

You could try audit logging or you could add a trigger that writes to a log file, each change with before and after data as well as date, time and user ID.

Jay

Sent from my iPhone

On Jul 28, 2023, at 9:03 PM, Joe Goldthwaite <j...@goldthwaites.com> wrote:



The trigger will work if you know what files are being changed. If you don't have the source code but want a clue as to what files it might be opening you can examine the object code. This is easy to do with a Windows or Linux hex editor.  Here's an example.

I have a program that opens these files

------- Original Message -------

To view this discussion on the web visit https://groups.google.com/d/msgid/mvdbms/CAKoH45BL6Fwp%2Bq6OJRjY4DaTctuj-ovA4fcBT7Be%2B6G4iwNxRg%40mail.gmail.com.

--
You received this message because you are subscribed to
the "Pick and MultiValue Databases" group.
To post, email to: mvd...@googlegroups.com
To unsubscribe, email to: mvdbms+un...@googlegroups.com
For more options, visit http://groups.google.com/group/mvdbms
---
You received this message because you are subscribed to the Google Groups "Pick and MultiValue Databases" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mvdbms+un...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/mvdbms/l2PnDt1805mk5VFiAnk9ZiA6QlP3VwRBcsnKcdVpL10Gu0ygtvRnKjplrkmNZGAdkWJwP2P6es6lEjnQ-do9NrCyiuiPM5nZrE9omg3wP94%3D%40goldthwaites.com.

[mgpe...@gmail.com]

We are migrating data from Universe to SQL and also need to know changes for each record.  We are using an index that writes the file / record id / date / time .. and any other information we need to a file we call Audit.Records.   It may be very similar to audit logging  

 

The ID of the records in this file are sequential numbers, but the records contain any data we wish;

 

Here is the globally catalog routine which is called by the index:

 

*Subroutine to demonstrate the use of using index subroutines to

*provide auditing of database updates.

*

*@idx.iotype =  0 - Not being used from an index;

*                 1 - INSERT (new) record;

*                 2 - DELETE record;

*                 3 - Derive old index value;

*                 4 - Derive new index value;

 

      subroutine AuditSubr(returnValue, filename)

     

INCLUDE DMSKELCODE COMMON

 

      common /auditing/ fileAuditTrail, flagOpen, dictAuditTrail  ;*, lastEntry, lastTimeStamp

 

      returnValue = ""

      IOTYPE = 0

 

      if not(flagOpen) then

         open "AUDIT.TRAIL" to fileAuditTrail else

            return                       ; * to caller.

         end

         open "DICT","AUDIT.TRAIL" to dictAuditTrail else

            return                       ; * to caller.

         end

         flagOpen = 1

      end

 

      if @IDX.IOTYPE then

        IOTYPE = TRIM("CD U"[@IDX.IOTYPE,1])

      end

     

      if IOTYPE and @ID then

*         if lastEntry = filename:"*":@ID then

*            if time()-lastTimeStamp < 100 then return

*         end

         readu dictrecAuditTrail from dictAuditTrail, "&NEXT.AVAILABLE&" else

            dictrecAuditTrail = "X"

            dictrecAuditTrail<2> = 1

         end

         idAuditTrail = dictrecAuditTrail<2>

         dictrecAuditTrail<2> += 1

         write dictrecAuditTrail on dictAuditTrail, "&NEXT.AVAILABLE&"

         release

        

         recAuditTrail = ""

         recAuditTrail<1> = @logname

         recAuditTrail<2> = filename

         recAuditTrail<3> = @ID

         recAuditTrail<4> = IOTYPE

         recAuditTrail<5> = date()

         recAuditTrail<6> = time()

         recAuditTrail<7> = LOWER(PROC.NAME)

         write recAuditTrail on fileAuditTrail, idAuditTrail

         lastEntry = filename:"*":@ID

         lastTimeStamp = recAuditTrail<6>

      end

      return

*--------------------------------------------------------------------

   End

 

And we create an index using this definition:

 

LIST.ITEM DICT DATA.OI AUDIT.RECORDS

DICT DATA.OI    07:05:14pm  30 Jul 2023  Page    1

 

 

    AUDIT.RECORDS

001 I

002 SUBR("AuditSubr","DATA.OI")

005 10L

006 S

To view this discussion on the web visit https://groups.google.com/d/msgid/mvdbms/586AF339-866A-4C0A-B41B-D78B346C2FE8%40paradigm-systems.us.

[Timothy Morris]

Thanks for the info everyone!

In a subroutine used for a file trigger, does anyone know how to get the name of what caused the INSERT, UPDATE, or DELETE e.g. ED if it was the editor or the name of the subroutine or program that made the change?

This will be the most important information to log so we can better understand the environment we inherited from previous staff members.

I'm reading these resources but haven't figured it out yet.

Auditing Database Changes with UniVerse Indexing Subroutines (intl-spectrum.com)

Rush Flat Consulting - UV Triggers

https://docs.rocketsoftware.com/bundle/UniVerse_SystemDescription_V1134/resource/UniVerse_SystemDescription_V1134.pdf (chapter 7 - File Triggers)

Thanks,

Tim

[Javier Polidura]

You can use SYSTEM(9001), it returns the full call stack (UniVerse_BASICCommandsRefGuide)

To view this discussion on the web visit https://groups.google.com/d/msgid/mvdbms/ad6b87ce-e0e1-4d07-afac-d96b1682ee7fn%40googlegroups.com.

[Timothy Morris]

Hi Javier,

Yep, I think that does it! I can see the calling program in the call stack with SYSTEM(9001).

Thank you very much! 🙏

Tim

[Kevin King]

If you can, I would recommend storing the whole call stack.  Never know when that little bit of extra information could be beneficial!

To view this discussion on the web visit https://groups.google.com/d/msgid/mvdbms/540946d8-dd33-45e5-a85d-e58a02402ba6n%40googlegroups.com.