Trying to group something

[Schklerg]

My mail server generates statistics in the following format
ID1 StartTime EndTime Type ID sender recipient list Gateway PeerIP
VirusName RBL SPAMScore Bytes SSL
67 2003-10-13 13:02:30 2003-10-13 13:12:35 IMAP 2
[email protected]

192.168.1.5

0 2789 0
68 2003-10-13 13:12:00 2003-10-13 13:13:04 IMAP 20
[email protected]

192.168.1.5

0 2533 0
69 2003-10-13 13:14:20 2003-10-13 13:14:21 POP 22
[email protected]

127.0.0.1

0 288 0
70 2003-10-13 13:14:18 2003-10-13 13:14:24 IMAP 21
[email protected]

127.0.0.1

0 1205 0
71 2003-10-13 13:14:25 2003-10-13 13:14:27 IMAP 23
[email protected]

127.0.0.1

0 1035 0
72 2003-10-13 13:14:27 2003-10-13 13:14:29 IMAP 24
[email protected]

127.0.0.1

0 954 0
73 2003-10-28 10:31:56 2003-10-28 10:31:57 SMTPI 1 [email protected] [email protected]

127.0.0.1

0 115881 0


What I am trying to do is set up a report that will summarize activity on a
by user basis. What this would mean is that I have a single header, say for
user jb@cc and it would summarize data where he is either the sender or the
recipient. Of course it's rather simple to have one report where he's the
sender, and one where he's the recipient, but I'd like to have it combined
into one report, summarized under each user. I hope this makes sense. Any
suggestions on how I can do this?

 

[Marshall Barton]

My mail server generates statistics in the following format
ID1 StartTime EndTime Type ID sender recipient list Gateway PeerIP
VirusName RBL SPAMScore Bytes SSL
67 2003-10-13 13:02:30 2003-10-13 13:12:35 IMAP 2
[email protected]

192.168.1.5

0 2789 0
68 2003-10-13 13:12:00 2003-10-13 13:13:04 IMAP 20
[email protected]

192.168.1.5

0 2533 0 [snip]

What I am trying to do is set up a report that will summarize activity on a
by user basis. What this would mean is that I have a single header, say for
user jb@cc and it would summarize data where he is either the sender or the
recipient. Of course it's rather simple to have one report where he's the
sender, and one where he's the recipient, but I'd like to have it combined
into one report, summarized under each user. I hope this makes sense. Any
suggestions on how I can do this?

Fairly easy, but are you sure you want each message to
appear in the report twice? If so, then just use a union
query:

SELECT ID1, StartTime, EndTime, Type, ID, sender, recipient
UNION
SELECT ID1, StartTime, EndTime, Type, ID, recipient, sender

The first select determines the field names in the result
dataset. You may want to add some additional text so the
report can display which is which??

 

[Schklerg]

Perfect! I can tell which is which by the type (smtp, pop, imap) so just
combining them is all I need. I'd never heard of union queries before.
Thanks.

--
Jason
Remove nospam for email replies

My mail server generates statistics in the following format
ID1 StartTime EndTime Type ID sender recipient list Gateway PeerIP
VirusName RBL SPAMScore Bytes SSL
67 2003-10-13 13:02:30 2003-10-13 13:12:35 IMAP 2
[email protected]

192.168.1.5

0 2789 0
68 2003-10-13 13:12:00 2003-10-13 13:13:04 IMAP 20
[email protected]

192.168.1.5

0 2533 0 [snip]

What I am trying to do is set up a report that will summarize activity on a
by user basis. What this would mean is that I have a single header, say for
user jb@cc and it would summarize data where he is either the sender or the
recipient. Of course it's rather simple to have one report where he's the
sender, and one where he's the recipient, but I'd like to have it combined
into one report, summarized under each user. I hope this makes sense. Any
suggestions on how I can do this?

Fairly easy, but are you sure you want each message to
appear in the report twice? If so, then just use a union
query:

SELECT ID1, StartTime, EndTime, Type, ID, sender, recipient
UNION
SELECT ID1, StartTime, EndTime, Type, ID, recipient, sender

The first select determines the field names in the result
dataset. You may want to add some additional text so the
report can display which is which??