public WebServiceResult ProcessRequest(string function, string[] parameters, string postData)
{
try
{
switch (function)
{
case "addTarget":
return(AddTarget(postData));
case "deleteTarget":
return(DeleteTarget(parameters));
case "getMetricGroupSettings":
return(MetricGroupTable());
case "getServiceAccount":
return(GetServiceAccount());
case "updateMetricGroupSettings":
return(UpdateMetricGroups(postData));
case "updateMetricGroupScript":
return(UpdateMetricGroupScript(postData));
default:
return(WebServiceResult.ReturnError(String.Format("Unknown method: [{0}].", function)));
}
}
catch (Exception e)
{
_logger.Error(e.Message);
_logger.Error(e.StackTrace);
return(WebServiceResult.ReturnError(e.Message));
}
}
protected virtual WebServiceResult GetData(CommandType cmdType, string sqlQuery, SqlParameters sqlParameters)
{
string data;
try
{
using (SqlConnection reposConnection = new SqlConnection(Configuration.GetReposConnectionString("WebService")))
{
reposConnection.Open();
using (SqlCommand reposCommand = reposConnection.CreateCommand())
{
reposCommand.CommandText = sqlQuery;
reposCommand.CommandType = cmdType;
for (int i = 0; i < sqlParameters.Count; i++)
{
reposCommand.Parameters.Add(sqlParameters[i].Item1, SqlDbType.VarChar, sqlParameters[i].Item2.Length);
reposCommand.Parameters[sqlParameters[i].Item1].Value = sqlParameters[i].Item2;
}
// prepare the statement if there is at least one parameter
if (sqlParameters.Count > 0)
{
reposCommand.Prepare();
}
SqlDataReader dataReader = reposCommand.ExecuteReader();
data = "[";
while (dataReader.Read())
{
if (data.Length > 1)
{
data += ",";
}
if (!DBNull.Value.Equals(dataReader[0]))
{
data += (string)dataReader[0];
}
}
data += "]";
}
}
}
catch (Exception e)
{
_logger.Error("GetData: " + e.Message);
_logger.Debug(e.StackTrace);
return(WebServiceResult.ReturnError(e.Message));
}
return(WebServiceResult.ReturnSuccess(data));
}
public WebServiceResult ProcessRequest(string function, string[] parameters, string postData)
{
try
{
return(StatusTable());
}
catch (Exception e)
{
_logger.Error(e.Message);
_logger.Error(e.StackTrace);
return(WebServiceResult.ReturnError(e.Message));
}
}
private WebServiceResult UpdateMetricGroupScript(string postDataRaw)
{
int metricGroupId;
string scriptBase64;
string script;
string errorMsg;
if (string.IsNullOrEmpty(postDataRaw))
{
return(WebServiceResult.ReturnError("POST data cannot be NULL or empty"));
}
string postData = DecodeUriCharacters(postDataRaw);
_logger.Debug(postData);
try
{
// Extract metricGroupId and script (Base64 encoded)
foreach (Match itemsMatch in Regex.Matches(postData, @"id=([0-9]*)&script=([0-9a-zA-Z+/=]*)"))
{
if (itemsMatch.Groups.Count != 3)
{
return(WebServiceResult.ReturnError(@"Data is not in ([0-9]*)\[interval*\]=(\-?[0-9]*)&[0-9]*\[retention\]=(\-?[0-9]*) format"));
}
metricGroupId = Int32.Parse(itemsMatch.Groups[1].Value);
scriptBase64 = itemsMatch.Groups[2].Value;
_logger.Debug(scriptBase64);
script = Base64.Decode(scriptBase64);
_logger.Debug(script);
if (!_cfg.UpdateMetricGroupScript(metricGroupId, script, out errorMsg))
{
return(WebServiceResult.ReturnError(errorMsg));
}
}
return(WebServiceResult.ReturnSuccess());
}
catch (Exception e)
{
_logger.Error(e.Message);
return(WebServiceResult.ReturnError(e.Message));
}
}
// returns a set of datetime-value tuples in a JSON string
// param0: MetricGroupName - MetricGroup->Name
// param1: MetricName - MetricGroup->Metrics->Name
// param2: TargetId - Targets->Id
// param3: StartDateTime - YYYYMMDDHHMM
// param4: EndDateTime - YYYYMMDDHHMM
// param5: Interval - M in minutes
// test string ok http://localhost:3128/ws/fastsingle/Range/SQL%20Server%20Activity/CPU%20mils/0/201305201800/201305201900/5
// test string not ok http://localhost:3128/ws/fastsingle/Range/SQL%20Server%20Activity/CPU%20mils/0/201305201800/201305M900/5
private WebServiceResult Range(string[] parameters)
{
if (parameters.Count() < 6)
{
return(WebServiceResult.ReturnError(GetType().Name + ".Range(): not enough parameters. Format is MetricGroupName/MetricName/TargetId/StartDateTime/EndDateTime/IntervalInMinutes"));
}
Regex r = new Regex("^[0-9]*$");
// check that supplied target id is valid
if (!r.IsMatch(parameters[2]) || !Configuration.targets.ContainsId(Convert.ToInt32(parameters[2])))
{
return(WebServiceResult.ReturnError(GetType().Name + ".Range(): TargetId is either not numeric or doesn't exist"));
}
if (!r.IsMatch(parameters[3]) || !r.IsMatch(parameters[4]) || !r.IsMatch(parameters[5]))
{
return(WebServiceResult.ReturnError(GetType().Name + ".Range(): at least one of the parameters is not numeric. Format is TargetId/StartDateTime/EndDateTime/IntervalInMinutes"));
}
if (parameters[3].Length != 12 || parameters[4].Length != 12)
{
return(WebServiceResult.ReturnError(GetType().Name + ".Range(): StartDateTime and EndDateTime must be in YYYYMMDDHHMM format"));
}
// look up metric group by name
int metricGroupId = Configuration.metricGroups[parameters[0]].id;
// look up metric by name
string columnName = parameters[1].Replace(' ', '_');
// prepare parameters
SqlParameters sqlParameters = new SqlParameters
{
{ "@tablename", SqlServerProbe.DataTableName(Convert.ToInt32(parameters[2]), Configuration.metricGroups[metricGroupId]) },
{ "@columnname", columnName },
{ "@start_dt", SqlServerProbe.FormatDate(parameters[3]) },
{ "@end_dt", SqlServerProbe.FormatDate(parameters[4]) },
{ "@interval", parameters[5] }
};
// execute procedure and return results
return(GetData(System.Data.CommandType.StoredProcedure, "dbo.GetRangeSingleRowFastCumulative", sqlParameters));
}
public WebServiceResult ProcessRequest(string function, string[] parameters, string postData)
{
try
{
switch (function)
{
case "Range":
return(Range(parameters));
default:
return(WebServiceResult.ReturnError(GetType().Name + ".ProcessRequest(): function " + function + " could not be found"));
}
}
catch (Exception e)
{
_logger.Error(e.Message);
_logger.Debug(e.StackTrace);
return(WebServiceResult.ReturnError(e.Message));
}
}
private WebServiceResult UpdateMetricGroups(string postDataRaw)
{
int metricGroupId;
int interval;
int retention;
if (string.IsNullOrEmpty(postDataRaw))
{
return(WebServiceResult.ReturnError("POST data cannot be NULL or empty"));
}
string postData = DecodeUriCharacters(postDataRaw);
// Split into separate items one per metric group (metricGroupId[interval]=value&metricGroupId[retention]=value)
foreach (Match lineMatch in Regex.Matches(postData, @"([0-9]*\[interval*\]=\-?[0-9]*&[0-9]*\[retention\]=\-?[0-9]*)"))
{
_logger.Debug(lineMatch.Value);
// Extract metricGroupId, intervalValue and retentionValue
foreach (Match itemsMatch in Regex.Matches(lineMatch.Value, @"([0-9]*)\[interval*\]=(\-?[0-9]*)&[0-9]*\[retention\]=(\-?[0-9]*)"))
{
if (itemsMatch.Groups.Count != 4)
{
return(WebServiceResult.ReturnError(@"Data is not in ([0-9]*)\[interval*\]=(\-?[0-9]*)&[0-9]*\[retention\]=(\-?[0-9]*) format"));
}
metricGroupId = Int32.Parse(itemsMatch.Groups[1].Value);
interval = Int32.Parse(itemsMatch.Groups[2].Value);
retention = Int32.Parse(itemsMatch.Groups[3].Value);
_cfg.UpdateMetricGroupConfiguration(metricGroupId, interval, retention);
}
}
return(WebServiceResult.ReturnSuccess());
}
/// <summary>Adds new target(s). Checks that server name format is correct FDQN(\InstanceName)</summary>
/// <param name="rawPostData">Raw HTTP POST data</param>
/// <returns>Newline-separated list of errors (if any)</returns>
private WebServiceResult AddTarget(string rawPostData)
{
bool isSqlAuthentication = false;
if (string.IsNullOrEmpty(rawPostData))
{
return(WebServiceResult.ReturnError("POST data cannot be NULL or empty"));
}
var postData = ParsePostData(rawPostData);
if (!postData.ContainsKey("auth"))
{
return(WebServiceResult.ReturnError("Parameter [auth] must be specified"));
}
string auth = postData["auth"];
if (!auth.Equals("sql") && !auth.Equals("windows"))
{
return(WebServiceResult.ReturnError(String.Format("Unknown authentication type [{0}]", auth)));
}
if (auth.Equals("sql"))
{
isSqlAuthentication = true;
}
if (!postData.ContainsKey("serverName"))
{
return(WebServiceResult.ReturnError("Parameter [serverName] must be specified"));
}
string serverName = postData["serverName"];
serverName = Base64.Decode(serverName);
// Host name: http://en.wikipedia.org/wiki/Hostname#Restrictions_on_valid_host_names
// Instance name: http://technet.microsoft.com/en-us/library/ms143744(v=sql.90).aspx
Regex r = new Regex(@"^[a-zA-Z0-9][a-zA-Z0-9\.\-]{0,254}(\\[^\\,:;'&#@]{0,16})?$");
// check that server name format is correct
if (!r.IsMatch(serverName))
{
return(WebServiceResult.ReturnError(String.Format("Supplied server name [{0}] is not in correct format - FDQN[\\InstanceName]",
serverName)));
}
string sqlUsername = null;
string sqlPassword = null;
if (isSqlAuthentication)
{
if (!postData.ContainsKey("username") || !postData.ContainsKey("password"))
{
return(WebServiceResult.ReturnError("Both parameters [username] and [password] must be specified for SQL authentication"));
}
sqlUsername = postData["username"];
sqlPassword = postData["password"];
sqlUsername = Base64.Decode(sqlUsername);
}
_cfg.AddTarget(serverName, isSqlAuthentication, sqlUsername, sqlPassword);
return(WebServiceResult.ReturnSuccess());
} // end of AddTarget method
// returns a set of datetime-value tuples in a JSON string
// param0: MetricGroupName - MetricGroup->Name
// param1: TargetId - Targets->Id
// param2: StartDateTime - YYYYMMDDHHMM
// param3: EndDateTime - YYYYMMDDHHMM
// param4: Interval - M in minutes
// param5: MetricName - MetricGroup->metrics->name
// param6: NumOfRowsToReturn - Number of records to return (TOP X)
// param7: DictionaryKeyName - MetricGroup->multiRowKeys->name
// param8: Optional. Dictionary keys to exclude
// test string ok http://localhost:3128/ws/fastmulti/Range/SQL%20Server%20Wait%20Stats/0/201305201800/201305201900/5/Wait%20Time%20ms/5/Wait%20Type/
// test string not ok http://localhost:3128/ws/fastmulti/Range/SQL%20Server%20Wait%20Stats/0/201305201800/201305M900/5/Wait%20Time%20ms/10/Wait%20Type/
private WebServiceResult Range(string[] parameters)
{
if (parameters.Count() < 8)
{
return(WebServiceResult.ReturnError(GetType().Name + ".Range(): too few parameters. Format is MetricGroupName/TargetId/StartDateTime/EndDateTime/IntervalInMinutes/MetricName/DictionaryKeyName/[DictionaryKeysToExclude]"));
}
if (parameters.Count() > 9)
{
return(WebServiceResult.ReturnError(GetType().Name + ".Range(): too many parameters. Format is MetricGroupName/TargetId/StartDateTime/EndDateTime/IntervalInMinutes/MetricName/numOfRowsToReturn/DictionaryKeyName/[DictionaryKeysToExclude]"));
}
Regex r = new Regex("^[0-9]*$");
// check that supplied target id is valid
if (!r.IsMatch(parameters[1]) || !Configuration.targets.ContainsId(Convert.ToInt32(parameters[1])))
{
return(WebServiceResult.ReturnError(GetType().Name + ".Range(): TargetId is either not numeric or target with specified id doesn't exist"));
}
if (!r.IsMatch(parameters[2]) || !r.IsMatch(parameters[3]) || !r.IsMatch(parameters[4]))
{
return(WebServiceResult.ReturnError(GetType().Name + ".Range(): TargetId or StartDateTime or EndDateTime or Interval is not numeric. Format is MetricGroupName/TargetId/StartDateTime/EndDateTime/IntervalInMinutes/MetricName/DictionaryKeyName/[DictionaryKeysToExclude]"));
}
if (parameters[2].Length != 12 || parameters[3].Length != 12)
{
return(WebServiceResult.ReturnError(GetType().Name + ".Range(): StartDateTime and EndDateTime must be in YYYYMMDDHHMM format"));
}
if (!r.IsMatch(parameters[6]))
{
return(WebServiceResult.ReturnError(GetType().Name + ".Range(): NumOfRowsToReturn is not numeric"));
}
// look up metric group by name
var metricGroup = Configuration.metricGroups[parameters[0]];
string metricColumn = parameters[5].Replace(' ', '_');
string numOfRowsToReturn = parameters[6];
string exclusionColumn = parameters[7].Replace(' ', '_');
string excludedValues = string.Empty;
if (parameters.Count() == 9)
{
excludedValues = parameters[8];
}
// prepare parameters
SqlParameters sqlParameters = new SqlParameters
{
{ "@dataTable", SqlServerProbe.DataTableName(Convert.ToInt32(parameters[1]), metricGroup) },
{ "@dictionary", SqlServerProbe.DictTableName(Convert.ToInt32(parameters[1]), metricGroup) },
{ "@start_dt", SqlServerProbe.FormatDate(parameters[2]) },
{ "@end_dt", SqlServerProbe.FormatDate(parameters[3]) },
{ "@interval", parameters[4] },
{ "@metricColumn", metricColumn },
{ "@numOfRowsToReturn", numOfRowsToReturn },
{ "@exclusionColumn", exclusionColumn },
{ "@excludedValues", excludedValues }
};
// execute procedure and return results
if (metricGroup.isCumulative)
{
return(GetData(System.Data.CommandType.StoredProcedure, "dbo.GetRangeMultiRowFastCumulative", sqlParameters));
}
return(GetData(System.Data.CommandType.StoredProcedure, "dbo.GetRangeMultiRowFastNonCumulative", sqlParameters));
}