Archives of the TeradataForum

Message Posted: Mon, 19 Jun 2006 @ 22:34:25 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Thank you, Mike.

I ran the query you provided. Here are the results.

     UserName:  ALL
     Account:    Empty
     Explain, Object, SQL, Step:  T
     Summary, Threshold: F
     TextSize:  0
     All Others:  ?

I did further research on this issue.

Table QryLogSQL has 5 columns. Three of them can be joined to Table QryLog. They are ProcID, QueryID, and CollectTimeStamp. Table QryLog has ProcID and QueryID but CollectTimeStamp. I found the query I provided in my previous email produced records with CollectTimeStamp earlier than QryLog.StartTime. The join uses ProcID and QueryID. The results included QryLog.StartTime and QryLogSQL.CollectTimeStamp.

I also found records with identical ProcID and QueryID in both tables, but QryLog.StartTime earlier than QryLogSQL.CollectTimeStamp. So, the join returns multiple records for each pair of ProcID and QueryID. Some of the older records were generated by the same users, some by others. My question is how we can use these query logs if my SQL is correct. Can it be reliable to audit user activities? I do not know whether it is proper to add QryLog.StartTime <= QryLogSQL.CollectTimeStamp to the WHERE clause in my query. I do not the relationship between these two tables. There are more than 10 tables with names like 'QryLog%'. What are their relationship. Data Definition Guide does not provide this information. Where to find it?

Can you provide me SQLs to check user activities/SQLs? If the information can be retrieved from Teradata tools, The results from different tools must match. I'd like to know which tools and how.

Following your instructions, using the 'Data Collection' tab of 'Configure, Teradata Manager', "Query Log" Entry shows,

     Configured:  Yes
     Summary Retention:  180
     Detail Retention:     7
     Next Event:  6/20/2006 6:05:31 PM

What is "Summary Retention"? And what is "Detail Retention"? As I said above, which tables are involved for this options? I saw both StartTime and CollectTimeStamp of March 2006. They were less than 180 days old, and older than 7 days. Is it possible Teradata messed up these tables? If I make a copy of what I want to archive, how to write queries to retrieve data from the 10+ QryLog* tables? Results of wrong queries may be misleading if tables are not joined in a proper way.

Thanks,

Charles

	<Prev	Next>		<<First	<Prev	Next>	Last>>