How to get additional data information from dms_audt and dms_secondary_audt table ?

Hi,

I am trying to get the audit data from dsm_audt and secondary_audt table.

However it is missing the additional data column information.

How can I retiview this information?

I tried using these 2 ways

1. If I use aaApi_SelectAuditTrailRecords , this method is returning null buffer for the document guid supplied.
And also it does not have option to get information from secondary table.

AADMSAUDTOBJECT objectToSelect = { 0L };
objectToSelect.lObjectType = AADMSAT_TYPE_DOCUMENT;
objectToSelect.idGUID = guids;

AADMSAUDTCRITERIA criteria = { 0L };
criteria.lObjectType = 0L;
criteria.lObjectCount = 1L;
criteria.lpObjects = &objectToSelect;
criteria.bRecursive = TRUE;
criteria.bIncludeChildObjects = TRUE;
criteria.lActionCount = 0L;
criteria.lplActions = NULL;
criteria.ulDateTimeType = AADMSAT_DATE_ANYTIME;
criteria.lpctstrStartDate = NULL;
criteria.lpctstrEndDate = NULL;
criteria.lUserCount = 0L;
criteria.lplUsers = 0L;

//AfxMessageBox(L"Came Here");
HAADMSBUFFER results = aaApi_SelectAuditTrailRecords(&criteria);

2. If I try to use the powershell Export-PWAuditTrailToSQLite , it just dump the table ,
here to additional data info is missing, however it has option to skip secondary table.



I am basically designing the audit panel same as projectwise for document selected, but will have information from both the audit and secondary_audt table.

Please let me know if anyone has done something similar.

Parents
  • I built a customization with the SDK that adds a "More Audit Trail" tab to both the Document and Project properties dialogs. This was NOT an easy thing to do though. There is no means that I'm aware of in the current PW client, nor natively within the SDK, to retrieve audit trail data stored in the secondary table. This has long been a pet peeve of mine.

    To accomplish this, I had to do 2 things. First, figure out the proper SQL query to get the corresponding audit trail data for the object, and then build a report of that data to look similar to the main Audit Trail reporting. It is HTML served up in the tab in an IE browser control in the tab. Because of the nature of my code and build, this isn't code I can readily share. But I'm sure I could answer any specific questions you may have. 

  • Thank you for information. 

    Can you tell me how you retrieved additional data information to be displayed  as this column is computed one and not available in table? 

  • I am not sure what you mean by the column being a computed one, but here is the SQL statement I build for a document (a CString) to get secondary audit trail table data.  Note this is written for Oracle, so it may need tweaking for a SQL Server environment.

    sSQL.Format(L"SELECT O_ACTION, TO_CHAR(O_ACTTIME,'fmMM/DD/YYYY HH12:fmMI:SS PM'), O_USERNAME, O_TEXTPARAM, O_COMMENTS, O_ITEMDESC, O_NUMPARAM2, O_GUIDPARAM, O_OBJTYPE, O_ITEMNAME FROM %s WHERE O_OBJGUID='%s' AND O_OBJTYPE IN (%ld,%ld) ORDER BY O_ACTTIME", m_sTableName.GetString(), strDocGuid.GetString(), AADMSAT_TYPE_DOCUMENT, AADMSAT_TYPE_DOCUMENT_SET);

    I'm not going to explain how the values of variables referenced in that string such as DocGUID are obtained prior for a selected document, but you should be able to see the intent and meaning of what needs to be in the SQL statement. Have a look at the SDK code samples custattrpage and projcustattributes which are a great starting point for this type of thing and show how to get information such as the DocGuid. Also note that the secondary table name can be obtained programmatically through the SDK, which I recommend instead of hardcoding.

    The query string sSQL is then passed to aaApi_SqlSelect and the contents returned contains all the secondary audit trail table data for the document. That information is fed back to my class that manages the interface and presentation. HTH.

