public List <Core.PluginMetadata> GetPluginList(string monitoredSystemMAC)
{
using (var dataContext = DataContextFactory.CreateReadOnlyDataContext())
{
int monitoredSystemID = PrecompiledQueries.GetMonitoredSystemIDByMAC(dataContext, monitoredSystemMAC);
return(MISD.Server.Manager.PluginManager.Instance.GetPluginList(monitoredSystemID));
}
}
public bool UploadIndicatorValues(string monitoredSystemMAC, string pluginName, List <Tuple <string, Object, MISD.Core.DataType, DateTime> > indicatorValues)
{
using (var dataContext = DataContextFactory.CreateReadOnlyDataContext())
{
int monitoredSystemID = PrecompiledQueries.GetMonitoredSystemIDByMAC(dataContext, monitoredSystemMAC);
return(MISD.Server.Manager.WorkstationManager.Instance.UploadIndicatorValues(monitoredSystemID, pluginName, indicatorValues));
}
}
public long GetUpdateInterval(string monitoredSystemMAC, string pluginName, string indicatorName)
{
using (var dataContext = DataContextFactory.CreateReadOnlyDataContext())
{
int monitoredSystemID = PrecompiledQueries.GetMonitoredSystemIDByMAC(dataContext, monitoredSystemMAC);
return(MISD.Server.Manager.UpdateIntervalManager.Instance.GetUpdateInterval(monitoredSystemID, pluginName, indicatorName));
}
}
public string GetFilter(string monitoredSystemMAC, string pluginName, string indicatorName)
{
using (var dataContext = DataContextFactory.CreateReadOnlyDataContext())
{
int monitoredSystemID = PrecompiledQueries.GetMonitoredSystemIDByMAC(dataContext, monitoredSystemMAC);
return(FilterManager.Instance.GetFilter(monitoredSystemID, pluginName, indicatorName));
}
}
public List <Tuple <string, string> > GetFilters(string monitoredSystemMAC, string pluginName)
{
using (var dataContext = DataContextFactory.CreateReadOnlyDataContext())
{
int monitoredSystemID = PrecompiledQueries.GetMonitoredSystemIDByMAC(dataContext, monitoredSystemMAC);
return(MISD.Server.Manager.FilterManager.Instance.GetFilters(monitoredSystemID, pluginName));
}
}
public List <Core.PluginFile> DownloadPlugins(string monitoredSystemMAC, List <string> pluginNames)
{
using (var dataContext = DataContextFactory.CreateReadOnlyDataContext())
{
int monitoredSystemID = PrecompiledQueries.GetMonitoredSystemIDByMAC(dataContext, monitoredSystemMAC);
byte monitoredSystemPlatform = PrecompiledQueries.GetMonitoredSystemPlatformByID(dataContext, monitoredSystemID);
return(MISD.Server.Manager.PluginManager.Instance.DownloadPlugins((Core.Platform)monitoredSystemPlatform, pluginNames));
}
}
public bool UploadIndicatorValue(string monitoredSystemMAC, string pluginName, string indicatorValueName, object value, DataType valueDataType, DateTime aquiredTimestamp)
{
using (var dataContext = DataContextFactory.CreateReadOnlyDataContext())
{
int monitoredSystemID = PrecompiledQueries.GetMonitoredSystemIDByMAC(dataContext, monitoredSystemMAC);
//Manager.Logger.Instance.WriteEntry("Reveiving Data from " + monitoredSystemMAC + " via single UploadIndicatorValue", LogType.Info);
List <Tuple <string, object, DataType, DateTime> > list = new List <Tuple <string, object, DataType, DateTime> >();
list.Add(new Tuple <string, object, DataType, DateTime>(indicatorValueName, value, valueDataType, aquiredTimestamp));
return(MISD.Server.Manager.WorkstationManager.Instance.UploadIndicatorValues(monitoredSystemID, pluginName, list));
}
}
/// <summary>
/// Updates the mapping state of a monitored system, if the mapping duration of this monitored system is out of date
/// </summary>
/// <param name="monitoredSystemID">MAC Adress of the monitored system to update</param>
public void UpdateMappingState(string monitoredSystemMac)
{
try
{
using (var dataContext = DataContextFactory.CreateDataContext())
{
var monitoredSystem = PrecompiledQueries.GetMonitoredSystemByMAC(dataContext, monitoredSystemMac);
MappingState newState = MappingState.Critical;
// Critical state
if (monitoredSystem.CriticalEnd != null)
{
if (monitoredSystem.CriticalEnd < DateTime.Now.Ticks)
{
newState = MappingState.Warning;
}
}
else
{
newState = MappingState.Warning;
}
// Warning state
if (monitoredSystem.WarningEnd != null)
{
if (monitoredSystem.WarningEnd < DateTime.Now.Ticks)
{
newState = MappingState.OK;
}
}
else
{
newState = MappingState.OK;
}
// Maintenance state
if (PrecompiledQueries.GetMonitoredSystemMaintenanceModeByID(dataContext, monitoredSystem.ID))
{
newState = MappingState.Maintenance;
}
monitoredSystem.Status = (byte)newState;
dataContext.SubmitChanges();
}
}
catch (Exception e)
{
Logger.Instance.WriteEntry("ValueManager_UpdateMappingState: Can't update the mapping of workstation (MAC: " + monitoredSystemMac + "). " + e.StackTrace, LogType.Exception);
}
}
/// <summary>
/// Updates the mapping state of a monitored system, if the mapping duration of this monitored system is out of date
/// </summary>
/// <param name="monitoredSystemMac">ID of the monitored system to update</param>
public void UpdateMappingState(int monitoredSystemID)
{
try
{
using (var dataContext = DataContextFactory.CreateReadOnlyDataContext())
{
var monitoredSystem = PrecompiledQueries.GetMonitoredSystemByID(dataContext, monitoredSystemID);
if (monitoredSystem.MacAddress != null)
{
UpdateMappingState(monitoredSystem.MacAddress);
}
}
}
catch (Exception e)
{
Logger.Instance.WriteEntry("ValueManager_UpdateMappingState: Can't update the mapping of workstation " + monitoredSystemID + ". " + e.StackTrace, LogType.Exception);
}
}
/// <summary>
/// Retuns all observer of an monitored system
/// </summary>
/// <param name="userID">ID of the users email adress</param>
/// <returns>List of MAC adress </returns>
public List <WorkstationInfo> GetObserver(int userID)
{
List <WorkstationInfo> result = new List <WorkstationInfo>();
try
{
using (var dataContext = DataContextFactory.CreateReadOnlyDataContext())
{
var msIDs = (from q in dataContext.EmailObserver
where q.EmailID == userID
select q.MonitoredSystem.ID).ToList();
foreach (var ms in msIDs)
{
//update mapping state
ValueManager.Instance.UpdateMonitoredSystemMappingState(ms);
var monitoredSystem = PrecompiledQueries.GetMonitoredSystemByID(dataContext, ms);
//create result
var workstationinfo = new WorkstationInfo()
{
Name = monitoredSystem.Name,
ID = monitoredSystem.ID,
FQDN = monitoredSystem.FQDN,
OuID = monitoredSystem.OrganizationalUnitID,
IsAvailable = monitoredSystem.IsAvailable,
CurrentOS = ((MISD.Core.Platform)monitoredSystem.OperatingSystem).ToString(),
MacAddress = monitoredSystem.MacAddress,
State = monitoredSystem.Status.HasValue ? (MappingState)monitoredSystem.Status : MappingState.OK
};
result.Add(workstationinfo);
}
}
return(result);
}
catch (Exception e)
{
Logger.Instance.WriteEntry("Mailer_GetObserver: Can't create result: " + e.StackTrace, LogType.Exception);
return(null);
}
}
/// <summary>
/// Loads the HTML-template for the warning mail and generates the mail
/// Template tags: [%WSName%], [%Date%], [%PluginName%], [%Indicator%], [%Value%]
/// </summary>
/// <returns>String containing the HTML-Email-Message</returns>
private string GenerateWarningMail(int monitoredSystemID, DateTime criticalValueReceived,
string pluginName, string indicator, string criticalValue)
{
string template = "";
//instantiate the WarningMailParser
parser.ClearTags();
using (var dataContext = DataContextFactory.CreateReadOnlyDataContext())
{
StreamReader reader = null;
try
{
string name = PrecompiledQueries.GetMonitoredSystemNameByID(dataContext, monitoredSystemID);
parser.AddTag("[%WSName%]", name);
parser.AddTag("[%Date%]", criticalValueReceived.ToString());
parser.AddTag("[%PluginName%]", pluginName);
parser.AddTag("[%Indicator%]", indicator);
parser.AddTag("[%Value%]", criticalValue);
//load the warning mail template
reader = new StreamReader(TemplatePath);
template = reader.ReadToEnd();
}
catch (Exception e)
{
//logging
StringBuilder messageEx1 = new StringBuilder();
messageEx1.Append("Mailer_GenerateWarningMail: ");
messageEx1.Append("Can't load warning mail template. " + e.ToString());
Logger.Instance.WriteEntry(messageEx1.ToString(), LogType.Exception);
}
finally
{
reader.Close();
}
//Parese template string
return(parser.ParseTemplateString(template));
}
}
/// <summary>
/// Gets the complete data for serveral plugins of certain workstations and a certain timespan.
/// </summary>
/// <param name="macAndProperties"> A list containing tuples of: MAC | PluginName | IndicatorName | LowerBound? | UpperBound? | Maximum numer of results?</param>
/// <returns>A list containing tuples of: MAC | PluginName | IndicatorName | Value | Mapping | Time</returns>
public List <Tuple <string, string, string, string, MappingState, DateTime> > GetDataOLDAPPROACH(List <Tuple <string, string, string, DateTime?, DateTime?, int?> > macAndProperties)
{
#region check arguments
if (macAndProperties == null || macAndProperties.Count == 0)
{
throw new ArgumentException("macAndProperties list item false.", "macAndProperties");
}
#endregion
List <Tuple <string, string, string, string, MappingState, DateTime> > result = new List <Tuple <string, string, string, string, MappingState, DateTime> >();
using (var dataContext = DataContextFactory.CreateReadOnlyDataContext())
{
//current: (MAC | PluginName | IndicatorName | LowerBound? | UpperBound? | Maximum numer of results?)
foreach (var c in macAndProperties)
{
try
{
// TODO Arno entfernen
//break;
#region check arguments
if (c.Item1 == null || c.Item1 == "")
{
throw new ArgumentException("macAndProperties list item false.", "macAdress (Item1)");
}
if (c.Item2 == null || c.Item2 == "")
{
throw new ArgumentException("macAndProperties list item false.", "pluginName (Item2)");
}
if (c.Item3 == null || c.Item3 == "")
{
throw new ArgumentException("macAndProperties list item false.", "IndicatorName (Item3)");
}
#endregion
//update mapping state
UpdateMappingState(c.Item1);
//set numbers of values
int numbersOfValues = (c.Item6 != null && c.Item6 > 0) ? (int)c.Item6 : int.MaxValue;
long lowerTicks = (c.Item4 != null) ? c.Item4.Value.Ticks : 0;
long upperTicks = (c.Item5 != null) ? c.Item5.Value.Ticks : long.MaxValue;
#region caching for single results, continuing in loop
if (numbersOfValues == 1)
{
Tuple <string, string, string, string, MappingState, DateTime> value = GetCachedIndicator(c.Item1, c.Item2, c.Item3);
if (value != null)
{
result.Add(value);
continue;
}
}
#endregion
//get valueX type
var type = (from q in dataContext.Indicator
where q.Name == c.Item3
select q.ValueType).FirstOrDefault();
if (!(type == (byte)Core.DataType.Byte || type == (byte)Core.DataType.Float || type == (byte)Core.DataType.Int || type == (byte)Core.DataType.String))
{
continue;
}
//get valueX, create sub-results and add them to the result
#region byte
if (type == (byte)Core.DataType.Byte)
{
var byteResult = PrecompiledQueries.GetByteValues(dataContext, c.Item1, c.Item2, c.Item3, lowerTicks, upperTicks, numbersOfValues);
result.AddRange(byteResult);
}
#endregion
#region int
if (type == (byte)DataType.Int)
{
var intResult = PrecompiledQueries.GetIntValues(dataContext, c.Item1, c.Item2, c.Item3, lowerTicks, upperTicks, numbersOfValues);
result.AddRange(intResult);
}
#endregion
#region float
if (type == (byte)DataType.Float)
{
var floatResult = PrecompiledQueries.GetFloatValues(dataContext, c.Item1, c.Item2, c.Item3, lowerTicks, upperTicks, numbersOfValues);
result.AddRange(floatResult);
}
#endregion
#region string
if (type == (byte)DataType.String)
{
var stringResult = PrecompiledQueries.GetStringValues(dataContext, c.Item1, c.Item2, c.Item3, lowerTicks, upperTicks, numbersOfValues);
result.AddRange(stringResult);
}
#endregion
//cache first element
if (numbersOfValues == 1 && result.ElementAt(0) != null)
{
this.CacheIndicator(result.ElementAt(0));
}
}
catch (Exception e)
{
#region logging
//logging exception
var messageEx = new StringBuilder();
messageEx.Append("ValueManager_GetData: Can't create List of PluginDatas" + ". " + e.ToString() + " StackTrace: " + e.StackTrace);
MISD.Core.Logger.Instance.WriteEntry(messageEx.ToString(), LogType.Exception);
#endregion
}
}
}
//TODO remove this log
Logger.Instance.WriteEntry("ValueManager_GetData: Data createtd and returned: " + result.Count + " elements for " + macAndProperties.Count() + " mac/plugin/indicator combis starting with " + macAndProperties.First().Item1 + " and Plugin " + macAndProperties.First().Item2, LogType.Debug);
return(result);
}
public List <WorkstationInfo> GetWorkstationInfo(List <Tuple <int, TimeSpan> > workstationsWithReset)
{
List <WorkstationInfo> returns = new List <WorkstationInfo>();
using (var dataContext = DataContextFactory.CreateDataContext())
{
dataContext.Log = Console.Out;
try
{
foreach (Tuple <int, TimeSpan> current in workstationsWithReset)
{
//update mapping state
UpdateMappingState(current.Item1);
var monitoredSystem = PrecompiledQueries.GetMonitoredSystemByID(dataContext, current.Item1);
WorkstationInfo workstationInfo;
if (monitoredSystem.LastUpdate != null)
{
//create result
workstationInfo = new WorkstationInfo()
{
Name = monitoredSystem.Name,
ID = monitoredSystem.ID,
FQDN = monitoredSystem.FQDN,
OuID = monitoredSystem.OrganizationalUnitID,
IsAvailable = monitoredSystem.IsAvailable,
CurrentOS = ((MISD.Core.Platform)monitoredSystem.OperatingSystem).ToString(),
MacAddress = monitoredSystem.MacAddress,
State = monitoredSystem.Status.HasValue ? (MappingState)monitoredSystem.Status : MappingState.OK,
LastUpdate = new DateTime((long)monitoredSystem.LastUpdate)
};
}
else
{
//create result
workstationInfo = new WorkstationInfo()
{
Name = monitoredSystem.Name,
ID = monitoredSystem.ID,
FQDN = monitoredSystem.FQDN,
OuID = monitoredSystem.OrganizationalUnitID,
IsAvailable = monitoredSystem.IsAvailable,
CurrentOS = ((MISD.Core.Platform)monitoredSystem.OperatingSystem).ToString(),
MacAddress = monitoredSystem.MacAddress,
State = monitoredSystem.Status.HasValue ? (MappingState)monitoredSystem.Status : MappingState.OK,
LastUpdate = null
};
}
returns.Add(workstationInfo);
}
return(returns);
}
catch (Exception e)
{
//logging exception
var messageEx = new StringBuilder();
messageEx.Append("Can't create List of WorkstationInfos" + ". " + e.ToString());
MISD.Core.Logger.Instance.WriteEntry(messageEx.ToString(), LogType.Exception);
return(null);
}
}
}
/// <summary>
/// Registers a new node in the database.
/// </summary>
/// <param name="node">The node to be registered.</param>
/// <param name="OUName">The name of the OU for the node.</param>
private void RegisterNode(WorkstationInfo node, string OUName)
{
using (var dataContext = DataContextFactory.CreateDataContext())
{
Database.MonitoredSystem monitoredSystem = null;
try
{
try
{
monitoredSystem = PrecompiledQueries.GetMonitoredSystemByID(dataContext, PrecompiledQueries.GetMonitoredSystemIDByMAC(dataContext, node.MacAddress));
}
catch (Exception)
{
monitoredSystem = null;
}
// monitored system is not yet known in the db
if (monitoredSystem == null)
{
monitoredSystem = new MonitoredSystem();
monitoredSystem.OrganizationalUnitID = this.OUID;
monitoredSystem.Name = node.Name;
monitoredSystem.FQDN = node.FQDN;
monitoredSystem.IsAvailable = node.IsAvailable;
monitoredSystem.IsIgnored = false;
monitoredSystem.OperatingSystem = (byte)PlatformHelper.ParsePlatform(node.CurrentOS);
monitoredSystem.MacAddress = node.MacAddress;
dataContext.MonitoredSystem.InsertOnSubmit(monitoredSystem);
dataContext.SubmitChanges();
//logging info
var messageOK = new StringBuilder();
messageOK.Append("ClusterManager_RegisterNode: ");
messageOK.Append("Cluster node " + monitoredSystem.Name + " ");
messageOK.Append("(" + monitoredSystem.OperatingSystem.ToString() + ") ");
messageOK.Append("is now added to the system.");
MISD.Core.Logger.Instance.WriteEntry(messageOK.ToString(), LogType.Info);
}
else
{
if (node.IsAvailable)
{
monitoredSystem.IsAvailable = node.IsAvailable;
monitoredSystem.Name = node.Name;
monitoredSystem.FQDN = node.FQDN;
monitoredSystem.OperatingSystem = (byte)PlatformHelper.ParsePlatform(node.CurrentOS);
monitoredSystem.OrganizationalUnitID = this.OUID;
}
else
{
// no changes necessary as the node might be booted under a different cluster
}
dataContext.SubmitChanges();
}
node.ID = monitoredSystem.ID;
}
catch (Exception e)
{
//logging exception
string workstationLogName;
string osLogName;
if (monitoredSystem != null)
{
workstationLogName = monitoredSystem.Name;
osLogName = monitoredSystem.OperatingSystem.ToString();
}
else
{
workstationLogName = "[unkown]";
osLogName = "system unkown";
}
var messageEx2 = new StringBuilder();
messageEx2.Append("ClusterManager_RegisterNode: ");
messageEx2.Append("Cluster node" + workstationLogName + " ");
messageEx2.Append("(" + osLogName + ") ");
messageEx2.Append("sign in has failed. " + e.ToString());
MISD.Core.Logger.Instance.WriteEntry(messageEx2.ToString(), LogType.Exception);
}
}
}
static void Main(string[] args)
{
Console.WriteLine("<-<-<-<-<PERFORMANCETEST>->->->->");
var macs = ValueManagerOld.Instance.GetWorkstationMACs();
List <string> macList = new List <string>();
for (int i = 0; i < macs.Count; i++)
{
if (i % 20 == 0)
{
macList.Add(macs.ElementAt(i));
Console.WriteLine("Added " + i + ". system " + macs.ElementAt(i));
}
}
#region new optimal approach
DateTime now3 = DateTime.Now;
Console.WriteLine("Starting NEW OPTIMAL APPROACH");
#region manual query
for (int j = 0; j < 1; j++)
{
List <Tuple <string, string, string, string, MappingState, DateTime> > result2 = new List <Tuple <string, string, string, string, MappingState, DateTime> >();
using (var dataContext = DataContextFactory.CreateReadOnlyDataContext())
{
foreach (var mac in macList)
{
var ID = PrecompiledQueries.GetMonitoredSystemIDByMAC(dataContext, mac);
var OS = PrecompiledQueries.GetMonitoredSystemPlatformByID(dataContext, ID);
DateTime superNow = DateTime.Now;
var query = new StringBuilder();
query.Append("select p.Name as PluginName, i.Name, Timestamp, iv.Mapping, i.ValueType, ");
query.Append("vb.Value as ByteValue, vi.Value as IntValue, ");
query.Append("vf.Value as FloatValue, vs.Value as StringValue from IndicatorValue iv ");
query.Append("left join Indicator i on iv.IndicatorID = i.ID ");
query.Append("left join PluginMetadata p on p.ID = i.PluginMetadataID ");
query.Append("left join ValueByte vb on vb.ID = iv.ValueID ");
query.Append("left join ValueInt vi on vi.ID = iv.ValueID ");
query.Append("left join ValueFloat vf on vf.ID = iv.ValueID ");
query.Append("left join ValueString vs on vs.ID = iv.ValueID ");
query.Append("where iv.ID in (select MAX(ID) from IndicatorValue ");
query.Append("where IndicatorID in ");
query.Append("(select ID from Indicator where MonitoredSystemID = " + ID + " and ");
query.Append("PluginMetadataID in (select ID from MISD.dbo.PluginMetadata Where [Platform] = " + OS + ")) ");
query.Append("group by IndicatorID)");
var result = dataContext.ExecuteQuery <QueryResult>(query.ToString()).ToList();
foreach (var item in result)
{
switch ((ValueType)item.ValueType)
{
case ValueType.Byte:
result2.Add(new Tuple <string, string, string, string, MappingState, DateTime>
(
mac,
item.PluginName,
item.Name,
item.ByteValue.ToString(),
(MappingState)item.Mapping,
new DateTime((long)item.Timestamp)
)
);
break;
case ValueType.Int:
result2.Add(new Tuple <string, string, string, string, MappingState, DateTime>
(
mac,
item.PluginName,
item.Name,
item.IntValue.ToString(),
(MappingState)item.Mapping,
new DateTime((long)item.Timestamp)
)
);
break;
case ValueType.Float:
result2.Add(new Tuple <string, string, string, string, MappingState, DateTime>
(
mac,
item.PluginName,
item.Name,
item.FloatValue.ToString(),
(MappingState)item.Mapping,
new DateTime((long)item.Timestamp)
)
);
break;
case ValueType.String:
result2.Add(new Tuple <string, string, string, string, MappingState, DateTime>
(
mac,
item.PluginName,
item.Name,
item.StringValue.ToString(),
(MappingState)item.Mapping,
new DateTime((long)item.Timestamp)
)
);
break;
default:
break;
}
}
DateTime superThen = DateTime.Now;
Console.WriteLine(Math.Round(superThen.Subtract(superNow).TotalMilliseconds) + " milliseconds for id " + mac + " needed ");
}
}
Console.WriteLine("Count: " + result2.Count());
//foreach (var entry in result2)
//{
// Console.WriteLine(entry.Item1 + " " + entry.Item2 + " " + entry.Item3 + " " + entry.Item4);
//}
}
#endregion
Console.WriteLine("Finished");
DateTime then3 = DateTime.Now;
TimeSpan difference3 = then3.Subtract(now3);
Console.WriteLine("Needed time RAW: " + difference3.TotalSeconds);
#endregion
#region new different approach
//DateTime now = DateTime.Now;
//Console.WriteLine("Starting NEW DIFFERENT METHOD");
//for (int j = 0; j < 1; j++)
//{
// var values = ValueManager.Instance.GetLatestMonitoredSystemData(macList);
// Console.WriteLine("Count: " + values.Count());
// foreach (var entry in values)
// {
// Console.WriteLine(entry.Item2 + " " + entry.Item3 + " " + entry.Item4);
// }
//}
//Console.WriteLine("Finished");
//DateTime then = DateTime.Now;
//TimeSpan difference = then.Subtract(now);
//Console.WriteLine("Needed time NEW DIFFERENT METHOD: " + difference.TotalSeconds);
#endregion
#region new approach
//DateTime now0 = DateTime.Now;
//Console.WriteLine("Starting NEW");
//for (int j = 0; j < 1; j++)
//{
// var values = ValueManager.Instance.GetCompletePluginData(macList, 1);
// Console.WriteLine("Count: " + values.Count());
// //foreach (var entry in values)
// //{
// // Console.WriteLine(entry.Item2 + " " + entry.Item3 + " " + entry.Item4);
// //}
//}
//Console.WriteLine("Finished");
//DateTime then0 = DateTime.Now;
//TimeSpan difference0 = then0.Subtract(now0);
//Console.WriteLine("Needed time NEW: " + difference0.TotalSeconds);
#endregion
#region raw approach
DateTime now1 = DateTime.Now;
//Console.WriteLine("Starting RAW APPROACH");
//for (int j = 0; j < 1; j++)
//{
// // Replace these with a valid user name and password to access the database.
// string userName = "******";
// string password = "******";
// if (userName == "" || password == "")
// {
// Console.WriteLine("Please set database user name and password in the code first.");
// return;
// }
// string whereClause = "";
// //if (!ReadInput(out whereClause))
// // return;
// Console.WriteLine("Retrieving values...");
// // Measure the time from before opening the connection to after retrieving the result set.
// DateTime startTime = DateTime.Now;
// SqlConnection connection = new SqlConnection(
// "Data Source=acid.visus.uni-stuttgart.de;Initial Catalog=MISD;" +
// "Persist Security Info=True;User ID=" + userName + ";Password="******"select m.Name, i.Name, Timestamp, i.ValueType, " +
// "vb.Value as ByteValue, vf.Value as FloatValue, vi.Value as IntValue, " +
// "vs.Value as StringValue from IndicatorValue v " +
// "left join Indicator i on v.IndicatorID = i.ID " +
// "left join MonitoredSystem m on m.ID = i.MonitoredSystemID " +
// "left join ValueByte vb on vb.ID = v.ValueID " +
// "left join ValueFloat vf on vf.ID = v.ValueID " +
// "left join ValueInt vi on vi.ID = v.ValueID " +
// "left join ValueString vs on vs.ID = v.ValueID " +
// "where v.ID in (select MAX(ID) from IndicatorValue " +
// //whereClause +
// "group by IndicatorID) " +
// "order by m.Name, i.Name",
// connection);
// SqlDataReader reader = command.ExecuteReader();
// TimeSpan queryTime = DateTime.Now - startTime;
// // Print and count the results.
// int values = PrintResults(reader);
// reader.Dispose();
// command.Dispose();
// connection.Dispose();
// // Print the statistics.
// Console.WriteLine();
// Console.WriteLine("Read a total of " + values + " values in " + queryTime);
//}
//Console.WriteLine("Finished");
//DateTime then1 = DateTime.Now;
//TimeSpan difference1 = then1.Subtract(now1);
//Console.WriteLine("Needed time RAW APPROACH: " + difference1.TotalSeconds);
#endregion
#region raw approach custom
//DateTime now2 = DateTime.Now;
//Console.WriteLine("Starting RAW APPROACH");
//for (int j = 0; j < 1; j++)
//{
// var whereClause = "where IndicatorID in (select ID from Indicator " +
// "where MonitoredSystemID = 1)";
// whereClause = "";
// using (DataContext context = DataContextFactory.CreateReadOnlyDataContext())
// {
// var result = context.ExecuteQuery<ValueResult>(
// "select m.Name, i.Name as IndicatorName, Timestamp, i.ValueType, " +
// "vb.Value as ByteValue, vf.Value as FloatValue, vi.Value as IntValue, " +
// "vs.Value as StringValue from IndicatorValue v " +
// "left join Indicator i on v.IndicatorID = i.ID " +
// "left join MonitoredSystem m on m.ID = i.MonitoredSystemID " +
// "left join ValueByte vb on vb.ID = v.ValueID " +
// "left join ValueFloat vf on vf.ID = v.ValueID " +
// "left join ValueInt vi on vi.ID = v.ValueID " +
// "left join ValueString vs on vs.ID = v.ValueID " +
// "where v.ID in (select MAX(ID) from IndicatorValue " +
// whereClause +
// "group by IndicatorID) " +
// "order by m.Name, i.Name"
// ).ToList();
// Console.WriteLine("Count: " + result.Count());
// //foreach (var item in result)
// //{
// // Console.WriteLine("name: " + item.name + " indicator: " + item.indicatorname + "time: " + item.timestamp + " type: " + item.valuetype);
// // Console.WriteLine(" " + item.stringvalue + " / " + item.floatvalue + " / " + item.intvalue + " / " + item.bytevalue);
// //}
// }
//}
//Console.WriteLine("Finished");
//DateTime then2 = DateTime.Now;
//TimeSpan difference2 = then2.Subtract(now2);
//Console.WriteLine("Needed time RAW APPROACH: " + difference2.TotalSeconds);
#endregion
#region old approach
//DateTime now1 = DateTime.Now;
//Console.WriteLine("Starting OLD");
//for (int j = 0; j < 1; j++)
//{
// var values = ValueManagerOld.Instance.GetCompletePluginData(macList, 1);
// Console.WriteLine("Count: " + values.Count());
// //foreach (var entry in values)
// //{
// // Console.WriteLine(entry.Item2 + " " + entry.Item3 + " " + entry.Item4);
// //}
//}
//Console.WriteLine("Finished");
//DateTime then1 = DateTime.Now;
//TimeSpan difference1 = then1.Subtract(now1);
//Console.WriteLine("Needed time OLD: " + difference1.TotalSeconds);
#endregion
#region precompiled
//DateTime now2 = DateTime.Now;
//Console.WriteLine("Starting PRECOMPILED");
//#region manual query
//for (int j = 0; j < 1; j++)
//{
// List<Tuple<string, string, string, string, MappingState, DateTime>> result = new List<Tuple<string, string, string, string, MappingState, DateTime>>();
// using (var dataContext = DataContextFactory.CreateReadOnlyDataContext())
// {
// foreach (var mac in macList)
// {
// for (int i = 0; i < 1; i++)
// {
// var pluginNames = (from p in dataContext.Indicator
// where p.MonitoredSystem.MacAddress == mac
// select p.PluginMetadata.Name).Distinct().ToList();
// foreach (var pName in pluginNames)
// {
// var indicators = (from q in dataContext.Indicator
// where q.MonitoredSystem.MacAddress == mac
// where q.PluginMetadata.Name == pName
// select new Tuple<string, byte, int>(q.Name, q.ValueType, q.ID)).ToList();
// foreach (var iName in indicators)
// {
// if (iName.Item2 == (byte)Core.DataType.Byte)
// {
// var byteResult = PrecompiledQueries.GetByteValuesFast(dataContext, mac, pName, iName.Item1, iName.Item3, 0, long.MaxValue, 1);
// result.AddRange(byteResult);
// }
// if (iName.Item2 == (byte)DataType.Int)
// {
// var intResult = PrecompiledQueries.GetIntValuesFast(dataContext, mac, pName, iName.Item1, iName.Item3, 0, long.MaxValue, 1);
// result.AddRange(intResult);
// }
// if (iName.Item2 == (byte)DataType.Float)
// {
// var floatResult = PrecompiledQueries.GetFloatValuesFast(dataContext, mac, pName, iName.Item1, iName.Item3, 0, long.MaxValue, 1);
// result.AddRange(floatResult);
// }
// if (iName.Item2 == (byte)DataType.String)
// {
// var stringResult = PrecompiledQueries.GetStringValuesFast(dataContext, mac, pName, iName.Item1, iName.Item3, 0, long.MaxValue, 1);
// result.AddRange(stringResult);
// }
// }
// }
// }
// }
// }
// Console.WriteLine("Count: " + result.Count());
// foreach (var entry in result)
// {
// Console.WriteLine(entry.Item2 + " " + entry.Item3 + " " + entry.Item4);
// }
//}
//#endregion
//Console.WriteLine("Finished");
//DateTime then2 = DateTime.Now;
//TimeSpan difference2 = then2.Subtract(now2);
//Console.WriteLine("Needed time PRECOMPILED: " + difference2.TotalSeconds);
#endregion
#region raw sql
//DateTime now3 = DateTime.Now;
//Console.WriteLine("Starting RAW");
//#region manual query
//for (int j = 0; j < 1; j++)
//{
// List<Tuple<string, string, string, string, MappingState, DateTime>> result2 = new List<Tuple<string, string, string, string, MappingState, DateTime>>();
// using (var dataContext = DataContextFactory.CreateReadOnlyDataContext())
// {
// foreach (var mac in macList)
// {
// for (int i = 0; i < 1; i++)
// {
// //DateTime now = DateTime.Now;
// var pluginNames = (from p in dataContext.Indicator
// where p.MonitoredSystem.MacAddress == mac
// select new Tuple<string, int>(p.PluginMetadata.Name, p.PluginMetadata.ID)).Distinct().ToList();
// //DateTime then = DateTime.Now;
// //Console.WriteLine(Math.Round(then.Subtract(now).TotalMilliseconds) + " milliseconds for query mac " + mac + " needed ");
// foreach (var plugin in pluginNames)
// {
// //DateTime superNow = DateTime.Now;
// var indicators = (from p in dataContext.Indicator
// where p.MonitoredSystem.MacAddress == mac
// where p.PluginMetadataID == plugin.Item2
// select new Tuple<string, byte, int>(p.Name, p.ValueType, p.ID)).ToList();
// //DateTime superThen = DateTime.Now;
// //Console.WriteLine(Math.Round(superThen.Subtract(superNow).TotalMilliseconds) + " milliseconds for query plugin " + plugin.Item1 + " needed ");
// //foreach (var indi in indicators)
// //{
// // Console.WriteLine("indicator: " + indi);
// //}
// foreach (var iName in indicators)
// {
// DateTime superNow2 = DateTime.Now;
// if (iName.Item2 == (byte)Core.DataType.Byte)
// {
// var query = new StringBuilder();
// //"select m.Name, i.Name as IndicatorName, Timestamp, i.ValueType, " +
// //"vb.Value as ByteValue, vf.Value as FloatValue, vi.Value as IntValue, " +
// //"vs.Value as StringValue from IndicatorValue v " +
// //"left join Indicator i on v.IndicatorID = i.ID " +
// //"left join MonitoredSystem m on m.ID = i.MonitoredSystemID " +
// //"left join ValueByte vb on vb.ID = v.ValueID " +
// //"left join ValueFloat vf on vf.ID = v.ValueID " +
// //"left join ValueInt vi on vi.ID = v.ValueID " +
// //"left join ValueString vs on vs.ID = v.ValueID " +
// //"where v.ID in (select MAX(ID) from IndicatorValue " +
// ////whereClause +
// //"group by IndicatorID) " +
// //"order by m.Name, i.Name"
// query.Append("select Timestamp, iv.Mapping, v.Value from IndicatorValue iv ");
// query.Append("left join Indicator i on iv.IndicatorID = i.ID ");
// query.Append("left join ValueByte v on v.ID = iv.ValueID ");
// query.Append("where iv.ID in (select TOP 1 ID from IndicatorValue ");
// query.Append("where IndicatorID = " + iName.Item3 + " order by ID desc)");
// //query.Append("SELECT IV.Timestamp, IV.Mapping, V.Value FROM ");
// //query.Append("((SELECT MAX(ID) FROM MISD.dbo.IndicatorValue IV where ");
// //query.Append("IV.IndicatorID=" + iName.Item3);
// //query.Append(") A JOIN MISD.dbo.ValueByte V on V.ID = A.ValueID) ");
// //query.Append("ORDER BY A.Timestamp DESC");
// var tempResult = dataContext.ExecuteQuery<ValueResultByte>(query.ToString()).ToList();
// foreach (var b in tempResult)
// {
// Tuple<string, string, string, string, MappingState, DateTime> t = new Tuple<string, string, string, string, MappingState, DateTime>
// (mac,
// plugin.Item1,
// iName.Item1,
// b.Value.ToString(),
// (MappingState)b.Mapping,
// new DateTime(b.Timestamp));
// result2.Add(t);
// }
// }
// if (iName.Item2 == (byte)DataType.Int)
// {
// var query = new StringBuilder();
// query.Append("select Timestamp, iv.Mapping, v.Value from IndicatorValue iv ");
// query.Append("left join Indicator i on iv.IndicatorID = i.ID ");
// query.Append("left join ValueInt v on v.ID = iv.ValueID ");
// query.Append("where iv.ID in (select TOP 1 ID from IndicatorValue ");
// query.Append("where IndicatorID = " + iName.Item3 + " order by ID desc)");
// //query.Append("SELECT IV.Timestamp, IV.Mapping, V.Value FROM ");
// //query.Append("((SELECT MAX(ID) FROM MISD.dbo.IndicatorValue IV where ");
// //query.Append("IV.IndicatorID=" + iName.Item3);
// //query.Append(") A JOIN MISD.dbo.ValueInt V on V.ID = A.ValueID) ");
// //query.Append("ORDER BY A.Timestamp DESC");
// var tempResult = dataContext.ExecuteQuery<ValueResultInt>(query.ToString()).ToList();
// foreach (var b in tempResult)
// {
// Tuple<string, string, string, string, MappingState, DateTime> t = new Tuple<string, string, string, string, MappingState, DateTime>
// (mac,
// plugin.Item1,
// iName.Item1,
// b.Value.ToString(),
// (MappingState)b.Mapping,
// new DateTime(b.Timestamp));
// result2.Add(t);
// }
// }
// if (iName.Item2 == (byte)DataType.Float)
// {
// var query = new StringBuilder();
// query.Append("select Timestamp, iv.Mapping, v.Value from IndicatorValue iv ");
// query.Append("left join Indicator i on iv.IndicatorID = i.ID ");
// query.Append("left join ValueFloat v on v.ID = iv.ValueID ");
// query.Append("where iv.ID in (select TOP 1 ID from IndicatorValue ");
// query.Append("where IndicatorID = " + iName.Item3 + " order by ID desc)");
// //query.Append("SELECT IV.Timestamp, IV.Mapping, V.Value FROM ");
// //query.Append("((SELECT MAX(ID) FROM MISD.dbo.IndicatorValue IV where ");
// //query.Append("IV.IndicatorID=" + iName.Item3);
// //query.Append(") A JOIN MISD.dbo.ValueFloat V on V.ID = A.ValueID) ");
// //query.Append("ORDER BY A.Timestamp DESC");
// var tempResult = dataContext.ExecuteQuery<ValueResultFloat>(query.ToString()).ToList();
// foreach (var b in tempResult)
// {
// Tuple<string, string, string, string, MappingState, DateTime> t = new Tuple<string, string, string, string, MappingState, DateTime>
// (mac,
// plugin.Item1,
// iName.Item1,
// b.Value.ToString(),
// (MappingState)b.Mapping,
// new DateTime(b.Timestamp));
// result2.Add(t);
// }
// }
// if (iName.Item2 == (byte)DataType.String)
// {
// var query = new StringBuilder();
// query.Append("select Timestamp, iv.Mapping, v.Value from IndicatorValue iv ");
// query.Append("left join Indicator i on iv.IndicatorID = i.ID ");
// query.Append("left join ValueString v on v.ID = iv.ValueID ");
// query.Append("where iv.ID in (select TOP 1 ID from IndicatorValue ");
// query.Append("where IndicatorID = " + iName.Item3 + " order by ID desc)");
// //query.Append("SELECT IV.Timestamp, IV.Mapping, V.Value FROM ");
// //query.Append("((SELECT MAX(ID) FROM MISD.dbo.IndicatorValue IV where ");
// //query.Append("IV.IndicatorID=" + iName.Item3);
// //query.Append(") A JOIN MISD.dbo.ValueString V on V.ID = A.ValueID) ");
// //query.Append("ORDER BY A.Timestamp DESC");
// var tempResult = dataContext.ExecuteQuery<ValueResultString>(query.ToString()).ToList();
// foreach (var b in tempResult)
// {
// Tuple<string, string, string, string, MappingState, DateTime> t = new Tuple<string, string, string, string, MappingState, DateTime>
// (mac,
// plugin.Item1,
// iName.Item1,
// b.Value.ToString(),
// (MappingState)b.Mapping,
// new DateTime(b.Timestamp));
// result2.Add(t);
// }
// }
// DateTime superThen2 = DateTime.Now;
// Console.WriteLine(Math.Round(superThen2.Subtract(superNow2).TotalMilliseconds) + " milliseconds for query indicator " + iName + " needed ");
// }
// }
// }
// }
// }
// Console.WriteLine("Count: " + result2.Count());
// //foreach (var entry in result2)
// //{
// // Console.WriteLine(entry.Item2 + " " + entry.Item3 + " " + entry.Item4);
// //}
//}
//#endregion
//Console.WriteLine("Finished");
//DateTime then3 = DateTime.Now;
//TimeSpan difference3 = then3.Subtract(now3);
//Console.WriteLine("Needed time RAW: " + difference3.TotalSeconds);
#endregion
Console.WriteLine("<-<-<-<-<LEVEL L>->->->->");
// build input
List <Tuple <string, string, string, DateTime?, DateTime?, int?> > input = new List <Tuple <string, string, string, DateTime?, DateTime?, int?> >();
foreach (var mac in macList)
{
input.Add(new Tuple <string, string, string, DateTime?, DateTime?, int?>(mac, "CPU", "Load", new DateTime(0), new DateTime(2100, 1, 1), 40));
input.Add(new Tuple <string, string, string, DateTime?, DateTime?, int?>(mac, "CPU", "ProcessorName", new DateTime(0), new DateTime(2100, 1, 1), 40));
input.Add(new Tuple <string, string, string, DateTime?, DateTime?, int?>(mac, "RAM", "Size", new DateTime(0), new DateTime(2100, 1, 1), 40));
input.Add(new Tuple <string, string, string, DateTime?, DateTime?, int?>(mac, "Storage", "NamePerDrive", new DateTime(0), new DateTime(2100, 1, 1), 40));
input.Add(new Tuple <string, string, string, DateTime?, DateTime?, int?>(mac, "Events", "Event", new DateTime(0), new DateTime(2100, 1, 1), 40));
}
// result
List <Tuple <string, string, string, string, MappingState, DateTime> > queryResult = new List <Tuple <string, string, string, string, MappingState, DateTime> >();
#region new
Console.WriteLine("Starting Level L new");
DateTime nowLevelL = DateTime.Now;
#region query
using (var dataContext = DataContextFactory.CreateReadOnlyDataContext())
{
foreach (var currentValue in input)
{
var ID = PrecompiledQueries.GetMonitoredSystemIDByMAC(dataContext, currentValue.Item1);
var OS = PrecompiledQueries.GetMonitoredSystemPlatformByID(dataContext, ID);
DateTime superNow = DateTime.Now;
var query = new StringBuilder();
//query.Append("select TOP " + currentValue.Item6 + " p.Name as PluginName, iv.Name, iv.Timestamp, iv.Mapping, iv.ValueType, ");
//query.Append("vb.Value as ByteValue, vi.Value as IntValue, ");
//query.Append("vf.Value as FloatValue, vs.Value as StringValue from IndiValComb iv ");
//query.Append("left join PluginMetadata p on p.ID = iv.PluginMetadataID ");
//query.Append("left join ValueByte vb on vb.ID = iv.ValueID ");
//query.Append("left join ValueInt vi on vi.ID = iv.ValueID ");
//query.Append("left join ValueFloat vf on vf.ID = iv.ValueID ");
//query.Append("left join ValueString vs on vs.ID = iv.ValueID ");
//query.Append("where iv.ID in (select ID from Indicator where MonitoredSystemID = " + ID + " and ");
//query.Append("PluginMetadataID in (select ID from PluginMetadata where Name = '" + currentValue.Item2 + "') and ");
//query.Append("Name = '" + currentValue.Item3 + "') and ");
//query.Append("Timestamp >= " + ((DateTime)currentValue.Item4).Ticks + " and ");
//query.Append("Timestamp <= " + ((DateTime)currentValue.Item5).Ticks + " ");
//query.Append("order by iv.Timestamp desc");
query.Append("select p.Name as PluginName, i.Name, Timestamp, iv.Mapping, i.ValueType, ");
query.Append("vb.Value as ByteValue, vi.Value as IntValue, ");
query.Append("vf.Value as FloatValue, vs.Value as StringValue from IndicatorValue iv ");
query.Append("left join Indicator i on iv.IndicatorID = i.ID ");
query.Append("left join PluginMetadata p on p.ID = i.PluginMetadataID ");
query.Append("left join ValueByte vb on vb.ID = iv.ValueID ");
query.Append("left join ValueInt vi on vi.ID = iv.ValueID ");
query.Append("left join ValueFloat vf on vf.ID = iv.ValueID ");
query.Append("left join ValueString vs on vs.ID = iv.ValueID ");
query.Append("where iv.ID in (select TOP " + currentValue.Item6 + " ID from IndicatorValue where ");
query.Append("IndicatorID in (select ID from Indicator where MonitoredSystemID = " + ID + ") and ");
query.Append("IndicatorID in (select ID from Indicator where PluginMetadataID in (select ID from PluginMetadata where Name = '" + currentValue.Item2 + "' and [Platform] = " + OS + ")) and ");
query.Append("IndicatorID in (select ID from Indicator where Name = '" + currentValue.Item3 + "') and ");
query.Append("Timestamp >= " + ((DateTime)currentValue.Item4).Ticks + " and ");
query.Append("Timestamp <= " + ((DateTime)currentValue.Item5).Ticks + " order by ID desc)");
var result = dataContext.ExecuteQuery <QueryResult>(query.ToString()).ToList();
foreach (var item in result)
{
switch ((ValueType)item.ValueType)
{
case ValueType.Byte:
queryResult.Add(new Tuple <string, string, string, string, MappingState, DateTime>
(
currentValue.Item1,
item.PluginName,
item.Name,
item.ByteValue.ToString(),
(MappingState)item.Mapping,
new DateTime((long)item.Timestamp)
)
);
break;
case ValueType.Int:
queryResult.Add(new Tuple <string, string, string, string, MappingState, DateTime>
(
currentValue.Item1,
item.PluginName,
item.Name,
item.IntValue.ToString(),
(MappingState)item.Mapping,
new DateTime((long)item.Timestamp)
)
);
break;
case ValueType.Float:
queryResult.Add(new Tuple <string, string, string, string, MappingState, DateTime>
(
currentValue.Item1,
item.PluginName,
item.Name,
item.FloatValue.ToString(),
(MappingState)item.Mapping,
new DateTime((long)item.Timestamp)
)
);
break;
case ValueType.String:
queryResult.Add(new Tuple <string, string, string, string, MappingState, DateTime>
(
currentValue.Item1,
item.PluginName,
item.Name,
item.StringValue.ToString(),
(MappingState)item.Mapping,
new DateTime((long)item.Timestamp)
)
);
break;
default:
break;
}
}
}
}
#endregion
Console.WriteLine("Cout: " + queryResult.Count());
foreach (var item in queryResult)
{
//Console.WriteLine(item);
}
DateTime thenLevelL = DateTime.Now;
Console.WriteLine("Finished Level L new");
Console.WriteLine("Level L data needed: " + Math.Round(thenLevelL.Subtract(nowLevelL).TotalMilliseconds) + " milliseconds.");
#endregion
#region old
Console.WriteLine("Starting Level L old");
DateTime nowLevelLold = DateTime.Now;
var queryResultOld = ValueManager.Instance.GetData(input);
Console.WriteLine("Count: " + queryResultOld.Count());
foreach (var item in queryResultOld)
{
//Console.WriteLine(item);
}
DateTime thenLevelLold = DateTime.Now;
Console.WriteLine("Finished Level L old");
Console.WriteLine("Level L data needed: " + Math.Round(thenLevelLold.Subtract(nowLevelLold).TotalMilliseconds) + " milliseconds.");
#endregion
Console.WriteLine("<-<-<-<-<PERFORMANCETEST>->->->->");
Console.ReadLine();
}
/// <summary>
/// Gets all update intervals for a specific system in combination with a plugin.
/// </summary>
/// <param name="monitoredSystemID">The ID of the system to get the update intervals from.</param>
/// <param name="pluginName">The plugin to get the update intervals form.</param>
/// <returns>A list containing tuples of IndicatorName | UpdateInterval.</returns>
public List <Tuple <string, TimeSpan> > GetUpdateIntervals(int monitoredSystemID, string pluginName)
{
using (var dataContext = DataContextFactory.CreateReadOnlyDataContext())
{
return(GetUpdateIntervalsByMonitoredSystemIDAndPluginName(dataContext, monitoredSystemID, PrecompiledQueries.GetPluginMetadataIDByName(dataContext, pluginName)).ToList());
}
}
