Name

global.BuildSuggestionsFromLogTable

Description

When a customer upgrades to new instance, this script will prepopulate searches into search analytics and suggestions tables from the text_search table or sp_log table

Script

var BuildSuggestionsFromLogTable = Class.create();
BuildSuggestionsFromLogTable.prototype = {
  initialize: function(maxRecordsToBuild) {
      this.max = maxRecordsToBuild;
  },

  buildPortalSuggestions: function(appId, dataSource, searchSources, searchSourceSysIds) {
      var appTable = "sp_portal";
      this._seedAnalyticsAndBuildSuggestions(appId, appTable, dataSource, searchSources, searchSourceSysIds);
  },

  buildScopedSuggestions: function(configId) {
      var appTable = "sys_scope";
      var dataSource = "text_search";
      var mobileAppId = this._getApplicationId(configId);
      var searchSourceSysIds = this._getApplicationSearchSourceSysIds(configId);
      var searchSources = this._getSearchSources(searchSourceSysIds);
      this._seedAnalyticsAndBuildSuggestions(mobileAppId, appTable, dataSource, searchSources, searchSourceSysIds);
  },

  _seedAnalyticsAndBuildSuggestions: function(appId, appTable, dataSource, searchSources, searchSourceSysIds) {
      var currentDateTime = new GlideDateTime();
      var suggestionService = new SearchSuggestionService();
      var count = 0;
      var done = false;

      var dataSourceGr = new GlideRecord(dataSource);
      dataSourceGr.addQuery('sys_created_on', "<", currentDateTime);
      dataSourceGr.addQuery("table", "IN", searchSources);
      dataSourceGr.orderByDesc("sys_created_on");
      dataSourceGr.query();
      dataSourceGr.next();

      var totalRecordsToProcess = Math.min(dataSourceGr.getRowCount(), this.max);
      //PROCESS BUILDING SUGGESTIONS IN CHUNKS OF 5000 RECORDS
      var recordsToProcess = Math.min(totalRecordsToProcess, 5000);
      while (count < this.max && !done) {
          if (count + recordsToProcess >= totalRecordsToProcess) {
              dataSourceGr.chooseWindow(count, totalRecordsToProcess);
              done = true;
          } else
              dataSourceGr.chooseWindow(count, count + recordsToProcess);

          dataSourceGr.query();

          while (dataSourceGr.next()) {
              var searchTerm = "";
              var results = 0;

              if (dataSource == "text_search") {
                  searchTerm = dataSourceGr.getValue('search_term');
                  results = parseInt(dataSourceGr.getValue('results'));
              } else if (dataSource == "sp_log") {
                  searchTerm = dataSourceGr.getValue('text');
                  results = parseInt(dataSourceGr.getValue('count'));
              }

              var user = dataSourceGr.getValue('user');
              var sessionId = dataSourceGr.getValue('session_id');

              this._insertSearchEvents(appId, appTable, searchTerm, user, sessionId, results, searchSourceSysIds);
              suggestionService.build();
              count = count + recordsToProcess;
          }
      }
  },

  _insertSearchEvents: function(appId, appTable, searchTerm, user, sessionId, results, searchSourceSysIds) {
      var searchEventGr = new GlideRecord('sys_search_event');
      var userLanguage = this._getUserLanguage(user);

      searchEventGr.initialize();
      searchEventGr.application_id = appId;
      searchEventGr.application_table = appTable;
      searchEventGr.setValue('search_query', searchTerm);
      searchEventGr.setValue('user', user);
      searchEventGr.setValue('session_id', sessionId);
      searchEventGr.setValue('has_results', results);
      searchEventGr.setValue('language', userLanguage);
      searchEventGr.setValue('click_rank', 0);

      var sysId = searchEventGr.insert();
      for (var i = 0; i < searchSourceSysIds.length; i++) {
          this._insertSearchSourceEvents(sysId, searchSourceSysIds[i], searchTerm);
      }
  },

  _insertSearchSourceEvents: function(sysId, sourceId, searchTerm) {
      var searchSourceEventGr = new GlideRecord('sys_search_source_event');
      searchSourceEventGr.initialize();
      searchSourceEventGr.search_event = sysId;
      searchSourceEventGr.source = sourceId;
      searchSourceEventGr.search_query = searchTerm;
      searchSourceEventGr.has_results = false;
      searchSourceEventGr.insert();
  },

  _getApplicationId: function(searchContextConfigId) {
      var appId;
      var searchContextConfigGR = new GlideRecord('sys_search_context_config');
      searchContextConfigGR.addQuery("sys_id", searchContextConfigId);
      searchContextConfigGR.query();

      if (searchContextConfigGR.next())
          appId = searchContextConfigGR.getValue("application_id");

      return appId;
  },

  _getApplicationSearchSourceSysIds: function(searchContextConfigId) {
      var sysIds = [];
      var searchSourceGR = new GlideRecord('m2m_search_context_config_search_source');
      searchSourceGR.addQuery('search_context_config', searchContextConfigId);
      searchSourceGR.query();

      while (searchSourceGR.next()) {
          sysIds.push(searchSourceGR.getValue("source") + "");
      }

      return sysIds;
  },

  _getSearchSources: function(searchSourceSysIds) {
      var sources = [];
      var gr = new GlideRecord('sys_search_source');
      gr.addQuery('sys_id', 'IN', searchSourceSysIds);
      gr.query();

      while (gr.next()) {
          sources.push(gr.getValue("source_table"));
      }

      return sources;
  },

  _getUserLanguage: function(userId) {
      var userGr = new GlideRecord('sys_user');
      userGr.addQuery('sys_id', userId);
      userGr.query();
      userGr.next();

      var answer = userGr.getValue('preferred_language');
      if (answer) {
          gs.info("User Language: " + answer);
          return answer;
      } else {
          var defaultLanguage = gs.getProperty("glide.sys.language");
          gs.info("Default Language: " + defaultLanguage);
          return defaultLanguage;
      }
  },

  type: 'BuildSuggestionsFromLogTable'
};

Sys ID

347608540f2133005203cbb2ff767e1b

Offical Documentation

Official Docs: