• Levi D

    Hi Jagadeesh,

    The audit logs for the standard items (incident, contact, organization) are standard reports.  You can identify the report by looking at the audit log in a workspace.  Once you have the report (I recommend getting the report ID), it can be ran in REST with this endpoint:  /services/rest/connect/v1.4/analyticsReportResults.  For example the Incident Audit Log report has an ID of 9041 and two filters (Incident ID and Exclude Transactions of Type).  With your REST request use the ID and set the filter Incident ID to get the audit log for a specific incident.  Check out this link for examples of how to run a report by ID and set filters:

  • Levi D

    Hi Kim,

    Did you happen to check the closed dates on the associated incidents?  Incidents need to be closed for more than X days (365 in your case) for the purge to work.

  • Levi D

    Perfect!  Glad this worked.

  • Levi D

    You're welcome Mohamed!

  • Levi D

    Make sure the second rule has a trigger for when Field1 changes.  Also, make sure that this rule is after the rule where Field1 is being set.

  • Levi D

    Hopefully I am understanding this correctly.  Given the notation here and assuming that means we have to account for other filters, I believe the ORing of the two NOT IN LIST filters would work.  The logical expression would like this though:  A & B & C & (D | E).  Filters D and E would be NOT IN LIST filters mentioned earlier.

    Another option would be to join incidents to products (outer join).  Then join products to products again (outer join) ON products.prod_lvl2_id =  The second product table will be known as products2.  Put a join filter on the second join to products of: products2.prod_hierarchy IN LIST <select the value equivalent to 186>.  Once you do this, you would create a report filter like this:  if(incidents.c$incident_type = 151 & products2.seq IS NOT NULL, 1, 0) equals 0.  Run the report with this filter = 0 and it will exclude all records where c$ = 151 & = 186 is true.  Run the report with this filter = 1 and it will return all records where c$ = 151 & = 186 is false.  This is a nice option as it serves as a good audit too.  If you examined 150 incidents and 20 of them met this condition c$ = 151 & = 186 then you could run the report with the filter one way and find these 20 or run the report with filter the other way and it would return the other 130.  Hope this makes sense.

  • Levi D

    Hi Sebastiaan,

    Based on the logic, wanting to exclude records where if(incidents.c$ = 151 & = 186, 1,0) = 1, this should be able to be accomplished with a filter/s equivalent.  This could be done with ORing the following two filters together:

    incidents.c$incident_type NOT IN LIST <select the value equivalent to 151>

    incidents.prod_hierarchy NOT IN LIST <select the value equivalent to 186>

    This above does assume not using the IDs, but functionally should provide the same output.

    Dealing with the product hierarchy or any of the hierarchies is challenging.  Other options that have been used to deal with product hierarchy issues is to join products to itself as many times as necessary to isolate a particular product at a desired level and deal with it that way (e.g. join incidents to products, then join products to products via a prod_lvl#).  For the provided example I don't believe this is necessary, but is something to keep in mind when reporting on any of hierarchical data items in the future.

  • Levi D

    I agree with Sebastiaan that a custom object and CPM combination would be a great way to create the data for this and then build a report(s) based off of the data.  We have implemented a very similar concept in multiple places and utilize it to track changes to any fields that are not tracked by the product.  In short, you are basically building a custom version of the transactions table.

  • Levi D

    You're welcome Vikash!

  • Levi D

    Hello Sergio,

    Considering just those two fields (document number and registration ID), this can be done to identify contacts that have the same document number but differing registration ID.  Here are the column definitions to make this work:

    column 1:  contacts.<document number>

    column 2:  count(distinct(contacts.<registration ID>))

    NOTE:  Due to the aggregate function use in column 2 this automatically causes the report to group results based on the contacts.<document number> field (see in the "Level" tab).  This is intended and needed to find the duplicates.

    Add a group filter (in the Level tab) that will be like this:  count(distinct(contacts.<registration ID>)) greater than 1

    The report could be run at this point and it would output all document Numbers that have more than 1 differing registration ID.  However, if would like to output more info like contacts.c_id or, etc. this can be done.  However, fields should not be added as-is (e.g. contacts.c_id) as they will be added into the grouping and will effectively "break" the logic on the report.  If more fields are added they need to be done in a way where they will not be included in the grouping.  Using the "group_concat" function would probably be ideal for use to output additional data.  Below are examples on how do this for the c_id and email fields.

    group_concat(contacts.c_id, ',')

    group_concat(, ',')

    Hope this helps you out.

  • Levi D

    Unfortunately threads can only be added via the data import wizard on incident creates only.  This is outlined in the documentation:

    Depending on how many records and the nature of the content you do have other options... If the note you are adding is static or can leverage variables supported by standard text:

    1.  Create a standard text for the content and a corresponding rule to add it to incidents.  Use an import to update the incidents and ensure the rule is triggered.


    2.  use incident multi-edit to add content (I believe you can only edit up to 250 records at a time though)

    If the thread content is variable and cannot leverage variables for the differences then doing a custom import via a Connect PHP script (or another API) would be another way to accomplish this.

  • Levi D

    You're welcome!

  • Levi D

    Attached is a report to output the product tree in order as if the product tree were full expanded in the PCD editor.  The same concepts should be able to be applied for reports that would do the same for categories and dispositions.  The seq value and product level are key components to making this work and self-joining to the product tree for each possible level (6 self joins).  The reason for this is that the seq is the position of the product in the tree with respect to its lowest level.  However, to fully order the data you need to know the seq of a product and the sequence of each of its parent products.  For example if you have a product that has 4 parents then there are 5 total levels of product.  To be able to determine where it resides you must know the seq of the product itself and the sequence of each of the 4 levels above it.  This is where the self-joins are needed as we can isolate the seq with respect to product level.

    The first two columns in the report are the lowest level product for that record.  If the product is a top-level product then the values in the Product lvl 1 columns should match the first two columns.  If the product is a second level product then there should be data in the product lvl 1 and 2 columns and the product lvl 2 columns should match the first two columns... etc out to the 6 possible levels.  Hope this helps to explain the report.  It may help to view it side-by-side with the product tree too.

  • Levi D

    You're welcome!

  • Levi D

    You may have to create your own report(s) for this.  I do some similar research looking for bot-like activity on our site and I currently use a two step approach as it is difficult to get everything in one report/dashboard.  I think you could do something similar for your case:

    1.  obtain the sessions of what you want to review from the clickstreams table

    -In your case you want all the unique clickstreams.cs_session_id values that have an action_id = /results

    2.  use the cs_session_id values as a filter in another report

    -the output of this report should be clickstreams.context1 where action_id = SOURCE (this will provide the IP address)

    The above is a rough outline of how to at least get the IPs that have had the /results action from the clickstreams table.  You may want to group on the IP address and do a count to find any IPs that may be hitting /results heavily.

    In the past I have participated in a similar discussion about looking for bot-like activity and you may find some of those concepts useful or relevant for what you are doing here:

    Hopefully this is all good starting information for you.