public static JObject GetSourceTargetMappingCore(HttpRequest req,
Logging logging)
{
string requestBody = new StreamReader(req.Body).ReadToEndAsync().Result;
dynamic data = JsonConvert.DeserializeObject(requestBody);
string _storageAccountName = data["StorageAccountName"].ToString();
string _storageAccountContainer = data["StorageAccountContainer"].ToString();
string _relativePath = data["RelativePath"].ToString();
string _metadataType = data["MetadataType"].ToString();
string _sourceType = data["SourceType"].ToString();
string _targetType = data["TargetType"].ToString();
string _schemaFileName = data["SchemaFileName"].ToString();
_storageAccountName = _storageAccountName.Replace(".dfs.core.windows.net", "").Replace("https://", "").Replace(".blob.core.windows.net", "");
TokenCredential StorageToken = new TokenCredential(Shared._AzureAuthenticationCredentialProvider.GetAzureRestApiToken(string.Format("https://{0}.blob.core.windows.net", _storageAccountName), Shared._ApplicationOptions.UseMSI));
string _schemaStructure = Shared.Azure.Storage.ReadFile(_storageAccountName, _storageAccountContainer, _relativePath, _schemaFileName, StorageToken);
JArray arr = (JArray)JsonConvert.DeserializeObject(_schemaStructure);
JObject Root = Mapping(arr, _sourceType, _targetType, _metadataType);
logging.LogInformation("GetSourceTargetMapping Function complete.");
return(Root);
}
public static JObject CheckPipelineStatusMethod(string subscriptionId, string resourceGroup, string factoryName, string pipelineName, string runId, Logging logging)
{
#region CreatePipelineRun
//Create a data factory management client
logging.LogInformation("Creating ADF connectivity client.");
string outputString = string.Empty;
using (var client = DataFactoryClient.CreateDataFactoryClient(subscriptionId))
{
//Get pipeline status with provided run id
PipelineRun pipelineRun;
pipelineRun = client.PipelineRuns.Get(resourceGroup, factoryName, runId);
logging.LogInformation("Checking ADF pipeline status.");
//Create simple status for Data Factory Until comparison checks
string simpleStatus;
if (pipelineRun.Status == "InProgress")
{
simpleStatus = "Running";
}
else
{
simpleStatus = "Done";
}
logging.LogInformation("ADF pipeline status: " + pipelineRun.Status);
//Final return detail
outputString = "{ \"PipelineName\": \"" + pipelineName +
"\", \"RunId\": \"" + pipelineRun.RunId +
"\", \"SimpleStatus\": \"" + simpleStatus +
"\", \"Status\": \"" + pipelineRun.Status +
"\" }";
}
#endregion
JObject outputJson = JObject.Parse(outputString);
return(outputJson);
}
public static JObject GetSQLMergeStatementCore(HttpRequest req,
Logging logging)
{
string requestBody = new StreamReader(req.Body).ReadToEndAsync().Result;
dynamic data = JsonConvert.DeserializeObject(requestBody);
JObject Root = new JObject();
TaskMetaDataDatabase TMD = new TaskMetaDataDatabase();
using (SqlConnection _con = TMD.GetSqlConnection())
{
string _token = Shared._AzureAuthenticationCredentialProvider.GetAzureRestApiToken("https://database.windows.net/");
String g = Guid.NewGuid().ToString().Replace("-", "");
_con.AccessToken = _token;
JArray arrStage = (JArray)data["Stage"];
string _StagingTableSchema = data["StagingTableSchema"].ToString();
string _StagingTableName = "#Temp_" + data["StagingTableName"].ToString() + g.ToString();
string _CreateStatementStage = GenerateSQLStatementTemplates.GetCreateTable(arrStage, _StagingTableSchema, _StagingTableName, false);
TMD.ExecuteSql(_CreateStatementStage, _con);
JArray arrTarget = (JArray)data["Target"];
string _TargetTableSchema = data["TargetTableSchema"].ToString();
string _TargetTableName = "#Temp_" + data["TargetTableName"].ToString() + g.ToString();
string _CreateStatementTarget = GenerateSQLStatementTemplates.GetCreateTable(arrTarget, _TargetTableSchema, _TargetTableName, false);
TMD.ExecuteSql(_CreateStatementTarget, _con);
string _MergeStatement = TMD.GenerateMergeSQL(_StagingTableSchema, _StagingTableName, _TargetTableSchema, _TargetTableName, _con, true, logging);
string fullStagingTableName = string.Format("[{0}].[{1}]", _StagingTableSchema, _StagingTableName.Replace("#Temp_", "").Replace(g.ToString(), ""));
string fullTargetTableName = string.Format("[{0}].[{1}]", _TargetTableSchema, _TargetTableName.Replace("#Temp_", "").Replace(g.ToString(), ""));
_MergeStatement = _MergeStatement.Replace(_TargetTableName, fullTargetTableName);
_MergeStatement = _MergeStatement.Replace(_StagingTableName, fullStagingTableName);
//Add Select for ADF Lookup Activity
_MergeStatement += Environment.NewLine + "Select 1 ";
Root["MergeStatement"] = _MergeStatement;
logging.LogInformation("GetSQLMergeStatement Function complete.");
}
return(Root);
}
public static JObject LogCore(HttpRequest req,
Logging LogHelper)
{
short _FrameworkNumberOfRetries = Shared.GlobalConfigs.GetInt16Config("FrameworkNumberOfRetries");
string requestBody = new StreamReader(req.Body).ReadToEndAsync().Result;
dynamic data = JsonConvert.DeserializeObject(requestBody);
dynamic _TaskInstanceId = JObject.Parse(data.ToString())["TaskInstanceId"];
dynamic _NumberOfRetries = JObject.Parse(data.ToString())["NumberOfRetries"];
dynamic _PostObjectExecutionUid = JObject.Parse(data.ToString())["ExecutionUid"];
dynamic _AdfRunUid = JObject.Parse(data.ToString())["RunId"];
dynamic _LogTypeId = JObject.Parse(data.ToString())["LogTypeId"]; //1 Error, 2 Warning, 3 Info, 4 Performance, 5 Debug
dynamic _LogSource = JObject.Parse(data.ToString())["LogSource"]; //ADF, AF
dynamic _ActivityType = JObject.Parse(data.ToString())["ActivityType"];
dynamic _StartDateTimeOffSet = JObject.Parse(data.ToString())["StartDateTimeOffSet"];
dynamic _Status = JObject.Parse(data.ToString())["Status"]; //Started Failed Completed
dynamic _Comment = JObject.Parse(data.ToString())["Comment"];
_Comment = _Comment == null ? null : _Comment.ToString().Replace("'", "");
dynamic _EndDateTimeOffSet = JObject.Parse(data.ToString())["EndDateTimeOffSet"];
dynamic _RowsInserted = JObject.Parse(data.ToString())["RowsInserted"];
if (_TaskInstanceId != null)
{
LogHelper.DefaultActivityLogItem.TaskInstanceId = (long?)_TaskInstanceId;
}
if (_LogSource != null)
{
LogHelper.DefaultActivityLogItem.LogSource = (string)_LogSource;
}
if (_LogTypeId != null)
{
LogHelper.DefaultActivityLogItem.LogTypeId = (short?)_LogTypeId;
}
if (_StartDateTimeOffSet != null)
{
LogHelper.DefaultActivityLogItem.StartDateTimeOffset = (DateTimeOffset)_StartDateTimeOffSet;
}
if (_Status != null)
{
LogHelper.DefaultActivityLogItem.Status = (string)_Status;
}
if (_EndDateTimeOffSet != null)
{
LogHelper.DefaultActivityLogItem.EndDateTimeOffset = (DateTimeOffset)_EndDateTimeOffSet;
}
if (_PostObjectExecutionUid != null)
{
LogHelper.DefaultActivityLogItem.ExecutionUid = (Guid?)_PostObjectExecutionUid;
}
LogHelper.LogInformation(_Comment);
TaskMetaDataDatabase TMD = new TaskMetaDataDatabase();
BaseTasks.TaskStatus TaskStatus = new BaseTasks.TaskStatus();
if (_ActivityType == "Data-Movement-Master")
{
if (_Status == "Failed")
{
//Todo Put Max Number of retries in DB at TaskMasterLevel -- This has now been done. Have left logic in function as stored procedure handles with all failed statuses.
_NumberOfRetries = (_NumberOfRetries == null) ? 0 : (int)_NumberOfRetries + 1;
TaskStatus = ((_NumberOfRetries < _FrameworkNumberOfRetries) ? BaseTasks.TaskStatus.FailedRetry : BaseTasks.TaskStatus.FailedNoRetry);
}
else
{
if (Enum.TryParse <BaseTasks.TaskStatus>(_Status.ToString(), out TaskStatus) == false)
{
string InvalidStatus = "TaskStatus Enum does not exist for: " + _Status.ToString();
LogHelper.LogErrors(new Exception("TaskStatus Enum does not exist for: " + _Status.ToString()));
_Comment = _Comment.ToString() + "." + InvalidStatus;
TaskStatus = BaseTasks.TaskStatus.FailedNoRetry;
}
}
TMD.LogTaskInstanceCompletion((Int64)_TaskInstanceId, (System.Guid)_PostObjectExecutionUid, TaskStatus, (System.Guid)_AdfRunUid, (String)_Comment);
}
JObject Root = new JObject
{
["Result"] = "Complete"
};
return(Root);
}
public static void QueryPipelineRuns(string subscriptionId, string resourceGroup, string factoryName, string _rungroupid, DateTime startDT, DateTime endDT, Logging logging)
{
#region QueryPipelineRuns
logging.LogInformation("Query ADF Pipeline Runs.");
string outputString = string.Empty;
DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn("ExecutionUid", typeof(Guid)));
dt.Columns.Add(new DataColumn("TaskInstanceId", typeof(Int64)));
dt.Columns.Add(new DataColumn("TaskMasterId", typeof(Int64)));
//dt.Columns.Add(new DataColumn("AdditionalProperties", typeof(String)));
dt.Columns.Add(new DataColumn("DurationInMs", typeof(Int64)));
//dt.Columns.Add(new DataColumn("InvokedBy", typeof(String)));
dt.Columns.Add(new DataColumn("IsLastest", typeof(Boolean)));
dt.Columns.Add(new DataColumn("LastUpdated", typeof(DateTime)));
//dt.Columns.Add(new DataColumn("Message", typeof(String)));
//dt.Columns.Add(new DataColumn("Parameters", typeof(String)));
dt.Columns.Add(new DataColumn("RunId", typeof(Guid)));
//dt.Columns.Add(new DataColumn("RunGroupId", typeof(Guid)));
dt.Columns.Add(new DataColumn("PipelineName", typeof(String)));
dt.Columns.Add(new DataColumn("RunStart", typeof(DateTime)));
dt.Columns.Add(new DataColumn("RunEnd", typeof(DateTime)));
dt.Columns.Add(new DataColumn("RunDimensions", typeof(String)));
dt.Columns.Add(new DataColumn("Status", typeof(String)));
DataTable ActivityDt = new DataTable();
ActivityDt.Columns.Add(new DataColumn("ActivityName", typeof(String)));
ActivityDt.Columns.Add(new DataColumn("RunId", typeof(Guid)));
ActivityDt.Columns.Add(new DataColumn("ActivityRunStart", typeof(DateTime)));
ActivityDt.Columns.Add(new DataColumn("ActivityRunEnd", typeof(DateTime)));
ActivityDt.Columns.Add(new DataColumn("ActivityRunId", typeof(Guid)));
ActivityDt.Columns.Add(new DataColumn("ActivityType", typeof(String)));
//dt.Columns.Add(new DataColumn("AdditionalProperties", typeof(String)));
ActivityDt.Columns.Add(new DataColumn("DurationInMs", typeof(Int64)));
//dt.Columns.Add(new DataColumn("Error", typeof(String)));
//dt.Columns.Add(new DataColumn("Input", typeof(String)));
//dt.Columns.Add(new DataColumn("LinkedServiceName", typeof(String)));
ActivityDt.Columns.Add(new DataColumn("OutPut", typeof(String)));
ActivityDt.Columns.Add(new DataColumn("PipelineName", typeof(String)));
ActivityDt.Columns.Add(new DataColumn("PipelineRunId", typeof(String)));
ActivityDt.Columns.Add(new DataColumn("Status", typeof(String)));
using (var client = DataFactoryClient.CreateDataFactoryClient(subscriptionId))
{
//Get pipeline status with provided run id
PipelineRunsQueryResponse pipelineRunsQueryResponse;
RunFilterParameters filterParameterActivityRuns = new RunFilterParameters();
filterParameterActivityRuns.LastUpdatedAfter = startDT;
filterParameterActivityRuns.LastUpdatedBefore = endDT.AddHours(+2);
RunFilterParameters filterParameter = new RunFilterParameters();
filterParameter.LastUpdatedAfter = startDT;
filterParameter.LastUpdatedBefore = endDT;
IList <string> rungroupid = new List <string> {
_rungroupid
};
IList <RunQueryFilter> filter = new List <RunQueryFilter>();
filter.Add(new RunQueryFilter
{
Operand = RunQueryFilterOperand.RunGroupId,
OperatorProperty = RunQueryFilterOperator.Equals,
Values = rungroupid
});
filterParameter.Filters = filter;
logging.LogInformation(String.Format("API PipelineRuns.QueryByFactory Start"));
pipelineRunsQueryResponse = client.PipelineRuns.QueryByFactory(resourceGroup, factoryName, filterParameter);
logging.LogInformation(String.Format("API PipelineRuns.QueryByFactory End"));
var enumerator = pipelineRunsQueryResponse.Value.GetEnumerator();
PipelineRun pipelineRuns;
string runId = String.Empty;
int item = 0;
while (true)
{
for (bool hasMoreRuns = enumerator.MoveNext(); hasMoreRuns;)
{
pipelineRuns = enumerator.Current;
hasMoreRuns = enumerator.MoveNext();
runId = pipelineRuns.RunId;
item += 1;
logging.LogInformation(String.Format("PipelineRuns.QueryByFactory RunId {0} Current Item {1} of {2}", runId, item, pipelineRunsQueryResponse.Value.Count));
DataRow dr = dt.NewRow();
string _param = string.Empty;
foreach (var element in pipelineRuns.Parameters)
{
_param = element.Value;
break;
}
dr["ExecutionUid"] = Shared.JsonHelpers.GetStringValueFromJSON(logging, "ExecutionUid", JObject.Parse(_param), null, true);
dr["TaskInstanceId"] = Shared.JsonHelpers.GetStringValueFromJSON(logging, "TaskInstanceId", JObject.Parse(_param), null, true);
dr["TaskMasterId"] = Shared.JsonHelpers.GetStringValueFromJSON(logging, "TaskMasterId", JObject.Parse(_param), null, true);
//dr["AdditionalProperties"] = pipelineRuns.AdditionalProperties ?? (object)DBNull.Value;
dr["DurationInMs"] = pipelineRuns.DurationInMs ?? (object)DBNull.Value;
//dr["InvokedBy"] = pipelineRuns.InvokedBy ?? (object)DBNull.Value;
dr["IsLastest"] = pipelineRuns.IsLatest ?? (object)DBNull.Value;
dr["LastUpdated"] = pipelineRuns.LastUpdated ?? (object)DBNull.Value;
//dr["Message"] = pipelineRuns.Message ?? (object)DBNull.Value;
//dr["Parameters"] = _param;
dr["RunId"] = pipelineRuns.RunId;
//dr["RunGroupId"] = pipelineRuns.RunGroupId ?? (object)DBNull.Value;
dr["PipelineName"] = pipelineRuns.PipelineName ?? (object)DBNull.Value;
dr["RunStart"] = pipelineRuns.RunStart ?? (object)DBNull.Value;
dr["RunEnd"] = pipelineRuns.RunEnd ?? (object)DBNull.Value;
dr["RunDimensions"] = pipelineRuns.PipelineName ?? (object)DBNull.Value;
dr["Status"] = pipelineRuns.Status ?? (object)DBNull.Value;
dt.Rows.Add(dr);
QueryActivityRuns(subscriptionId, resourceGroup, factoryName, runId, runId, filterParameterActivityRuns, logging, ref ActivityDt);
}
if (pipelineRunsQueryResponse.ContinuationToken == null)
{
break;
}
filterParameter.ContinuationToken = pipelineRunsQueryResponse.ContinuationToken;
pipelineRunsQueryResponse = client.PipelineRuns.QueryByFactory(resourceGroup, factoryName, filterParameter);
enumerator = pipelineRunsQueryResponse.Value.GetEnumerator();
item = 0;
}
}
if (ActivityDt.Rows.Count > 0)
{
string TempTableName = "#Temp_ADFActivities_" + Guid.NewGuid().ToString();
TaskMetaDataDatabase TMD = new TaskMetaDataDatabase();
TMD.AutoBulkInsertAndMerge(ActivityDt, TempTableName, "ADFActivity");
}
if (dt.Rows.Count > 0)
{
string TempTableName = "#Temp_ADFPipelineRun_" + Guid.NewGuid().ToString();
TaskMetaDataDatabase TMD = new TaskMetaDataDatabase();
TMD.AutoBulkInsertAndMerge(dt, TempTableName, "ADFPipelineRun");
}
#endregion
}
public static void QueryActivityRuns(string subscriptionId, string resourceGroup, string factoryName, string runId, string parentRunId, RunFilterParameters filterParameterActivityRuns, Logging logging, ref DataTable dt)
{
#region QueryActivityRuns
logging.LogInformation(String.Format("QueryActivityRuns - RunId {0}", runId));
string outputString = string.Empty;
using (var client = DataFactoryClient.CreateDataFactoryClient(subscriptionId))
{
//Get pipeline status with provided run id
logging.LogInformation(String.Format("QueryActivityRuns - RunId {0} - API QueryByPipelineRun Start", runId));
ActivityRunsQueryResponse activityRunsQueryResponse = client.ActivityRuns.QueryByPipelineRun(resourceGroup, factoryName, runId, filterParameterActivityRuns);
logging.LogInformation(String.Format("QueryActivityRuns - RunId {0} - API QueryByPipelineRun End", runId));
var enumeratorActivity = activityRunsQueryResponse.Value.GetEnumerator();
ActivityRun activityRun;
for (bool hasMoreActivityRuns = enumeratorActivity.MoveNext(); hasMoreActivityRuns;)
{
activityRun = enumeratorActivity.Current;
hasMoreActivityRuns = enumeratorActivity.MoveNext();
DataRow dr = dt.NewRow();
dr["ActivityName"] = activityRun.ActivityName ?? (object)DBNull.Value;
dr["RunId"] = parentRunId;
dr["ActivityRunStart"] = activityRun.ActivityRunStart ?? (object)DBNull.Value;
dr["ActivityRunEnd"] = activityRun.ActivityRunEnd ?? (object)DBNull.Value;
dr["ActivityRunId"] = activityRun.ActivityRunId ?? (object)DBNull.Value;
dr["ActivityType"] = activityRun.ActivityType ?? (object)DBNull.Value;
//dr["AdditionalProperties"] = activityRun.AdditionalProperties ?? (object)DBNull.Value;
dr["DurationInMs"] = activityRun.DurationInMs ?? (object)DBNull.Value;
//dr["Error"] = activityRun.Error ?? (object)DBNull.Value;
//dr["Input"] = activityRun.Input ?? (object)DBNull.Value;
//dr["LinkedServiceName"] = activityRun.LinkedServiceName ?? (object)DBNull.Value;
dr["OutPut"] = activityRun.Output ?? (object)DBNull.Value;
dr["PipelineName"] = activityRun.PipelineName ?? (object)DBNull.Value;
dr["PipelineRunId"] = activityRun.PipelineRunId ?? (object)DBNull.Value;
dr["Status"] = activityRun.Status ?? (object)DBNull.Value;
dt.Rows.Add(dr);
if (activityRun.ActivityType == "ExecutePipeline")
{
string _runId = Shared.JsonHelpers.GetStringValueFromJSON(logging, "pipelineRunId", (JObject)activityRun.Output, null, true);
if (!String.IsNullOrEmpty(_runId))
{
QueryActivityRuns(subscriptionId, resourceGroup, factoryName, _runId, parentRunId, filterParameterActivityRuns, logging, ref dt);
}
else
{
logging.LogInformation(String.Format("QueryActivityRuns - RunId Is null for {0} ", (JObject)activityRun.Output));
}
}
}
}
#endregion
}
public static JObject ExecutePipelineMethod(string subscriptionId, string resourceGroup, string factoryName, string pipelineName, Dictionary <string, object> parameters, Logging logging)
{
#region CreatePipelineRun
//Create a data factory management client
logging.LogInformation("Creating ADF connectivity client.");
string outputString = string.Empty;
using (var client = DataFactoryClient.CreateDataFactoryClient(subscriptionId))
{
//Run pipeline
CreateRunResponse runResponse;
PipelineRun pipelineRun;
if (parameters.Count == 0)
{
logging.LogInformation("Called pipeline without parameters.");
runResponse = client.Pipelines.CreateRunWithHttpMessagesAsync(
resourceGroup, factoryName, pipelineName).Result.Body;
}
else
{
logging.LogInformation("Called pipeline with parameters.");
logging.LogInformation("Number of parameters provided: " + parameters.Count);
System.Threading.Thread.Sleep(1000);
runResponse = client.Pipelines.CreateRunWithHttpMessagesAsync(
resourceGroup, factoryName, pipelineName, parameters: parameters).Result.Body;
runResponse = new CreateRunResponse();
runResponse.RunId = Guid.NewGuid().ToString();
}
logging.LogInformation("Pipeline run ID: " + runResponse.RunId);
//Wait and check for pipeline to start...
logging.LogInformation("Checking ADF pipeline status.");
//while (true)
//{
// pipelineRun = client.PipelineRuns.Get(
// resourceGroup, factoryName, runResponse.RunId);
// logging.LogInformation("ADF pipeline status: " + pipelineRun.Status);
//if (pipelineRun.Status == "Queued")
// System.Threading.Thread.Sleep(15000);
//else
// break;
//}
//Final return detail
outputString = "{ \"PipelineName\": \"" + pipelineName +
"\", \"RunId\": \"" + runResponse.RunId +
"\", \"Status\": \"" + "InProgress" + //pipelineRun.Status +
"\" }";
}
#endregion
JObject outputJson = JObject.Parse(outputString);
return(outputJson);
}
public dynamic GetADFPipelineRuns(Logging logging)
{
//string workspaceId = System.Environment.GetEnvironmentVariable("LogAnalyticsWorkspaceId");
using var client = _logAnalyticsContext.httpClient.CreateClient(_logAnalyticsContext.httpClientName);
TaskMetaDataDatabase TMD = new TaskMetaDataDatabase();
using SqlConnection _conRead = TMD.GetSqlConnection();
//Get Last Request Date
var MaxTimesGen = _conRead.QueryWithRetry(@"
Select a.*, MaxPipelineTimeGenerated from
DataFactory a left join
( Select b.DataFactoryId, MaxPipelineTimeGenerated = Max(MaxPipelineTimeGenerated)
from ADFPipelineRun b
group by b.DatafactoryId) b on a.Id = b.DatafactoryId");
DateTimeOffset MaxPipelineTimeGenerated = DateTimeOffset.UtcNow.AddDays(-30);
foreach (var datafactory in MaxTimesGen)
{
if (datafactory.MaxPipelineTimeGenerated != null)
{
MaxPipelineTimeGenerated = ((DateTimeOffset)datafactory.MaxPipelineTimeGenerated).AddMinutes(-180);
}
string workspaceId = datafactory.LogAnalyticsWorkspaceId.ToString();
Dictionary <string, object> KqlParams = new Dictionary <string, object>
{
{ "MaxPipelineTimeGenerated", MaxPipelineTimeGenerated.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(Path.Combine(Path.Combine(Shared._ApplicationBasePath, Shared._ApplicationOptions.LocalPaths.KQLTemplateLocation), "GetADFPipelineRuns.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.loganalytics.io/v1/workspaces/{workspaceId}/query", postContent).Result;
logging.LogInformation(response.ToString());
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 = GetADFStats.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++)
{
dr[i] = ((Newtonsoft.Json.Linq.JValue)r[i]).Value;
}
dt.Rows.Add(dr);
}
Table t = new Table();
t.Schema = "dbo";
string TableGuid = Guid.NewGuid().ToString();
t.Name = $"#ADFPipelineRun{TableGuid}";
using (SqlConnection _conWrite = TMD.GetSqlConnection())
{
TMD.BulkInsert(dt, t, true, _conWrite);
Dictionary <string, string> SqlParams = new Dictionary <string, string>
{
{ "TempTable", t.QuotedSchemaAndName() },
{ "DatafactoryId", datafactory.Id.ToString() }
};
string MergeSQL = GenerateSQLStatementTemplates.GetSQL(System.IO.Path.Combine(Shared._ApplicationBasePath, Shared._ApplicationOptions.LocalPaths.SQLTemplateLocation), "MergeIntoADFPipelineRun", SqlParams);
_conWrite.ExecuteWithRetry(MergeSQL);
_conWrite.Close();
_conWrite.Dispose();
}
}
else
{
logging.LogErrors(new Exception("Kusto query failed getting ADFPipeline Stats."));
}
}
}
return(new { });
}
public static void CreateTaskInstance(Logging logging)
{
logging.LogInformation("Create ScheduleInstance called.");
TaskMetaDataDatabase TMD = new TaskMetaDataDatabase();
DateTimeOffset _date = DateTimeOffset.Now;
DataTable dtScheduleInstance = new DataTable();
dtScheduleInstance.Columns.Add(new DataColumn("ScheduleMasterId", typeof(long)));
dtScheduleInstance.Columns.Add(new DataColumn("ScheduledDateUtc", typeof(DateTime)));
dtScheduleInstance.Columns.Add(new DataColumn("ScheduledDateTimeOffset", typeof(DateTimeOffset)));
dtScheduleInstance.Columns.Add(new DataColumn("ActiveYN", typeof(bool)));
dynamic resScheduleInstance = TMD.GetSqlConnection().QueryWithRetry(@"
Select
SM.ScheduleMasterId,
SM.ScheduleCronExpression,
Coalesce(SI.MaxScheduledDateTimeOffset,cast('1900-01-01' as datetimeoffset)) as MaxScheduledDateTimeOffset
from
ScheduleMaster SM
join (
Select distinct ScheduleMasterId from TaskMaster TM where TM.ActiveYN = 1) TM on TM.ScheduleMasterId = SM.ScheduleMasterId
left outer join
(
Select ScheduleMasterId, Max(ScheduledDateTimeOffset) MaxScheduledDateTimeOffset
From ScheduleInstance
Where ActiveYN = 1
Group By ScheduleMasterId
) SI on SM.ScheduleMasterId = SI.ScheduleMasterId
Where SM.ActiveYN = 1");
foreach (dynamic _row in resScheduleInstance)
{
DateTimeOffset?nextUtc;
if (_row.ScheduleCronExpression.ToString() == "N/A")
{
nextUtc = DateTime.UtcNow.AddMinutes(-1);
}
else
{
CronExpression _cronExpression = CronExpression.Parse(_row.ScheduleCronExpression.ToString(), CronFormat.IncludeSeconds);
nextUtc = _cronExpression.GetNextOccurrence(_row.MaxScheduledDateTimeOffset, TimeZoneInfo.Utc);
}
if (nextUtc?.DateTime <= DateTime.UtcNow)
{
DataRow dr = dtScheduleInstance.NewRow();
dr["ScheduleMasterId"] = _row.ScheduleMasterId;
dr["ScheduledDateUtc"] = _date.Date;
dr["ScheduledDateTimeOffset"] = _date;
dr["ActiveYN"] = true;
dtScheduleInstance.Rows.Add(dr);
}
}
//Persist TEMP ScheduleInstance
SqlConnection _con = TMD.GetSqlConnection();
Table tmpScheduleInstanceTargetTable = new Table
{
Name = "#Temp" + Guid.NewGuid().ToString()
};
TMD.BulkInsert(dtScheduleInstance, tmpScheduleInstanceTargetTable, true, _con);
//Create TaskInstance
logging.LogInformation("Create TaskInstance called.");
DataTable dtTaskInstance = new DataTable();
dtTaskInstance.Columns.Add(new DataColumn("ExecutionUid", typeof(Guid)));
dtTaskInstance.Columns.Add(new DataColumn("TaskMasterId", typeof(long)));
dtTaskInstance.Columns.Add(new DataColumn("ScheduleInstanceId", typeof(long)));
dtTaskInstance.Columns.Add(new DataColumn("ADFPipeline", typeof(string)));
dtTaskInstance.Columns.Add(new DataColumn("TaskInstanceJson", typeof(string)));
dtTaskInstance.Columns.Add(new DataColumn("LastExecutionStatus", typeof(string)));
dtTaskInstance.Columns.Add(new DataColumn("ActiveYN", typeof(bool)));
dynamic resTaskInstance = TMD.GetSqlConnection().QueryWithRetry(@"Exec dbo.GetTaskMaster");
DataTable dtTaskTypeMapping = GetTaskTypeMapping(logging);
foreach (dynamic _row in resTaskInstance)
{
DataRow drTaskInstance = dtTaskInstance.NewRow();
logging.DefaultActivityLogItem.TaskInstanceId = _row.TaskInstanceId;
logging.DefaultActivityLogItem.TaskMasterId = _row.TaskMasterId;
try
{
dynamic sourceSystemJson = JsonConvert.DeserializeObject(_row.SourceSystemJSON);
dynamic taskMasterJson = JsonConvert.DeserializeObject(_row.TaskMasterJSON);
dynamic targetSystemJson = JsonConvert.DeserializeObject(_row.TargetSystemJSON);
string _ADFPipeline = GetTaskTypeMappingName(logging, _row.TaskExecutionType.ToString(), dtTaskTypeMapping, _row.TaskTypeId, _row.SourceSystemType.ToString(), taskMasterJson?.Source.Type.ToString(), _row.TargetSystemType.ToString(), taskMasterJson?.Target.Type.ToString(), _row.TaskDatafactoryIR);
drTaskInstance["TaskMasterId"] = _row.TaskMasterId ?? DBNull.Value;
drTaskInstance["ScheduleInstanceId"] = 0;//_row.ScheduleInstanceId == null ? DBNull.Value : _row.ScheduleInstanceId;
drTaskInstance["ExecutionUid"] = logging.DefaultActivityLogItem.ExecutionUid;
drTaskInstance["ADFPipeline"] = _ADFPipeline;
drTaskInstance["LastExecutionStatus"] = "Untried";
drTaskInstance["ActiveYN"] = true;
JObject Root = new JObject();
if (_row.SourceSystemType == "ADLS" || _row.SourceSystemType == "Azure Blob")
{
if (taskMasterJson?.Source.Type.ToString() != "Filelist")
{
Root["SourceRelativePath"] = TaskInstancesStatic.TransformRelativePath(JObject.Parse(_row.TaskMasterJSON)["Source"]["RelativePath"].ToString(), _date.DateTime);
}
}
if (_row.TargetSystemType == "ADLS" || _row.TargetSystemType == "Azure Blob")
{
if (JObject.Parse(_row.TaskMasterJSON)["Target"]["RelativePath"] != null)
{
Root["TargetRelativePath"] = TaskInstancesStatic.TransformRelativePath(JObject.Parse(_row.TaskMasterJSON)["Target"]["RelativePath"].ToString(), _date.DateTime);
}
}
if (JObject.Parse(_row.TaskMasterJSON)["Source"]["IncrementalType"] == "Watermark")
{
Root["IncrementalField"] = _row.TaskMasterWaterMarkColumn;
Root["IncrementalColumnType"] = _row.TaskMasterWaterMarkColumnType;
if (_row.TaskMasterWaterMarkColumnType == "DateTime")
{
Root["IncrementalValue"] = _row.TaskMasterWaterMark_DateTime ?? "1900-01-01";
}
else if (_row.TaskMasterWaterMarkColumnType == "BigInt")
{
Root["IncrementalValue"] = _row.TaskMasterWaterMark_BigInt ?? -1;
}
}
if (Root == null)
{
drTaskInstance["TaskInstanceJson"] = DBNull.Value;
}
else
{
drTaskInstance["TaskInstanceJson"] = Root;
}
dtTaskInstance.Rows.Add(drTaskInstance);
}
catch (Exception e)
{
logging.LogErrors(new Exception(string.Format("Failed to create new task instances for TaskMasterId '{0}'.", logging.DefaultActivityLogItem.TaskInstanceId)));
logging.LogErrors(e);
}
}
//Persist TMP TaskInstance
Table tmpTaskInstanceTargetTable = new Table
{
Name = "#Temp" + Guid.NewGuid().ToString()
};
TMD.BulkInsert(dtTaskInstance, tmpTaskInstanceTargetTable, true, _con);
Dictionary <string, string> SqlParams = new Dictionary <string, string>
{
{ "tmpScheduleInstance", tmpScheduleInstanceTargetTable.QuotedSchemaAndName() },
{ "tmpTaskInstance", tmpTaskInstanceTargetTable.QuotedSchemaAndName() }
};
string InsertSQL = GenerateSQLStatementTemplates.GetSQL(Shared.GlobalConfigs.GetStringConfig("SQLTemplateLocation"), "InsertScheduleInstance_TaskInstance", SqlParams);
_con.ExecuteWithRetry(InsertSQL);
_con.Close();
}
public static async Task <JObject> StartAndStopVMsCore(HttpRequest req, Logging logging)
{
string requestBody = await new StreamReader(req.Body).ReadToEndAsync();
JObject data = JsonConvert.DeserializeObject <JObject>(requestBody);
string _TaskInstanceId = data["TaskInstanceId"].ToString();
string _ExecutionUid = data["ExecutionUid"].ToString();
try
{
logging.LogInformation("StartAndStopVMs function processed a request.");
string Subscription = data["Target"]["SubscriptionUid"].ToString();
string VmName = data["Target"]["VMname"].ToString();
string VmResourceGroup = data["Target"]["ResourceGroup"].ToString();
string VmAction = data["Target"]["Action"].ToString();
Microsoft.Azure.Management.Fluent.Azure.IAuthenticated azureAuth = Microsoft.Azure.Management.Fluent.Azure.Configure().WithLogLevel(HttpLoggingDelegatingHandler.Level.BodyAndHeaders).Authenticate(Shared.Azure.AzureSDK.GetAzureCreds(Shared._ApplicationOptions.UseMSI));
IAzure azure = azureAuth.WithSubscription(Subscription);
logging.LogInformation("Selected subscription: " + azure.SubscriptionId);
IVirtualMachine vm = azure.VirtualMachines.GetByResourceGroup(VmResourceGroup, VmName);
if (vm.PowerState == Microsoft.Azure.Management.Compute.Fluent.PowerState.Deallocated && VmAction.ToLower() == "start")
{
logging.LogInformation("VM State is: " + vm.PowerState.Value.ToString());
vm.StartAsync().Wait(5000);
logging.LogInformation("VM Start Initiated: " + vm.Name);
}
if (vm.PowerState != Microsoft.Azure.Management.Compute.Fluent.PowerState.Deallocated && VmAction.ToLower() == "stop")
{
logging.LogInformation("VM State is: " + vm.PowerState.Value.ToString());
vm.DeallocateAsync().Wait(5000);
logging.LogInformation("VM Stop Initiated: " + vm.Name);
}
JObject Root = new JObject {
["Result"] = "Complete"
};
TaskMetaDataDatabase TMD = new TaskMetaDataDatabase();
if (VmName != null)
{
Root["Result"] = "Complete";
}
else
{
Root["Result"] = "Please pass a name, resourcegroup and action to request body";
TMD.LogTaskInstanceCompletion(System.Convert.ToInt64(_TaskInstanceId), System.Guid.Parse(_ExecutionUid), TaskMetaData.BaseTasks.TaskStatus.FailedRetry, System.Guid.Empty, "Task missing VMname, ResourceGroup or SubscriptionUid in Target element.");
return(Root);
}
//Update Task Instance
TMD.LogTaskInstanceCompletion(System.Convert.ToInt64(_TaskInstanceId), System.Guid.Parse(_ExecutionUid), TaskMetaData.BaseTasks.TaskStatus.Complete, System.Guid.Empty, "");
return(Root);
}
catch (System.Exception TaskException)
{
logging.LogErrors(TaskException);
TaskMetaDataDatabase TMD = new TaskMetaDataDatabase();
TMD.LogTaskInstanceCompletion(System.Convert.ToInt64(_TaskInstanceId), System.Guid.Parse(_ExecutionUid), TaskMetaData.BaseTasks.TaskStatus.FailedRetry, System.Guid.Empty, "Failed when trying to start or stop VM");
JObject Root = new JObject
{
["Result"] = "Failed"
};
return(Root);
}
}
public static dynamic RunFrameworkTasksCore(HttpRequest req, Logging logging)
{
TaskMetaDataDatabase TMD = new TaskMetaDataDatabase();
short TaskRunnerId = System.Convert.ToInt16(req.Query["TaskRunnerId"]);
try
{
TMD.ExecuteSql(string.Format("Insert into Execution values ('{0}', '{1}', '{2}')", logging.DefaultActivityLogItem.ExecutionUid, DateTimeOffset.Now.ToString("u"), DateTimeOffset.Now.AddYears(999).ToString("u")));
//Fetch Top # tasks
JArray _Tasks = AdsGoFast.TaskMetaData.TaskInstancesStatic.GetActive_ADFJSON((Guid)logging.DefaultActivityLogItem.ExecutionUid, TaskRunnerId, logging);
var UtcCurDay = DateTime.UtcNow.ToString("yyyyMMdd");
foreach (JObject _Task in _Tasks)
{
long _TaskInstanceId = System.Convert.ToInt64(Shared.JsonHelpers.GetDynamicValueFromJSON(logging, "TaskInstanceId", _Task, null, true));
logging.DefaultActivityLogItem.TaskInstanceId = _TaskInstanceId;
//TO DO: Update TaskInstance yto UnTried if failed
string _pipelinename = _Task["DataFactory"]["ADFPipeline"].ToString();
System.Collections.Generic.Dictionary <string, object> _pipelineparams = new System.Collections.Generic.Dictionary <string, object>();
logging.LogInformation(string.Format("Executing ADF Pipeline for TaskInstanceId {0} ", _TaskInstanceId.ToString()));
//Check Task Type and execute appropriate ADF Pipeline
//Todo: Potentially extract switch into metadata
if (Shared._ApplicationOptions.TestingOptions.GenerateTaskObjectTestFiles)
{
string FileFullPath = Shared._ApplicationOptions.TestingOptions.TaskObjectTestFileLocation + /*UtcCurDay +*/ "/";
// Determine whether the directory exists.
if (!System.IO.Directory.Exists(FileFullPath))
{
// Try to create the directory.
System.IO.DirectoryInfo di = System.IO.Directory.CreateDirectory(FileFullPath);
}
FileFullPath = FileFullPath + _Task["TaskType"].ToString() + "_" + _pipelinename.ToString() + "_" + _Task["TaskMasterId"].ToString() + ".json";
System.IO.File.WriteAllText(FileFullPath, _Task.ToString());
TMD.LogTaskInstanceCompletion(_TaskInstanceId, (Guid)logging.DefaultActivityLogItem.ExecutionUid, TaskMetaData.BaseTasks.TaskStatus.Complete, System.Guid.Empty, "Complete");
}
else
{
try
{
if (_Task["TaskExecutionType"].ToString() == "ADF")
{
_pipelinename = "Master";
_pipelineparams.Add("TaskObject", _Task);
if (_pipelinename != "")
{
JObject _pipelineresult = ExecutePipeline.ExecutePipelineMethod(_Task["DataFactory"]["SubscriptionId"].ToString(), _Task["DataFactory"]["ResourceGroup"].ToString(), _Task["DataFactory"]["Name"].ToString(), _pipelinename, _pipelineparams, logging);
logging.DefaultActivityLogItem.AdfRunUid = Guid.Parse(_pipelineresult["RunId"].ToString());
TMD.GetSqlConnection().Execute(string.Format(@"
INSERT INTO TaskInstanceExecution (
[ExecutionUid]
,[TaskInstanceId]
,[DatafactorySubscriptionUid]
,[DatafactoryResourceGroup]
,[DatafactoryName]
,[PipelineName]
,[AdfRunUid]
,[StartDateTime]
,[Status]
,[Comment]
)
VALUES (
@ExecutionUid
,@TaskInstanceId
,@DatafactorySubscriptionUid
,@DatafactoryResourceGroup
,@DatafactoryName
,@PipelineName
,@AdfRunUid
,@StartDateTime
,@Status
,@Comment
)" ), new
{
ExecutionUid = logging.DefaultActivityLogItem.ExecutionUid.ToString(),
TaskInstanceId = System.Convert.ToInt64(_Task["TaskInstanceId"]),
DatafactorySubscriptionUid = _Task["DataFactory"]["SubscriptionId"].ToString(),
DatafactoryResourceGroup = _Task["DataFactory"]["ResourceGroup"].ToString(),
DatafactoryName = _Task["DataFactory"]["Name"].ToString(),
PipelineName = _pipelineresult["PipelineName"].ToString(),
AdfRunUid = Guid.Parse(_pipelineresult["RunId"].ToString()),
StartDateTime = DateTimeOffset.UtcNow,
Status = _pipelineresult["Status"].ToString(),
Comment = ""
});
}
//To Do // Batch to make less "chatty"
//To Do // Upgrade to stored procedure call
}
else if (_Task["TaskExecutionType"].ToString() == "AF")
{
//The "AF" branch is for calling Azure Function Based Tasks that do not require ADF. Calls are made async (just like the ADF calls) and calls are made using "AsyncHttp" requests even though at present the "AF" based Tasks reside in the same function app. This is to "future proof" as it is expected that these AF based tasks will be moved out to a separate function app in the future.
switch (_pipelinename)
{
case "AZ-Storage-SAS-Uri-SMTP-Email":
using (var client = new System.Net.Http.HttpClient())
{
//Lets get an access token based on MSI or Service Principal
var secureFunctionAPIURL = string.Format("{0}/api/GetSASUriSendEmailHttpTrigger", Shared._ApplicationOptions.ServiceConnections.CoreFunctionsURL);
var accessToken = Shared._AzureAuthenticationCredentialProvider.GetAzureRestApiToken(secureFunctionAPIURL);
using HttpRequestMessage httpRequestMessage = new HttpRequestMessage
{
Method = HttpMethod.Post,
RequestUri = new Uri(secureFunctionAPIURL),
Content = new StringContent(_Task.ToString(), System.Text.Encoding.UTF8, "application/json"),
Headers = { { System.Net.HttpRequestHeader.Authorization.ToString(), "Bearer " + accessToken } }
};
//Todo Add some error handling in case function cannot be reached. Note Wait time is there to provide sufficient time to complete post before the HttpClient is disposed.
var HttpTask = client.SendAsync(httpRequestMessage).Wait(3000);
}
break;
case "AZ-Storage-Cache-File-List":
using (var client = new System.Net.Http.HttpClient())
{
//Lets get an access token based on MSI or Service Principal
var secureFunctionAPIURL = string.Format("{0}/api/AZStorageCacheFileListHttpTrigger", Shared._ApplicationOptions.ServiceConnections.CoreFunctionsURL);
var accessToken = Shared._AzureAuthenticationCredentialProvider.GetAzureRestApiToken(secureFunctionAPIURL);
using HttpRequestMessage httpRequestMessage = new HttpRequestMessage
{
Method = HttpMethod.Post,
RequestUri = new Uri(secureFunctionAPIURL),
Content = new StringContent(_Task.ToString(), System.Text.Encoding.UTF8, "application/json"),
Headers = { { System.Net.HttpRequestHeader.Authorization.ToString(), "Bearer " + accessToken } }
};
//Todo Add some error handling in case function cannot be reached. Note Wait time is there to provide sufficient time to complete post before the HttpClient is disposed.
var HttpTask = client.SendAsync(httpRequestMessage).Wait(3000);
}
break;
case "StartAndStopVMs":
using (var client = new System.Net.Http.HttpClient())
{
//Lets get an access token based on MSI or Service Principal
var accessToken = GetSecureFunctionToken(_pipelinename);
using HttpRequestMessage httpRequestMessage = new HttpRequestMessage
{
Method = HttpMethod.Post,
RequestUri = new Uri(GetSecureFunctionURI(_pipelinename)),
Content = new StringContent(_Task.ToString(), System.Text.Encoding.UTF8, "application/json"),
Headers = { { System.Net.HttpRequestHeader.Authorization.ToString(), "Bearer " + accessToken } }
};
//Todo Add some error handling in case function cannot be reached. Note Wait time is there to provide sufficient time to complete post before the HttpClient is disposed.
var HttpTask = client.SendAsync(httpRequestMessage).Wait(3000);
}
break;
case "Cache-File-List-To-Email-Alert":
using (var client = new System.Net.Http.HttpClient())
{
SendAlert(_Task, logging);
}
break;
default:
var msg = $"Could not find execution path for Task Type of {_pipelinename} and Execution Type of {_Task["TaskExecutionType"].ToString()}";
logging.LogErrors(new Exception(msg));
TMD.LogTaskInstanceCompletion((Int64)_TaskInstanceId, (System.Guid)logging.DefaultActivityLogItem.ExecutionUid, BaseTasks.TaskStatus.FailedNoRetry, Guid.Empty, (String)msg);
break;
}
//To Do // Batch to make less "chatty"
//To Do // Upgrade to stored procedure call
}
}
catch (Exception TaskException)
{
logging.LogErrors(TaskException);
TMD.LogTaskInstanceCompletion((Int64)_TaskInstanceId, (System.Guid)logging.DefaultActivityLogItem.ExecutionUid, BaseTasks.TaskStatus.FailedNoRetry, Guid.Empty, (String)"Runner failed to execute task.");
}
}
}
}
catch (Exception RunnerException)
{
//Set Runner back to Idle
TMD.ExecuteSql(string.Format("exec [dbo].[UpdFrameworkTaskRunner] {0}", TaskRunnerId));
logging.LogErrors(RunnerException);
//log and re-throw the error
throw RunnerException;
}
//Set Runner back to Idle
TMD.ExecuteSql(string.Format("exec [dbo].[UpdFrameworkTaskRunner] {0}", TaskRunnerId));
//Return success
JObject Root = new JObject
{
["Succeeded"] = true
};
return(Root);
}
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(Shared.GlobalConfigs.GetStringConfig("HTMLTemplateLocation") + _emailTemplateFileName + ".txt");
_plainTextContent = _plainTextContent.FormatWith(Params, MissingKeyBehaviour.ThrowException, null, '{', '}');
string _htmlContent = System.IO.File.ReadAllText(Shared.GlobalConfigs.GetStringConfig("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);
}
}
public dynamic GetActivityLevelLogsCore(Logging logging)
{
string AppInsightsWorkspaceId = _appOptions.Value.ServiceConnections.AppInsightsWorkspaceId;
using var client = _appInsightsContext.httpClient.CreateClient(_appInsightsContext.httpClientName);
TaskMetaDataDatabase TMD = new TaskMetaDataDatabase();
using SqlConnection _conRead = TMD.GetSqlConnection();
//Get Last Request Date
var MaxTimesGenQuery = _conRead.QueryWithRetry(@"
select max([timestamp]) maxtimestamp from ActivityLevelLogs");
foreach (var datafactory in MaxTimesGenQuery)
{
DateTimeOffset MaxAllowedLogTimeGenerated = DateTimeOffset.UtcNow.AddDays(-1 * _appOptions.Value.ServiceConnections.AppInsightsMaxNumberOfDaysToRequest);
DateTimeOffset MaxObservedLogTimeGenerated = DateTimeOffset.UtcNow.AddDays(-1 * _appOptions.Value.ServiceConnections.AppInsightsMaxNumberOfDaysToRequest);
if (datafactory.maxtimestamp != null)
{
MaxObservedLogTimeGenerated = ((DateTimeOffset)datafactory.maxtimestamp).AddMinutes(-1 * _appOptions.Value.ServiceConnections.AppInsightsMinutesOverlap);
//Make sure that we don't get more than max to ensure we dont get timeouts etc.
if ((MaxObservedLogTimeGenerated) <= MaxAllowedLogTimeGenerated)
{
MaxObservedLogTimeGenerated = MaxAllowedLogTimeGenerated;
}
}
//string workspaceId = datafactory.LogAnalyticsWorkspaceId.ToString();
Dictionary <string, object> KqlParams = new Dictionary <string, object>
{
{ "MaxLogTimeGenerated", MaxObservedLogTimeGenerated.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(System.IO.Path.Combine(Shared._ApplicationBasePath, Shared._ApplicationOptions.LocalPaths.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(System.IO.Path.Combine(Shared._ApplicationBasePath, Shared._ApplicationOptions.LocalPaths.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 { });
}
