Kusto query to add the names of links clicked, in a separate column

Question

The page views are collected in the pageViews table and the click responses are stored in the customEvents table. I’m trying to get the names of the links clicked on, per page view, in a separate column. PFB the webpage code created from the documentation.

var clickPluginInstance = new Microsoft.ApplicationInsights.ClickAnalyticsPlugin();
var clickPluginConfig = {
  autoCapture: true,
  dataTags: {
    useDefaultContentNameOrId: true
  }
} // Click Analytics configuration
var configObj = {
  instrumentationKey: "<>",
  extensions: [clickPluginInstance],
  extensionConfig: {
    [clickPluginInstance.identifier]: clickPluginConfig
  },
}; // Application Insights Configuration replace instrumentationKey

// Application Insights Snippet code
! function(T, l, y) {
  var S = T.location,
    k = "script",
    D = "ee791e68-981f-468d-947b-eda88f4d79f9",
    C = "https://eastus2-0.in.applicationinsights.azure.com/",
    I = "disableExceptionTracking",
    E = "ai.device.",
    b = "toLowerCase",
    w = "crossOrigin",
    N = "POST",
    e = "appInsightsSDK",
    t = y.name || "appInsights";
  (y.name || T[e]) && (T[e] = t);
  var n = T[t] || function(d) {
    var g = !1,
      f = !1,
      m = {
        initialize: !0,
        queue: [],
        sv: "5",
        version: 2,
        config: d
      };

    function v(e, t) {
      var n = {},
        a = "Browser";
      return n[E + "id"] = a[b](), n[E + "type"] = a, n["ai.operation.name"] = S && S.pathname || "_unknown_", n["ai.internal.sdkVersion"] = "javascript:snippet_" + (m.sv || m.version), {
        time: function() {
          var e = new Date;

          function t(e) {
            var t = "" + e;
            return 1 === t.length && (t = "0" + t), t
          }
          return e.getUTCFullYear() + "-" + t(1 + e.getUTCMonth()) + "-" + t(e.getUTCDate()) + "T" + t(e.getUTCHours()) + ":" + t(e.getUTCMinutes()) + ":" + t(e.getUTCSeconds()) + "." + ((e.getUTCMilliseconds() / 1e3).toFixed(3) + "").slice(2, 5) + "Z"
        }(),
        iKey: e,
        name: "Microsoft.ApplicationInsights." + e.replace(/-/g, "") + "." + t,
        sampleRate: 100,
        tags: n,
        data: {
          baseData: {
            ver: 2
          }
        }
      }
    }
    var h = d.url || y.src;
    if (h) {
      function a(e) {
        var t, n, a, i, r, o, s, c, u, p, l;
        g = !0, m.queue = [], f || (f = !0, t = h, s = function() {
          var e = {},
            t = d.connectionString;
          if (t)
            for (var n = t.split(";"), a = 0; a < n.length; a++) {
              var i = n[a].split("=");
              2 === i.length && (e[i[0][b]()] = i[1])
            }
          if (!e[C]) {
            var r = e.endpointsuffix,
              o = r ? e.location : null;
            e[C] = "https://" + (o ? o + "." : "") + "dc." + (r || "services.visualstudio.com")
          }
          return e
        }(), c = s[D] || d[D] || "", u = s[C], p = u ? u + "/v2/track" : d.endpointUrl, (l = []).push((n = "SDK LOAD Failure: Failed to load Application Insights SDK script (See stack for details)", a = t, i = p, (o = (r = v(c, "Exception")).data).baseType = "ExceptionData", o.baseData.exceptions = [{
          typeName: "SDKLoadFailed",
          message: n.replace(/\./g, "-"),
          hasFullStack: !1,
          stack: n + "\nSnippet failed to load [" + a + "] -- Telemetry is disabled\nHelp Link: https://go.microsoft.com/fwlink/?linkid=2128109\nHost: " + (S && S.pathname || "_unknown_") + "\nEndpoint: " + i,
          parsedStack: []
        }], r)), l.push(function(e, t, n, a) {
          var i = v(c, "Message"),
            r = i.data;
          r.baseType = "MessageData";
          var o = r.baseData;
          return o.message = 'AI (Internal): 99 message:"' + ("SDK LOAD Failure: Failed to load Application Insights SDK script (See stack for details) (" + n + ")").replace(/\"/g, "") + '"', o.properties = {
            endpoint: a
          }, i
        }(0, 0, t, p)), function(e, t) {
          if (JSON) {
            var n = T.fetch;
            if (n && !y.useXhr) n(t, {
              method: N,
              body: JSON.stringify(e),
              mode: "cors"
            });
            else if (XMLHttpRequest) {
              var a = new XMLHttpRequest;
              a.open(N, t), a.setRequestHeader("Content-type", "application/json"), a.send(JSON.stringify(e))
            }
          }
        }(l, p))
      }

      function i(e, t) {
        f || setTimeout(function() {
          !t && m.core || a()
        }, 500)
      }
      var e = function() {
        var n = l.createElement(k);
        n.src = h;
        var e = y[w];
        return !e && "" !== e || "undefined" == n[w] || (n[w] = e), n.onload = i, n.onerror = a, n.onreadystatechange = function(e, t) {
          "loaded" !== n.readyState && "complete" !== n.readyState || i(0, t)
        }, n
      }();
      y.ld < 0 ? l.getElementsByTagName("head")[0].appendChild(e) : setTimeout(function() {
        l.getElementsByTagName(k)[0].parentNode.appendChild(e)
      }, y.ld || 0)
    }
    try {
      m.cookie = l.cookie
    } catch (p) {}

    function t(e) {
      for (; e.length;) ! function(t) {
        m[t] = function() {
          var e = arguments;
          g || m.queue.push(function() {
            m[t].apply(m, e)
          })
        }
      }(e.pop())
    }
    var n = "track",
      r = "TrackPage",
      o = "TrackEvent";
    t([n + "Event", n + "PageView", n + "Exception", n + "Trace", n + "DependencyData", n + "Metric", n + "PageViewPerformance", "start" + r, "stop" + r, "start" + o, "stop" + o, "addTelemetryInitializer", "setAuthenticatedUserContext", "clearAuthenticatedUserContext", "flush"]), m.SeverityLevel = {
      Verbose: 0,
      Information: 1,
      Warning: 2,
      Error: 3,
      Critical: 4
    };
    var s = (d.extensionConfig || {}).ApplicationInsightsAnalytics || {};
    if (!0 !== d[I] && !0 !== s[I]) {
      var c = "onerror";
      t(["_" + c]);
      var u = T[c];
      T[c] = function(e, t, n, a, i) {
        var r = u && u(e, t, n, a, i);
        return !0 !== r && m["_" + c]({
          message: e,
          url: t,
          lineNumber: n,
          columnNumber: a,
          error: i
        }), r
      }, d.autoExceptionInstrumented = !0
    }
    return m
  }(y.cfg);

  function a() {
    y.onInit && y.onInit(n)
  }(T[t] = n).queue && 0 === n.queue.length ? (n.queue.push(a), n.trackPageView({})) : a()
}(window, document, {
  src: "https://js.monitor.azure.com/scripts/b/ai.2.min.js",
  crossOrigin: "anonymous",
  cfg: configObj
});

