public static dynamic GetActivityLevelLogsCore(Logging logging) { string AppInsightsWorkspaceId = System.Environment.GetEnvironmentVariable("AppInsightsWorkspaceId"); using var client = new HttpClient(); string token = Shared.Azure.AzureSDK.GetAzureRestApiToken("https://api.applicationinsights.io"); client.DefaultRequestHeaders.Accept.Clear(); client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json")); client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", token); TaskMetaDataDatabase TMD = new TaskMetaDataDatabase(); using SqlConnection _conRead = TMD.GetSqlConnection(); //Get Last Request Date var MaxTimesGen = _conRead.QueryWithRetry(@" select max([timestamp]) maxtimestamp from ActivityLevelLogs"); DateTimeOffset MaxLogTimeGenerated = DateTimeOffset.UtcNow.AddDays(-30); foreach (var datafactory in MaxTimesGen) { if (datafactory.maxtimestamp != null) { MaxLogTimeGenerated = ((DateTimeOffset)datafactory.maxtimestamp).AddMinutes(-5); } //string workspaceId = datafactory.LogAnalyticsWorkspaceId.ToString(); Dictionary <string, object> KqlParams = new Dictionary <string, object> { { "MaxLogTimeGenerated", MaxLogTimeGenerated.ToString("yyyy-MM-dd HH:mm:ss.ff K") } //{"SubscriptionId", ((string)datafactory.SubscriptionUid.ToString()).ToUpper()}, //{"ResourceGroupName", ((string)datafactory.ResourceGroup.ToString()).ToUpper() }, //{"DataFactoryName", ((string)datafactory.Name.ToString()).ToUpper() }, //{"DatafactoryId", datafactory.Id.ToString() } }; string KQL = System.IO.File.ReadAllText(Shared.GlobalConfigs.GetStringConfig("KQLTemplateLocation") + "GetActivityLevelLogs.kql"); KQL = KQL.FormatWith(KqlParams, FormatWith.MissingKeyBehaviour.ThrowException, null, '{', '}'); JObject JsonContent = new JObject(); JsonContent["query"] = KQL; var postContent = new StringContent(JsonContent.ToString(), System.Text.Encoding.UTF8, "application/json"); var response = client.PostAsync($"https://api.applicationinsights.io/v1/apps/{AppInsightsWorkspaceId}/query", postContent).Result; if (response.StatusCode == System.Net.HttpStatusCode.OK) { //Start to parse the response content HttpContent responseContent = response.Content; var content = response.Content.ReadAsStringAsync().Result; var tables = ((JArray)(JObject.Parse(content)["tables"])); if (tables.Count > 0) { DataTable dt = new DataTable(); var rows = (JArray)(tables[0]["rows"]); var columns = (JArray)(tables[0]["columns"]); foreach (JObject c in columns) { DataColumn dc = new DataColumn(); dc.ColumnName = c["name"].ToString(); dc.DataType = KustoDataTypeMapper[c["type"].ToString()]; dt.Columns.Add(dc); } foreach (JArray r in rows) { DataRow dr = dt.NewRow(); for (int i = 0; i < columns.Count; i++) { if (((Newtonsoft.Json.Linq.JValue)r[i]).Value != null) { dr[i] = ((Newtonsoft.Json.Linq.JValue)r[i]).Value; } else { dr[i] = DBNull.Value; } } dt.Rows.Add(dr); } Table t = new Table(); t.Schema = "dbo"; string TableGuid = Guid.NewGuid().ToString(); t.Name = "#ActivityLevelLogs{TableGuid}"; using (SqlConnection _conWrite = TMD.GetSqlConnection()) { TMD.BulkInsert(dt, t, true, _conWrite); Dictionary <string, string> SqlParams = new Dictionary <string, string> { { "TempTable", t.QuotedSchemaAndName() }, { "DatafactoryId", "1" } }; string MergeSQL = GenerateSQLStatementTemplates.GetSQL(Shared.GlobalConfigs.GetStringConfig("SQLTemplateLocation"), "MergeIntoActivityLevelLogs", SqlParams); logging.LogInformation(MergeSQL.ToString()); _conWrite.ExecuteWithRetry(MergeSQL); _conWrite.Close(); _conWrite.Dispose(); } } else { logging.LogErrors(new Exception("Kusto query failed getting ADFPipeline Stats.")); } } } return(new { }); }
public static async Task <JObject> SendEmailSASUri(HttpRequest req, Logging logging) { string requestBody = new StreamReader(req.Body).ReadToEndAsync().Result; dynamic taskInformation = JsonConvert.DeserializeObject(requestBody); string _TaskInstanceId = taskInformation["TaskInstanceId"].ToString(); string _ExecutionUid = taskInformation["ExecutionUid"].ToString(); try { //Get SAS URI string _blobStorageAccountName = taskInformation["Source"]["StorageAccountName"].ToString(); string _blobStorageContainerName = taskInformation["Source"]["StorageAccountContainer"].ToString(); string _blobStorageFolderPath = taskInformation["Source"]["RelativePath"].ToString(); string _dataFileName = taskInformation["Source"]["DataFileName"].ToString(); int _accessDuration = (int)taskInformation["Source"]["SasURIDaysValid"]; string _targetSystemUidInPHI = taskInformation["Source"]["TargetSystemUidInPHI"]; string _FileUploaderWebAppURL = taskInformation["Source"]["FileUploaderWebAppURL"]; string SASUri = Storage.CreateSASToken(_blobStorageAccountName, _blobStorageContainerName, _blobStorageFolderPath, _dataFileName, _accessDuration); //Send Email string _emailRecipient = taskInformation["Target"]["EmailRecipient"].ToString(); string _emailRecipientName = taskInformation["Target"]["EmailRecipientName"].ToString(); string _emailTemplateFileName = taskInformation["Target"]["EmailTemplateFileName"].ToString(); string _senderEmail = taskInformation["Target"]["SenderEmail"].ToString(); string _senderDescription = taskInformation["Target"]["SenderDescription"].ToString(); string _subject = taskInformation["Target"]["EmailSubject"].ToString(); //Get Plain Text and Email Subject from Template Files Dictionary <string, string> Params = new Dictionary <string, string> { { "NAME", _emailRecipientName }, { "SASTOKEN", SASUri }, { "FileUploaderUrl", _FileUploaderWebAppURL }, { "TargetSystemUidInPHI", _targetSystemUidInPHI }, }; string _plainTextContent = System.IO.File.ReadAllText(System.IO.Path.Combine(Shared._ApplicationBasePath, Shared._ApplicationOptions.LocalPaths.HTMLTemplateLocation, _emailTemplateFileName + ".txt")); _plainTextContent = _plainTextContent.FormatWith(Params, MissingKeyBehaviour.ThrowException, null, '{', '}'); string _htmlContent = System.IO.File.ReadAllText(System.IO.Path.Combine(Shared._ApplicationBasePath, Shared._ApplicationOptions.LocalPaths.HTMLTemplateLocation, _emailTemplateFileName + ".html")); _htmlContent = _htmlContent.FormatWith(Params, MissingKeyBehaviour.ThrowException, null, '{', '}'); var apiKey = System.Environment.GetEnvironmentVariable("SENDGRID_APIKEY"); var client = new SendGridClient(new SendGridClientOptions { ApiKey = apiKey, HttpErrorAsException = true }); var msg = new SendGridMessage() { From = new EmailAddress(_senderEmail, _senderDescription), Subject = _subject, PlainTextContent = _plainTextContent, HtmlContent = _htmlContent }; msg.AddTo(new EmailAddress(_emailRecipient, _emailRecipientName)); try { var response = await client.SendEmailAsync(msg).ConfigureAwait(false); logging.LogInformation($"SendGrid Response StatusCode - {response.StatusCode}"); } catch (Exception ex) { SendGridErrorResponse errorResponse = JsonConvert.DeserializeObject <SendGridErrorResponse>(ex.Message); logging.LogInformation($"Error Message - {ex.Message}"); throw new Exception("Could not send email"); } //Update Task Instace TaskMetaDataDatabase TMD = new TaskMetaDataDatabase(); TMD.LogTaskInstanceCompletion(System.Convert.ToInt64(_TaskInstanceId), System.Guid.Parse(_ExecutionUid), TaskMetaData.BaseTasks.TaskStatus.Complete, Guid.Empty, ""); JObject Root = new JObject { ["Result"] = "Complete" }; return(Root); } catch (Exception TaskException) { logging.LogErrors(TaskException); TaskMetaDataDatabase TMD = new TaskMetaDataDatabase(); TMD.LogTaskInstanceCompletion(System.Convert.ToInt64(_TaskInstanceId), System.Guid.Parse(_ExecutionUid), TaskMetaData.BaseTasks.TaskStatus.FailedRetry, Guid.Empty, "Failed when trying to Generate Sas URI and Send Email"); JObject Root = new JObject { ["Result"] = "Failed" }; return(Root); } }