TMF audit for enscribe file 'record'

[Shiva]

I have a FORMAT 1, FILECODE 0, ENSCRIBE TYPE K file which is pretty huge. The file is audited. One of the records in the file interests me. I'd like to know the history about that particular record. Say, since the moment it was entered into the file, to the last moment when it was altered - I want to know what alteration was done, what was the value before alteration and who did the alteration and so on. You get my point. Can I get that information through TMF? Or do I need to install and other 3rd party utilities for that?

I would not be able to install any 3rd party utilities, I hope the system would have enough information to help me?

Any thoughts are appreciated. :) Thanks in advance.

[wbreidbach]

Post by Shiva
I have a FORMAT 1, FILECODE 0, ENSCRIBE TYPE K file which is pretty huge. The file is audited. One of the records in the file interests me. I'd like to know the history about that particular record. Say, since the moment it was entered into the file, to the last moment when it was altered - I want to know what alteration was done, what was the value before alteration and who did the alteration and so on. You get my point. Can I get that information through TMF? Or do I need to install and other 3rd party utilities for that?
I would not be able to install any 3rd party utilities, I hope the system would have enough information to help me?
Any thoughts are appreciated. :) Thanks in advance.

The information you are using can be found in the audittrails. Unfortunately the chances to search the audittrail without any 3rd party tools are really poor, the only tool available for that is SNOOP and searching the audittrail with SNOOP is very expensive, so I recommend to start SNOOP with a very low priority. The command within SNOOP is READAUDIT, you will be prompted for all necessary information. But I do not expect that to fullfil you needs.
In addition the only information that can be obtained from the audittrail is the change in data and the transaction ID. You will not find the user who did it.
From my experience you need to write a history or a logging within the application.

[Keith Dick]

Post by wbreidbach

Post by Shiva
I have a FORMAT 1, FILECODE 0, ENSCRIBE TYPE K file which is pretty huge. The file is audited. One of the records in the file interests me. I'd like to know the history about that particular record. Say, since the moment it was entered into the file, to the last moment when it was altered - I want to know what alteration was done, what was the value before alteration and who did the alteration and so on. You get my point. Can I get that information through TMF? Or do I need to install and other 3rd party utilities for that?
I would not be able to install any 3rd party utilities, I hope the system would have enough information to help me?
Any thoughts are appreciated. :) Thanks in advance.

The information you are using can be found in the audittrails. Unfortunately the chances to search the audittrail without any 3rd party tools are really poor, the only tool available for that is SNOOP and searching the audittrail with SNOOP is very expensive, so I recommend to start SNOOP with a very low priority. The command within SNOOP is READAUDIT, you will be prompted for all necessary information. But I do not expect that to fullfil you needs.
In addition the only information that can be obtained from the audittrail is the change in data and the transaction ID. You will not find the user who did it.
From my experience you need to write a history or a logging within the application.

I don't know anything about SNOOP, so I don't know why it puts such a load on the system to use it to search the audit trail, but I imagine Wolfgang is correct about that. He has used it and I have not.

There is a set of library functions that can be used to write a program that reads through the audit trail. It is possible that it would be as must of a load on the system as SNOOP to use, but if you are motivated enough, you could try writing a program using that library in the hope that it is not as bad as SNOOP in that regard. The procedures are documented in section 5 of the TMF Application Programmer's Guide. As long as the file in question is not using audit compression, interpreting the change records for the file is not very difficult. You should be able to check the key field and pick out the records you want to seek. Wolfgang is correct that the userid is not part of the audit trail. The timestamp, transaction ID, filename, and operation type (insert, update, delete) are the only information that is available besides the contents of the record. For update, you get both the before image and the after image of the record.

You can only go back in time as far as TMF still has the audit trail files. I believe the documentation of the library says that if TMF's catalog still has a record of what tape an audit trail file was dumped to, it will automatically restore the file temporarily so your program will be able to read it. If you only want to search the audit trail files that still are online, you need to make sure you don't request positioning to a file of the audit trail that has been removed.

[Shiva]

Thanks Keith, I'd look into that programmer's guide. And btw what is audit compression? I heard that at my workplace, audit trail is erased after 3 days. That's a really bad news. I don't know why they do that. But still I'd like to try this out as I had sought this in the first place - in hope of some TMF exercise.

And Wolfgang, how do I use SNOOP? Fortunately, my workplace has SNOOP for some reason. But I've heard that systems can be crashed by using SNOOP and I don't fall victim to such situations, so can you tell me how do I use SNOOP to check the audit trails of a file. Assume that I have all details necessary. Thanks in advance.

[wbreidbach]

Post by Shiva
Thanks Keith, I'd look into that programmer's guide. And btw what is audit compression? I heard that at my workplace, audit trail is erased after 3 days. That's a really bad news. I don't know why they do that. But still I'd like to try this out as I had sought this in the first place - in hope of some TMF exercise.
And Wolfgang, how do I use SNOOP? Fortunately, my workplace has SNOOP for some reason. But I've heard that systems can be crashed by using SNOOP and I don't fall victim to such situations, so can you tell me how do I use SNOOP to check the audit trails of a file. Assume that I have all details necessary. Thanks in advance.