Reply
  • I am not sure what you mean by the column being a computed one, but here is the SQL statement I build for a document (a CString) to get secondary audit trail table data.  Note this is written for Oracle, so it may need tweaking for a SQL Server environment.

    sSQL.Format(L"SELECT O_ACTION, TO_CHAR(O_ACTTIME,'fmMM/DD/YYYY HH12:fmMI:SS PM'), O_USERNAME, O_TEXTPARAM, O_COMMENTS, O_ITEMDESC, O_NUMPARAM2, O_GUIDPARAM, O_OBJTYPE, O_ITEMNAME FROM %s WHERE O_OBJGUID='%s' AND O_OBJTYPE IN (%ld,%ld) ORDER BY O_ACTTIME", m_sTableName.GetString(), strDocGuid.GetString(), AADMSAT_TYPE_DOCUMENT, AADMSAT_TYPE_DOCUMENT_SET);

    I'm not going to explain how the values of variables referenced in that string such as DocGUID are obtained prior for a selected document, but you should be able to see the intent and meaning of what needs to be in the SQL statement. Have a look at the SDK code samples custattrpage and projcustattributes which are a great starting point for this type of thing and show how to get information such as the DocGuid. Also note that the secondary table name can be obtained programmatically through the SDK, which I recommend instead of hardcoding.

    The query string sSQL is then passed to aaApi_SqlSelect and the contents returned contains all the secondary audit trail table data for the document. That information is fed back to my class that manages the interface and presentation. HTH.

