private DataTable ExecuteKustoQuery(string shortName)
{
var kustoContext = new KustoContext(shortName);
string kustoQuery = string.Format(@"MonRgLoad
| where originalEventTimestampTo > ago(30m)
| where event == ""cpu_total_load_cap_per_core"" and NodeName startswith ""DB"" and (node_sku contains ""G3"" or node_sku contains ""G4"" or node_sku contains ""GZ"" or node_sku contains ""G5"")
| project TIMESTAMP, node_sku, ClusterName , NodeName, node_user_cpu_usage, node_user_cpu_allocation
| extend node_cpu_cap = iff(node_sku contains ""G3"", 1400, iff(node_sku contains ""G4"", 2200, iff(node_sku contains ""G5"", 7200, 2800)))
| extend is_violated = iff(node_user_cpu_usage * 1.0 / node_cpu_cap > 0.8, 1, 0)
| summarize arg_max(TIMESTAMP, node_user_cpu_allocation), node_cpu_cap = any(node_cpu_cap), peak_cpu_usage = max(node_user_cpu_usage), node_sku = any(node_sku), cnt = count(), violation_cnt = sum(is_violated) by ClusterName, NodeName
| extend overbook_ratio = node_user_cpu_allocation * 1.0 / node_cpu_cap
| where violation_cnt * 1.0 / cnt > 0.9
| join kind = leftouter (
MonRgLoad
| where TIMESTAMP > ago(20m)
| where event == ""application_load""
| summarize max(app_cpu_load) by ClusterName, NodeName
) on ClusterName, NodeName
| where overbook_ratio > 1.05 or (overbook_ratio <= 1.05 and max_app_cpu_load * 1.0 / node_cpu_cap < 0.3)
| project ClusterName, NodeName, node_sku, overbook_ratio, peak_cpu_usage, node_cpu_cap");
var dt = kustoContext.ExecuteQuery(kustoQuery);
return(dt);
}
private string ExecuteCMSQuery(IcM icm)
{
//tr1.eastus1-a.worker.database.windows.net ad4356228452
var appName = icm.ApplicationName;
var kustoContext = new KustoContext(icm.ClusterName);
string query = Queries.AppBelowQuery2;
//query = string.Format(query, "tr1.eastus1-a.worker.database.windows.net", "ad4356228452");
string result = "There is no active instance";
var kutoQuery = query.Replace("Dynamic_AppName", icm.ApplicationName); //string.Format(query, icm.RingName, appName);
kutoQuery = kutoQuery.Replace("Dynamic_Source_Ring_Name", icm.RingName);
var dt1 = kustoContext.ExecuteQuery(kutoQuery);
if (dt1.Rows.Count <= 0)
{
if (icm.ApplicationName.Contains("v-"))
{
return($"{icm.ApplicationName} most of the v- appNames created by Backup/Restore team, Please check with them and route it to same");
}
else
{
return($"{icm.ApplicationName} is not able to move, please verify this app Name");
}
}
var command = dt1.Rows[0]["command"].ToString(); //Dynamic_Target_Ring_Name
string slotype = dt1.Rows[0]["slo"].ToString();
Regex rgx = new Regex(@"^cr\d+\.");
var runnerName = "AppBelowTargetReplicaCount";
var shortRunnerName = runnerName.Substring(runnerName.IndexOf('.', runnerName.IndexOf('.') + 1) + 1);
var ControlRingAddress = GetControlRingAddress(icm.ClusterName);//"cr3.westus2-a.control.database.windows.net";
var sterlingCasContext = new SterlingCasContext(shortRunnerName, rgx.Replace(ControlRingAddress, string.Empty), "", false);
if (!string.IsNullOrEmpty(slotype) && slotype.ToLower().Contains("gen4"))
{
slotype = "SQLG4VM";
}
else
{
slotype = "SQLG5";
}
var cmsQuery = string.Format(Queries.AppBelowQuery1, slotype);
DataTable dt = sterlingCasContext.QueryCms(cmsQuery).Tables[0];
if (dt.Rows.Count > 0)
{
result = command.Replace("Dynamic_Target_Ring_Name", dt.Rows[0]["target_tenant_ring"].ToString());
}
return(result);
}
public static async Task <HttpResponseMessage> Run([HttpTrigger(AuthorizationLevel.Function, "get", "post", Route = null)] HttpRequestMessage req, TraceWriter log)
{
log.Info("C# HTTP trigger function processed a request.");
// parse query parameter
string name = req.GetQueryNameValuePairs()
.FirstOrDefault(q => string.Compare(q.Key, "name", true) == 0)
.Value;
var list = new List <string>();
try
{
var kustoContext = new KustoContext();
//string kustoQuery = "MonSqlVm | where ClusterName == 'cr1.uksouth1-a.control.database.windows.net' | top 10 by AppName asc";
string kustoQuery = string.Format(@"Notifications | where PrimaryTargetType == 'TEAMID' | where PrimaryTarget in (38526, 38525, 37536, 38523, 38527, 38524, 36369, 32529, 10979, 22315, 10613, 11230, 36392, 36391)
| where AcknowledgeDate >= datetime('2019-02-25') and AcknowledgeDate <= datetime('2019-02-26')
| summarize by IncidentId
| top 10 by IncidentId");
var dt = kustoContext.ExecuteQuery(kustoQuery);
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
list.Add(row[0].ToString());
}
}
}
catch (Exception ex)
{
throw;
}
if (name == null)
{
// Get request body
dynamic data = await req.Content.ReadAsAsync <object>();
name = data?.name;
}
return(name == null
? req.CreateResponse(HttpStatusCode.BadRequest, "Please pass a name on the query string or in the request body")
: req.CreateResponse(HttpStatusCode.OK, "Hello " + name));
}