Yes, depending on what you are doing SNOOP can be really dangerous!
At first you have to find the location of the audittrails (on our development system it is $SYSTEM.ZTMFAT.AAnnnnnn, nnnnnn is the sequence number) and afterwards you should do the following:

SNOOP /PRI 5/
0,1085#READAUDIT
Audit trail name or 'MERGE' (no default):$system.ztmfat.aa002612

Audit trail name : $system.ztmfat.aa
Audit trail direction : forward
Relative byte offset : 0
Allow audit restores : no
Display octal/hex dumps : no
Detailed display : no

Do you want to use all the defaults (no):

Audit trail name or 'MERGE' ($system.ztmfat.aa):
Low sequence number (002612):
High sequence number (002612):
Audit trail direction (forward):
Relative byte offset (0):
Octal/Hex dumps displayed (no):
Detailed display (no):
Enter the selection expression for audit records:


Have a look at what you get and try to change the parameters set to no by default to yes. I do not think that you will be really happy with the result.

[Tone]

Post by wbreidbach

Post by Shiva
Thanks Keith, I'd look into that programmer's guide. And btw what is audit compression? I heard that at my workplace, audit trail is erased after 3 days. That's a really bad news. I don't know why they do that. But still I'd like to try this out as I had sought this in the first place - in hope of some TMF exercise.
And Wolfgang, how do I use SNOOP? Fortunately, my workplace has SNOOP for some reason. But I've heard that systems can be crashed by using SNOOP and I don't fall victim to such situations, so can you tell me how do I use SNOOP to check the audit trails of a file. Assume that I have all details necessary. Thanks in advance.

Yes, depending on what you are doing SNOOP can be really dangerous!
SNOOP /PRI 5/
0,1085#READAUDIT
Audit trail name or 'MERGE' (no default):$system.ztmfat.aa002612
Audit trail name : $system.ztmfat.aa
Audit trail direction : forward
Relative byte offset : 0
Allow audit restores : no
Display octal/hex dumps : no
Detailed display : no
Have a look at what you get and try to change the parameters set to no by default to yes. I do not think that you will be really happy with the result.

There should be a $SYSTEM.SYSnn.SNOOPDOC file that documents SNOOP.

Finding activity against a certain file is not difficult. Finding
activity against a certain record in the file could be quite difficult
if there is a lot of activity on the file.

You also may find that the audittrails are secured so that normal users
cannot read them.

Finding all activity on a record since its addition to the file
will be difficult unless your audittrails go back to a time before the
insert occurred. TMF's primary purposes are file integrity and recovery
not maintaining a history of activity on a file since its creation.

[Keith Dick]

Post by Shiva
Thanks Keith, I'd look into that programmer's guide. And btw what is audit compression? I heard that at my workplace, audit trail is erased after 3 days. That's a really bad news. I don't know why they do that. But still I'd like to try this out as I had sought this in the first place - in hope of some TMF exercise.
And Wolfgang, how do I use SNOOP? Fortunately, my workplace has SNOOP for some reason. But I've heard that systems can be crashed by using SNOOP and I don't fall victim to such situations, so can you tell me how do I use SNOOP to check the audit trails of a file. Assume that I have all details necessary. Thanks in advance.

It could be true that TMF is configured not to dump the old audit trail files to tape before purging them, but don't assume nothing older than three days is available until you check to see whether the audit trails are dumped to tape.

You can check by entering the following command at a TACL prompt:

TMFCOM INFO AUDITDUMP

[wbreidbach]

Post by Keith Dick

Post by Shiva
Thanks Keith, I'd look into that programmer's guide. And btw what is audit compression? I heard that at my workplace, audit trail is erased after 3 days. That's a really bad news. I don't know why they do that. But still I'd like to try this out as I had sought this in the first place - in hope of some TMF exercise.
And Wolfgang, how do I use SNOOP? Fortunately, my workplace has SNOOP for some reason. But I've heard that systems can be crashed by using SNOOP and I don't fall victim to such situations, so can you tell me how do I use SNOOP to check the audit trails of a file. Assume that I have all details necessary. Thanks in advance.

It could be true that TMF is configured not to dump the old audit trail files to tape before purging them, but don't assume nothing older than three days is available until you check to see whether the audit trails are dumped to tape.
TMFCOM INFO AUDITDUMP

As soon as an audittrail has been dumped, TMF might "purge" it, in fact TMF does purgedata and rename. It seems that in this case TMF is configured to take Onlinedumps daily and keep the last 3 of them.
But as I wrote earlier I do not think that TMF is the tool for creating history.

[Keith Dick]

