Query on TMS SQL DB

[philokoi1]

Hi,

To provide relevant statistics on VC's, I am looking for a link between the objSystemCallLog record and the E164 Alias from the MCU MSE-8500.

The data is available in the field StrValue from the field_SystemField Table, this StrValue field is linked to the objSystem_ID (488 for our MSE) and the field_Field_Id = 121 for the E164 Alias but this one can have several FieldIndex values each value represent the Conference Number (120 differents values in our case).

I need to have the right E164 Alias in the CDR record for a MCU call, I don't find a link between the CDR record and the MCU Conference Number, except in the RemoteSite field but only in the call part where the remote site is the MCU, not in the call part from  the MCU to the Endpoint.

Hope someone can help me.

Best regards,

Philippe

[Steve Kapinos]

Note, Cisco does not support direct access to the database in this fashion as this information is not documented nor is it guaranteed to be consistent.

Just thinking off the top of my head.. I don't think the data you are after (getting the alias for the conference in the CDR record) exists in the call records.  The bridge knows there is a conference.. the dial-in alias for it is just a property that is just a secondary property.

you might be better off trying to follow the conference back to the scheduledCall entry for the row as that would know which meeting port was used for the call.. and meeting port to alias could be worked out.

If you can tell us what you are trying to do.. there may be a different path available.  Like using the gatekeeper logs instead of the MCU, etc.

[philokoi0]

Hi Steve,

Thank you for your answer.

I agree with you about the support of direct access to the database but the CDR reporting is very limited in TMS, it was the only way I found to provide statistics on our 230 endpoints.

It's really important to provide statistics to our hierarchy to increase budgets for video-conference systems to decrease the impact on carbon footprint from our institution.

In attachment, please find the table link for the query.

Best regards,

Philippe

[Steve Kapinos]

I'm still not really following what you are after.  Basically everything you highlighted in that report is what the CDR records are.

The difference is the CDR reports FOR A DEVICE do not report what alias was used in the incoming call.  If you could explain what you are trying to do (your need, not your proposed solution), we can better help guide you through what is available.

If you want both incoming and outgoing aliases... the gatekeeper log is what you want to pursue as it reports both.

[Jens Didriksen]

Maybe the TMS Analytics Extension could be of some help? http://www.cisco.com/c/dam/en/us/td/docs/telepresence/infrastructure/tmsae/admin_guide/Cisco_TMSAE_admin_guide_1-2.pdf

We use vAnalytics by Vyopta, not cheap, but we like it. 

/jens

Please rate replies and mark question(s) as "answered" if applicable.

[sammy1604]

Hi,

Did you get a solution for this query?