Ever wanted to know what has been going on with your DataCollection Server Web concurrents? Interested in finding out if you are going over your limit of allowable concurrents frequently? If the answer to these questions is yes , then this set of articles is for you. In it we will show you how to look at the log files produced on your servers and calculate what has been going on and when. It should be noted that this article assumes that you have some knowledge of Data Management scripts as well as Tables scripts and VBA.
So let’s get started. First off we need to create a DMS script that will get all the records out of the log files that we need and start to calculate a few things. I am sure you have looked at the log files before but if not you can read about them in the DDL , just search for ( Interviewer Server Log Files ). The files we want to look at are just the IVW files so get some of them put on your local machine , or at least a machine that has the ability to run DMS and TOM scripts.
This first DMS script we are going to call Concurrents.dms and it will have the following sections,
Event(OnJobStart) End Event InputDatasource(Input, "") ConnectionString = "" SelectQuery = "" End InputDatasource Event(OnNextCase, "") End Event OutputDatasource(Output, "") UseInputAsOutput = True End OutputDatasource Event(OnJobEnd, "") End Event
If you would like to know more about the sections and Events then you can search the DDL for Timeline. Let’s take a look at each of our section in turn, first off we have the OnJobStart Event. Type the following in it.
Dim oListStart,oListFinish,oListLogEntry
Set oListFinish = CreateObject("Scripting.Dictionary")
Set oListStart = CreateObject("Scripting.Dictionary")
Set oListLogEntry = CreateObject("Scripting.Dictionary")
dmgrGlobal.Add("ListStart")
dmgrGlobal.Add("ListFinish")
dmgrGlobal.Add("ListLogEntry")
Set dmgrGlobal.ListStart = oListStart
Set dmgrGlobal.ListFinish = oListFinish
Set dmgrGlobal.ListLogEntry = oListLogEntry
The first thing we do , as always ,is to declare our variables. In this case we have 3 of them and we are going to set them up as Dictionary objects. Dictionary objects are like arrays , just a newer version of them . With Dictionary objects you can add , remove and find your items without any additional code. Once we have created them we add them to the dmgrGlobal object that is provided to us , so that we can pass things around our DMS script. Next we have our InputDatasource section.
ConnectionString = "Provider=mrOleDB.Provider.2;
Initial Catalog=C:tempLogs;MR Init MDSC=mrLogDsc"
SelectQuery = "select dateTime, Project,respondentID,InterviewID ,
LogEntry from vdata where logEntry Like 'Interview %'
order by datetime"
From this we can see that we are using the LogDSC to point to our log files ( we used the connection wizard ) and then we specify the Select Query , which in this case take the ( dateTime, Project, Respondent, InterviewID, LogEntry ) columns out of the file but only for the records that start with a logEntry record of “Interview ” and ordered them by the DateTime field. Next we have the OnNextCase Event.
Dim sAnswer,sKey
sKey = """" + Project + """" + mr.Tab + """" + RespondentID +
"""" + mr.Tab + """" + InterviewID + """"
If ( logentry = "Interview start" ) Then
dmgrGlobal.ListStart.add(sKey,ctext(DateTime))
Else
On Error Resume Next
dmgrGlobal.ListFinish.remove(sKey)
dmgrGlobal.ListLogEntry.remove(sKey)
On Error Goto 0
dmgrGlobal.ListFinish.add(sKey,ctext(DateTime))
dmgrGlobal.ListLogEntry.add(sKey,ctext(LogEntry))
End If
if you look inside a log file , and you can use dmquery or notepad to do this you will see that there is lots of information in them. As mentioned in the InputDatasource section we are after all the log entries that start with the word “Interview “. You will see that there are many records that start with this word and some of them are followed by the words “start” or “complete”. Now in a normal situation a survey would produce a “start” log entry and then a “complete” log entry , but sometime we see other words for instance like “timeout” and this might be correct also because perhaps the respondent had an internet issue and could not complete the survey. Anyway we need to know all this info as well as how to make all this unique. To make it unique is easy , we just include the (Project ,Respondent, InterviewID ) fields for each record.
So what’s the code doing , well we make up the unique key and store that into the variable sKey and then if the logenty equals “Interview start” we add the key and the datetime for that log entry to the ListStart Dictionary object. If it does not equal “Interview start” then firstly , just in case we have more than one item with the same key , we remove the key and its value from the ListFinish and ListLogEntry objects and then re-adds them to the objects but this time with the latest information. At this stage you might wonder why we are doing this, but it will become apparent when you see the file we produce.
Next we have the OutputDatasource, but because we are not doing anything with the log files we are using the option UseInputAsOutput so that no output is produced. Finally we have the OnJobEnd Event,
Dim oFSO, oFile,oItem,sLine
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFile = oFSO.CreateTextFile("D:DATASDLogsMRIConcurrents.csv", True)
oFile.WriteLine("""ProjectID""" + mr.Tab + """RespondentId""" + mr.Tab +
"""InterviewID""" + mr.Tab + """StartTime""" + mr.Tab +
"""FinishTime""" + mr.Tab + """Duration""" + mr.Tab +
"""LastLogEntry""" + mr.Tab + """TimeSlot""")
For each oItem in dmgrGlobal.ListStart.Keys()
sLine = "" + oItem + mr.Tab + dmgrGlobal.ListStart.Item[oItem] + ""
If ( dmgrGlobal.ListFinish.Exists(oItem) ) Then
sLine = sLine + mr.Tab + dmgrGlobal.ListFinish.item[oItem]
sLine = sLine + mr.Tab + CTEXT(DateDiff(dmgrGlobal.ListStart.item[oItem],
dmgrGlobal.ListFinish.Item[oItem],"n"))
sLine = sLine + mr.Tab + """" + dmgrGlobal.ListLogEntry.Item[oItem] + """"
sLine = sLine + mr.Tab
Else
sLine = sLine + mr.Tab + "0"
sLine = sLine + mr.Tab + """" + dmgrGlobal.ListLogEntry.Item[oItem] + """"
End If
oFile.WriteLine(sLine)
sLine = ""
Next
oFile.Close()
Set oFSO = Null
In this event we loop through the Dictionary objects that we have populated in the OnNextCase event and write the information out in such a way that the objects are linked up via the key to produce a TAB delimited csv file. So first off we create our file with the FileSystemObject and write out the header row. Then we loop through each key item in the ListStart Dictionary object and find any relating items in the ListFinish.
Once we have found the items , and in particular the ListStart and ListFinish ones we do a subtraction of the two dates in minutes to work out the duration of the particular survey and finally we write them to the file.
Once this is all completed we will end up with a file that looks like this.
"ProjectID" "RespondentId" "InterviewID" "StartTime" "FinishTime" "Duration" "TimeSlot" "LastLogEntry" "s1" "A" "x" 26/09/2008 12:16:53 PM 26/09/2008 12:16:57 PM 12 "Interview stopped using Stop navigation button" "s3" "A" "y" 26/09/2008 12:17:02 PM 26/09/2008 12:27:06 PM 10 "Interview timeout" "s4" "A" "z" 29/09/2008 11:12:59 AM 29/09/2008 11:23:03 AM 11 "Interview timeout" "s1" "B" "a" 29/09/2008 5:23:32 PM 29/09/2008 5:24:08 PM 1 "Interview complete"
On its own this file is not of any use , but if we create an MDD with the following metadata ,
Metadata(en-AU, Analysis, Label, SystemVariables = false)
ProjectID "Project"
text;
RespondentId "RespondentId"
text;
InterviewID "InterviewID"
text;
StartTime "StartTime"
date;
FinishTime "FinishTime"
date;
Duration "Duration"
long;
LastLogEntry "LastLogEntry"
text;
TimeSlot "What time slots does this interview sit in"
categorical [1..]
{
_12_00_00_AM "12:00:00 AM",
_12_01_00_AM "12:01:00 AM",
_12_02_00_AM "12:02:00 AM",
_12_03_00_AM "12:03:00 AM",
_12_04_00_AM "12:04:00 AM",
_12_05_00_AM "12:05:00 AM",
_12_06_00_AM "12:06:00 AM",
_12_07_00_AM "12:07:00 AM",
_12_08_00_AM "12:08:00 AM",
...
_11_51_00_PM "11:51:00 PM",
_11_52_00_PM "11:52:00 PM",
_11_53_00_PM "11:53:00 PM",
_11_54_00_PM "11:54:00 PM",
_11_55_00_PM "11:55:00 PM",
_11_56_00_PM "11:56:00 PM",
_11_57_00_PM "11:57:00 PM",
_11_58_00_PM "11:58:00 PM",
_11_59_00_PM "11:59:00 PM"
};
End Metadata
It will be allot more useful. What we have done is to make a question for each column in the csv file that we have created and added some expected categories. We have not displayed the full list of minutes in this article but you can get to the text you need from the link provided at the end of this article.
That’s it for this article. In the next article we will see how to turn this csv file into a DDF file that we can use for tabulation.
The full code for this article can be found in :
http://CodeCorner.SmaterDimensions.Com @ Code to read IVW log files and make a text file