Trying to group something

  • Thread starter Thread starter Schklerg
  • Start date Start date
S

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
(e-mail address removed)

192.168.1.5

0 2789 0
68 2003-10-13 13:12:00 2003-10-13 13:13:04 IMAP 20
(e-mail address removed)

192.168.1.5

0 2533 0
69 2003-10-13 13:14:20 2003-10-13 13:14:21 POP 22
(e-mail address removed)

127.0.0.1

0 288 0
70 2003-10-13 13:14:18 2003-10-13 13:14:24 IMAP 21
(e-mail address removed)

127.0.0.1

0 1205 0
71 2003-10-13 13:14:25 2003-10-13 13:14:27 IMAP 23
(e-mail address removed)

127.0.0.1

0 1035 0
72 2003-10-13 13:14:27 2003-10-13 13:14:29 IMAP 24
(e-mail address removed)

127.0.0.1

0 954 0
73 2003-10-28 10:31:56 2003-10-28 10:31:57 SMTPI 1 (e-mail address removed) (e-mail address removed)

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?
 
Schklerg said:
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
(e-mail address removed)

192.168.1.5

0 2789 0
68 2003-10-13 13:12:00 2003-10-13 13:13:04 IMAP 20
(e-mail address removed)

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?
Click to expand...


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??
 
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
Marshall Barton said:
Schklerg said:
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
(e-mail address removed)

192.168.1.5

0 2789 0
68 2003-10-13 13:12:00 2003-10-13 13:13:04 IMAP 20
(e-mail address removed)

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?
Click to expand...


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??
Click to expand...
 
Back
Top