Reporting through Command Line

[sean]

Is there a way to run reports on the Sever from the command line?

[Jlorenz]

Is there a way to run reports on the Sever from the command line?

If you look at the links (Old Links ST 6.1 and below)

you will see a link with the syntax, you will also note that this syntax is ensuring CPU utilization, not to over load the server.

You can run that from the cmd prompt

they are executable Crystal Reports EXE's so yes you can, but I am not sure if this is the answer your looking for

[sean]

Reason being is because we want to automate some reporting.

So like Schedule a report so it will run automatically.

[Jlorenz]

Reason being is because we want to automate some reporting.

So like Schedule a report so it will run automatically.

ST has no automated reporting, there are 3rd party programs or Windows batch / macro you can use to automate this.

Winbatch comes to mind, or a solid VB programmer can do this nicely

[sean]

any ideas on what 3rd party applications to use or any tutorials on how to do the windows batches?

WTB 1337 programmer, have skillz in writing batches! :P

[Jlorenz]

any ideas on what 3rd party applications to use or any tutorials on how to do the windows batches?

WTB 1337 programmer, have skillz in writing batches! :P

Winbatch

Under windows aka Vista or XP yu should be able to use Task Scheduler
Start > All Programs > Accessories > System Tools > Task Scheduler

[sean]

Winbatch

Under windows aka Vista or XP yu should be able to use Task Scheduler
Start > All Programs > Accessories > System Tools > Task Scheduler

Do you have an example of a script that i can go off of?

[louplou]

querying mysql CDR

It is fairly easy to run reports directly from the mysql database on the ShoreTel server. You can use any ODBC tool, such as Microsoft Access, to connect to the mysql server. In access, you can setup a data source, and use the mysql driver. Make sure it is the most current one. You can download the driver from: MySQL :: MySQL Connector/ODBC 5.1 Downloads
Connection strings you may need to access MySQL CDR database:
• DRIVER={MySQL ODBC 3.51 Driver}
• SERVER = localhost (or the server where you have MySQL installed)
• DATABASE = shorewarecdr
• USER =st_cdrreport
• password =passwordcdrreport
Once you are connected to the database and viewing the tables, it is pretty easy to run queries. Then, let's say you want to see if a particular extension is calling New York City, you can use the following query:
SELECT Call.ID, Call.CallID, Call.StartTime, Call.StartTimeMS, Call.EndTime, Call.EndTimeMS,
Call.CallNote, Call.BillingCode, Call.FriendlyBillingCode, Call.Locked, Call.Extension,
Call.Duration, Call.CallType, Call.WorkgroupCall, Call.LongDistance, Call.DialedNumber,
Call.CallerID, Call.Archived
FROM Call WHERE (((Call.Extension)="170"));
you can then use access to further filter, or expand the where clause for new york or new york city area codes, such as 212, 646, 718, etc.

Lou Person
brightstack TECHNOLOGIES
New York Metro ShoreTel Dealer
474 7th Avenue, New York City, NY

[catatonique]

3rd Party Apps?

any ideas on what 3rd party applications to use or any tutorials on how to do the windows batches?

WTB 1337 programmer, have skillz in writing batches! :P

I am developing an application such as the one you wish for. See my attached PDF. Send me PM.

[neeper67]

I followed the instructions and was able to view all the tables in excel.
Do you know where I can find the total calls from the previous day and the total abandoned calls?
Thanks in advance.

[Palitto Consulting]

I followed the instructions and was able to view all the tables in excel.
Do you know where I can find the total calls from the previous day and the total abandoned calls?
Thanks in advance.

The information in the database represents every call that went through the system. Only detail is stored, there is no summary information. You will have to write or have someone write a query to determine this information.

[Jlorenz]

The PIG Appendix C I believe has all the tables listed

[neeper67]

I was able to pull queries against the database, but I need queries such as Shoretel's built-in reports that I can alter. Do you know a way I can get their Crystal Report's queries?

[Palitto Consulting]

Some of the queries just call stored procedures. These are easy enough to get via SHOW CREATE PROCEDURE.

sp_userActivityDetail
sp_userActivitySummary
sp_workgroupAgentDetail

I'm not immediately aware of how to retrieve the queries for the other reports, but after seeing those, I don't think I want to.

When we develop our reports, we start from scratch. Thusfar we have duplicated most of ShoreTel's reports using a single query and made them many times more efficient than the code you will see in those stored procedures.

[neeper67]

I am able to see all the stored procedures using Mysql Query Browser.

Should I be viewing them or running them?