//TODO: add methods for other types
// W3C - COMW3CInputContextClassClass
// NCSA - COMIISNCSAInputContextClassClass
private static DataTable Execute <T>(string query) where T : new()
{
LogQueryClassClass log = new LogQueryClassClass();
ILogRecordset recordset = log.Execute(query, new T());
ILogRecord record = null;
DataTable dt = new DataTable();
Int32 columnCount = recordset.getColumnCount();
for (int i = 0; i < columnCount; i++)
{
dt.Columns.Add(recordset.getColumnName(i), types[recordset.getColumnType(i) - 1]);
}
for (; !recordset.atEnd(); recordset.moveNext())
{
DataRow dr = dt.NewRow();
record = recordset.getRecord();
for (int i = 0; i < columnCount; i++)
{
dr[i] = record.getValue(i);
}
dt.Rows.Add(dr);
}
return(dt);
}
public void ReadColumnMap(ILogRecordset rs)
{
this.columnMap = new Dictionary<string, int>();
for (int col = 0; col < rs.getColumnCount(); col++)
{
string colName = rs.getColumnName(col);
this.columnMap[colName] = col;
}
}
public void ReadColumnMap(ILogRecordset rs)
{
this.columnMap = new Dictionary <string, int>();
for (int col = 0; col < rs.getColumnCount(); col++)
{
string colName = rs.getColumnName(col);
this.columnMap[colName] = col;
}
}
public ILogRecordset extractRawJson(String fileName)
{
LogQueryClass logQuery = new LogQueryClassClass();
MSUtil.COMCSVInputContextClassClass inputFormat = new COMCSVInputContextClassClass();
//C:\\Users\\jthomas\\Desktop\\SendGrid\\11_27-12_3\\11_15-11_18Output_30.csv' ";
String strQuery = @"SELECT _raw FROM '" + fileName + "'";
ILogRecordset results = logQuery.Execute(strQuery, inputFormat);
return(results);
}
static void Main(string[] args)
{
LogParser logParser = new LogParser();
//String fileName = "C:\\Users\\jthomas\\Desktop\\SendGrid\\11_27-12_3\\11_15-11_18Output_30.csv ";
Console.WriteLine("Enter Filename");
String fileName = Console.ReadLine();
ILogRecordset results = logParser.extractRawJson(fileName);
DataAccess da = new DataAccess();
da.insertData(results);
}
private DataTable insertIntoDataTable(ILogRecordset results)
{
//Console.WriteLine("Inside Insert Data");
DataTable dt = new DataTable("RawJsonData");
dt.Columns.Add("RawJson", typeof(String));
dt.Columns.Add("Timestamp", typeof(DateTime));
while (!results.atEnd())
{
String res = results.getRecord().getValue(0).ToString();
var parts = res.Split(new char[] { '{' }, 2);
jsonString = "{" + parts[1];
/*
* JObject obj = JObject.Parse(jsonString);
* string eventType = (string)obj["event"];
* // var uniqueArgs = obj["unique_args"];
*
*
*
* switch (eventType)
* {
* case EventCategory.OPEN: eventString = cp.getJsonString(obj, new OpenEvent());
* break;
* case EventCategory.BOUNCE: eventString = cp.getJsonString(obj, new BounceEvent());
* break;
* case EventCategory.CLICK: eventString = cp.getJsonString(obj, new ClickEvent());
* break;
* case EventCategory.DEFERRED: eventString = cp.getJsonString(obj, new DeferredEvent());
* break;
* case EventCategory.DELIVERED: eventString = cp.getJsonString(obj, new DeliveredEvent());
* break;
* case EventCategory.DROP: eventString = cp.getJsonString(obj, new DropEvent());
* break;
* case EventCategory.PROCESSED: eventString = cp.getJsonString(obj, new ProcessedEvent());
* break;
* case EventCategory.SPAMREPORT: eventString = cp.getJsonString(obj, new SpamReportEvent());
* break;
* case EventCategory.UNSUBSCRIBE: eventString = cp.getJsonString(obj, new UnsubscribeEvent());
* break;
* }*/
dt.Rows.Add(jsonString, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
results.moveNext();
}
return(dt);
}
public int GerErrorsCount()
{
int result = 0;
ILogRecordset logRecordset = _logQuery.Execute($"SELECT COUNT(*) AS rowCount FROM {_logFileName} WHERE Field2 = 'ERROR'", _input);
while (!logRecordset.atEnd())
{
ILogRecord record = logRecordset.getRecord();
result = record.getValue("rowCount");
logRecordset.moveNext();
}
return(result);
}
private DataTable insertIntoDataTable(ILogRecordset results)
{
//Console.WriteLine("Inside Insert Data");
DataTable dt = new DataTable("RawJsonData");
dt.Columns.Add("RawJson", typeof(String));
dt.Columns.Add("Timestamp", typeof(DateTime));
while (!results.atEnd())
{
String res = results.getRecord().getValue(0).ToString();
var parts = res.Split(new char[] { '{' }, 2);
jsonString = "{" + parts[1];
/*
JObject obj = JObject.Parse(jsonString);
string eventType = (string)obj["event"];
// var uniqueArgs = obj["unique_args"];
switch (eventType)
{
case EventCategory.OPEN: eventString = cp.getJsonString(obj, new OpenEvent());
break;
case EventCategory.BOUNCE: eventString = cp.getJsonString(obj, new BounceEvent());
break;
case EventCategory.CLICK: eventString = cp.getJsonString(obj, new ClickEvent());
break;
case EventCategory.DEFERRED: eventString = cp.getJsonString(obj, new DeferredEvent());
break;
case EventCategory.DELIVERED: eventString = cp.getJsonString(obj, new DeliveredEvent());
break;
case EventCategory.DROP: eventString = cp.getJsonString(obj, new DropEvent());
break;
case EventCategory.PROCESSED: eventString = cp.getJsonString(obj, new ProcessedEvent());
break;
case EventCategory.SPAMREPORT: eventString = cp.getJsonString(obj, new SpamReportEvent());
break;
case EventCategory.UNSUBSCRIBE: eventString = cp.getJsonString(obj, new UnsubscribeEvent());
break;
}*/
dt.Rows.Add(jsonString, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
results.moveNext();
}
return dt;
}
public string GetLogMetadata()
{
StringBuilder result = new StringBuilder();
ILogRecordset logRecordset = _logQuery.Execute($"SELECT Field2 as logLevel, COUNT(*) AS rowCount FROM {_logFileName} GROUP BY Field2", _input);
while (!logRecordset.atEnd())
{
ILogRecord record = logRecordset.getRecord();
if (!string.IsNullOrEmpty(record.getValue("logLevel").ToString()))
{
result.Append($"Level: {record.getValue("logLevel")}; Count: {record.getValue("rowCount")}\n");
}
logRecordset.moveNext();
}
return(result.ToString());
}
private int GetRecordCount(string from, Object input)
{
string countQuery = "SELECT COUNT(*) FROM " + from;
ILogRecordset returnedCount = GetRecordSet(countQuery, input);
string returnedValue = "0";
if (!returnedCount.atEnd())
{
returnedValue = returnedCount.getRecord().getValue(0).ToString();
}
returnedCount.close();
int recordsCount;
int.TryParse(returnedValue, out recordsCount);
return(recordsCount);
}
static List <string> ScanRegistry()
{
List <string> keys = new List <string>();
ILogRecordset rs = null;
try
{
LogQueryClass logQ = new LogQueryClass();
COMRegistryInputContextClass registryFormat = new COMRegistryInputContextClass();
string query = @"SELECT Path FROM \ where Value LIKE 'o2Mate%'";
rs = logQ.Execute(query, registryFormat);
for (; !rs.atEnd(); rs.moveNext())
{
keys.Add(rs.getRecord().toNativeString(","));
}
}
finally
{
rs.close();
}
return(keys);
}
public ILogRecordset executeCommand()
{
// prepare LogParser Recordset & Record objects
ILogRecordset rsLP = null;
ILogRecord rowLP = null;
LogQueryClassClass LogParser = null;
COMCSVInputContextClass W3Clog = null;
LogParser = new LogQueryClassClass();
W3Clog = new COMCSVInputContextClass();
try
{
//W3C Logparsing SQL. Replace this SQL query with whatever
//you want to retrieve. The example below
//will sum up all the bandwidth
//Usage of a specific folder with name
//"userID". Download Log Parser 2.2
//from Microsoft and see sample queries.
foreach (string s in logList)
{
cmd = Utils.ReplaceFirst(cmd, s, "'" + currentDir + "\\Logs\\" + s + "'");
}
Debug.WriteLine(cmd);
// run the query against W3C log
rsLP = LogParser.Execute(cmd, W3Clog);
}
catch
{
throw;
}
return(rsLP);
}
private DataTable GetDataFromLogs()
{
DataTable dataResults = new DataTable();
LogQueryClass LogParser = new LogQueryClass();
ILogRecordset rs = LogParser.Execute("SELECT * FROM '" + textBoxFrom.Text.Trim() + "'", new COMIISW3CInputContextClass());
for (int i = 0; i < rs.getColumnCount(); i++)
{
if (!dataResults.Columns.Contains(rs.getColumnName(i)))
{
dataResults.Columns.Add(rs.getColumnName(i));
}
}
//dataGridView1.DataSource = dataResults.DefaultView;
ILogRecord rcd;
DataRow row;
while (!rs.atEnd())
{
row = dataResults.NewRow();
rcd = rs.getRecord();
for (int i = 0; i < rs.getColumnCount(); i++)
{
row[rs.getColumnName(i)] = rcd.getValue(rs.getColumnName(i));
}
dataResults.Rows.Add(row);
rs.moveNext();
}
rs.close();
return(dataResults);
}
public void insertData(ILogRecordset results)
{
DataTable dt = insertIntoDataTable(results);
string connectionString = getConnectionString();
SqlConnection connection = new SqlConnection(connectionString);
try
{
connection.Open();
using (SqlBulkCopy sbc = new SqlBulkCopy(connection))
{
sbc.DestinationTableName = "dbo.RawJsonTest";
sbc.ColumnMappings.Add("RawJson", "RawJson");
sbc.ColumnMappings.Add("Timestamp", "TimeStamp");
sbc.BulkCopyTimeout = 0;
sbc.WriteToServer(dt);
}
connection.Close();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
}
public void insertData(ILogRecordset results)
{
DataTable dt = insertIntoDataTable(results);
string connectionString = getConnectionString();
SqlConnection connection = new SqlConnection(connectionString);
try
{
connection.Open();
using (SqlBulkCopy sbc = new SqlBulkCopy(connection))
{
sbc.DestinationTableName = "dbo.RawJsonTest";
sbc.ColumnMappings.Add("RawJson", "RawJson");
sbc.ColumnMappings.Add("Timestamp", "TimeStamp");
sbc.BulkCopyTimeout = 0;
sbc.WriteToServer(dt);
}
connection.Close();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
}
public void getLogTable(object sender, EventArgs e)
{
LogTable.Rows.Clear();
Logger l = new Logger(query.Text);
ILogRecordset result = l.executeCommand();
ILogRecord dataRow = null;
TableHeaderRow header = new TableHeaderRow();
TableHeaderCell headerCell;
for (int i = 0; i < result.getColumnCount(); i++)
{
headerCell = new TableHeaderCell();
headerCell.Text = result.getColumnName(i);
headerCell.CssClass = "forumHeader";
headerCell.Style.Add("border", "1px solid black");
header.Cells.Add(headerCell);
}
LogTable.Rows.Add(header);
while (!result.atEnd())
{
dataRow = result.getRecord();
TableRow row = new TableRow();
TableCell cell;
for (int i = 0; i < result.getColumnCount(); i++)
{
cell = new TableCell();
cell.Text = dataRow.getValue(i).ToString();
cell.Style.Add("border", "1px solid black");
row.Cells.Add(cell);
}
LogTable.Rows.Add(row);
result.moveNext();
}
}
private void PerformSQLQuery()
{
DateTime startTime = DateTime.Now;
dataGridViewResults.DataSource = null;
toolStripStatusLabelDataInfo.Text = "";
QueryButton.Enabled = false;
toolStripStatusLabelTimeTaken.Text = "Performing query.";
string sqlQuery = BuildSqlQuery();
textFinalQuery.Text = sqlQuery;
// Perform the query requested
ICOMIISW3CInputContext iisW3c = new COMIISW3CInputContextClassClass();
int recordsCount = GetRecordCount(FromText.Text.Trim(), iisW3c);
if (recordsCount > 0)
{
toolStripProgressBar.Maximum = recordsCount;
toolStripProgressBar.Value = 0;
ILogRecordset rs = GetRecordSet(sqlQuery.ToString(), iisW3c);
ILogRecord rcd;
DataTable table = new DataTable();
if (!rs.atEnd())
{
for (int i = 0; i < rs.getColumnCount(); i++)
{
table.Columns.Add(rs.getColumnName(i), Type.GetType("System.String"));
}
}
DataRow row;
//textScratchPad.Text = "";
int currentRecord = 0;
while (!rs.atEnd())
{
currentRecord++;
rcd = rs.getRecord();
row = table.NewRow();
for (int i = 0; i < rs.getColumnCount(); i++)
{
row[i] = rcd.getValue(i).ToString();
}
table.Rows.Add(row);
//textBox1.Text += rcd.getValue("cs-uri-stem").ToString() + System.Environment.NewLine;
rs.moveNext();
if (currentRecord <= toolStripProgressBar.Maximum)
{
toolStripProgressBar.Value = currentRecord;
}
else
{
toolStripProgressBar.Value = toolStripProgressBar.Maximum;
}
}
rs.close();
dataGridViewResults.DataSource = table;
toolStripStatusLabelDataInfo.Text = "Records returned: " + table.Rows.Count.ToString();
toolStripProgressBar.Value = 0;
}
else
{
MessageBox.Show("No records would be returned by your query.");
}
DateTime endTime = DateTime.Now;
toolStripStatusLabelTimeTaken.Text = "";
toolStripStatusLabelTimeTaken.Text += "Time taken: " + endTime.Subtract(startTime).ToString();
/*
* ICOMDataGridOutputContext dataGrid = new COMDataGridOutputContextClassClass();
*
* LogParser.ExecuteBatch("SELECT TOP 10 * FROM " + FromText.Text + "", iisW3c, dataGrid);
*/
/*
* ICOMW3COutputContext w3cOutput = new COMW3COutputContextClassClass();
*
* LogParser.ExecuteBatch("SELECT TOP 10 * FROM '" + FromText.Text + "'", iisW3c, w3cOutput);
*/
QueryButton.Enabled = true;
}
public override void Import(DateTime @from)
{
var logQuery = new LogQueryClass();
var inputFormat = new COMW3CInputContextClass();
string strQuery = string.Format(@"SELECT to_timestamp(date, time) as date,
s-ip as sourceIP,
cs-method as method,
cs-uri-stem as uri,
cs-uri-query as query,
s-port as port,
c-ip as clientIP,
cs(User-Agent) as userAgent,
cs-host as clientToServerHost,
sc-status as statusCode,
sc-substatus as subStatus,
sc-win32-status as win32Status,
sc-bytes as serverToClientBytes,
cs-bytes as clientToServerBytes,
time-taken as duration FROM {0}", "SOME FILE");
ILogRecordset results = logQuery.Execute(strQuery, inputFormat);
var tweets = new List <Entry>();
while (!results.atEnd())
{
ILogRecord logRecord = results.getRecord();
dynamic date = logRecord.getValue("date");
dynamic sourceIP = logRecord.getValue("sourceIP");
dynamic method = logRecord.getValue("method");
dynamic uri = logRecord.getValue("uri");
dynamic query = logRecord.getValue("query") is DBNull ? string.Empty : logRecord.getValue("query");
dynamic port = logRecord.getValue("port");
dynamic clientIP = logRecord.getValue("clientIP") is DBNull
? string.Empty
: logRecord.getValue("clientIP");
dynamic userAgent = logRecord.getValue("userAgent") is DBNull
? string.Empty
: logRecord.getValue("userAgent");
dynamic clientToServerHost = logRecord.getValue("clientToServerHost") is DBNull
? string.Empty
: logRecord.getValue("clientToServerHost");
dynamic statusCode = logRecord.getValue("statusCode");
dynamic subStatus = logRecord.getValue("subStatus");
dynamic win32Status = logRecord.getValue("win32Status");
dynamic serverToClientBytes = logRecord.getValue("serverToClientBytes");
dynamic clientToServerBytes = logRecord.getValue("clientToServerBytes");
dynamic duration = logRecord.getValue("duration");
tweets.Add(new Entry
{
Date = date,
SourceIP = sourceIP,
Method = method,
Uri = uri,
Query = query,
Port = port,
ClientIP = clientIP,
UserAgent = userAgent,
ClientToServerHost = clientToServerHost,
StatusCode = statusCode,
SubStatus = subStatus,
Win32Status = win32Status,
ServerToClientBytes = serverToClientBytes,
ClientToServerBytes = clientToServerBytes,
Duration = duration
});
results.moveNext();
}
var serializer = new JsonNetSerializer();
string bulkCommand = new BulkCommand(index: "log", type: "iis");
string bulkJson =
new BulkBuilder(serializer)
.BuildCollection(tweets,
(builder, tweet) => builder.Create(tweet)
);
_connection.Post(bulkCommand, bulkJson);
}