Post by Shiva
Thanks Keith, I'd look into that programmer's guide. And btw what is audit compression? I heard that at my workplace, audit trail is erased after 3 days. That's a really bad news. I don't know why they do that. But still I'd like to try this out as I had sought this in the first place - in hope of some TMF exercise.
And Wolfgang, how do I use SNOOP? Fortunately, my workplace has SNOOP for some reason. But I've heard that systems can be crashed by using SNOOP and I don't fall victim to such situations, so can you tell me how do I use SNOOP to check the audit trails of a file. Assume that I have all details necessary. Thanks in advance.

Oh, I forgot to answer the question about audit compression. Audit compression can be used to reduce the amount of data put into the audit trail. Basically, it omits logging the parts of the record that were not changed in an update. It does not affect logging of inserts or deletes. It makes interpreting the change records a little more difficult. Not impossible; just more difficult. If audit compression is on for a file or table, you cannot determine the value of the whole record after an update, unless the program reading the audit trail keeps track of the values of the whole record as it reads through the audit trail.

The default setting for audit compression depends on the file. I think the default for Enscribe and SQL/MP is audit compression off while the default for SQL/MX is audit compression on, but I might be remember the defaults incorrectly. That will be documented with the commands for creating the different kinds of files.

[Shiva]

Wolfgang,

Unfortunately the chances to search the audittrail without any 3rd party tools are really poor - Would you know of a way to do it without the 3rd party tools? You've told me that it is hard. But is it impossible? I want to give it a try. I don't think the management would like me to try SNOOP and I really don't want to get in a mess as well unless someone is present to guide me through it. Too many people have advised me against it that the fear of breaking down the system has gotten into me. It is a production system and I'd be damned for all I know if I do something unworthy.

So I'd have to find a way to do it without the third parties, how so ever hard it may be.

And the more I hear about TMF the more I'm inclined to believe your opinion - 'But as I wrote earlier I do not think that TMF is the tool for creating history.' But you did say that we can find the information I'm looking for - in audit trails.

Tone,

What's a QCB number? DISCOPEN?

'but there needs to be a measurement running at the time of the update' - What do you mean by that? That HP GNSC document, I could not find it. Do you have that link? Or a copy of the document would suffice too. Thanks already for the explanations. The information I seek is too valuable to neglect, so no matter how difficult it gets, I'd have to give it a try. Any help is appreciated. I hope I have enough audit trails to help me find it. And yes, there's a lot of activity going around that file.


Keith,

Timestamp, transaction ID, filename, and operation type - These are good enough information for me. It will always be only one user ID. At least the transaction ID and timestamp should be some useful information for me. And I will start working on that Section 5 for TMF programmer's guide today - hoping that it would work.

TMFCOM INFO AUDITDUMP? What am I looking for? I could not find any information that would say for how long the audit trails are saved.

And thanks for that information about audit compression. The file does not have it on and it won't matter anyway as you have mentioned that the updates will be there for sure. Let me get started.

I'll update you lot when I get something going. Thanks a lot for all your input. Much appreciated. :)

[Keith Dick]

This post might be inappropriate. Click to display it.

[Shiva]

Post by Keith Dick
The TMFCOM INFO AUDITDUMP command will show whether TMF is configured to dump audittrail files to tape or not. All you need to learn from it is whether audit trail dumping is enabled or not. If it says that no audittrail dumps are configured, or that audittrail dumps are configured but disabled, then your audittrail files are not being dumped to tape before being purged.

Hmm, it did provide me enough information about the audit dump. And yes, audit dump is enabled. So I'm good to go! :)

Post by Keith Dick
If the audittrail files are not dumped to tape, then the information available in the audittrails will go back only as far as the oldest audittrail file that is still online. If the audittrail files are dumped to tape, then a program such as SNOOP or one that uses the TMF audit trail reading library can ask TMF to restore older audittrail files from those tapes when you want to look at information older than what is in the earliest audittrail files that still are online. The tapes are retained at least as far back as the most recent online dump, possibly longer.

I don't know how to find if the files are dumped to tape. I have three files in the audit volume. So I guess that files are produced per day?

Post by Keith Dick
An OCB is an open control block and DISCOPEN is a name used in Measure commands to configure recording of information about disk file opens. Tone is mentioning that there is an indirect way to learn the userid responsible for a change, even though the userid is not recorded by TMF. Measure is the NonStop tool for collecting data from a running system that lets you analyze the performance of the system, to help you find where bottlenecks are. A "measurement" is happening when you have asked Measure to gather and record various bits of information. Some sites run measurements continuously so that if a slowdown occurs, they will have data to examine to figure out what caused the slowdown. Some sites start measurements only when they recognize that a performance problem is happening.

Hmm, makes sense. I'll check with my admin team if that's enabled! Good one! Let's see if that brings out anything.

And btw, I did study a few pages from Section 5 of TMF programmer's guide. Without any examples it seems a bit hard to grasp at first sight. But as I kept pushing I was able to get some knowledge out of it, and as far as I see I can use TAL, C, COBOL as languages that I can program this from? Occasionally I see TACL, but I'm not sure if that can be coded in TACL?

