public List <SpotsPerServer> GetAgentsPerServerByPool(int agentTypeArtifactId, int poolId) { List <SpotsPerServer> outputList = new List <SpotsPerServer>(); string SQL = @" SELECT AG.[ServerArtifactID], Count(AG.[ArtifactID]) AS [Count] FROM [Agent] AG WITH(NOLOCK) INNER JOIN [Artifact] A WITH(NOLOCK) ON AG.[ArtifactID] = A.[ArtifactID] INNER JOIN [ServerResourceGroup] S WITH(NOLOCK) ON AG.[ServerArtifactID] = S.[ResourceServerArtifactID] WHERE A.[DeleteFlag] = 0 AND AG.[AgentTypeArtifactID] = @AgentTypeArtifactID AND S.[ResourceGroupArtifactID] = @ResourceGroupArtifactID GROUP BY AG.[ServerArtifactID]"; SqlParameter agentTypeArtifactIdParam = new SqlParameter("@AgentTypeArtifactID", System.Data.SqlDbType.Char) { Value = agentTypeArtifactId }; SqlParameter poolIdParam = new SqlParameter("@ResourceGroupArtifactID", System.Data.SqlDbType.Char) { Value = poolId }; DataTable results = _eddsDbContext.ExecuteSqlStatementAsDataTable(SQL, new SqlParameter[] { agentTypeArtifactIdParam, poolIdParam }); if (results != null) { foreach (DataRow row in results.Rows) { int serverArtifactId; int count; if (!int.TryParse(row["ServerArtifactID"].ToString(), out serverArtifactId)) { throw new Exception("Unable to cast agent server ArtifactID returned from database to Int32"); } if (!int.TryParse(row["Count"].ToString(), out count)) { throw new Exception("Unable to cast agent count returned from database to Int32"); } outputList.Add(new SpotsPerServer() { AgentServerArtifactId = serverArtifactId, Spots = count }); } } return(outputList); }
public Theme getTheme() { DataRowCollection data; Theme theme = new Theme(); using (IRSAPIClient proxy = _helper.GetServicesManager().CreateProxy <IRSAPIClient>(ExecutionIdentity.System)) { int workspace = _workspaceID; IDBContext dbContext = _helper.GetDBContext(-1); string sql = $@"SELECT [Value] FROM [eddsdbo].[InstanceSetting] WHERE Name LIKE '%Theme UI (light/dark)%'"; data = dbContext.ExecuteSqlStatementAsDataTable(sql).Rows; foreach (DataRow item in data) { foreach (var d in item.ItemArray) { theme.textValue = (string)d; } } theme.value = theme.textValue.Equals("true") ? true : false; theme.textValue = theme.value ? "LIGHT" : "DARK"; } return(theme); }
public DataTable RetrieveApplicationWorkspaces(IDBContext eddsDbContext, Guid applicationGuid) { const string sql = @"DECLARE @appArtifactID INT SET @appArtifactID = (SELECT ArtifactID FROM ArtifactGuid WHERE ArtifactGuid = @appGuid) SELECT C.ArtifactID, C.Name FROM CaseApplication (NOLOCK) CA INNER JOIN eddsdbo.[ExtendedCase] C ON CA.CaseID = C.ArtifactID INNER JOIN eddsdbo.ResourceServer RS ON C.ServerID = RS.ArtifactID INNER JOIN eddsdbo.Artifact A (NOLOCK) ON C.ArtifactID = A.ArtifactID INNER JOIN eddsdbo.[ApplicationInstall] as AI on CA.CurrentApplicationInstallID = AI.ApplicationInstallID WHERE CA.ApplicationID = @appArtifactId AND AI.[Status] = 6 --Installed ORDER BY A.CreatedOn" ; var sqlParams = new List <SqlParameter> { new SqlParameter("@appGuid", SqlDbType.UniqueIdentifier) { Value = applicationGuid } }; return(eddsDbContext.ExecuteSqlStatementAsDataTable(sql, sqlParams)); }
public override Response PreMassOperation() { ConsoleEventHandlerComment console = new ConsoleEventHandlerComment(); kCura.EventHandler.Response retVal = new kCura.EventHandler.Response(); retVal.Success = true; retVal.Message = "Successful Pre Execute Operation method"; IDBContext dbContext = this.Helper.GetDBContext(this.Helper.GetActiveCaseID()); string sqlText = $" SELECT * FROM [Comment] WHERE ArtifactID IN (SELECT ARTIFACTID from[RESOURCE].[{ this.MassActionTableName}]) "; System.Data.DataRowCollection results = dbContext.ExecuteSqlStatementAsDataTable(sqlText).Rows; foreach (System.Data.DataRow row in results) { DTOs.RDO comme = new DTOs.RDO((int)row.ItemArray[0]); comme.ArtifactTypeGuids.Add(new Guid(ARTIFACT_TYPE)); comme.Fields.Add(new DTOs.FieldValue(new Guid(COMMENT_FIELD_GUID.ToString()), row.ItemArray[1])); console.insertJob(dbContext, this.Helper.GetAuthenticationManager().UserInfo.FullName, comme); DTOs.Choice choice = new DTOs.Choice(ERROR_TYPE_FIELD_GUID); comme.Fields.Add(new DTOs.FieldValue(TYPE_FIELD_GUID, choice)); using (kCura.Relativity.Client.IRSAPIClient client = this.Helper.GetServicesManager().CreateProxy <kCura.Relativity.Client.IRSAPIClient>(Relativity.API.ExecutionIdentity.System)) { client.APIOptions.WorkspaceID = this.Helper.GetActiveCaseID(); client.Repositories.RDO.UpdateSingle(comme); } } return(retVal); }
public override AgentsDesired GetAgentsDesired() { int agentCount = 0; string SQL = @" SELECT D.[WorkspaceArtifactID], D.[SetArtifactID] FROM [dtSearchIndexQueue] D WITH(NOLOCK) INNER JOIN [Case] C WITH(NOLOCK) ON D.[WorkspaceArtifactID] = C.[ArtifactID] WHERE C.[ResourceGroupArtifactID] = @ResourceGroupArtifactID"; SqlParameter poolIdParam = new SqlParameter("@ResourceGroupArtifactID", System.Data.SqlDbType.Int) { Value = AgentAgentResourcePool }; DataTable queueResults = _eddsDbContext.ExecuteSqlStatementAsDataTable(SQL, new SqlParameter[] { poolIdParam }); if (queueResults.Rows.Count > 0) { foreach (DataRow row in queueResults.Rows) { int workspaceId; int setId; if (!int.TryParse(row["WorkspaceArtifactID"].ToString(), out workspaceId)) { throw new Exception("Unable to cast WorkspaceArtifactID returned from database to Int32"); } if (!int.TryParse(row["SetArtifactID"].ToString(), out setId)) { throw new Exception("Unable to cast SetArtifactId returned from database to Int32"); } IDBContext workspaceDbContext = _agentHelper.GetDBContext(workspaceId); SQL = @" SELECT Count(ID) FROM [dtSearchSubIndex] WITH(NOLOCK) WHERE [dtSearchIndexID] = @DtSearchIndexID"; SqlParameter jobIdParam = new SqlParameter("@DtSearchIndexID", System.Data.SqlDbType.Char) { Value = setId }; agentCount += workspaceDbContext.ExecuteSqlStatementAsScalar <int>(SQL, new SqlParameter[] { jobIdParam }); } } return(new AgentsDesired() { Guid = Guid, RespectsResourcePool = RespectsResourcePool, Count = agentCount }); }
public DataRowCollection showCommentChilds(IDBContext dbcontext, int parentCommentId) { string comment_child_query = $@"SELECT [ArtifactID] FROM [{dbcontext.Database}].[EDDSDBO].[Comment] where RelatedComments = {parentCommentId}"; DataRowCollection data = dbcontext.ExecuteSqlStatementAsDataTable(comment_child_query).Rows; return(data); }
public DataRow RetrieveSingleInManagerQueueByArtifactId(IDBContext dbContext, Int32 artifactId, Int32 workspaceArtifactId) { var sql = String.Format(@" DECLARE @offset INT SET @offset = (SELECT DATEDIFF(HOUR,GetUTCDate(),GetDate())) SELECT Q.[ID] ,DATEADD(HOUR,@offset,Q.[TimeStampUTC]) [Added On] ,Q.WorkspaceArtifactID [Workspace Artifact ID] ,C.Name [Workspace Name] ,CASE Q.[QueueStatus] WHEN @notStartedStatusId THEN 'Waiting' WHEN @inProgressStatusId THEN 'In Progress' WHEN @errorStatusId THEN 'Error' END [Status] ,Q.AgentID [Agent Artifact ID] ,Q.ExtractorSetArtifactID [Job ID] FROM EDDSDBO.{0} Q INNER JOIN EDDS.EDDSDBO.ExtendedCase C ON Q.WorkspaceArtifactID = C.ArtifactID WHERE Q.ExtractorSetArtifactID = @artifactId AND Q.WorkspaceArtifactID = @workspaceArtifactId" , Constant.Tables.ManagerQueue); var sqlParams = new List <SqlParameter> { new SqlParameter("@notStartedStatusId", SqlDbType.Int) { Value = Constant.QueueStatus.NotStarted }, new SqlParameter("@inProgressStatusId", SqlDbType.Int) { Value = Constant.QueueStatus.InProgress }, new SqlParameter("@errorStatusId", SqlDbType.Int) { Value = Constant.QueueStatus.Error }, new SqlParameter("@artifactId", SqlDbType.Int) { Value = artifactId }, new SqlParameter("@workspaceArtifactId", SqlDbType.Int) { Value = workspaceArtifactId } }; var dt = dbContext.ExecuteSqlStatementAsDataTable(sql, sqlParams); if (dt.Rows.Count > 0) { return(dt.Rows[0]); } return(null); }
public DataTable RetrieveWorkspaces(IDBContext eddsDbContext) { const string sql = @" SELECT ArtifactId [CaseArtifactID] ,Name [CaseName] ,DBLocation [ServerName] FROM EDDSDBO.[ExtendedCase] WITH(NOLOCK) " ; return(eddsDbContext.ExecuteSqlStatementAsDataTable(sql)); }
public List <Comment> GetCommentsChild(int commentId) { string sql = $@"SELECT [ArtifactID] FROM [{DBContextSql.Database}].[EDDSDBO].[Comment] Where RelatedComments = {commentId}"; List <Comment> commentsChild = new List <Comment>(); DataRowCollection CommentsChildsList = DBContextSql.ExecuteSqlStatementAsDataTable(sql).Rows; foreach (DataRow childs in CommentsChildsList) { foreach (var childArtifacId in childs.ItemArray) { Comment comment = new Comment((int)childArtifacId); commentsChild.Add(comment); } } return(commentsChild); }
public dynamic ExecuteSql(IDBContext dbContext, Action action, DapiJson dapiJson) { List <SqlParameter> sqlParameters = new List <SqlParameter>(); foreach (Parameter param in action.Parameters) { SqlParameter sqlParam = new SqlParameter(param.Name, Mapper.Map <SqlDbType>(param.DataType)); if (param.Length > 0) { sqlParam.Size = param.Length ?? sqlParam.Size; } var dParam = dapiJson.Parameters.Find(d => d.name.Equals(param.Name, StringComparison.OrdinalIgnoreCase)); sqlParam.Value = dParam?.value; sqlParameters.Add(sqlParam); } dynamic result; try { switch (action.Outputs.OutputMode) { case OutputMode.NoReturnValue: result = dbContext.ExecuteNonQuerySQLStatement(action.SQL, sqlParameters); result = ""; break; case OutputMode.Scalar: result = dbContext.ExecuteSqlStatementAsScalar <dynamic>(action.SQL, sqlParameters); break; case OutputMode.Rowset: result = dbContext.ExecuteSqlStatementAsDataTable(action.SQL, sqlParameters); break; default: result = ""; break; } DidActionExecute = true; } catch (Exception ex) { DidActionExecute = false; result = ex.Message; throw; } return(result); }
public SmtpSettings GetSmptSettings(IDBContext context) { SmtpSettings settings = null; DataTable result = null; string sql = @"SELECT [Name],[Value] FROM [EDDS].[EDDSDBO].[Configuration] WITH (NOLOCK) WHERE Section='kCura.Notification'"; result = context.ExecuteSqlStatementAsDataTable(sql); if (result.Rows.Count > 0) { settings = new SmtpSettings(); // ENHANCEMENT: Good task to figure out a better way to map the values to SmtpSetting object (than using switch-statement) string name; string value; foreach (DataRow dataRow in result.Rows) { name = dataRow["Name"] as string; value = dataRow["Value"] as string; if ((name == "AuthenticationEmailFrom" || name == "SMTPPassword" || name == "SMTPPort" || name == "SMTPServer" || name == "SMTPUserName") && String.IsNullOrEmpty(value) == true) { throw new TextExtractor.Helpers.CustomExceptions.IncorrectSmtpSettingsException("There are one or more missing SMTP setting. "); } switch (name) { case "AuthenticationEmailFrom": settings.RelativityInstanceFromEmailAddress = value; break; case "SMTPPassword": settings.Password = value; break; case "SMTPPort": settings.Port = Convert.ToInt32(value); break; case "SMTPServer": settings.Server = value; break; case "SMTPUserName": settings.UserName = value; break; } } } return(settings); }
public List <Int32> RetrieveExtractorProfilesForField(IDBContext eddsDbContext, String fieldGuid1, String fieldGuid2, Int32 fieldArtifactId) { var sql = String.Format(@" DECLARE @FieldArtifactID1 INT, @FieldArtifactID2 INT DECLARE @MultiObjectTableName VARCHAR(50), @MultiObjectFieldName1 VARCHAR(50), @MultiObjectFieldName2 VARCHAR(50) SELECT @FieldArtifactID1 = ArtifactID FROM [EDDSDBO].[ArtifactGuid] WITH(NOLOCK) WHERE ArtifactGuid = @FieldGuid1 SELECT @FieldArtifactID2 = ArtifactID FROM [EDDSDBO].[ArtifactGuid] WITH(NOLOCK) WHERE ArtifactGuid = @FieldGuid2 SELECT @MultiObjectTableName = RelationalTableSchemaName, @MultiObjectFieldName1 = RelationalTableFieldColumnName1, @MultiObjectFieldName2 = RelationalTableFieldColumnName2 FROM [EDDSDBO].[ObjectsFieldRelation] WITH(NOLOCK) WHERE FieldArtifactId1 = @FieldArtifactID1 AND FieldArtifactId2 = @FieldArtifactID2 DECLARE @SQL NVARCHAR(MAX) SET @SQL = ' SELECT ' + @MultiObjectFieldName2 + ' [TextExtractorProfileArtifactID] FROM [EDDSDBO].[' + @MultiObjectTableName + '] WITH(NOLOCK) WHERE ' + @MultiObjectFieldName1 + ' = ' + CONVERT(VARCHAR(20), {0}) EXEC(@sql)" , fieldArtifactId); var sqlParams = new List <SqlParameter> { new SqlParameter("@FieldGuid1", SqlDbType.VarChar) { Value = fieldGuid1 }, new SqlParameter("@FieldGuid2", SqlDbType.VarChar) { Value = fieldGuid2 }, }; DataTable dtProfiles = eddsDbContext.ExecuteSqlStatementAsDataTable(sql, sqlParams); if (dtProfiles.Rows.Count > 0) { var list = dtProfiles.Rows.OfType <DataRow>().Select(dr => dr.Field <int>("TextExtractorProfileArtifactID")).ToList(); return(list); } return(null); }
//Returns the artifactId of a particular Guid public static Int32 FindArtifactIdByGuid(IDBContext workspaceContext, Guid guid) { var parameters = new List <SqlParameter> { new SqlParameter { ParameterName = "@Guid", SqlDbType = SqlDbType.UniqueIdentifier, Value = guid } }; var artifactIdQuery = workspaceContext.ExecuteSqlStatementAsDataTable(SQL.ArtifactIdByGuid, parameters); if (artifactIdQuery.Rows.Count < 1) { throw new Exception("Guid does not exist in workspace: " + guid); } return((Int32)artifactIdQuery.Rows[0]["ArtifactID"]); }
//Returns the first ArtifactId of the workspace that has the oldest install of the provided App Guid public static Int32 FindFirstWorkspaceWhereAppIsInstalled(IDBContext eddsDbContext, Guid appGuid) { var applicationGuid = appGuid; var parameters = new List <SqlParameter> { new SqlParameter { ParameterName = "@ApplicationGuid", SqlDbType = SqlDbType.UniqueIdentifier, Value = applicationGuid } }; var workspaceQuery = eddsDbContext.ExecuteSqlStatementAsDataTable(SQL.WorkspacesWhereAppIsInstalled, parameters); if (workspaceQuery.Rows.Count < 1) { throw new Exceptions.DisclaimerAppIsNotInstalledExcepetion(); } return((Int32)workspaceQuery.Rows[0]["CaseID"]); }
//Returns a list of UserIds that represent the users that need to accept the disclaimer public static IEnumerable <Int32> FindUsersWhoNeedToAcceptDisclaimer(IDBContext eddsDbContext, Models.Disclaimer disclaimer) { var usersWhoNeedToAccept = new List <Int32>(); var groupListFormattedForSql = String.Join(",", disclaimer.ApplicableGroups); var retrieveEligibleUsers = false; var query = string.Empty; var parameters = new List <SqlParameter> { new SqlParameter { ParameterName = "@DisclaimerArtifactID", SqlDbType = SqlDbType.Int, Value = disclaimer.DisclaimerId } }; if (disclaimer.AllUsers && disclaimer.ReacceptancePeriod > 0) { query = SQL.NonAccepptedAllUsersTimeCriteria; parameters.Add(new SqlParameter { ParameterName = "@ReacceptancePeriod", SqlDbType = SqlDbType.Int, Value = disclaimer.ReacceptancePeriod }); retrieveEligibleUsers = true; } else if (disclaimer.AllUsers) { query = SQL.NonAccepptedAllUsers; retrieveEligibleUsers = true; } else if (disclaimer.ReacceptancePeriod > 0 && disclaimer.ApplicableGroups.Any()) { query = String.Format(SQL.NonAcceptedUsersByGroupsTimeCriteria, groupListFormattedForSql); parameters.Add(new SqlParameter { ParameterName = "@ReacceptancePeriod", SqlDbType = SqlDbType.Int, Value = disclaimer.ReacceptancePeriod }); retrieveEligibleUsers = true; } else if (disclaimer.ApplicableGroups.Any()) { query = String.Format(SQL.NonAcceptedUsersByGroups, groupListFormattedForSql); retrieveEligibleUsers = true; } if (retrieveEligibleUsers) { usersWhoNeedToAccept.AddRange(eddsDbContext.ExecuteSqlStatementAsDataTable(query, parameters).AsEnumerable().Select(x => Int32.Parse(x["ArtifactID"].ToString())).ToList()); } return(usersWhoNeedToAccept); }
public IEnumerable <AuditComment> getCommentAudit(int commentId) { List <AuditComment> audit = new List <AuditComment>(); DataRowCollection data; using (IRSAPIClient proxy = _helper.GetServicesManager().CreateProxy <IRSAPIClient>(ExecutionIdentity.System)) { int workspace = _workspaceID; IDBContext dbContext = _helper.GetDBContext(workspace); string sql = $@"SELECT TOP (1000) [ArtifactId] ,[CommentId] ,[CreatedOn] ,[CreateByUserId] ,[CreatedByUserName] ,[ModifiedOn] ,[ModifiedByUserId] ,[ModifiedByUserName] ,[ReplysAmount] ,[comment] ,[type] FROM [EDDSDBO].[AuditComment] WHERE [CommentId] ={commentId};"; data = dbContext.ExecuteSqlStatementAsDataTable(sql).Rows; foreach (DataRow item in data) { AuditComment commentAudit = new AuditComment(); commentAudit.commentId = (int)item.ItemArray[1]; commentAudit.createdOn = (item.ItemArray[2]).ToString(); commentAudit.createByUserId = commentAudit.createdOn == string.Empty ? 0 : (int)item.ItemArray[3]; commentAudit.createdByUserName = commentAudit.createdOn == string.Empty ? "" : (string)item.ItemArray[4]; commentAudit.modifiedOn = (item.ItemArray[5]).ToString(); commentAudit.modifiedByUserId = commentAudit.createdOn == string.Empty ? (int)item.ItemArray[6] : 0; commentAudit.modifiedByUserName = commentAudit.createdOn == string.Empty ? (string)item.ItemArray[7] : ""; commentAudit.replysAmount = (int)item.ItemArray[8]; commentAudit.comment = (string)item.ItemArray[9]; commentAudit.type = (string)item.ItemArray[10]; audit.Add(commentAudit); } } return(audit); }
public DataTable RetrieveCommonRow(IDBContext eddsDbContext, Int32 agentId, String queueTableName) { var sql = String.Format(@"SELECT TOP 1 [WorkspaceArtifactID], [ExtractorSetArtifactID] FROM [EDDSDBO].{0} WHERE [AgentID] = @agentID" , queueTableName); var sqlParams = new List <SqlParameter> { new SqlParameter("@agentID", SqlDbType.Int) { Value = agentId } }; var table = eddsDbContext.ExecuteSqlStatementAsDataTable(sql, sqlParams); return(table); }
public bool IsOkToDelete(IDBContext eddsDbContext, string tempTable, string database) { string sql = $@" SELECT j.Status FROM [EDDSResource].[EDDSDBO].[{tempTable}] AS t WITH(NOLOCK),[{database}].[EDDSDBO].[{GetJobType()}] AS j WITH(NOLOCK) WHERE t.ArtifactID = j.ArtifactID "; var exportJobs = eddsDbContext.ExecuteSqlStatementAsDataTable(sql); var statusOkToDelete = new List <string> { Constant.Status.Job.NEW, Constant.Status.Job.CANCELLED, Constant.Status.Job.COMPLETED, Constant.Status.Job.COMPLETED_WITH_ERRORS, Constant.Status.Job.COMPLETED_WITH_ERRORS_AND_SKIPPED_DOCUMENTS, Constant.Status.Job.COMPLETED_WITH_SKIPPED_DOCUMENTS, Constant.Status.Job.ERROR, Constant.Status.Job.REVERTED, Constant.Status.Job.VALIDATED, }; return(exportJobs?.Rows == null || (from DataRow dataRow in exportJobs.Rows select(string) dataRow[0]).All(status => statusOkToDelete.Contains(status))); }
public override void Execute() { // Update Security Protocol ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12; // Get logger Relativity.API.IAPILog _logger = this.Helper.GetLoggerFactory().GetLogger().ForContext <Agent>(); // Get current Agent ID int agentArtifactId = this.AgentID; _logger.LogDebug("Audit Log Elastic Search, current Agent ID: {agentArtifactId}", agentArtifactId.ToString()); // Display initial message this.RaiseMessageNoLogging("Getting Instance Settings.", 10); // Get ES URI Instance Settings List <Uri> elasticUris = new List <Uri>(); try { string[] uris = this.Helper.GetInstanceSettingBundle().GetString("Relativity.AuditLogElasticSearch", "ElasticSearchUris").Split(';'); foreach (string uri in uris) { if (Uri.IsWellFormedUriString(uri, UriKind.Absolute)) { elasticUris.Add(new Uri(uri)); } else { _logger.LogError("Audit Log Elastic Search, Agent ({agentArtifactId}), Instance Settings error (ElasticSearchUri), single URI error ({uri})", agentArtifactId.ToString(), uri); this.RaiseMessageNoLogging(string.Format("Instance Settings error (ElasticSearchUri), single URI error ({0}).", uri), 1); return; } } } catch (Exception e) { _logger.LogError(e, "Audit Log Elastic Search, Agent ({agentArtifactId}), Instance Settings error (ElasticSearchUri)", agentArtifactId.ToString()); this.RaiseMessageNoLogging("Instance Settings error (ElasticSearchUri).", 1); return; } // Get ES authentication API Key Instance Settings string[] elasticApiKey = new string[] { "", "" }; try { string apiKey = this.Helper.GetInstanceSettingBundle().GetString("Relativity.AuditLogElasticSearch", "ElasticSearchApiKey"); if (apiKey.Length > 0) { if (apiKey.Split(':').Length == 2) { elasticApiKey = apiKey.Split(':'); } else { _logger.LogError("Audit Log Elastic Search, Agent ({agentArtifactId}), Instance Settings error (ElasticSearchApiKey), API Key format error ({apiKey})", agentArtifactId.ToString(), apiKey); this.RaiseMessageNoLogging(string.Format("Instance Settings error (ElasticSearchApiKey), API Key format error ({0}).", apiKey), 1); return; } } } catch (Exception e) { _logger.LogError(e, "Audit Log Elastic Search, Agent ({agentArtifactId}), Instance Settings error (ElasticSearchApiKey)", agentArtifactId.ToString()); this.RaiseMessageNoLogging("Instance Settings error (ElasticSearchApiKey).", 1); return; } // Get ES index prefix Instance Settings (must by lowercase) string elasticIndexPrefix = ""; try { elasticIndexPrefix = this.Helper.GetInstanceSettingBundle().GetString("Relativity.AuditLogElasticSearch", "ElasticSearchIndexPrefix").ToLower(); } catch (Exception e) { _logger.LogError(e, "Audit Log Elastic Search, Agent ({agentArtifactId}), Instance Settings error (ElasticSearchIndexPrefix)", agentArtifactId.ToString()); this.RaiseMessageNoLogging("Instance Settings error (ElasticSearchIndexPrefix).", 1); return; } // Get ES index number of replicas Instance Settings int elasticIndexReplicas = 1; try { elasticIndexReplicas = this.Helper.GetInstanceSettingBundle().GetInt("Relativity.AuditLogElasticSearch", "ElasticSearchIndexReplicas").Value; if (elasticIndexReplicas < 0) { elasticIndexReplicas = 1; } } catch (Exception e) { _logger.LogError(e, "Audit Log Elastic Search, Agent ({agentArtifactId}), Instance Settings error (ElasticSearchIndexReplicas)", agentArtifactId.ToString()); this.RaiseMessageNoLogging("Instance Settings error (ElasticSearchIndexReplicas).", 1); return; } // Get ES index number of shards Instance Settings int elasticIndexShards = 1; try { elasticIndexShards = this.Helper.GetInstanceSettingBundle().GetInt("Relativity.AuditLogElasticSearch", "ElasticSearchIndexShards").Value; if (elasticIndexShards < 0) { elasticIndexShards = 1; } } catch (Exception e) { _logger.LogError(e, "Audit Log Elastic Search, Agent ({agentArtifactId}), Instance Settings error (ElasticSearchIndexShards)", agentArtifactId.ToString()); this.RaiseMessageNoLogging("Instance Settings error (ElasticSearchIndexShards).", 1); return; } // Get ES synchronization threshold for one agent run int elasticSyncSize = 1000; try { elasticSyncSize = this.Helper.GetInstanceSettingBundle().GetInt("Relativity.AuditLogElasticSearch", "ElasticSearchSyncSize").Value; if (elasticSyncSize < 1000) { elasticSyncSize = 1000; } } catch (Exception e) { _logger.LogError(e, "Audit Log Elastic Search, Agent ({agentArtifactId}), Instance Settings error (ElasticSearchSyncSize)", agentArtifactId.ToString()); this.RaiseMessageNoLogging("Instance Settings error (ElasticSearchSyncSize).", 1); return; } // Get database context of the instance IDBContext instanceContext = Helper.GetDBContext(-1); // Check if management table exists try { int exists = instanceContext.ExecuteSqlStatementAsScalar <int>("IF OBJECT_ID('[eddsdbo].[" + this.tableName + "]', 'U') IS NOT NULL SELECT 1 ELSE SELECT 0"); _logger.LogDebug("Audit Log Elastic Search, Agent ({agentArtifactId}), application management table " + (exists == 1 ? "exists" : "does not exist"), agentArtifactId.ToString()); if (exists != 1) { _logger.LogError("Audit Log Elastic Search, Agent ({agentArtifactId}), application management table does not exist", agentArtifactId.ToString()); this.RaiseMessageNoLogging("Application management table does not exist.", 1); return; } } catch (Exception e) { _logger.LogError(e, "Audit Log Elastic Search, Agent ({agentArtifactId}), application management table existence check error", agentArtifactId.ToString()); } _logger.LogDebug("Audit Log Elastic Search, Agent ({agentArtifactId}), selecting Workspace", agentArtifactId.ToString()); this.RaiseMessageNoLogging("Selecting Workspace.", 10); // Check what needs to be done int workspaceId = -1; long auditRecordId = -1; int status = -1; instanceContext.BeginTransaction(); try { // Get workspace that was synchronized latest DataTable dataTable = instanceContext.ExecuteSqlStatementAsDataTable(@" SELECT TOP(1) [CaseArtifactID], [AuditRecordID], [Status] FROM [eddsdbo].[" + this.tableName + @"] WHERE [AgentArtifactID] IS NULL ORDER BY [Status] ASC, [LastUpdated] ASC "); // If there is no workspace check if table is empty and if it is, delete it _logger.LogDebug("Audit Log Elastic Search, Agent ({agentArtifactId}), Workspace selection row count: {count}", agentArtifactId.ToString(), dataTable.Rows.Count.ToString()); if (dataTable.Rows.Count == 0) { int count = instanceContext.ExecuteSqlStatementAsScalar <int>("SELECT COUNT(*) FROM [eddsdbo].[" + this.tableName + "]"); _logger.LogDebug("Audit Log Elastic Search, Agent ({agentArtifactId}), application management table row count: {count}", agentArtifactId.ToString(), count.ToString()); // If there are no rows in the application management table better to drop it if (count == 0) { instanceContext.ExecuteNonQuerySQLStatement("DROP TABLE [eddsdbo].[" + this.tableName + "]"); _logger.LogDebug("Audit Log Elastic Search, Agent ({agentArtifactId}), application management table was deleted", agentArtifactId.ToString()); } } // Else we have workspace to work with else { DataRow dataRow = dataTable.Rows[0]; workspaceId = Convert.ToInt32(dataRow["CaseArtifactID"]); auditRecordId = Convert.ToInt64(dataRow["AuditRecordID"]); status = Convert.ToInt32(dataRow["Status"]); // Update the application management table with Agent ID lock SqlParameter agentArtifactIdParam = new SqlParameter("@agentArtifactId", agentArtifactId); SqlParameter workspaceIdParam = new SqlParameter("@workspaceId", workspaceId); instanceContext.ExecuteNonQuerySQLStatement("UPDATE [eddsdbo].[" + this.tableName + "] SET [AgentArtifactID] = @agentArtifactId WHERE [CaseArtifactID] = @workspaceId", new SqlParameter[] { agentArtifactIdParam, workspaceIdParam }); } instanceContext.CommitTransaction(); } catch (Exception e) { instanceContext.RollbackTransaction(); _logger.LogError(e, "Audit Log Elastic Search, Agent ({agentArtifactId}), application management table querying error", agentArtifactId.ToString()); this.RaiseMessageNoLogging("Application management table querying error.", 1); return; } // If we have Workspace ID we have to do something if (workspaceId > 0) { // Construct ES index name string elasticIndexName = elasticIndexPrefix + workspaceId.ToString(); // Construct connector to ES cluster Nest.ElasticClient elasticClient = null; try { Elasticsearch.Net.StaticConnectionPool pool = new Elasticsearch.Net.StaticConnectionPool(elasticUris, true); elasticClient = new Nest.ElasticClient(new Nest.ConnectionSettings(pool).DefaultIndex(elasticIndexName).ApiKeyAuthentication(elasticApiKey[0], elasticApiKey[1]).EnableHttpCompression()); } catch (Exception e) { this.releaseAgentLock(agentArtifactId, auditRecordId, workspaceId); _logger.LogError(e, "Audit Log Elastic Search, Agent ({agentArtifactId}) Elastic Search connection call error ({elasticUris}, {indexName})", agentArtifactId.ToString(), string.Join(";", elasticUris.Select(x => x.ToString()).ToArray()), elasticIndexName); this.RaiseMessageNoLogging(string.Format("Elastic Search connection call error ({0}, {1}).", string.Join(";", elasticUris.Select(x => x.ToString()).ToArray()), elasticIndexName), 1); return; } // Check ES cluster connection Nest.PingResponse pingResponse = elasticClient.Ping(); if (pingResponse.IsValid) { _logger.LogDebug("Audit Log Elastic Search, Agent ({agentArtifactId}), Ping succeeded ({elasticUris}, {indexName})", agentArtifactId.ToString(), string.Join(";", elasticUris.Select(x => x.ToString()).ToArray()), elasticIndexName); } else { this.releaseAgentLock(agentArtifactId, auditRecordId, workspaceId); _logger.LogError("Audit Log Elastic Search, Agent ({agentArtifactId}), Ping failed, check cluster health and connection settings ({elasticUris}, {indexName}, {elasticError})", agentArtifactId.ToString(), string.Join(";", elasticUris.Select(x => x.ToString()).ToArray()), elasticIndexName, pingResponse.DebugInformation); this.RaiseMessageNoLogging(string.Format("Elastic Search ping failed, check cluster health and connection settings ({0}, {1}, {2}).", string.Join(";", elasticUris.Select(x => x.ToString()).ToArray()), elasticIndexName, pingResponse.DebugInformation), 1); return; } switch (status) { // If the status is 0 we will be deleting ES index case 0: _logger.LogDebug("Audit Log Elastic Search, Agent ({agentArtifactId}), deleting ES index ({indexName})", agentArtifactId.ToString(), elasticIndexName); this.RaiseMessageNoLogging(string.Format("Deleting ES index ({0}).", elasticIndexName), 10); // Delete ES index try { Nest.DeleteIndexResponse deleteIndexResponse = elasticClient.Indices.Delete(elasticIndexName); if (deleteIndexResponse.Acknowledged) { _logger.LogDebug("Audit Log Elastic Search, Agent ({agentArtifactId}), Elastic Search index deleted ({elasticUris}, {indexName})", agentArtifactId.ToString(), string.Join(";", elasticUris.Select(x => x.ToString()).ToArray()), elasticIndexName); } else { this.releaseAgentLock(agentArtifactId, auditRecordId, workspaceId); _logger.LogError("Audit Log Elastic Search, Agent ({agentArtifactId}), Elastic Search index deletion error ({elasticUris}, {indexName})", agentArtifactId.ToString(), string.Join(";", elasticUris.Select(x => x.ToString()).ToArray()), elasticIndexName); this.RaiseMessageNoLogging(string.Format("Elastic Search index deletion error ({0}, {1}).", string.Join(";", elasticUris.Select(x => x.ToString()).ToArray()), elasticIndexName), 1); return; } } catch (Exception e) { this.releaseAgentLock(agentArtifactId, auditRecordId, workspaceId); _logger.LogError(e, "Audit Log Elastic Search, Agent ({agentArtifactId}) Elastic Search deletion call error ({indexName})", agentArtifactId.ToString(), elasticIndexName); this.RaiseMessageNoLogging(string.Format("Elastic Search deletion call error ({0}).", elasticIndexName), 1); return; } // Delete related row from the application management table try { SqlParameter workspaceIdParam = new SqlParameter("@workspaceId", workspaceId); instanceContext.ExecuteNonQuerySQLStatement("DELETE FROM [eddsdbo].[" + this.tableName + "] WHERE [Status] = 0 AND [CaseArtifactID] = @workspaceId", new SqlParameter[] { workspaceIdParam }); } catch (Exception e) { this.releaseAgentLock(agentArtifactId, auditRecordId, workspaceId); _logger.LogError(e, "Audit Log Elastic Search, Agent ({agentArtifactId}), application management table delete error", agentArtifactId.ToString()); this.RaiseMessageNoLogging("Application management table delete error.", 1); return; } break; // If the status is 1 we will be synchronizing Audit Log with ES index case 1: _logger.LogDebug("Audit Log Elastic Search, Agent ({agentArtifactId}), synchronizing Audit Log of Workspace ({workspaceId}) to ES index ({indexName})", agentArtifactId.ToString(), workspaceId.ToString(), elasticIndexName); this.RaiseMessageNoLogging(string.Format("Synchronizing Audit Log of Workspace ({0}) to ES index ({1})", workspaceId.ToString(), elasticIndexName), 10); // If there is no records synchronized yet, we have to create ES index first if (auditRecordId == 0) { // Create ES index try { Nest.CreateIndexResponse createIndexResponse = elasticClient.Indices.Create(elasticIndexName, c => c.Settings(s => s.NumberOfShards(elasticIndexShards).NumberOfReplicas(elasticIndexReplicas)).Map <AuditRecord>(m => m.AutoMap())); if (createIndexResponse.Acknowledged) { _logger.LogDebug("Audit Log Elastic Search, Agent ({agentArtifactId}), Elastic Search index created ({elasticUris}, {indexName})", agentArtifactId.ToString(), string.Join(";", elasticUris.Select(x => x.ToString()).ToArray()), elasticIndexName); } else { this.releaseAgentLock(agentArtifactId, auditRecordId, workspaceId); _logger.LogError("Audit Log Elastic Search, Agent ({agentArtifactId}), Elastic Search index creation error ({elasticUris}, {indexName}, {serverError})", agentArtifactId.ToString(), string.Join(";", elasticUris.Select(x => x.ToString()).ToArray()), elasticIndexName, createIndexResponse.ServerError.ToString()); this.RaiseMessageNoLogging(string.Format("Elastic Search index creation error ({0}, {1}).", string.Join(";", elasticUris.Select(x => x.ToString()).ToArray()), elasticIndexName), 1); return; } } catch (Exception e) { this.releaseAgentLock(agentArtifactId, auditRecordId, workspaceId); _logger.LogError(e, "Audit Log Elastic Search, Agent ({agentArtifactId}) Elastic Search index creation call error ({elasticUris}, {indexName})", agentArtifactId.ToString(), string.Join(";", elasticUris.Select(x => x.ToString()).ToArray()), elasticIndexName); this.RaiseMessageNoLogging(string.Format("Elastic Search index creation call error ({0}, {1}).", string.Join(";", elasticUris.Select(x => x.ToString()).ToArray()), elasticIndexName), 1); return; } } // Get database context of the given workspace IDBContext workspaceContext = Helper.GetDBContext(workspaceId); // Synchronize until threshold is reached int syncCount = 0; while (syncCount < elasticSyncSize) { try { // Get Audit Log to synchronize SqlParameter auditRecordIdParam = new SqlParameter("@auditRecordId", auditRecordId); DataTable dataTable = workspaceContext.ExecuteSqlStatementAsDataTable(@" SELECT TOP (1000) [AuditRecord].[ID], [AuditRecord].[TimeStamp], [AuditRecord].[ArtifactID], [AuditRecord].[Action] AS [ActionID], [AuditAction].[Action], [AuditRecord].[UserID], [AuditUser].[FullName] AS [User], [AuditRecord].[ExecutionTime], [AuditRecord].[Details], [AuditRecord].[RequestOrigination], [AuditRecord].[RecordOrigination] FROM [EDDSDBO].[AuditRecord] WITH (NOLOCK) JOIN [EDDSDBO].[AuditUser] WITH (NOLOCK) ON [AuditRecord].[UserID] = [AuditUser].[UserID] JOIN [EDDSDBO].[AuditAction] WITH (NOLOCK) ON [AuditRecord].[Action] = [AuditAction].[AuditActionID] WHERE [AuditRecord].[ID] > @auditRecordId ORDER BY [AuditRecord].[ID] ASC ", new SqlParameter[] { auditRecordIdParam }); // If there is nothing to synchronize end _logger.LogDebug("Audit Log Elastic Search, Agent ({agentArtifactId}), Audit Log row count to synchronize: {count}", agentArtifactId.ToString(), dataTable.Rows.Count.ToString()); if (dataTable.Rows.Count == 0) { // Log end of Agent execution this.releaseAgentLock(agentArtifactId, auditRecordId, workspaceId); _logger.LogDebug("Audit Log Elastic Search, Agent ({agentArtifactId}), completed, nothing to synchronize", agentArtifactId.ToString()); this.RaiseMessageNoLogging("Completed.", 10); return; } // Else synchronize workspace Audit Log with ES index else { // Synchronizing workspace Audit Log with ES index List <AuditRecord> auditRecords = new List <AuditRecord>(); long newAuditRecordId = auditRecordId; for (int i = 0; i < dataTable.Rows.Count; i++) { // Read Audit Log data AuditRecord auditRecord = new AuditRecord(); DataRow dataRow = dataTable.Rows[i]; auditRecord.AuditRecordId = Convert.ToInt64(dataRow["ID"]); auditRecord.TimeStamp = Convert.ToDateTime(dataRow["TimeStamp"]); auditRecord.ArtifactId = Convert.ToInt32(dataRow["ArtifactID"]); auditRecord.ActionId = Convert.ToInt32(dataRow["ActionID"]); auditRecord.Action = Convert.ToString(dataRow["Action"]); auditRecord.UserId = Convert.ToInt32(dataRow["UserID"]); auditRecord.User = Convert.ToString(dataRow["User"]); auditRecord.ExecutionTime = dataRow["ExecutionTime"] is DBNull ? default : Convert.ToInt32(dataRow["ExecutionTime"]); auditRecord.Details = dataRow["Details"] is DBNull ? default : Convert.ToString(dataRow["Details"]); auditRecord.RequestOrigination = dataRow["RequestOrigination"] is DBNull ? default : Convert.ToString(dataRow["RequestOrigination"]); auditRecord.RecordOrigination = dataRow["RecordOrigination"] is DBNull ? default : Convert.ToString(dataRow["RecordOrigination"]); auditRecords.Add(auditRecord); // Record last Audit Log ID if (newAuditRecordId < auditRecord.AuditRecordId) { newAuditRecordId = auditRecord.AuditRecordId; } // Index data in threshold is reached or we are at the last row if (auditRecords.Count >= 500 || i + 1 >= dataTable.Rows.Count) { try { Nest.BulkResponse bulkResponse = elasticClient.Bulk(b => b.Index(elasticIndexName).IndexMany(auditRecords, (descriptor, s) => descriptor.Id(s.AuditRecordId.ToString()))); if (!bulkResponse.Errors) { auditRecords.Clear(); _logger.LogDebug("Audit Log Elastic Search, Agent ({agentArtifactId}), documents synchronized to Elastic Serach index ({indexName})", agentArtifactId.ToString(), elasticIndexName); } else { this.releaseAgentLock(agentArtifactId, auditRecordId, workspaceId); foreach (Nest.BulkResponseItemBase itemWithError in bulkResponse.ItemsWithErrors) { _logger.LogError("Audit Log Elastic Search, Agent ({agentArtifactId}), Elastic Serach bulk index error to index {indexName} ({elasticUris}) on document {docIs}:{docError}", agentArtifactId.ToString(), elasticIndexName, string.Join(";", elasticUris.Select(x => x.ToString()).ToArray()), itemWithError.Id, itemWithError.Error.ToString()); } this.RaiseMessageNoLogging(string.Format("Elastic Serach bulk index error to index {0} ({1}).", elasticIndexName, string.Join(";", elasticUris.Select(x => x.ToString()).ToArray())), 1); return; } } catch (Exception e) { this.releaseAgentLock(agentArtifactId, auditRecordId, workspaceId); _logger.LogError(e, "Audit Log Elastic Search, Agent ({agentArtifactId}) Elastic Search bulk index call error ({elasticUris}, {indexName})", agentArtifactId.ToString(), string.Join(";", elasticUris.Select(x => x.ToString()).ToArray()), elasticIndexName); this.RaiseMessageNoLogging(string.Format("Elastic Search bulk index call error ({0}, {1}).", string.Join(";", elasticUris.Select(x => x.ToString()).ToArray()), elasticIndexName), 1); return; } } } // After successful indexing assign new Audit Log ID auditRecordId = newAuditRecordId; } }
public DataTable RetrieveNextBatchInWorkerQueue(IDBContext eddsDbContext, Int32 agentId, Int32 batchSize, String uniqueTableName, Int32 resourceServerArtifactId) { var sql = String.Format(@" BEGIN TRAN IF NOT OBJECT_ID('EDDSDBO.{1}') IS NULL BEGIN DROP TABLE EDDSDBO.{1} END CREATE TABLE EDDSDBO.{1}(ID INT) DECLARE @workspaceArtifactID INT DECLARE @extractorSetArtifactID INT SELECT TOP 1 @workspaceArtifactID = [WorkspaceArtifactID], @extractorSetArtifactID = [ExtractorSetArtifactID] FROM EDDSDBO.{0} WHERE QueueStatus = @notStartedQueueStatus AND [WorkspaceArtifactID] IN ( SELECT ArtifactID FROM [EDDSDBO].[ExtendedCase] WITH(NOLOCK) WHERE ResourceGroupArtifactID IN ( SELECT ResourceGroupArtifactID FROM [EDDSDBO].[ServerResourceGroup] WITH(NOLOCK) WHERE ResourceServerArtifactID = @resourceServerArtifactId) ) ORDER BY [ID] ASC INSERT INTO EDDSDBO.{1}(ID) SELECT TOP (@batchSize) ID FROM EDDSDBO.{0} WITH(UPDLOCK,READPAST) WHERE [WorkspaceArtifactID] = @workspaceArtifactID AND [ExtractorSetArtifactID] = @extractorSetArtifactID AND [QueueStatus] = @notStartedQueueStatus AND [DocumentArtifactID] NOT IN ( SELECT DISTINCT [DocumentArtifactID] FROM EDDSDBO.{0} WHERE [AgentID] IS NOT NULL ) ORDER BY [ID] ASC, [ExtractorSetArtifactID] ASC, [DocumentArtifactID] ASC UPDATE S SET AgentID = @agentID, QueueStatus = @inProgressQueueStatus FROM EDDSDBO.{1} B INNER JOIN EDDSDBO.{0} S ON B.ID = S.ID COMMIT SELECT S.ID [QueueID] ,S.[WorkspaceArtifactID] ,S.[QueueStatus] ,S.[AgentID] ,S.[ExtractorSetArtifactID] ,S.[DocumentArtifactID] ,S.[ExtractorProfileArtifactID] ,S.[SourceLongTextFieldArtifactID] FROM EDDSDBO.{1} B INNER JOIN EDDSDBO.{0} S ON B.ID = S.ID " , Constant.Tables.WorkerQueue, uniqueTableName); var sqlParams = new List <SqlParameter> { new SqlParameter("@agentID", SqlDbType.Int) { Value = agentId }, new SqlParameter("@batchSize", SqlDbType.Int) { Value = batchSize }, new SqlParameter("@notStartedQueueStatus", SqlDbType.Int) { Value = Constant.QueueStatus.NotStarted }, new SqlParameter("@inProgressQueueStatus", SqlDbType.Int) { Value = Constant.QueueStatus.InProgress }, new SqlParameter("@resourceServerArtifactID", SqlDbType.Int) { Value = resourceServerArtifactId } }; return(eddsDbContext.ExecuteSqlStatementAsDataTable(sql, sqlParams)); }
public DataTable RetrieveNextBatchInManagerQueue(IDBContext eddsDbContext, Int32 batchSize, Int32 agentId) { var sql = String.Format(@" -- Assigns a batch of rows to the Agent, respecting resource pool UPDATE [EDDSDBO].[{0}] SET [AgentID] = @agentID, [QueueStatus] = @inProgressQueueStatus FROM (SELECT TOP (@batchSize) [ID], [AgentID], [QueueStatus], [WorkspaceArtifactID] FROM [EDDSDBO].[TextExtractor_ManagerQueue] WITH (UPDLOCK, READPAST) WHERE [QueueStatus] = @notStartedQueueStatus AND [WorkspaceArtifactID] IN ( SELECT [ArtifactID] AS 'WorkspacesInResourceGroup' FROM [EDDSDBO].[ExtendedCase] WHERE [ResourceGroupArtifactID] IN ( SELECT [ResourceGroupArtifactID] AS 'AgentsResourceGroup' FROM [EDDSDBO].[ServerResourceGroup] WHERE [ResourceServerArtifactID] = ( SELECT [ServerArtifactID] FROM [EDDSDBO].[Agent] WHERE ArtifactID = @agentID ) ) ) ORDER BY [TimeStampUTC] ) innerTable WHERE innerTable.[ID] = [EDDSDBO].[{0}].[ID] SELECT TOP (@batchSize) [ID], [WorkspaceArtifactID], [QueueStatus], [SavedSearchArtifactID], [ExtractorSetArtifactID], [ExtractorProfileArtifactID], [SourceLongTextFieldArtifactID] FROM [EDDSDBO].[{0}] WITH (NOLOCK) WHERE [AgentID] = @agentID ORDER BY [TimeStampUTC] " , Constant.Tables.ManagerQueue); var sqlParams = new List <SqlParameter> { new SqlParameter("@agentID", SqlDbType.Int) { Value = agentId }, new SqlParameter("@batchSize", SqlDbType.Int) { Value = batchSize }, new SqlParameter("@notStartedQueueStatus", SqlDbType.Int) { Value = Constant.QueueStatus.NotStarted }, new SqlParameter("@inProgressQueueStatus", SqlDbType.Int) { Value = Constant.QueueStatus.InProgress } }; return(eddsDbContext.ExecuteSqlStatementAsDataTable(sql, sqlParams)); }
public override void Execute() { int contador = 0; //Get the current Agent artifactID Int32 agentArtifactID = this.AgentID; //Get a dbContext for the EDDS database IDBContext eddsDBContext = this.Helper.GetDBContext(-1); List <int> worksapcesID = new List <int>(); Comment comment = new Comment(); try { using (IRSAPIClient proxy = Helper.GetServicesManager().CreateProxy <IRSAPIClient>(ExecutionIdentity.System)) { RelativityAppCore.BLL.Service.RSAPIService.CommentRSAPIService commentRSAPIService = new CORE.BLL.Service.RSAPIService.CommentRSAPIService(proxy); IDBContext DBContext = this.Helper.GetDBContext(-1); DataRowCollection data = DBContext.ExecuteSqlStatementAsDataTable(Queries.GetWorkspacesWithApp).Rows; RaiseMessage("Find for the workspaces with the application", 1); foreach (var item in data[0].ItemArray) { worksapcesID.Add((int)item); } foreach (var item in worksapcesID) { proxy.APIOptions.WorkspaceID = item; DTOs.Query <DTOs.RDO> query = new DTOs.Query <DTOs.RDO>(); DTOs.QueryResultSet <DTOs.RDO> results = new DTOs.QueryResultSet <DTOs.RDO>(); query.ArtifactTypeGuid = new Guid(comment.ARTIFACT_TYPE); query.Fields = DTOs.FieldValue.AllFields; // query.Condition = new BooleanCondition(new Guid(guidSet), BooleanConditionEnum.EqualTo, false); try { results = proxy.Repositories.RDO.Query(query); foreach (var c in results.Results) { RaiseMessage($"verifying if the comment: {c.Artifact.ArtifactID} already has the thumnails", 1); DTOs.RDO commentDto = new DTOs.RDO(c.Artifact.ArtifactID); commentDto.ArtifactTypeGuids.Add(new Guid(comment.ARTIFACT_TYPE)); commentDto.Fields = DTOs.FieldValue.AllFields; commentDto.Fields.Add(new DTOs.FieldValue(new Guid(guidSet))); commentDto = proxy.Repositories.RDO.ReadSingle(c.Artifact.ArtifactID); //bool fieldValue = (bool)commentDto[new Guid(guidSet)].Value; string image = (string)commentDto[new Guid(Image_guid_field)].Value; if (!string.IsNullOrEmpty(image)) { RaiseMessage($"Creating Thumbnails for the comment {c.Artifact.ArtifactID}", 1); string thumbnail = getImage(c.Artifact.ArtifactID, this.Helper.GetDBContext(proxy.APIOptions.WorkspaceID)); commentDto.Fields.Add(new DTOs.FieldValue(new Guid(thumbnailsImage), thumbnail)); commentDto.Fields.Add(new DTOs.FieldValue(new Guid(guidSet), true)); proxy.Repositories.RDO.UpdateSingle(commentDto); } else { contador = contador + 1; commentDto.Fields.Add(new DTOs.FieldValue(new Guid(guidSet), false)); proxy.Repositories.RDO.UpdateSingle(commentDto); } } } catch (Exception) { throw; } } } RaiseMessage($"There are {contador} comments without thumbnail", 1); } catch (System.Exception ex) { //Your Agent caught an exception this.RaiseError(ex.Message, ex.Message); } }