SQL query to see the last activity for all registered phones

[Nadav]

Hi everyone,

Does anyone know how I can see via CLI the last activity (outgoing call) of all registered phones in the cluster? I went over the data dictionary for 8.6.2 but I can't seem to find such a timestamp. Preferably it should be human readable and not as linux time (seconds since 1970), but linux time is also fine.

Thanks alot for your time!

[Deepak Rawat]

Refer to below post:

https://supportforums.cisco.com/discussion/12735986/how-search-oldest-ip-phones-cucm-862

Regards

Deepak

[Nadav]

Hi Deepak,

The risdb query returns the latest change in registration, not activity. The phone could be online for over a year in some remote site and yet hasn't been used in months. I'm interested in extracting when was the last time each registered phone has made an outgoing call. 

[Jaime Valencia]

Use CDR/CAR for that purpose. 

[Nadav]

Using CAR shows all calls but not latest call for each phone. I did use the CDR database though using the following query which did the job:

run sql car SELECT callingpartynumber, MAX(datetimestampconnect) AS latestcallconnected FROM tbl_billing_data GROUP BY callingpartynumber

The records are of course subject to purging every N days according to the CDR configuration.