And anyways, do any of you have any such sample programs like what I'm trying to write? It would be a great help if you did and if you are able to share it with me. Because the TMF programmer's guide does not have a single example though it has all the explanation for procedure calls.

It'd give me a good place to start writing my code if I get to see some example. Any language is fine. Thanks for all your help already :)

[Tone]

Post by Shiva

Post by Keith Dick
The TMFCOM INFO AUDITDUMP command will show whether TMF is configured to dump audittrail files to tape or not. All you need to learn from it is whether audit trail dumping is enabled or not. If it says that no audittrail dumps are configured, or that audittrail dumps are configured but disabled, then your audittrail files are not being dumped to tape before being purged.

Hmm, it did provide me enough information about the audit dump. And yes, audit dump is enabled. So I'm good to go! :)

Post by Keith Dick
If the audittrail files are not dumped to tape, then the information available in the audittrails will go back only as far as the oldest audittrail file that is still online. If the audittrail files are dumped to tape, then a program such as SNOOP or one that uses the TMF audit trail reading library can ask TMF to restore older audittrail files from those tapes when you want to look at information older than what is in the earliest audittrail files that still are online. The tapes are retained at least as far back as the most recent online dump, possibly longer.

I don't know how to find if the files are dumped to tape. I have three files in the audit volume. So I guess that files are produced per day?

Post by Keith Dick
An OCB is an open control block and DISCOPEN is a name used in Measure commands to configure recording of information about disk file opens. Tone is mentioning that there is an indirect way to learn the userid responsible for a change, even though the userid is not recorded by TMF. Measure is the NonStop tool for collecting data from a running system that lets you analyze the performance of the system, to help you find where bottlenecks are. A "measurement" is happening when you have asked Measure to gather and record various bits of information. Some sites run measurements continuously so that if a slowdown occurs, they will have data to examine to figure out what caused the slowdown. Some sites start measurements only when they recognize that a performance problem is happening.

Hmm, makes sense. I'll check with my admin team if that's enabled! Good one! Let's see if that brings out anything.
And btw, I did study a few pages from Section 5 of TMF programmer's guide. Without any examples it seems a bit hard to grasp at first sight. But as I kept pushing I was able to get some knowledge out of it, and as far as I see I can use TAL, C, COBOL as languages that I can program this from? Occasionally I see TACL, but I'm not sure if that can be coded in TACL?
And anyways, do any of you have any such sample programs like what I'm trying to write? It would be a great help if you did and if you are able to share it with me. Because the TMF programmer's guide does not have a single example though it has all the explanation for procedure calls.
It'd give me a good place to start writing my code if I get to see some example. Any language is fine. Thanks for all your help already :)

I suggest doing a TMFCOM INFO DUMPS $VOL.SUBVOL.FILE for the file you
are interested in. This will show how far back your audit goes for that
file.

As for examples of audit reading programs check out TMFSTAT and TMFREAD
at :

http://193.65.99.19/kku/tools.html

[Shiva]

Hi Tone,

