public bool CheckInstance(Instance instance, string nameFilter, string serverNameFilter, string versionFilter, string sqlversion)
{
bool result = true;
if (!string.IsNullOrEmpty(nameFilter))
{
result = result && instance.InstanceName.Contains(nameFilter);
}
if (!string.IsNullOrEmpty(serverNameFilter))
{
result = result && instance.ServerName.Contains(nameFilter);
}
if (!string.IsNullOrEmpty(versionFilter) && instance.InstanceVersion != null)
{
result = result && instance.InstanceVersion.Version.ToString().Contains(versionFilter);
}
if (instance.InstanceVersion != null)
{
SQLVersion version = SQLVersions.GetSQLVersionFromString(instance.InstanceVersion.Version);
result = result && SQLVersions.IsVersionInlist(version, sqlversion);
}
return(result);
}
public async Task <IActionResult> Crear([FromBody] CrearViewModel model)
{
if (!ModelState.IsValid)
{
return(BadRequest(ModelState));
}
SQLVersion sQLVersion = new SQLVersion
{
idsql = model.idsql,
mssqlversion = model.mssqlversion,
mssqldescription = model.mssqldescription,
estado = true
};
_context.SQLVersions.Add(sQLVersion);
try
{
await _context.SaveChangesAsync();
}
catch (Exception ex)
{
return(BadRequest());
}
return(Ok());
}
/// <summary>
/// Initializes a new instance of the <see cref="SQLVersionModel" /> class.
/// </summary>
/// <param name="createSVM">The version from API.</param>
public SQLVersionModel(SQLVersion createSVM)
{
this.SQLVersionId = createSVM.SQLVersionId;
this.Name = createSVM.Name;
this.Description = createSVM.Description;
this.IsActive = createSVM.IsActive;
}
/*
* You can find more info in this URL https://localhost:40000/sld/sld.svc/$metadata
* change the hostname for the sld of choice
*/
private void AddDatabaseInstance(SQLVersion TheSQLVersion, string InstanceName, string CommonDBName, string DBUsername, string DBPassword)
{
try
{
string p_sqlServerType = null;
switch (TheSQLVersion)
{
case SQLVersion.HANA:
p_sqlServerType = "HANA";
break;
case SQLVersion.MSSQL2008:
p_sqlServerType = "MSSQL2008";
break;
case SQLVersion.MSSQL2008R2:
p_sqlServerType = "MSSQL2008";
break;
case SQLVersion.MSSQL2012:
p_sqlServerType = "MSSQL2012";
break;
case SQLVersion.MSSQL2014:
p_sqlServerType = "MSSQL2014";
break;
case SQLVersion.MSSQL2016:
p_sqlServerType = "MSSQL2016";
break;
}
DataServiceQuery <DatabaseInstance> result_query;
if (TheSQLVersion == SQLVersion.HANA)
{
result_query = sldDataServiceContext.CreateQuery <DatabaseInstance>("AddDatabaseInstance").
AddQueryOption("DBInstance", string.Format("'{0}'", InstanceName)).
AddQueryOption("ServerType", string.Format("'{0}'", p_sqlServerType)).
AddQueryOption("IsTrustedConnection", string.Format("'{0}'", "False")).
AddQueryOption("Username", string.Format("'{0}'", DBUsername)).
AddQueryOption("Password", string.Format("'{0}'", DBPassword)).
AddQueryOption("CommonDB", string.Format("'{0}'", CommonDBName));
}
else
{
result_query = sldDataServiceContext.CreateQuery <DatabaseInstance>("AddDatabaseInstance").
AddQueryOption("DBInstance", string.Format("'{0}'", InstanceName)).
AddQueryOption("ServerType", string.Format("'{0}'", p_sqlServerType)).
AddQueryOption("IsTrustedConnection", string.Format("'{0}'", "False")).
AddQueryOption("Username", string.Format("'{0}'", DBUsername)).
AddQueryOption("Password", string.Format("'{0}'", DBPassword)).
AddQueryOption("CommonDB", string.Format("'{0}'", CommonDBName));
}
DatabaseInstance result = result_query.FirstOrDefault();
if (result == null)
{
throw new Exception("AddDatabaseInstance - incorrect return value");
}
}
catch
{
throw;
}
}
public int Perform(string SLDURL, string SLD_Username, string SLD_Password, string InstanceName, SQLVersion TheSQLVersion, string DBUsername, string DBPassword)
{
int p_errorCode = 0;
try
{
cookies = new CookieContainer();
Console.WriteLine("Login to SLD");
bool p_logon = LoginIntoSLD(SLDURL, SLD_Username, SLD_Password);
if (!p_logon)
{
Console.WriteLine("SLD Logon failed. LogonByNamedUser failed for user: "******"\n\n");
p_errorCode = -5;
return(p_errorCode);
}
string p_commonDBName = null;
if (TheSQLVersion == SQLVersion.HANA)
{
p_commonDBName = "SBOCOMMON";
}
else
{
p_commonDBName = "SBO-COMMON";
}
Console.WriteLine("Add Instance");
try
{
AddDatabaseInstance(TheSQLVersion, InstanceName, p_commonDBName, DBUsername, DBPassword);
}
catch (Exception ex)
{
if (ex.ToString().Contains("You cannot add entity; entity already exists"))
{
Console.WriteLine("Entity already exists\n\n" + ex.ToString() + "\n\n");
p_errorCode = -4;
}
else
{
throw;
}
}
}
catch (Exception ex)
{
Console.WriteLine("General Error\n\n" + ex.ToString() + "\n\n");
p_errorCode = -3;
}
return(p_errorCode);
}
private static Dictionary <String, Query> LoadQuery(SQLVersion ver)
{
Dictionary <String, Query> list = new Dictionary <String, Query>();
CollectConfigHandler appconfig = null;
Query.QueryTarget target = Query.QueryTarget.Instance;
ResourceSet res = null;
IDictionaryEnumerator files = null;
CollectConfigItem conf = null;
int system_query_num = 0;
int db_query_num = 0;
int instance_query_num = 0;
// 外部クエリのロード
if (Parameters.ExternalQueryDir != null)
{
foreach (string filename in System.IO.Directory.GetFiles(Parameters.ExternalQueryDir, "*.sql"))
{
using (StreamReader sr = new StreamReader(filename, System.Text.Encoding.Default))
{
// ファイル名が"Instance（大文字小文字を区別しない）"で開始されていた場合はインスタンスレベルのクエリであると解釈する
if (Path.GetFileName(filename).StartsWith("Instance", true, null))
{
target = Query.QueryTarget.Instance;
instance_query_num++;
}
// そうでない場合は、DB名が指定されている場合はDBレベルのクエリであると解釈する
else
{
if (Parameters.DatabaseName != null || Parameters.DatabaseNameList != null)
{
target = Query.QueryTarget.Database;
db_query_num++;
}
else
{
target = Query.QueryTarget.Instance;
instance_query_num++;
}
}
list.Add(Path.GetFileNameWithoutExtension(filename)
, new Query(sr.ReadToEnd(), target));
#if TRACE
logger.Info("ExternalQuery = " + Path.GetFileNameWithoutExtension(filename)
+ ", Target = " + target.ToString()
);
#endif
}
}
}
// 内部クエリのロード
if (!Parameters.IsExternalQueryOnly)
{
appconfig = (CollectConfigHandler)ConfigurationManager.GetSection("CollectConfig");
//内部クエリのロード
res = Properties.Resources.ResourceManager.GetResourceSet(new CultureInfo("en-US"), true, true);
if (res == null)
{
//リソースファイルが見つかりません
throw new Exception("Resource file can not be found");
}
files = res.GetEnumerator();
while (files.MoveNext())
{
String key = files.Entry.Key.ToString();
conf = (CollectConfigItem)appconfig.Query[key];
if (conf != null)
{
//HasFlag は .net v4以降
//if (conf.Run
// && ((SQLVersion)conf.Version).HasFlag(ver))
if (
conf.Run
&&
(
((((SQLVersion)conf.Version) & ver) == ver)
||
(conf.Version == 0)
)
)
{
if (conf.Target.ToString().Equals("System"))
{
target = Query.QueryTarget.System;
system_query_num++;
}
else if (conf.Target.ToString().Equals("Instance"))
{
target = Query.QueryTarget.Instance;
instance_query_num++;
}
else if (conf.Target.ToString().Equals("Database"))
{
target = Query.QueryTarget.Database;
db_query_num++;
}
else
{
logger.Error(String.Format("Invalid target string({0}),", conf.Target.ToString()));
}
list.Add(files.Entry.Key.ToString()
, new Query(files.Entry.Value.ToString(), target));
}
#if TRACE
logger.Info("InternalQuery = " + conf.Name.ToString()
+ ", Version = " + String.Format("0x{0:X4}", conf.Version)
+ ", Target = " + conf.Target.ToString()
+ ", Run = " + conf.Run.ToString()
/* + ", Query = " + files.Entry.Value.ToString() */
);
#endif
conf = null;
}
}
}
#if TRACE
logger.Info("Total Query Count= " + (system_query_num + instance_query_num + db_query_num).ToString()
+ ", System Query Count = " + system_query_num.ToString()
+ ", Instance Query Count = " + instance_query_num.ToString()
+ ", Database Query Count = " + db_query_num.ToString()
);
#endif
return(list);
}
static void Main(string[] args)
{
SqlConnection Conn = null;
SqlCommand Cmd = null;
SQLVersion DBVersion = SQLVersion.UNKNOWN;
List <String> DBNameList = null;
Dictionary <String, Query> QueryList = null;
String OutputDir = String.Empty;
try
{
//実行環境の設定(作業ディレクトリの設定とlog4netによるログ出力先の設定)
SetProcEnv();
//１．処理開始の通知
logger.Info("1/10. Start.");
//２．コマンドライン引数の解析
logger.Info("2/10. Parse CommandLine Parameters.");
ParseCommandLineParams(args);
using (Conn = new SqlConnection(BuildConnectionString(Parameters)))
using (Cmd = new SqlCommand())
{
//３．コネクションオープン
logger.Info("3/10. Try to Connect SQL Server.");
#if TRACE
logger.Info("ConnectionString = " + Conn.ConnectionString);
#endif
Conn.Open();
Cmd.Connection = Conn;
SetSessionEnv(Cmd);
#if DEBUG
if (Parameters.IsDebug)
{
DebugCode(Cmd);
}
#endif
//４．バージョンチェック
logger.Info("4/10. Check SQL Server Version.");
DBVersion = CheckDBVersion(Cmd);
if (DBVersion == SQLVersion.UNKNOWN)
{
// エラーのケースは２通り
// ・DBのバージョンがツールのサポート対象外のケース
// ・設定ファイルで指定した対象DBバージョンと実際のDBバージョンが異なるケース
logger.Error("This database version does not support tool execution.");
//このデータベースバージョンはツールの実行がサポートされていません。
Environment.Exit(1);
}
//５．対象DB一覧取得
logger.Info("5/10. Get Database List.");
DBNameList = GetDBNameList(Cmd);
if (DBNameList == null)
{
// エラーのケースは１通り
// ・コマンドライン引数 -d が無指定 ＆ システムDB以外のユーザDBが一つも存在しないケース
logger.Error("Please specify the target database with -d or -D.");
// -d or -D で対象のデータベースを指定してください。
Environment.Exit(1);
}
else
{
if (!Parameters.IsSilentMode)
{
Console.WriteLine();
Console.WriteLine(
" The target SQL Server has "
+ DBNameList.Count.ToString()
+ " databases to collect data."
+ "Processing takes time depending on the number of databases."
);
Console.WriteLine();
Console.WriteLine(" To continue the process, type Y, otherwise type N.");
Console.WriteLine();
Console.Write(" (Type Y or N) : ");
//対象のSQL Serverには情報収集の対象となるデータベースがN個あります。
//データベースの個数に応じて処理に時間を要します。
//処理を継続する場合はYをそうでなければNをタイプしてください。
if (!String.Equals(Console.ReadLine(), "Y"))
{
logger.Info("Execution canceled.");
//処理をキャンセルしました。
Environment.Exit(1);
}
}
}
//６．出力フォルダ（OutputRootDir + yyyyMMddhhmmss）作成
logger.Info("6/10. Create Result Folder.");
OutputDir = Path.Combine(Parameters.OutputRootDir
, @"mssqlstats_" + DateTime.Now.ToString("yyyyMMddhhmmss"));
foreach (String dbname in DBNameList)
{
Directory.CreateDirectory(Path.Combine(OutputDir, dbname));
#if TRACE
logger.Info("OutputDir = " + Path.Combine(OutputDir, dbname));
#endif
}
//７．実行クエリのロード
logger.Info("7/10. Load Query.");
QueryList = LoadQuery(DBVersion);
if (QueryList == null)
{
logger.Fatal("Executable query was not found.");
//実行可能なクエリが見つかりませんでした。
Environment.Exit(1);
}
//８．クエリ実行 ＆ CSV出力
logger.Info("8/10. Execute Query.");
SqlDataReader reader = null;
foreach (String key in QueryList.Keys)
{
try
{
switch (QueryList[key].Target)
{
case Query.QueryTarget.System:
#if TRACE
logger.Info("System:" + key + "->" + OutputDir + @"\" + key + ".csv");
#endif
ManagementScope scope = new ManagementScope(String.Format(@"\\{0}\root\cimv2", Parameters.Server));
WriteCSV(scope, QueryList[key].Text, OutputDir + @"\" + key + ".csv");
//scope = new ManagementScope(String.Format(@"\\{0}\root\rsop\computer", m.Groups["server"].Value));
//WriteCSV(scope, "SELECT Name FROM RSOP_UserPrivilegeRight", OutputDir + @"\" + key + ".csv");
break;
case Query.QueryTarget.Instance:
#if TRACE
logger.Info("Instance:" + key + "->" + OutputDir + @"\" + key + ".csv");
#endif
Cmd.CommandText = "USE [master]; " + QueryList[key].Text;
using (reader = Cmd.ExecuteReader())
{
WriteCSV(reader, OutputDir + @"\" + key + ".csv");
}
break;
case Query.QueryTarget.Database:
foreach (String dbname in DBNameList)
{
#if TRACE
logger.Info("DB(" + dbname + "):" + key + "->" + OutputDir + @"\" + dbname + @"\" + key + ".csv");
#endif
Cmd.CommandText = "USE [" + dbname + @"];" + QueryList[key].Text;
using (reader = Cmd.ExecuteReader())
{
WriteCSV(reader, OutputDir + @"\" + dbname + @"\" + key + ".csv");
}
}
break;
default:
logger.Fatal("It is an illegal execution route...");
//不正な実行ルートです。
Environment.Exit(1);
break;
}
}
catch (Exception ex)
{
logger.Error("query exec error.");
logger.Error(ex.Message);
logger.Error(ex.StackTrace);
}
}
//stats系DMVをクリア（既定はクリアしない）
if (Parameters.IsStatsClear)
{
logger.Info("Clear Stats.");
ClearStats(Cmd);
}
//９．出力フォルダをZIP圧縮 ＆ 出力フォルダを削除
logger.Info("9/10. Archive Result Folder.");
ArchiveOutputFiles(OutputDir);
//１０．処理完了の通知
logger.Info("10/10. Completed.");
CopyLogFile(OutputDir);
}
}
//ユーザエラー
catch (MssqlStatsException ex)
{
logger.Error(ex.Message);
}
//内部エラー
catch (Exception ex)
{
logger.Fatal("The tool terminated abnormally.");
logger.Fatal(ex.Message);
logger.Fatal(ex.StackTrace);
}
logger.Info("Done.");
}
private static SQLVersion CheckDBVersion(SqlCommand cmd)
{
SQLVersion Version = SQLVersion.UNKNOWN;
cmd.CommandText = Properties.Resources._GET_PRODUCT_VERSION;
String v = cmd.ExecuteScalar().ToString();
Regex r = new Regex(@"^\d+\.\d+", RegexOptions.IgnoreCase);
switch (r.Match(v).Value)
{
case "9.0":
Version = SQLVersion.SQL2005;
break;
case "10.0":
Version = SQLVersion.SQL2008;
break;
case "10.50":
Version = SQLVersion.SQL2008R2;
break;
case "11.0":
Version = SQLVersion.SQL2012;
break;
case "12.0":
Version = SQLVersion.SQL2014;
break;
case "13.0":
Version = SQLVersion.SQL2016;
break;
case "14.0":
Version = SQLVersion.SQL2017;
break;
default:
//DBのバージョンがツールのサポート対象外のケース
Version = SQLVersion.UNKNOWN;
break;
}
#if TRACE
logger.Info("SQL Server ProductVersion = " + v);
logger.Info("SQL Server Version = " + Version.ToString());
logger.Info("DB version specified in App.config = " + ConfigurationManager.AppSettings["verbit"]);
#endif
if (!Parameters.IsBypassVersionCheck)
{
//ツールが想定しているDBバージョン（＝App.configでの設定値）と対象となる実際のDBバージョンが等しいかをチェック
//if (!String.Format("0x{0:X4}", (int)Version).Equals(ConfigurationManager.AppSettings["verbit"]))
if ((((SQLVersion)Convert.ToInt32(ConfigurationManager.AppSettings["verbit"], 16))
& Version) != Version)
{
//設定ファイルで指定した対象DBバージョン(16進数)と実際のDBバージョンが異なるケース
/* SQL2005(9.0) = 0x0001
* SQL2008(10.0) = 0x0002
* SQL2008R2(10.50) = 0x0004
* SQL2012(11.0) = 0x0008
* SQL2014(12.0) = 0x0010
* SQL2016(13.0) = 0x0020
* SQL2017(14.0) = 0x0040 */
Version = SQLVersion.UNKNOWN;
}
}
return(Version);
}
public static bool IsVersionInlist(SQLVersion version, string list)
{
switch (version)
{
case SQLVersion.SQL2000:
if (list.ElementAt(6) == '1')
{
return(true);
}
else
{
return(false);
}
case SQLVersion.SQL2005:
if (list.ElementAt(5) == '1')
{
return(true);
}
else
{
return(false);
}
case SQLVersion.SQL2008:
if (list.ElementAt(4) == '1')
{
return(true);
}
else
{
return(false);
}
case SQLVersion.SQL2012:
if (list.ElementAt(3) == '1')
{
return(true);
}
else
{
return(false);
}
case SQLVersion.SQL2014:
if (list.ElementAt(2) == '1')
{
return(true);
}
else
{
return(false);
}
case SQLVersion.SQL2016:
if (list.ElementAt(1) == '1')
{
return(true);
}
else
{
return(false);
}
case SQLVersion.OTHER:
if (list.ElementAt(0) == '1')
{
return(true);
}
else
{
return(false);
}
default:
return(false);
}
return(false);
}