RE: xAP MSDE Logger

["Sullivan, Glenn" <gsullivan@xxxxxxxxxxxxxx>]

------_=_NextPart_001_01C5A26F.017F165C
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Sorry for the delay Neil... I was on Holidays in Maine...
=20
Here is what I use.  It is fairly convoluted, due to the fact that you
have to link up the "Block Contents" table three times, but once
the
procedure is written, who cares, right?
=20
It produces a list of Date, Number, Name sorted by date Descending.  It
takes the liberty of formatting the date and number for you, in the US
format...
=20
Here you go...
=20
CREATE PROCEDURE sp_GetCIDList=20
as
SELECT=20
CAST(
SUBSTRING(CallDate.value,1,4) + '/' +=20
SUBSTRING(CallDate.value,5,2) + '/' +
SUBSTRING(CallDate.value,7,2) + ' ' +=20
SUBSTRING(CallDate.value,9,2) + ':' +=20
SUBSTRING(CallDate.value,11,2)=20
as datetime) AS [Date],=20
CASE=20
WHEN callnumber.value=3D'None' THEN 'None'
ELSE
CAST(
'(' + SUBSTRING(CallNumber.value,1,3) +=20
')' + SUBSTRING(CallNumber.value,4,3) +=20
'-' + SUBSTRING(CallNumber.value,7,4)=20
as char(13))
END
AS PhoneNumber,=20
CAST(CallName.value as char(30)) AS Name
FROM=20
(
(message_header INNER JOIN=20
(message_block INNER JOIN=20
block_contents AS CallNumber=20
ON (message_block.header_id =3D CallNumber.header_id) AND
(message_block.block_id =3D CallNumber.block_id)
)=20
ON message_header.header_id =3D message_block.header_id
)=20
INNER JOIN=20
Block_Contents AS CallDate=20
ON (message_block.header_id =3D CallDate.header_id) AND
(message_block.block_id =3D CallDate.block_id))=20
LEFT JOIN=20
(Select * from Block_Contents where keyname=3D'Name') AS CallName=20
ON message_block.block_id =3D CallName.block_id
WHERE=20
message_block.block_name=3D'CID.Incoming'
AND CallNumber.keyname=3D'Phone'
AND CallDate.keyname=3D'DateTime'
AND (CallName.keyname=3D'Name' Or CallName.keyname Is Null)
ORDER BY CallDate.value desc
=20
=20
=20

Glenn Sullivan, MCSE+I  MCDBA
David Clark Company Inc.=20

=20


________________________________

From: xap_automation@xxxxxxx
[mailto:xap_automation@xxxxxxx] On
Behalf Of Neil Frost
Sent: Friday, August 05, 2005 5:55 AM
To: xap_automation@xxxxxxx
Subject: RE: [xap_automation] xAP MSDE Logger
=09
=09
Hi Glen,
=09=20
This is great, I now have a whole raft of "stuff" logged in my
SQL database.
=09=20
Do you have a stored procedure to pull out the telephone
numbers?
=09=20
Thanks
Neil

________________________________

From: xap_automation@xxxxxxx on behalf of Sullivan,
Glenn
Sent: Tue 02/08/2005 13:38
To: xap_automation@xxxxxxx;
xAP_developer@xxxxxxx
Subject: [xap_automation] xAP MSDE Logger
=09
=09

I have just uploaded the first beta of my xAP MSDE Logger
Service=20
application.=20

More information can be found here:=20
=09
http://wiki.xapautomation.org/tiki-index.php?page=3DxAP+MSDE+Logger=20

Originally, this was spurned by my desire to have a log of
1-wire=20
temperature readings that I could graph from, and I figured, if
I wanted=20
to log that, why not make it log other things too?  Now I have a
history=20
of my PCs' healths (logged xAP Watcher messages), phone calls
(logged=20
CID/Switchboard messages), and weather (logger xAP Weather
messages) all=20
in one place, where my intranet apps can get to them...=20

Hope this helps someone...=20

Glenn Sullivan, MCSE+I  MCDBA=20
David Clark Company Inc.=20