I did search through the HPKB through a friend (I don't have a logon) and was able to find the document through search. But it was not viewable (?) Let me know if you can send the document, else I'll try again with some other friend whether they are able to access the document. It says that only some users can access the document.

Anyways, I will do the INFO DUMP. I did try to find, but I didn't know this. And ah, the blog you point me to ... I've read through the TACL utilities present there long ago, but I never knew that this was there because back then I wasn't as aware of TMF anyway! And btw, it was you? It is your blog? It is a great source of knowledge, Tone. A wonderful thing that's built, of the very little that I see of Tandem online, that holds a fair share.

I will look into the source, thank you. I'll come back if I have any questions. Thanks a lot. :)

[Tone]

Post by Shiva
Hi Tone,
I did search through the HPKB through a friend (I don't have a logon) and was able to find the document through search. But it was not viewable (?) Let me know if you can send the document, else I'll try again with some other friend whether they are able to access the document. It says that only some users can access the document.
Anyways, I will do the INFO DUMP. I did try to find, but I didn't know this. And ah, the blog you point me to ... I've read through the TACL utilities present there long ago, but I never knew that this was there because back then I wasn't as aware of TMF anyway! And btw, it was you? It is your blog? It is a great source of knowledge, Tone. A wonderful thing that's built, of the very little that I see of Tandem online, that holds a fair share.
I will look into the source, thank you. I'll come back if I have any questions. Thanks a lot. :)

Not sure why you were unable to view it. I have sent a copy to
your email.

No, this is not my site.

[Renán Mera]

Post by Tone

Post by Shiva
Hi Tone,
I did search through the HPKB through a friend (I don't have a logon) and was able to find the document through search. But it was not viewable (?) Let me know if you can send the document, else I'll try again with some other friend whether they are able to access the document. It says that only some users can access the document.
Anyways, I will do the INFO DUMP. I did try to find, but I didn't know this. And ah, the blog you point me to ... I've read through the TACL utilities present there long ago, but I never knew that this was there because back then I wasn't as aware of TMF anyway! And btw, it was you? It is your blog? It is a great source of knowledge, Tone. A wonderful thing that's built, of the very little that I see of Tandem online, that holds a fair share.
I will look into the source, thank you. I'll come back if I have any questions. Thanks a lot. :)

Not sure why you were unable to view it. I have sent a copy to
your email.
No, this is not my site.

Hi Tone,

I´ve been reading your chats in here.. and I am trying something with TMF like the procedures described here. I was wondering if maybe you still have the chance to send me the program/code related to Audit Reading that you also shared with Shiva.

Thanks so much in advance. Greetings!

[Shiva]

And btw, how do I UNPAK on MAC? The source on the same site for UNPAK as java utility worked for me before but now gives me an error in opening the jar file. I use Mac OS X Mavericks. I'll try with W8 and update if I still face an issue. It used to work for me before. Now I am unable to even open the older version. May be it is my OS.

[Tone]

Post by Shiva
And btw, how do I UNPAK on MAC? The source on the same site for UNPAK as java utility worked for me before but now gives me an error in opening the jar file. I use Mac OS X Mavericks. I'll try with W8 and update if I still face an issue. It used to work for me before. Now I am unable to even open the older version. May be it is my OS.

Sorry, can't help with that. I use a NonStop to UNPAK things.

[Keith Dick]

Post by Shiva

Post by Keith Dick
The TMFCOM INFO AUDITDUMP command will show whether TMF is configured to dump audittrail files to tape or not. All you need to learn from it is whether audit trail dumping is enabled or not. If it says that no audittrail dumps are configured, or that audittrail dumps are configured but disabled, then your audittrail files are not being dumped to tape before being purged.

Hmm, it did provide me enough information about the audit dump. And yes, audit dump is enabled. So I'm good to go! :)

Post by Keith Dick
If the audittrail files are not dumped to tape, then the information available in the audittrails will go back only as far as the oldest audittrail file that is still online. If the audittrail files are dumped to tape, then a program such as SNOOP or one that uses the TMF audit trail reading library can ask TMF to restore older audittrail files from those tapes when you want to look at information older than what is in the earliest audittrail files that still are online. The tapes are retained at least as far back as the most recent online dump, possibly longer.

I don't know how to find if the files are dumped to tape. I have three files in the audit volume. So I guess that files are produced per day?

If the files are produced once per day, I think it would be a coincidence, unless your site has set up a daily job that runs a TMFCOM NEXT AUDITTRAIL command. The way it normally works is that the TMF configuration defines the maximum size for those individual files in an audittrail, and whenever one of the files becomes full storing the records that describe the changes made to audited files, TMF switches to the next file in the series. If I remember correctly, it actually creates the next file some time in advance of when it needs the file, so switching to it happens essentially instantly.

How quickly an individual file in the audit trail fills up depends on its configured size, the average transaction rate, and the average size of the changes in a transaction. TMF might switch to a new audit trail several times a day, or it might go several days between times it needs to switch to the next file.

If you want to see how many other audittrail files are available on tape (or on a disk backup area, I forgot to mention that audittrail dumps can be sent to another disk), I believe a command like this will do it:

TMFCOM INFO DUMPS, BRIEF, STATUS ASSIGNED, TYPE AUDITDUMP

I have not done much about managing TMF, so I'm not very familiar with the commands. I'm just going by the description in the manual. I believe the output from this command will show in the first column the names of the audittrail files that have accessible backups, and in the third column the dates the dumps were done. You would only need this information to check how far back in time audittrail files are still available. If you make a request for an old audittrail file through the audit trail reading library, TMF will figure out how to retrieve that file and start reading it.

I see Tone also answered with a different command. He probably is more familiar with this aspect of TMF than I am, so his answer might be better.

You do not have to be logged on as a privileged user in order to use this command to see what audittrail files are still known to TMF.

Post by Shiva

Post by Keith Dick
An OCB is an open control block and DISCOPEN is a name used in Measure commands to configure recording of information about disk file opens. Tone is mentioning that there is an indirect way to learn the userid responsible for a change, even though the userid is not recorded by TMF. Measure is the NonStop tool for collecting data from a running system that lets you analyze the performance of the system, to help you find where bottlenecks are. A "measurement" is happening when you have asked Measure to gather and record various bits of information. Some sites run measurements continuously so that if a slowdown occurs, they will have data to examine to figure out what caused the slowdown. Some sites start measurements only when they recognize that a performance problem is happening.

Hmm, makes sense. I'll check with my admin team if that's enabled! Good one! Let's see if that brings out anything.
And btw, I did study a few pages from Section 5 of TMF programmer's guide. Without any examples it seems a bit hard to grasp at first sight. But as I kept pushing I was able to get some knowledge out of it, and as far as I see I can use TAL, C, COBOL as languages that I can program this from? Occasionally I see TACL, but I'm not sure if that can be coded in TACL?

You cannot use the TMF audit trail reading library from TACL. I would recommend using it from C or TAL, or rather CCOMP or EPTAL -- I believe it cannot be used from non-native code.

Something I think I did not mention before is that a program that uses the TMF audit trail reading library must be licensed and must be run by SUPER.SUPER. That might make it difficult for you to experiment with it. The reason for that restriction is that the audit trail contains the contents of data records from any audited file that has been changed, regardless of the file security settings on those files, so if use of the TMF audit trail reading library were available to any user, that could reveal data that user should not be able to see.

Post by Shiva
And anyways, do any of you have any such sample programs like what I'm trying to write? It would be a great help if you did and if you are able to share it with me. Because the TMF programmer's guide does not have a single example though it has all the explanation for procedure calls.
It'd give me a good place to start writing my code if I get to see some example. Any language is fine. Thanks for all your help already :)

I will send an example to you via email, since I think it is too large to be reasonable to post here.

[Tone]

Post by Shiva
Wolfgang,
Unfortunately the chances to search the audittrail without any 3rd party tools are really poor - Would you know of a way to do it without the 3rd party tools? You've told me that it is hard. But is it impossible? I want to give it a try. I don't think the management would like me to try SNOOP and I really don't want to get in a mess as well unless someone is present to guide me through it. Too many people have advised me against it that the fear of breaking down the system has gotten into me. It is a production system and I'd be damned for all I know if I do something unworthy.
So I'd have to find a way to do it without the third parties, how so ever hard it may be.
And the more I hear about TMF the more I'm inclined to believe your opinion - 'But as I wrote earlier I do not think that TMF is the tool for creating history.' But you did say that we can find the information I'm looking for - in audit trails.
Tone,
What's a QCB number? DISCOPEN?
'but there needs to be a measurement running at the time of the update' - What do you mean by that? That HP GNSC document, I could not find it. Do you have that link? Or a copy of the document would suffice too. Thanks already for the explanations. The information I seek is too valuable to neglect, so no matter how difficult it gets, I'd have to give it a try. Any help is appreciated. I hope I have enough audit trails to help me find it. And yes, there's a lot of activity going around that file.

The document I referred to is not found in the normal manual set
but rather in the HP Knowledgebase. You can register to use it at

https://h22204.www2.hp.com/

Post by Shiva
Keith,
Timestamp, transaction ID, filename, and operation type - These are good enough information for me. It will always be only one user ID. At least the transaction ID and timestamp should be some useful information for me. And I will start working on that Section 5 for TMF programmer's guide today - hoping that it would work.
TMFCOM INFO AUDITDUMP? What am I looking for? I could not find any information that would say for how long the audit trails are saved.
And thanks for that information about audit compression. The file does not have it on and it won't matter anyway as you have mentioned that the updates will be there for sure. Let me get started.
I'll update you lot when I get something going. Thanks a lot for all your input. Much appreciated. :)

[Tone]

Post by wbreidbach

Post by Shiva
I have a FORMAT 1, FILECODE 0, ENSCRIBE TYPE K file which is pretty huge. The file is audited. One of the records in the file interests me. I'd like to know the history about that particular record. Say, since the moment it was entered into the file, to the last moment when it was altered - I want to know what alteration was done, what was the value before alteration and who did the alteration and so on. You get my point. Can I get that information through TMF? Or do I need to install and other 3rd party utilities for that?
I would not be able to install any 3rd party utilities, I hope the system would have enough information to help me?
Any thoughts are appreciated. :) Thanks in advance.

The information you are using can be found in the audittrails. Unfortunately the chances to search the audittrail without any 3rd party tools are really poor, the only tool available for that is SNOOP and searching the audittrail with SNOOP is very expensive, so I recommend to start SNOOP with a very low priority. The command within SNOOP is READAUDIT, you will be prompted for all necessary information. But I do not expect that to fullfil you needs.
In addition the only information that can be obtained from the audittrail is the change in data and the transaction ID. You will not find the user who did it.
From my experience you need to write a history or a logging within the application.

Actually there is a way of associating database updates of an audited
file with a particular process but there needs to be a measurement
running at the time of the update and it needs to include the DISCOPEN
entity for that file. Then you can use the OCB number to link the
audit record from the audittrail with a DISCOPEN record in the
measurement. There is a document in the HP GNSC knowledge base entitled
"How to identify what process updated a certain record in a file" which
shows an example.

[Keith Dick]

Post by Tone

Post by wbreidbach

Post by Shiva
I have a FORMAT 1, FILECODE 0, ENSCRIBE TYPE K file which is pretty huge. The file is audited. One of the records in the file interests me. I'd like to know the history about that particular record. Say, since the moment it was entered into the file, to the last moment when it was altered - I want to know what alteration was done, what was the value before alteration and who did the alteration and so on. You get my point. Can I get that information through TMF? Or do I need to install and other 3rd party utilities for that?
I would not be able to install any 3rd party utilities, I hope the system would have enough information to help me?
Any thoughts are appreciated. :) Thanks in advance.

The information you are using can be found in the audittrails. Unfortunately the chances to search the audittrail without any 3rd party tools are really poor, the only tool available for that is SNOOP and searching the audittrail with SNOOP is very expensive, so I recommend to start SNOOP with a very low priority. The command within SNOOP is READAUDIT, you will be prompted for all necessary information. But I do not expect that to fullfil you needs.
In addition the only information that can be obtained from the audittrail is the change in data and the transaction ID.. You will not find the user who did it.
From my experience you need to write a history or a logging within the application.

Actually there is a way of associating database updates of an audited
file with a particular process but there needs to be a measurement
running at the time of the update and it needs to include the DISCOPEN
entity for that file. Then you can use the OCB number to link the
audit record from the audittrail with a DISCOPEN record in the
measurement. There is a document in the HP GNSC knowledge base entitled
"How to identify what process updated a certain record in a file" which
shows an example.

That is interesting. Thanks for mentioning it.

I'm sure you are right that the method describe would work if you have an appropriate measurement in progress.

A program using the audit trail reading library gets only a subset of data from the audit trail records, and the OCB number is not among the items returned, so even if a measurement were in progress, I think a program using the audit trail reading library could not use this approach.

[p***@gravic.com]

Post by Shiva
I have a FORMAT 1, FILECODE 0, ENSCRIBE TYPE K file which is pretty huge. The file is audited. One of the records in the file interests me. I'd like to know the history about that particular record. Say, since the moment it was entered into the file, to the last moment when it was altered - I want to know what alteration was done, what was the value before alteration and who did the alteration and so on. You get my point. Can I get that information through TMF? Or do I need to install and other 3rd party utilities for that?
I would not be able to install any 3rd party utilities, I hope the system would have enough information to help me?
Any thoughts are appreciated. :) Thanks in advance.