<script type="text/javascript" src="https://js.monitor.azure.com/scripts/b/ext/ai.clck.2.6.2.min.js"></script>

<body>
  <a href="https://google.com" data-custom-id="Google">Google</a> <br>
  <a href="https://youtube.com" data-custom-id="Youtube">Youtube</a> <br>
  <a href="https://spotify.com" data-custom-id="Spotify">Spotify</a> <br>
</body>

PFB the attempted query.

union pageViews, customEvents
| where timestamp > ago(4h)
| project timestamp, client_OS, client_Browser, client_City, operation_Id, name

How can one search the customEvents table for the same operation_id and add a separate column with just the names of all the links clicked?

Answer 1

Looks like you want to join the pageViews table with the customEvents table on operation_id, while from the latter you only want to take the list of names.

This should be achieved by two steps:

1. Create a mapping from operation_id to the list of names
2. Use lookup to join the data from pageViews and the mapping from the previous step

This is how you do it (I omitted columns that are not used for the join, just to make the example simpler):

// Synthetic data - don't copy this part
let pageViews =
    datatable (timestamp:datetime, operation_Id:string, client_City:string)
        [
            datetime(2022-01-20T06:50:10.343Z), "5cd8b209da4a4062ac11e73a52914e4f", "Chicago"
        ];
let customEvents =
    datatable (timestamp:datetime, operation_Id:string, name:string)
        [
            datetime(2022-01-20T06:50:18.873Z), "5cd8b209da4a4062ac11e73a52914e4f", "Spotify",
            datetime(2022-01-20T06:50:19.928Z), "5cd8b209da4a4062ac11e73a52914e4f", "Youtube"
        ];
// This is the real query
let namesPerOperationId =
    customEvents
    | summarize make_set(name) by operation_Id;
pageViews
| lookup (namesPerOperationId) on operation_Id

Result:

timestamp	operation_Id	client_City	set_name
2022-01-20 06:50:10.3430000	5cd8b209da4a4062ac11e73a52914e4f	Chicago	[ "Spotify", "Youtube" ]