I am having the same exact issue as the original post. I am running Windows Vista (Home Premium) and i am sucking all of my IIS log information in sql server then i am processing it. In the serviceprocessinstaller I have the "Local system" account attached to the service. The service is suppose to update the database every 10 seconds but obviously it is not updating it. When I run the code in a web page it works fine. But when I insert the code in a service it does not work
string sqlconn = @"Server=luke-pc\SQLEXPRESS;Database=test2;User ID=test;Password=test;";
// Create a DataTable.
DataTable table = new DataTable();
// Create a DataColumn and set various properties.
DataColumn column;
/*
DataColumn column = new DataColumn();
column.DataType = System.Type.GetType("System.String");
column.AllowDBNull = false;
column.ColumnName = "LogFilename";
table.Columns.Add(column);
column = new DataColumn();
column.DataType = System.Type.GetType("System.Int32");
column.AllowDBNull = false;
column.ColumnName = "RowNumber";
table.Columns.Add(column);
column = new DataColumn();
column.DataType = System.Type.GetType("System.DateTime");
column.AllowDBNull = false;
column.ColumnName = "date";
table.Columns.Add(column);
*/
column = new DataColumn();
column.DataType = System.Type.GetType("System.DateTime");
column.AllowDBNull = true;
column.ColumnName = "time";
table.Columns.Add(column);
column = new DataColumn();
column.DataType = System.Type.GetType("System.Int32");
column.AllowDBNull = true;
column.ColumnName = "count";
table.Columns.Add(column);
/*
column = new DataColumn();
column.DataType = System.Type.GetType("System.String");
column.AllowDBNull = false;
column.ColumnName = "s-ip";
table.Columns.Add(column);
column = new DataColumn();
column.DataType = System.Type.GetType("System.String");
column.AllowDBNull = false;
column.ColumnName = "cs-method";
table.Columns.Add(column);
*/
column = new DataColumn();
column.DataType = System.Type.GetType("System.String");
column.AllowDBNull = true;
column.ColumnName = "cs-uri-stem";
table.Columns.Add(column);
/*
column = new DataColumn();
column.DataType = System.Type.GetType("System.String");
column.AllowDBNull = true;
column.ColumnName = "cs-uri-query";
table.Columns.Add(column);
column = new DataColumn();
column.DataType = System.Type.GetType("System.String");
column.AllowDBNull = true;
column.ColumnName = "s-port";
table.Columns.Add(column);
column = new DataColumn();
column.DataType = System.Type.GetType("System.String");
column.AllowDBNull = true;
column.ColumnName = "cs-username";
table.Columns.Add(column);
*/
column = new DataColumn();
column.DataType = System.Type.GetType("System.String");
column.AllowDBNull = true;
column.ColumnName = "c-ip";
table.Columns.Add(column);
/*
column = new DataColumn();
column.DataType = System.Type.GetType("System.String");
column.AllowDBNull = true;
column.ColumnName = "cs(User-Agent)";
table.Columns.Add(column);
column = new DataColumn();
column.DataType = System.Type.GetType("System.String");
column.AllowDBNull = true;
column.ColumnName = "sc-status";
table.Columns.Add(column);
column = new DataColumn();
column.DataType = System.Type.GetType("System.String");
column.AllowDBNull = true;
column.ColumnName = "sc-substatus";
table.Columns.Add(column);
column = new DataColumn();
column.DataType = System.Type.GetType("System.String");
column.AllowDBNull = true;
column.ColumnName = "sc-win32-status";
table.Columns.Add(column);
column = new DataColumn();
column.DataType = System.Type.GetType("System.String");
column.AllowDBNull = true;
column.ColumnName = "time-taken";
table.Columns.Add(column);
*/
column = new DataColumn();
column.DataType = System.Type.GetType("System.Int32");
column.AllowDBNull = true;
column.ColumnName = "questionid";
table.Columns.Add(column);
//get all search eninge ips
List IPAddressesToAvoid = new List();
string queryq = string.Format("select ip from SearchEngineIPs UNION select ip from BotIPs");
using (SqlConnection conn = new SqlConnection(sqlconn))
{
using (SqlCommand cmd = new SqlCommand(queryq, conn))
{
conn.Open();
SqlDataReader r = cmd.ExecuteReader();
while (r.Read())
IPAddressesToAvoid.Add("'" + r.GetString(0) + "'");
if (r != null)
{
r.Close();
}
conn.Close();
}
}
//lets join the ip addresses
string ListofIPstoAvoid = string.Join(";", IPAddressesToAvoid.ToArray());
// prepare LogParser Recordset & Record objects
ILogRecordset rsLP = null;
ILogRecord row = null;
LogQueryClassClass LogParser = null;
COMW3CInputContextClassClass W3Clog = null;
string strSQL = null;
LogParser = new LogQueryClassClass();
W3Clog = new COMW3CInputContextClassClass();
strSQL = @"select * from C:\inetpub\logs\LogFiles\W3SVC1\*.log where date = '" + DateTime.Now.ToString("yyyy-MM-dd") + "' and sc-status = 200 " +
"and cs-method = 'GET' and cs-uri-stem LIKE '/q/%' and cs-username IS NULL " +
"and cs-uri-stem not like '%.png' and cs-uri-stem not like '%.jpg' and cs-uri-stem not like '%.gif' and cs-uri-stem not like '%.css' and cs-uri-stem not like '%.js' " +
"and cs(User-Agent) not like '%bot%' and cs(User-Agent) not like '%slurp%' and cs(User-Agent) not like '%crawl%' " +
"and c-ip NOT IN (" + ListofIPstoAvoid + ")";
// run the query against W3C log
System.Diagnostics.Debugger.Launch();
rsLP = LogParser.Execute(strSQL, W3Clog);
DataRow dr;
while (!rsLP.atEnd())
{
dr = table.NewRow();
row = rsLP.getRecord();
string[] urlarr = row.getValue("cs-uri-stem").ToString().Split('/');
dr["time"] = (DateTime)row.getValue("time");
dr["cs-uri-stem"] = row.getValue("cs-uri-stem").ToString();
dr["c-ip"] = row.getValue("c-ip").ToString();
dr["questionid"] = int.Parse(urlarr[3].ToString());
table.Rows.Add(dr);
rsLP.moveNext();
}
//get unique users for each question
DataTable dt = table.Clone();
var query = from row1 in table.AsEnumerable()
let time = row1.Field("time")
let uri = row1.Field("cs-uri-stem")
let ip = row1.Field("c-ip")
let questionid = row1.Field("questionid")
select new
{
time,
uri,
ip,
questionid
};
//get all distinct question IDs from query vairable
var query2 = from row2 in query
group row2 by row2.questionid into g
select new
{
time = g.Select(x => x.time).FirstOrDefault(),
theip = g.Select(x => x.ip).FirstOrDefault(),
theurl = g.Select(x => x.uri).FirstOrDefault(),
question1 = g.Key
};
//get all question information from distinct information
var query3 = from row3 in query2
select new
{
question2 = row3.question1,
questionInfo = (from row3Sub in query
where row3Sub.questionid == row3.question1
group row3Sub by row3Sub.ip into g
select new
{
time = g.Select(x => x.time).FirstOrDefault(),
theip = g.Key,
theurl = g.Select(x => x.uri).FirstOrDefault(),
question3 = g.Select(x => x.questionid).FirstOrDefault()
})
};
var query4 = from row4 in query3
select new
{
question4 = row4.question2,
count2 = row4.questionInfo.Count()
};
//get rid of the DataTable
table.Dispose();
StringBuilder sb = new StringBuilder();
DateTime CurrDate = DateTime.Now;
foreach (var row4 in query4)
{
sb.Append("INSERT INTO qstatistics (qstatistics_questionid, qstatistics_questionid_uniquehits, qstatistics_date) VALUES (");
sb.Append(row4.question4);
sb.Append(",");
sb.Append(row4.count2);
sb.Append(",'");
sb.Append(CurrDate);
sb.Append("');");
}
SqlConnection conn1 = new SqlConnection(sqlconn);
SqlCommand myCommand = new SqlCommand(sb.ToString(), conn1);
conn1.Open();
myCommand.ExecuteNonQuery();
conn1.Close();