Hi all, I need a monthly count of all SMS messages we send. I have the notice (almost due, overdue, expiration) stats already so now I’m looking for text eReceipt (check-out and manual renewal) stats. I’m thinking this will need a SQL search but don’t know where to start. Thoughts? Many thanks!
Hello Kristen,
Try the polaristransactions emaillog table. Obviously querying it is going to get you emails as well, but something like this should narrow it down to text messages pretty effectively. There’s probably a more efficient way to write that regex…
select * from polaristransactions..emaillog with (nolock)
where senddate > '2025-04-01'
and toaddr like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%'
If you want to double check that actual emails aren’t slipping through, this should show you a list of domains - it should be a lot of carriers, like “txt.att.net”:
select right(toaddr,charindex('@',reverse(toaddr))-1) as 'domain',count(*) from polaristransactions..emaillog with (nolock)
where senddate > '2025-04-01'
and toaddr like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%'
group by right(toaddr,charindex('@',reverse(toaddr))-1)
1 Like
Oh Eleanor, you’re a life-saver! This does the trick and also gives me more detail that I need but hadn’t thought to combine with this query. Really appreciate you taking the time to reply!
Kristen