private static void GetNetflowInterface(NpgsqlConnection oConn, dsETLNetflowAnalyzerTableAdapters.qryNetworkUtilization daQry)
{
try
{
string sSQL = @"SELECT interface_id, router_id, snmp_description, speed, link_name FROM netflow_interface;";
DataSet ds = new DataSet();
DataTable dt = new DataTable();
NpgsqlDataAdapter da = new NpgsqlDataAdapter(sSQL, oConn);
ds.Reset();
da.Fill(ds);
dt = ds.Tables[0];
foreach (DataRow dr in dt.Rows)
{
daQry.InsertQuerydimInterface(
Convert.ToInt64(dr["interface_id"]),
dr["snmp_description"].ToString(),
dr["link_name"].ToString(),
Convert.ToInt64(dr["speed"]),
Convert.ToInt64(dr["router_id"])
);
}
}
catch (Exception ex)
{
throw (ex);
}
}
private static void GetNetflowRouter(NpgsqlConnection oConn, dsETLNetflowAnalyzerTableAdapters.qryNetworkUtilization daQry)
{
try
{
string sSQL = @"SELECT router_id, router_ip, router_displayname FROM netflow_router;";
DataSet ds = new DataSet();
DataTable dt = new DataTable();
NpgsqlDataAdapter da = new NpgsqlDataAdapter(sSQL, oConn);
ds.Reset();
da.Fill(ds);
dt = ds.Tables[0];
foreach (DataRow dr in dt.Rows)
{
daQry.InsertQuerydimCabang(
Convert.ToInt64(dr["router_id"]),
dr["router_ip"].ToString(),
dr["router_displayname"].ToString()
);
}
}
catch (Exception ex)
{
throw (ex);
}
}
//private static void GetInDstifhourly_dstif_1h_20150320180822(NpgsqlConnection oConn, dsETLNetflowAnalyzerTableAdapters.qryNetworkUtilization daQry)
//{
// try
// {
// StringBuilder sb = new StringBuilder();
// sb.Append(" SELECT dst_if, collection_time, min_octets, max_octets,");
// sb.Append(" avg_octets");
// sb.Append(" FROM dstif_1h_20150320180822 ");
// //sb.Append(" where collection_time >= to_timestamp(to_char(current_date, 'YYYY-MM-DD') || ' 00:00 ' , 'YYYY-MM-DD HH24:MI')::timestamp without time zone - interval '2 day'");
// //sb.Append(" and collection_time < to_timestamp(to_char(current_date, 'YYYY-MM-DD') || ' 00:00 ' , 'YYYY-MM-DD HH24:MI')::timestamp without time zone - interval '1 day'");
// sb.Append(" WHERE collection_time >= '2015-03-18 20:00' ");
// string sSQL = sb.ToString();
// DataSet ds = new DataSet();
// DataTable dt = new DataTable();
// NpgsqlDataAdapter da = new NpgsqlDataAdapter(sSQL, oConn);
// ds.Reset();
// da.Fill(ds);
// dt = ds.Tables[0];
// Console.WriteLine(" # of Rows = {0} .....", dt.Rows.Count);
// foreach (DataRow dr in dt.Rows)
// {
// daQry.InsertQueryDstUtilization(
// Convert.ToDateTime(dr["collection_time"].ToString()),
// Convert.ToInt32(dr["dst_if"]),
// Convert.ToInt64(dr["min_octets"]),
// Convert.ToInt64(dr["max_octets"]),
// Convert.ToInt64(dr["avg_octets"])
// );
// }
// }
// catch (Exception ex)
// {
// throw (ex);
// }
//}
//private static void GetInDstifhourly_dstif_1h_20150324185722(NpgsqlConnection oConn, dsETLNetflowAnalyzerTableAdapters.qryNetworkUtilization daQry)
//{
// try
// {
// StringBuilder sb = new StringBuilder();
// sb.Append(" SELECT dst_if, collection_time, min_octets, max_octets,");
// sb.Append(" avg_octets");
// sb.Append(" FROM dstif_1h_20150324185722 ");
// //sb.Append(" where collection_time >= to_timestamp(to_char(current_date, 'YYYY-MM-DD') || ' 00:00 ' , 'YYYY-MM-DD HH24:MI')::timestamp without time zone - interval '2 day'");
// //sb.Append(" and collection_time < to_timestamp(to_char(current_date, 'YYYY-MM-DD') || ' 00:00 ' , 'YYYY-MM-DD HH24:MI')::timestamp without time zone - interval '1 day'");
// sb.Append(" WHERE collection_time >= '2015-03-18 20:00' ");
// string sSQL = sb.ToString();
// DataSet ds = new DataSet();
// DataTable dt = new DataTable();
// NpgsqlDataAdapter da = new NpgsqlDataAdapter(sSQL, oConn);
// ds.Reset();
// da.Fill(ds);
// dt = ds.Tables[0];
// Console.WriteLine(" # of Rows = {0} .....", dt.Rows.Count);
// foreach (DataRow dr in dt.Rows)
// {
// daQry.InsertQueryDstUtilization(
// Convert.ToDateTime(dr["collection_time"].ToString()),
// Convert.ToInt32(dr["dst_if"]),
// Convert.ToInt64(dr["min_octets"]),
// Convert.ToInt64(dr["max_octets"]),
// Convert.ToInt64(dr["avg_octets"])
// );
// }
// }
// catch (Exception ex)
// {
// throw (ex);
// }
//}
private static void GetInSrcifhourly(NpgsqlConnection oConn, dsETLNetflowAnalyzerTableAdapters.qryNetworkUtilization daQry)
{
try
{
StringBuilder sb = new StringBuilder();
sb.Append(" SELECT src_if, collection_time, min_octets, max_octets,");
sb.Append(" avg_octets");
sb.Append(" FROM srcifhourly ");
sb.Append(" where collection_time >= to_timestamp(to_char(current_date, 'YYYY-MM-DD') || ' 00:00 ' , 'YYYY-MM-DD HH24:MI')::timestamp without time zone - interval '1 day'");
sb.Append(" and collection_time < to_timestamp(to_char(current_date, 'YYYY-MM-DD') || ' 00:00 ' , 'YYYY-MM-DD HH24:MI')::timestamp without time zone");
//sb.Append(" WHERE collection_time >= '2015-03-18 20:00' ");
//sb.Append(" AND collection_time < to_timestamp(to_char(current_date, 'YYYY-MM-DD') || ' 00:00 ' , 'YYYY-MM-DD HH24:MI')::timestamp without time zone");
string sSQL = sb.ToString();
DataSet ds = new DataSet();
DataTable dt = new DataTable();
NpgsqlDataAdapter da = new NpgsqlDataAdapter(sSQL, oConn);
ds.Reset();
da.Fill(ds);
dt = ds.Tables[0];
Console.WriteLine(" # of Rows = {0} .....", dt.Rows.Count);
foreach (DataRow dr in dt.Rows)
{
daQry.InsertQuerySrcUtilization(
Convert.ToDateTime(dr["collection_time"].ToString()),
Convert.ToInt32(dr["src_if"]),
Convert.ToInt64(dr["min_octets"]),
Convert.ToInt64(dr["max_octets"]),
Convert.ToInt64(dr["avg_octets"])
);
}
}
catch (Exception ex)
{
throw (ex);
}
}
//private static void GetInSrcifhourly_srcif_1h_20150320180822(NpgsqlConnection oConn, dsETLNetflowAnalyzerTableAdapters.qryNetworkUtilization daQry)
//{
// try
// {
// StringBuilder sb = new StringBuilder();
// sb.Append(" SELECT src_if, collection_time, min_octets, max_octets,");
// sb.Append(" avg_octets");
// sb.Append(" FROM srcif_1h_20150320180822 ");
// //sb.Append(" where collection_time >= to_timestamp(to_char(current_date, 'YYYY-MM-DD') || ' 00:00 ' , 'YYYY-MM-DD HH24:MI')::timestamp without time zone - interval '2 day'");
// //sb.Append(" and collection_time < to_timestamp(to_char(current_date, 'YYYY-MM-DD') || ' 00:00 ' , 'YYYY-MM-DD HH24:MI')::timestamp without time zone - interval '1 day'");
// sb.Append(" WHERE collection_time >= '2015-03-18 20:00' ");
// string sSQL = sb.ToString();
// DataSet ds = new DataSet();
// DataTable dt = new DataTable();
// NpgsqlDataAdapter da = new NpgsqlDataAdapter(sSQL, oConn);
// ds.Reset();
// da.Fill(ds);
// dt = ds.Tables[0];
// Console.WriteLine(" # of Rows = {0} .....", dt.Rows.Count);
// foreach (DataRow dr in dt.Rows)
// {
// daQry.InsertQuerySrcUtilization(
// Convert.ToDateTime(dr["collection_time"].ToString()),
// Convert.ToInt32(dr["src_if"]),
// Convert.ToInt64(dr["min_octets"]),
// Convert.ToInt64(dr["max_octets"]),
// Convert.ToInt64(dr["avg_octets"])
// );
// }
// }
// catch (Exception ex)
// {
// throw (ex);
// }
//}
//private static void GetInSrcifhourly_srcif_1h_20150324185722(NpgsqlConnection oConn, dsETLNetflowAnalyzerTableAdapters.qryNetworkUtilization daQry)
//{
// try
// {
// StringBuilder sb = new StringBuilder();
// sb.Append(" SELECT src_if, collection_time, min_octets, max_octets,");
// sb.Append(" avg_octets");
// sb.Append(" FROM srcif_1h_20150324185722 ");
// //sb.Append(" where collection_time >= to_timestamp(to_char(current_date, 'YYYY-MM-DD') || ' 00:00 ' , 'YYYY-MM-DD HH24:MI')::timestamp without time zone - interval '2 day'");
// //sb.Append(" and collection_time < to_timestamp(to_char(current_date, 'YYYY-MM-DD') || ' 00:00 ' , 'YYYY-MM-DD HH24:MI')::timestamp without time zone - interval '1 day'");
// sb.Append(" WHERE collection_time >= '2015-03-18 20:00' ");
// string sSQL = sb.ToString();
// DataSet ds = new DataSet();
// DataTable dt = new DataTable();
// NpgsqlDataAdapter da = new NpgsqlDataAdapter(sSQL, oConn);
// ds.Reset();
// da.Fill(ds);
// dt = ds.Tables[0];
// Console.WriteLine(" # of Rows = {0} .....", dt.Rows.Count);
// foreach (DataRow dr in dt.Rows)
// {
// daQry.InsertQuerySrcUtilization(
// Convert.ToDateTime(dr["collection_time"].ToString()),
// Convert.ToInt32(dr["src_if"]),
// Convert.ToInt64(dr["min_octets"]),
// Convert.ToInt64(dr["max_octets"]),
// Convert.ToInt64(dr["avg_octets"])
// );
// }
// }
// catch (Exception ex)
// {
// throw (ex);
// }
//}
static void Main(string[] args)
{
/*
*
* Setting diambil dari NetworkUtilization.ServerVariables
* 1. Tarik data netflow_router, masukan ke table temp, yang tidak ada dimasukan ke NetworkUtilization.dimCabang
* 2. Tarik data netflow_interface, masukan ke table temp, yang tidak ada dimasukan ke NetworkUtilization.dimInterface
* 3. Tarik data dstif10min & srcif10min masukan ke table temp, inner join dgn dimCabang, dimInterface, dimRegional
* masukan ke table NetworkUtilization.factUtils
*
*/
try
{
Console.WriteLine("Start .....");
Console.WriteLine("=================================================================");
Console.WriteLine("Initializing .....");
Console.WriteLine("-----------------------------------------------------------------");
dsETLNetflowAnalyzerTableAdapters.ServerVariablesTableAdapter daServer = new dsETLNetflowAnalyzerTableAdapters.ServerVariablesTableAdapter();
dsETLNetflowAnalyzer.ServerVariablesDataTable dtServer = daServer.GetData();
dsETLNetflowAnalyzerTableAdapters.qryNetworkUtilization daUtils = new dsETLNetflowAnalyzerTableAdapters.qryNetworkUtilization();
string HostName = string.Empty;
string HostPort = string.Empty;
string UserId = string.Empty;
string Password = string.Empty;
string DatabaseName = string.Empty;
Console.WriteLine("Get Server Variables, # of Server = {0} .....", dtServer.Rows.Count);
Console.WriteLine("-----------------------------------------------------------------");
int ServerNo = 1;
string connString = string.Empty;
foreach (DataRow dr in dtServer.Rows)
{
Console.WriteLine("Server # {0} of {1}", ServerNo, dtServer.Rows.Count);
Console.WriteLine("-----------------------------------------------------------------");
HostName = dr["ServerIPAddress"].ToString();
HostPort = dr["ServerPortNo"].ToString();
UserId = dr["UserId"].ToString();
Password = dr["Password"].ToString();
DatabaseName = dr["DatabaseName"].ToString();
connString = string.Format("Server={0};Port={1};User Id={2};Password={3};Database={4};",
HostName, HostPort, UserId, Password, DatabaseName);
NpgsqlConnection oConn = new NpgsqlConnection(connString);
oConn.Open();
Console.WriteLine("Get Netflow Router -- Start");
Console.WriteLine("-----------------------------------------------------------------");
//GetNetflowRouter(oConn, daUtils);
Console.WriteLine("Get Netflow Router -- Done");
Console.WriteLine("-----------------------------------------------------------------");
Console.WriteLine("Get Netflow Interface -- Start");
Console.WriteLine("-----------------------------------------------------------------");
//GetNetflowInterface(oConn, daUtils);
Console.WriteLine("Get Netflow Interface -- Done");
Console.WriteLine("-----------------------------------------------------------------");
Console.WriteLine("Get IN Archieved Data -- Start");
Console.WriteLine("-----------------------------------------------------------------");
//GetInDstIf10Min(oConn, daUtils);
GetInDstifhourly(oConn, daUtils);
Console.WriteLine("Get IN Archieved Data -- Done");
Console.WriteLine("-----------------------------------------------------------------");
Console.WriteLine("Get OUT Archieved Data -- Start");
Console.WriteLine("-----------------------------------------------------------------");
//GetOutSrcIf10Min(oConn, daUtils);
GetInSrcifhourly(oConn, daUtils);
Console.WriteLine("Get OUT Archieved Data -- Done");
Console.WriteLine("-----------------------------------------------------------------");
oConn.Close();
}
daUtils.InsertFactUtilizationFromIn();
daUtils.InsertFactUtilizationFromOut();
daUtils.TruncateTempTable();
Console.WriteLine("DONE ...");
Console.WriteLine("-----------------------------------------------------------------");
//Console.Read();
}
catch (Exception msg)
{
Console.WriteLine(msg.ToString());
throw;
}
}