Hello, there is a new HP product available called HP Shadowbase. One of the available utilities in it is called the HP Shadowbase Audit Reader (HP SAR). Using it, you can run a query against your audit trail along these lines: SELECT * FROM <FILE> WHERE (predicate); this will show all of the file changes for that file that satisfy the predicate, from the START TIME to the END TIME (assuming the audit trail files are still available).

if you attach a DDL record definition to the file previously, you can use the DDL field names in the predicate, for example WHERE (ACCCOUNT-NUM = "ABC123"); Contact your HP account representative for more information.

[Shiva]

Tone,

Got your email. Thanks! Though this was not a case where MEASURE was not running for that particular file, it provided great insight in understanding the system, so thank you!

And sorry, my bad. I guessed it wrong.

Keith,

Yes, it was coincidence. CCOMP is good. And I did read that licensing bit from the manual. My organization obviously did not approve of that idea but I'm still allowed to try this out in the development region. So I will try to replicate this out in my leisure. But if I could get those three audit files off those disks to the development region that would be great.

And I was wondering why restrict, but your explanation makes it sensible. Thanks for your email. It was a lot of information. I was wondering how was the original one written! I will compose my code and ask here if I have any doubts, but all information is provided in the manual, thanks a lot!

Phole,

Shadowbase, you say? Is this product introduced along with Nonstop X? Anyways, I can't ask my management to let me use SNOOP, let alone ask them to get this product so that I can use it. But it is good information - so thank you! :)