Children
  • I am doing the same thing as you said

     

     string sSQL = "select * from " + "DMS_AUDT" + " a WHERE o_objguid='" + iGuid + "'";

     

     and I get these columns

     

     

     

    However the column I am looking into is Additional Data column which is missing in the column list mentioned above.

     

    The column “o_textparam” does not contain this information.

     

    I got the information that “Additional Data” column is the computed one

    There is simply NO WAY to populate that information solely by querying a table.column.

     If it is an o_objtype=2 (Document) with an o_action=1012 (State Changed) then o_numparam1 is the ID of the From workflow state and o_numparam2 is the ID of the To Workflow State. The state descriptions would need to be looked up from dms_stat. If you want the workflow description it would need to come from dms_work using the o_workflowno from the dms_doc.o_docguid = o_objguid record.

     

     

    So still my question remains the same as to how get this information?

  • Ah, now I know what you are looking for. Didn't realize you were that far into it, having reached the "fun" part of this exercise. Yes, that column is a pain to populate because it is computed. I have a long switch/case block that figures it all out after the query is run. This was the best I could figure out and seemed to mimic the results I would see in the regular Audit Trail interface. It is entirely possible that I missed something or made a mistake here. I'm not doing anything with O_NUMPARAM1 (not even in my query) as I couldn't see a use for it in the reporting being mimicked at the time. Things may have changed since then.  It's this kind of stuff that makes this issue such a pet peeve. And if a PW upgrade changes any this*, you're screwed.  Anyway, here is the block of code, HTH.

    	// Loop over SQL results
    		for (LONG i = 0; i < lRowCount; i++)
    		{
    			LONG lObjType = _ttol(aaApi_SqlSelectGetData(i, 8L));
    			LONG lActionId = _ttol(aaApi_SqlSelectGetData(i, 0L));
    			CString sAddlData = aaApi_SqlSelectGetData(i, 3L);
    			CString sAction = L"";
    
    			switch (lObjType)
    			{
    			case AADMSAT_TYPE_DOCUMENT:
    			{
    				switch (lActionId)
    				{
    				case AADMSAT_ACT_DOC_UNKNOWN:
    				{
    					sAction = AADMSAT_ACT_DOC_UNKNOWN_TXT;
    					break;
    				}
    				case AADMSAT_ACT_DOC_CREATE:
    				{
    					sAction = AADMSAT_ACT_DOC_CREATE_TXT;
    					GUID folderGUID;
    					BOOL bConvertedGuid = StringToGuid(aaApi_SqlSelectGetData(i, 7L), folderGUID);
    					LONG lRows = aaApi_GUIDSelectProject(&folderGUID);
    					if (lRows == 1)
    					{
    						CString sTempAddlData = aaApi_GetProjectStringProperty(PROJ_PROP_NAME, 0L);
    						sAddlData.Format(L"In folder '%s'", sTempAddlData.GetString());
    					}
    					break;
    				}
    				case AADMSAT_ACT_DOC_MODIFY:
    				{
    					sAction = AADMSAT_ACT_DOC_MODIFY_TXT;
    					break;
    				}
    				case AADMSAT_ACT_DOC_ATTR:
    				{
    					sAction = AADMSAT_ACT_DOC_ATTR_TXT;
    					switch (_ttol(aaApi_SqlSelectGetData(i, 6L)))
    					{
    					case AADMSAT_ACT_DOC_ATTR_CREATE:
    					{
    						sAddlData = L"New Sheet";
    						break;
    					}
    					case AADMSAT_ACT_DOC_ATTR_MODIFY:
    					{
    						sAddlData = L"Modified Sheet";
    						break;
    					}
    					case AADMSAT_ACT_DOC_ATTR_DELETE:
    					{
    						sAddlData = L"Deleted Sheet";
    						break;
    					}
    					}
    					break;
    				}
    				case AADMSAT_ACT_DOC_FILE_ADD:
    				{
    					sAction = AADMSAT_ACT_DOC_FILE_ADD_TXT;
    					break;
    				}
    				case AADMSAT_ACT_DOC_FILE_REM:
    				{
    					sAction = AADMSAT_ACT_DOC_FILE_REM_TXT;
    					break;
    				}
    				case AADMSAT_ACT_DOC_FILE_REP:
    				{
    					sAction = AADMSAT_ACT_DOC_FILE_REP_TXT;
    					break;
    				}
    				case AADMSAT_ACT_DOC_CIN:
    				{
    					sAction = AADMSAT_ACT_DOC_CIN_TXT;
    					break;
    				}
    				case AADMSAT_ACT_DOC_VIEW:
    				{
    					sAction = AADMSAT_ACT_DOC_VIEW_TXT;
    					break;
    				}
    				case AADMSAT_ACT_DOC_CHOUT:
    				{
    					sAction = AADMSAT_ACT_DOC_CHOUT_TXT;
    					break;
    				}
    				case AADMSAT_ACT_DOC_CPOUT:
    				{
    					sAction = AADMSAT_ACT_DOC_CPOUT_TXT;
    					break;
    				}
    				case AADMSAT_ACT_DOC_GOUT:
    				{
    					sAction = AADMSAT_ACT_DOC_GOUT_TXT;
    					break;
    				}
    				case AADMSAT_ACT_DOC_STATE:
    				{
    					sAction = AADMSAT_ACT_DOC_STATE_TXT;
    					break;
    				}
    				case AADMSAT_ACT_DOC_FINAL_S:
    				{
    					sAction = AADMSAT_ACT_DOC_FINAL_S_TXT;
    					break;
    				}
    				case AADMSAT_ACT_DOC_FINAL_R:
    				{
    					sAction = AADMSAT_ACT_DOC_FINAL_R_TXT;
    					break;
    				}
    				case AADMSAT_ACT_DOC_VERSION:
    				{
    					sAction = AADMSAT_ACT_DOC_VERSION_TXT;
    					sAddlData.Format(L"Label: '%s'", aaApi_SqlSelectGetData(i, 3L));
    					break;
    				}
    				case AADMSAT_ACT_DOC_MOVE:
    				{
    					sAction = AADMSAT_ACT_DOC_MOVE_TXT;
    					break;
    				}
    				case AADMSAT_ACT_DOC_COPY:
    				{
    					sAction = AADMSAT_ACT_DOC_COPY_TXT;
    					break;
    				}
    				case AADMSAT_ACT_DOC_SECUR:
    				{
    					sAction = AADMSAT_ACT_DOC_SECUR_TXT;
    					break;
    				}
    				case AADMSAT_ACT_DOC_REDLINE:
    				{
    					sAction = AADMSAT_ACT_DOC_REDLINE_TXT;
    					break;
    				}
    				case AADMSAT_ACT_DOC_DELETE:
    				{
    					sAction = AADMSAT_ACT_DOC_DELETE_TXT;
    					break;
    				}
    				case AADMSAT_ACT_DOC_EXPORT:
    				{
    					sAction = AADMSAT_ACT_DOC_EXPORT_TXT;
    					break;
    				}
    				case AADMSAT_ACT_DOC_FREE:
    				{
    					sAction = AADMSAT_ACT_DOC_FREE_TXT;
    					break;
    				}
    				case AADMSAT_ACT_DOC_EXTRACT:
    				{
    					sAction = AADMSAT_ACT_DOC_EXTRACT_TXT;
    					break;
    				}
    				case AADMSAT_ACT_DOC_DISTRIBUTE:
    				{
    					sAction = AADMSAT_ACT_DOC_DISTRIBUTE_TXT;
    					break;
    				}
    				case AADMSAT_ACT_DOC_SEND_TO:
    				{
    					sAction = AADMSAT_ACT_DOC_SEND_TO_TXT;
    					break;
    				}
    				case AADMSAT_ACT_DOC_COMMENT:
    				{
    					sAction = AADMSAT_ACT_DOC_COMMENT_TXT;
    					break;
    				}
    				case AADMSAT_ACT_DOC_IMPORT:
    				{
    					sAction = AADMSAT_ACT_DOC_IMPORT_TXT;
    					break;
    				}
    				case AADMSAT_ACT_DOC_ACL_ASSIGN:
    				{
    					sAction = AADMSAT_ACT_DOC_ACL_ASSIGN_TXT;
    					break;
    				}
    				case AADMSAT_ACT_DOC_ACL_MODIFY:
    				{
    					sAction = AADMSAT_ACT_DOC_ACL_MODIFY_TXT;
    					break;
    				}
    				case AADMSAT_ACT_DOC_ACL_REMOVE:
    				{
    					sAction = AADMSAT_ACT_DOC_ACL_REMOVE_TXT;
    					break;
    				}
    				case AADMSAT_ACT_DOC_REVIT:
    				{
    					sAction = AADMSAT_ACT_DOC_REVIT_TXT;
    					break;
    				}
    				case AADMSAT_ACT_DOC_PACK:
    				{
    					sAction = AADMSAT_ACT_DOC_PACK_TXT;
    					break;
    				}
    				case AADMSAT_ACT_DOC_UNPACK:
    				{
    					sAction = AADMSAT_ACT_DOC_UNPACK_TXT;
    					break;
    				}
    				}
    			}
    			case AADMSAT_TYPE_DOCUMENT_SET:
    			{
    				switch (lActionId)
    				{
    				case AADMSAT_ACT_SET_CREATE:
    				{
    					sAction = AADMSAT_ACT_SET_CREATE_TXT;
    					break;
    				}
    				case AADMSAT_ACT_SET_ADD:
    				{
    					sAction = AADMSAT_ACT_SET_ADD_TXT;
    					break;
    				}
    				case AADMSAT_ACT_SET_REMOVE:
    				{
    					sAction = AADMSAT_ACT_SET_REMOVE_TXT;
    					break;
    				}
    				}
    			}
    			}
    		
    		    // Do stuff here with results
    		
    		}
    	// End loop
    

    * One thing that this code probably already overlooks is the time stamp in the query (second column) having been converted to UTC in the upgrade to 10.00.03.280 (which my code pre-dates). I have no idea if the dmsconv changed what was already in the secondary table as well, nor how that might affect what is displayed in the native audit trail report (does it adjust the output now based on the client time zone, etc). 

  • Thank you Jeff. The code helped me. 

    However I have more scenarios to handle and I am figuring them out.