OK, some more info related to date/time, and a full working PowerShell example at the end of this post!
First of all I found OData specs, in particular the filter section, here:
https://docs.oasis-open.org/odata/o...t1-protocol-complete.html#_The_$filter_System
So here is few more filters that could be helpful:
Code:
$filtercontent = "DstDn ne 'EndCall' and SrcDn ge '1234'"
$filtercontent = "DstDn eq 'EndCall' and SrcDn lt '1324'"
$filtercontent = "SrcId in (9123149,8113248,7112347,6132146)"
$filtercontent = "SegmentId ge 123456"
$filtercontent = "year(SegmentStartTime) eq 2025 and month(SegmentStartTime) eq 02 and day(SegmentStartTime) eq 25 and hour(SegmentStartTime) eq 11 and minute(SegmentStartTime) eq 09"
$filtercontent = "date(SegmentStartTime) eq 2025-02-24"
$filtercontent = "date(SegmentStartTime) ge 2025-02-01 and date(SegmentStartTime) le 2025-02-28"
$filtercontent = "date(SegmentStartTime) ge 2025-02-01 and date(SegmentStartTime) le 2025-02-28 and CallAnswered eq false"
I append
$filtercontent after
$filter in the URI, just to be clear, you can see it in full example below, so you can mix and match as you need.
As you can see, for this discussion, most helpful is the
date(SegmentStartTime) eq 2025-02-24 that completely solves the issue
@mpedwatty had. Using
date(SegmentStartTime) you can filter exact date, or (using gt/ge/lt/le operators) get the date range (eg month, year, week), which is perfect for reports and analytics of any kind.
I'd also like to confirm that I can now filter by any field in the CallHistoryView, except I haven't yet figured out CallTime, but I don't really see point in filtering by the length of the call, as long as I can fetch it that's enough. Other data types (strings, integers, boolean) work fine and you can see those in example filter code above.
Now, below is a piece of PowerShell code that works for selectig one month (February 2025 in this case) by using API key (NOT! username/passsword!). I've included instructions how to get API key (Client ID and secret/key) in case you don't know how.
Code is functional by simply copy/pasting to PowerShell, just make sure to change
$website to your actual domain (and port if different 5001). When asked for credentials just use those you've created in API Integrations section of your 3CX Web UI.
One more note, make sure you don't have firewall or console access limitations set in a way that your connection would be blocked in the first place. If you can access standard web console URL from the device you're running this code from, then it should work just fine. Double checking wouldn't hurt!
Finally, the working code (MAKE SURE TO CHANGE
$website TO YOUR WEB CONSOLE URL!!)
Code:
### FOR 3CX COMMUNITY FORUM POST
### TESTING DATE/TIME FILTERING WITH CALL HISTORY VIEW ENDPOINT
# CREATING 3CX API INTEGRATION CLIENT ID & KEY/SECRET
# 1) Login with system owner account to your 3CX Web UI / web console
# 2) Click Admin (bottom left corner)
# 3) Under: Integrations -> API -> click "+ Add"
# 4) Enter "Client ID" e.g. "test", set Department to "DEFAULT" and Role to "System Owner"
# 5) Save, you will get a new pop-up window, click the Copy icon and save your API key/secret somewhere safe, and confirm with OK
# 6) Tripple check that your API integration was saved as system OWNER because system ADMIN will NOT WORK FOR CALL HISTORY VIEW!
# Login with your Client ID e.g. "test" as "User name" and the API key/secret that was generated for you as "Password" in the "Windows PowerShell credential request" pop-up that you'll get after "Get-Credential" is executed
# Just making sure all variables are blank before starting
$website = ''
$port = ''
$URL = ''
$credentials = ''
$user = ''
$key = ''
$postParams = ''
$request = ''
$content = ''
$token = ''
$headers = ''
$HistoryURI = ''
$filter = ''
$filtercontent = ''
$FullURI = ''
$callhistory = ''
# Change this variable to your own correct website domain and port
$website = 'YourSubdomainHere.3cx.eu'
$port = '5001'
$URL = 'https://'+$website+':'+$port
$credentials = Get-Credential
$user = $credentials.UserName
$key = $credentials.GetNetworkCredential().Password
$postParams = @{client_id=$user;client_secret=$key;grant_type='client_credentials'}
$request = Invoke-WebRequest -Uri $URL/connect/token -Method POST -Body $postParams
$content = $request.Content| ConvertFrom-Json
$token = $content.access_token
$token
$headers = @{Authorization="Bearer $token"}
# This will fetch top 100.000 records from call history for 2024-02, will print to console first and last 30 entries as preview,
# and will then proceed to export whole dataset as CSV and XLSX files to local directory
# (PowerShell module "ImportExcel" required for XLSX export, for installation see link: https://github.com/dfinke/ImportExcel )
$HistoryURI = $URL+'/xapi/v1/CallHistoryView?'
$filter = '$orderby=SegmentStartTime asc&$top=100000&$skip=0&$select=SegmentId,SegmentStartTime,SrcExtendedDisplayName,DstExtendedDisplayName,CallTime,CallAnswered&$count=true&$filter='
$filtercontent = "date(SegmentStartTime) ge 2025-02-01 and date(SegmentStartTime) le 2025-02-28"
$FullURI = "$HistoryURI$filter$filtercontent"
$callhistory = Invoke-RestMethod -Method Get -Uri "$FullURI" -Headers $headers
$callhistory | Select-Object -ExpandProperty value | Select-Object -First 30 | ft
$callhistory | Select-Object -ExpandProperty value | Select-Object -Last 30 | ft
$callhistory | Select-Object -ExpandProperty value | Export-Csv -Path CallHistoryView-2025-02.csv
Import-Module ImportExcel
$callhistory | Select-Object -ExpandProperty value | Export-Excel -Path CallHistoryView-2025-02.xlsx
# To see total count simply call:
$callhistory
# If count is lower than 100k, and matches number of rows in your CSV / XLSX, then you have it all exported successfully
Code isn't exactly how someone would actually write this properly, it is meant for testing and readability and easier understanding what is calling what and what is doing what. If more comments are needed please let me know and I'll fill in the blanks.
If you read this and found it useful throw a like below, I'm always interested to see if I helped anyone
Cheers!
