How are you, friends? Here I bring you a new episode of this beautiful segment called #BestInMicro.
Today I am going to tell you about a situation I had recently. So, a manager asked me for a list of all the reports and objects that were modified by users. The ultimate goal was to detect which users had more privileges than they actually used because as we all know, depending on the licenses that each user has, the license price of MicroStrategy varies.
My first thought was to do a simple search of all the objects and filter them by owner. In this way, I was going to be able to find that users are creating different objects. So far, so easy… until I ran into different situations:
– First, it was not going to be able to detect who was modifying the objects.
– Second, it is possible to change the owner of an object through properties. so the result of my search was not going to be entirely accurate.
That’s when it occurred to me to look in the Change Journal, and for those who don’t know…
This is a MicroStrategy tool that registers the modifications of the objects, and it can be consulted from the properties menu of each object:
In previous versions, it was possible to disable logging, but now this is no longer possible. However, in order for users to have access to view the activity log, it is necessary to grant the “Audit Change Journal” privilege.
By default, MicroStrategy even allows us to add comments when editing an object, which will then be reflected in the change journal. It is common to disable this input, but it is also possible to enable this input in Tools > MicroStrategy Developer Preference>Optional Actions>General>Display change journal comments input dialog.
Returning to our problem, although it is possible to see the modifications that the object had, my case was still very complicated because the project in question had more than 6000 reports (and logically I would not go through them one by one), but, in addition, I had only been asked for the web users. That is to say, I am not interested in obtaining information on the objects that were modified by the developer.
For this, we must consult the complete journal record. Within the MicroStrategy administration menu, in the System administrator tab, we can find the change journal history. This tab allows us not only to filter the different fields on which we are interested in seeing the elements of the change Journal but also allows us to export it, so the only thing I needed to do was to define the project of interest, the access mode to be exclusive via web and the date range from which I was interested in extracting it. I had never used this filter before and I found it quite interesting.
One last detail that caught my attention. The journal is not only divided by pages (i.e. the number of records), but also by transactions (save, delete, create, etc).
Be careful with this, because they may think they have reached the end of the page when in fact it is the end of the transaction. The number of records displayed can be modified from the Intelligence server menu. To do this, the number of transactions and/or records must be modified in the menu shown below:
And of course, as we are all curious and really want to get to the bottom of each of these things, we already know (from reading previous articles such as those on metadata queries) that all MicroStrategy information is stored in Metadata and statistics tables.
The change journal is no exception, and is stored in the following tables:
DSSMDJRNOBJS: Basically the change journal. 4 columns: transaction, project, object, and modification made. While there is a code, KB44377 describes what each code refers to. Here is a query you can use.
*Note: 1-3: Records any creation, modification, or deletion of any object.
4: Each time a folder is deleted, the changes are recorded in the parent folder.
5-7: Link Items are objects that connect two or more objects (e.g., security filters connect users to metrics).
8-10: Records each time the change journal is activated, deactivated, or deleted.
DSSMDJRNINFO: Enter the details of the transaction, including what was done, when, from where and by whom. As before, here is the description of each type of transaction
*Note: 1-3: The Change journal will be logged each time the user activates, deactivates, or wishes to delete the journal history.
4-5: Records when saving one or more objects.
6: Link items are objects that link two or more objects. For example, a security filter will link a user and a metric.
7-9: Records when one or more objects or projects are deleted.
10: Records when objects are copied.
11: Duplicate users can be combined and this transaction is recorded.
12: Record when using the “Unused Managed System Objects” in the administration menu.
13: Create a new system property.
14. Create a database password
DSSMDJRNLNKS: This table reflects the “indirectly” affected objects. For example, if I modify an attribute, a filter affected by this change will be reflected here.
DSSMDJRNOBJC: This stores the comments made in the change journal.
DSSMDJRNOBJD: Stores a little more detail about the modified object. This is crossed with the DSSMDOBJINFO and we will obtain all the details of the object.
I also leave here some comments that I found in note from the community that deserve a mention:
– You can always use Freeform SQL in MicroStrategy to execute these queries. Therefore, you can review the change journal on the Web and even create distributions.
– It is not recommended to create a new table within the metadata repository (why is explained in the ScanMD article), but query performance can be greatly improved if we store our searches in physical tables.
– Similar queries can be created using the Command Manager and the COM API; however, I personally found this to be much easier and more comprehensive.
– Running multiple queries on the metadata repository database may affect the overall performance of the environment. Use them discretely.
* Tu información será utilizada exclusivamente para contactarte en relación al Business Data Master. No hacemos spam ni compartimos datos con terceros.
