Create Reports From Script.xml Log File

[jamesh.BPI]

I am trying to create some statistical reports from our /Voiceguide/Scripts/Mainscript.xml log file, but I can't find a single reporting program or method which can parse it.

Everything I've tried so far sees the .xml tag, attempts to open the file as such, and crashes telling me it's not formatted correctly, contains illegal characters, etc etc. (<$RV_STARTTIME> for one example).

 

Has anyone reading this had any success parsing this file? I'm at my wit's end. Off the top of my head, I've tried Jasper Reports, Talend Openstudio, Altova Stylevision, just to name a few. I had marginal success with Talend, but my project produced less and less useful data as I added more fields.

 

What I'm trying to report is total Number of calls per day and 'who pressed what button' type information.

 

Optimally, a PDF or HTML report would be great.

[SupportTeam]

For the " 'who pressed what button' type information. " , how would you like this information to be included in the report?

 

Can you sketch or type out a template of what you would like this report to look like and post this template here?

[jamesh.BPI]

Basically the reason for our interest in the .xml script log is that it has the tags.

The .csv file is much more accessible, but our script is extremely complex and without the tags, there's too much variation in our call logs for the data to be usable.

 

I'd love to be able to tell an app, at this point any app, that <$RV_STARTTIME>2012-12-19 08:49:03</$RV_STARTTIME> is column 1, heading Call_Start, etc etc

Basically I'd like to be able to build a report based on various fields, for example: how many callers on a given date had a StatusID of '8' (<LookUpInDB_StatusID>8</LookUpInDB_StatusID>)

 

Here's a rather sanitized single line from our script log.

 