[Keith Dick]

Post by Shiva
Tone,
Got your email. Thanks! Though this was not a case where MEASURE was not running for that particular file, it provided great insight in understanding the system, so thank you!
And sorry, my bad. I guessed it wrong.
Keith,
Yes, it was coincidence. CCOMP is good. And I did read that licensing bit from the manual. My organization obviously did not approve of that idea but I'm still allowed to try this out in the development region. So I will try to replicate this out in my leisure. But if I could get those three audit files off those disks to the development region that would be great.

I have never tried moving an audit trail file to another system, then trying to read it using ARLIB, but the documentation of AROPEN seems to indicate that you can give an actual filename to AROPEN, not just an audit trail abbreviation and let ARLIB ask TMF where to find the actual files. So if you can get your system managers to approve moving copies of the production audit trail files to the development system, you might be able to use ARLIB to read them. I don't know how to do it, but I imagine there is a way to restore older audit trail files from the backups that TMF's audittrail dump makes, so you might even be able to look at older audit trail fiels on the development system.

Post by Shiva
And I was wondering why restrict, but your explanation makes it sensible. Thanks for your email. It was a lot of information. I was wondering how was the original one written! I will compose my code and ask here if I have any doubts, but all information is provided in the manual, thanks a lot!
Phole,
Shadowbase, you say? Is this product introduced along with Nonstop X? Anyways, I can't ask my management to let me use SNOOP, let alone ask them to get this product so that I can use it. But it is good information - so thank you! :)

[Shiva]

Thanks Keith, I'll do that and update if I get success on that route! And my system does not keep dumps of audit trails. They have just backups up to 3 days and that's it. Nothing is written to tape. Too bad! Could've made for a wonderful experiment! :)

[Keith Dick]