<$RV_STARTTIME>2012-12-19 08:49:03</$RV_STARTTIME><$RV_DEVICEID>227</$RV_DEVICEID><Dlgcvce>dxxxB19C3</Dlgcvce><DlgcNetwork>dtiB4T3</DlgcNetwork><$RV_CIDNAME></$RV_CIDNAME><$RV_DNIS>1111</$RV_DNIS><DNIS>1111</DNIS><ISDN_SETUP> l 11111111111p 1111</ISDN_SETUP><$RV_CIDNUMBER>11111111111</$RV_CIDNUMBER><LookUpInDBProduct_Column1>2</LookUpInDBProduct_Column1><LookUpInDBProduct_1>2</LookUpInDBProduct_1><LookUpInDBProduct_Column1_1>2</LookUpInDBProduct_Column1_1><LookUpInDBProduct_1_1>2</LookUpInDBProduct_1_1><LookUpInDBProduct_RowCount>1</LookUpInDBProduct_RowCount><LookUpInDB_CustomerID>111111</LookUpInDB_CustomerID><LookUpInDB_1>111111</LookUpInDB_1><LookUpInDB_CustomerID_1>111111</LookUpInDB_CustomerID_1><LookUpInDB_1_1>111111</LookUpInDB_1_1><LookUpInDB_StatusID>8</LookUpInDB_StatusID><LookUpInDB_2>8</LookUpInDB_2><LookUpInDB_StatusID_1>8</LookUpInDB_StatusID_1><LookUpInDB_2_1>8</LookUpInDB_2_1><LookUpInDB_ServiceEndDate>12/19/2012 12:00:00 AM</LookUpInDB_ServiceEndDate><LookUpInDB_3>12/19/2012 12:00:00 AM</LookUpInDB_3><LookUpInDB_ServiceEndDate_1>12/19/2012 12:00:00 AM</LookUpInDB_ServiceEndDate_1><LookUpInDB_3_1>12/19/2012 12:00:00 AM</LookUpInDB_3_1><LookUpInDB_VendorID>2</LookUpInDB_VendorID><LookUpInDB_4>2</LookUpInDB_4><LookUpInDB_VendorID_1>2</LookUpInDB_VendorID_1><LookUpInDB_4_1>2</LookUpInDB_4_1><LookUpInDB_BalanceAvailable>0</LookUpInDB_BalanceAvailable><LookUpInDB_5>0</LookUpInDB_5><LookUpInDB_BalanceAvailable_1>0</LookUpInDB_BalanceAvailable_1><LookUpInDB_5_1>0</LookUpInDB_5_1><LookUpInDB_vceBalance>250</LookUpInDB_vceBalance><LookUpInDB_6>250</LookUpInDB_6><LookUpInDB_vceBalance_1>250</LookUpInDB_vceBalance_1><LookUpInDB_6_1>250</LookUpInDB_6_1><LookUpInDB_txtBalance>0</LookUpInDB_txtBalance><LookUpInDB_7>0</LookUpInDB_7><LookUpInDB_txtBalance_1>0</LookUpInDB_txtBalance_1><LookUpInDB_7_1>0</LookUpInDB_7_1><LookUpInDB_Account>1</LookUpInDB_Account><LookUpInDB_8>1</LookUpInDB_8><LookUpInDB_Account_1>1</LookUpInDB_Account_1><LookUpInDB_8_1>1</LookUpInDB_8_1><LookUpInDB_Account_Expiration>9/6/2013 12:00:00 AM</LookUpInDB_Account_Expiration><LookUpInDB_9>9/6/2013 12:00:00 AM</LookUpInDB_9><LookUpInDB_Account_Expiration_1>9/6/2013 12:00:00 AM</LookUpInDB_Account_Expiration_1><LookUpInDB_9_1>9/6/2013 12:00:00 AM</LookUpInDB_9_1><LookUpInDB_PlanBalanceType>1</LookUpInDB_PlanBalanceType><LookUpInDB_10>1</LookUpInDB_10><LookUpInDB_PlanBalanceType_1>1</LookUpInDB_PlanBalanceType_1><LookUpInDB_10_1>1</LookUpInDB_10_1><LookUpInDB_PlanCombo>0</LookUpInDB_PlanCombo><LookUpInDB_11>0</LookUpInDB_11><LookUpInDB_PlanCombo_1>0</LookUpInDB_PlanCombo_1><LookUpInDB_11_1>0</LookUpInDB_11_1><LookUpInDB_TUES>0</LookUpInDB_TUES><LookUpInDB_12>0</LookUpInDB_12><LookUpInDB_TUES_1>0</LookUpInDB_TUES_1><LookUpInDB_12_1>0</LookUpInDB_12_1><LookUpInDB_TUED>1/1/1900 12:00:00 AM</LookUpInDB_TUED><LookUpInDB_13>1/1/1900 12:00:00 AM</LookUpInDB_13><LookUpInDB_TUED_1>1/1/1900 12:00:00 AM</LookUpInDB_TUED_1><LookUpInDB_13_1>1/1/1900 12:00:00 AM</LookUpInDB_13_1><LookUpInDB_TUBalanceType>1</LookUpInDB_TUBalanceType><LookUpInDB_14>1</LookUpInDB_14><LookUpInDB_TUBalanceType_1>1</LookUpInDB_TUBalanceType_1><LookUpInDB_14_1>1</LookUpInDB_14_1><LookUpInDB_TUvce>0</LookUpInDB_TUvce><LookUpInDB_15>0</LookUpInDB_15><LookUpInDB_TUvce_1>0</LookUpInDB_TUvce_1><LookUpInDB_15_1>0</LookUpInDB_15_1><LookUpInDB_TUtxt>0</LookUpInDB_TUtxt><LookUpInDB_16>0</LookUpInDB_16><LookUpInDB_TUtxt_1>0</LookUpInDB_TUtxt_1><LookUpInDB_16_1>0</LookUpInDB_16_1><LookUpInDB_TUCombo>0</LookUpInDB_TUCombo><LookUpInDB_17>0</LookUpInDB_17><LookUpInDB_TUCombo_1>0</LookUpInDB_TUCombo_1><LookUpInDB_17_1>0</LookUpInDB_17_1><LookUpInDB_CUT>False</LookUpInDB_CUT><LookUpInDB_18>False</LookUpInDB_18><LookUpInDB_CUT_1>False</LookUpInDB_CUT_1><LookUpInDB_18_1>False</LookUpInDB_18_1><LookUpInDB_RowCount>1</LookUpInDB_RowCount><retrVendorBalanceRequest_Result_Code>OK</retrVendorBalanceRequest_Result_Code><retrVendorBalanceRequest_Result_Desc>OK</retrVendorBalanceRequest_Result_Desc><retrVendorBalanceRequest_Result_Code>OK</retrVendorBalanceRequest_Result_Code><retrVendorBalanceRequest_Result_Desc>OK</retrVendorBalanceRequest_Result_Desc><Hangup Source>Hangup Module</Hangup Source>

[SupportTeam]

This version no longer has $RV_ prefixes in any of the XML log tags.

[old link removed]

the simplest way to get a count of certain entries on a certain date is to run a command like this from a Command Prompt:

find "STARTTIME>2012-12-19" mylog.xml | find /c "<LookUpInDB_StatusID>8"

above will return a count of calls on 2012-12-19 date that have <LookUpInDB_StatusID>8 showing in the .xml log.

A batch file that makes multiple similar calls (say one for each day from 1 to 31) can be used to generate the required raw data for a report that shows what this count was each day.

[jamesh.BPI]

Thanks, but since it's not properly formed .xml, the .xml extension just confuses any app that tries to open it.

Since we're not nesting the other parameters within <STARTTIME>, why not dispense with the pseudo-xml and do something we all can use, like comma delimited with keys, like STARTTIME=(date/time),PARAMETER1=(this),PARAMETER2=(that), etc etc...

[SupportTeam]

It would be straightforward to add a JSON style log file. Would JSON be OK?

 

(above proposed 'comma delimited with keys' is fairly close to JSON format)

[jamesh.BPI]

Could we please see an example?

[SupportTeam]

For each call there would be an entry in the .json log file like this:

 

{

"STARTTIME": "2012-12-19 08:49:03",

"DEVICEID": "227",

"DlgcVoice": "dxxxB19C3",

"DlgcNetwork": "dtiB4T3",

"CIDNAME": "",

"DNIS": "1111",

"ISDN_SETUP": " l 11111111111p 1111",

"CIDNUMBER": "11111111111",

"LookUpInDBProduct_Column1": "2",

"LookUpInDBProduct_1": "2",

"LookUpInDBProduct_Column1_1": "2",

"LookUpInDBProduct_1_1": "2",

"LookUpInDBProduct_RowCount": "1",

"LookUpInDB_CustomerID": "111111",

"LookUpInDB_1": "111111",

"LookUpInDB_CustomerID_1": "111111",

"LookUpInDB_1_1": "111111",

"LookUpInDB_StatusID": "8",

"LookUpInDB_2": "",

"LookUpInDB_StatusID_1": "8",

"LookUpInDB_2_1": "8",

"LookUpInDB_ServiceEndDate": "12/19/2012 12:00:00 AM",

"LookUpInDB_3": "12/19/2012 12:00:00 AM",

"LookUpInDB_ServiceEndDate_1": "12/19/2012 12:00:00 AM",

"LookUpInDB_3_1": "12/19/2012 12:00:00 AM",

"LookUpInDB_VendorID": "2",

"LookUpInDB_4": "2",

"LookUpInDB_VendorID_1": "2",

"LookUpInDB_4_1": "2",

"LookUpInDB_BalanceAvailable": "0",

"LookUpInDB_5": "0",

 

etc etc...

 

}

 

 

Basically each RV would be on a separate line, formatted as shown above, and the set of RVs for the call surrounded by curly brackets, with the curly brackets on their own separate lines.

[SupportTeam]

Alternatively the json log could have all data for each call on one line.

 

so each call would be a line that looks like this:

 

{"STARTTIME":"2012-12-19 08:49:03","DEVICEID":"227","DlgcVoice":"dxxxB19C3", etc etc... }

[jamesh.BPI]

Could we have an equal sign as a key seperator? Otherwise the value of starttime might be misinterpreted as key:value

[SupportTeam]

Then the log would no longer would be in JSON format. JSON format uses : as the separator.

see: http://www.json.org/ and http://en.wikipedia.org/wiki/JSON

 

 

What would you be using to read in or process this key-value log? Is it an off the shelf application/library or a custom written application/script?

 

 

We would prefer to keep in in JSON format as this makes log usable by wider range of users.

 

There are many libraries and applications out there that can read in JSON and will not be confused by : as key-value separator.

All values logged by VoiceGuide would be written as strings and hence would be enclosed in double quotes, allowing straightforward parsing of the log entries.

 

If your application/script has problems with using : as key-value separator the please let us know and we can at the application/script and determine best way of resolving this.

[jamesh.BPI]

{"STARTTIME":"2012-12-19 08:49:03","DEVICEID":"227","DlgcVoice":"dxxxB19C3", etc etc... }

 

I was concerned about the highlighted text above.

[SupportTeam]

Are you referring to the ":" character in the "08:49:03"?

 

The ":" in "2012-12-19 08:49:03" date/time expression is a pretty standard character to separate the hours/minutes/seconds values. To change it to another character would make the date/time expression look unfamiliar to other users.

 

Anything that parses JSON (and there are many applications and libraries out there that do that) would not be confused by a ":" within a value expression.

 

Would you be using a JSON library/application to parse the JSON input? or something else?

[jamesh.BPI]

I'd certainly be interested in trying the JSON log file, if that's not too much trouble.

 

Thanks!