Post by Shiva
Thanks Keith, I'll do that and update if I get success on that route! And my system does not keep dumps of audit trails. They have just backups up to 3 days and that's it. Nothing is written to tape. Too bad! Could've made for a wonderful experiment! :)

That's odd. I thought you said the TMF INFO AUDITDUMPS command showed that audit trail dumps were configured and enabled. Did you misinterpret the output?

[Shiva]

Nope. It was on. But the dumps where configured for two volumes where it was also mentioned that it was limited to 3 files per disk or something like that!

[Keith Dick]

Post by Shiva
Nope. It was on. But the dumps where configured for two volumes where it was also mentioned that it was limited to 3 files per disk or something like that!

Well, I think that means that those three backup files would be in addition to the three files that are online, so you might have a few more audit trail files available than you think you do. You probably would be able to backup the auditdump files from those disk locations to tape and restore them to your development system. Or if you can access the production system from the development system via Expand or TCP, a FUP DUP or FTP over the network could transfer the files without using a tape, but those audit trail files are usually very large, so that might not be good from the point of view of overloading the network.

[wbreidbach]

Post by Keith Dick

Post by Shiva
Nope. It was on. But the dumps where configured for two volumes where it was also mentioned that it was limited to 3 files per disk or something like that!

Well, I think that means that those three backup files would be in addition to the three files that are online, so you might have a few more audit trail files available than you think you do. You probably would be able to backup the auditdump files from those disk locations to tape and restore them to your development system. Or if you can access the production system from the development system via Expand or TCP, a FUP DUP or FTP over the network could transfer the files without using a tape, but those audit trail files are usually very large, so that might not be good from the point of view of overloading the network.

I still think that TMF is not an option for what you have in mind. I do not think there is a real chance to find out who did the change. And the amount of data you have to process can be extremely large in comparison to the amount of data you really need.
Do not forget: The audittrail contains only the information what has changed in which record of a file, nothing else. And if a transaction is aborted a backout occurs. In that case you would see 2 changes, at first the planned new contenst and then the old contents again.
Logging data changes together with other information like userid has to be done within the application. The only rather simple option I could imagine is writing something like an access-module for that file which could do the logging.

[Shiva]

You're right again, Keith. Three, three. So six files. Not easy to get the production files onto test servers without proper approvals from data owners. All too complex at the moment. The reason I started this investigation is not going to be met. But this will give me an experience, so I'll still go ahead with this in the dev region! Thanks a lot for all your help :)

Wolfgang, you are right. TMF is no history tool to go search your record history. There's no unique tool for that on NS as far as I believe. Other than that Shadowbase which was mentioned above. But we can still use TMF and Measure (as Tone has pointed out) to get information out from the system which could be valuable to us at least to some extent. I was actually not against the user who did the record manipulation. It will always be the only user ID under which the whole application is ran. So I always knew that. (although my explanation at the beginning of this issue did not express so, as you always know, not too good with words!) I wanted to know whether the data present in the file now is the one that was inserted into the table at first. Or was the value amended after that? And if so, when? Night means batch did it, day means the end user did it in online. Basically a detective's job of finding out more information from these system logs which were not designed to help us that way. Something and anything that might help me solve the puzzle that I'm after! But thanks a lot for your help though! :)

[wbreidbach]

We handle such things within the application. For many tables we have history tables as well, as soon as the data is changed the previous content is written to the history. We do that with SQL tables but in former times we did the same with Enscribe files.
If you are reading the audittrails you have to match the changes against the record description.
By the way, I do not think that Measure can help you in any way. You can measure a lot of things but if 2 processes update your file you do not know which update was made by which process. In addition matching Measure data with audittrail data might be possible but that would be something I would not like to spend my time with.
There might be the chance to replace the I/O routines like WRITE by user written routines, that is not easy at all but within such a library you could write the application log and no change would be necessary within the application programs they only would have to be started with that library.

[Shiva]

Even we do handle logs within the application for most of the important files. We have history files too. But just that this file contains relatively insignificant data, which at this point of time has created some issues that need looking into. As we are unable to find how such rogue field value entered the record present in the file, we are trying to find out the initial record which was present there because we are sure that the rogue value for that field was injected some point later after the initial record was added. We have searched elsewhere in the code with no avail to find out what could alter that field, and we have checked with the users too who say that they do not have access to modify that particular field without modifying the whole record.

Long story short, we are planning to copy the audit trail files and dumps into dev region and then try read them! Thanks to all your help! :

[wbreidbach]

Post by Shiva
Even we do handle logs within the application for most of the important files. We have history files too. But just that this file contains relatively insignificant data, which at this point of time has created some issues that need looking into. As we are unable to find how such rogue field value entered the record present in the file, we are trying to find out the initial record which was present there because we are sure that the rogue value for that field was injected some point later after the initial record was added. We have searched elsewhere in the code with no avail to find out what could alter that field, and we have checked with the users too who say that they do not have access to modify that particular field without modifying the whole record.

In that case I think the audittrail is the only way to find the cause. I had a similar problem several times and anylyzing the audittrail is pretty hard work and very time-consuming.