Visual Basic

1-Apr-10 17:26

I'm by no means an expert but I would like to format the following text that I'm pulling from a MySQL database.

'From C:\e-mail\daily-totals.csv

"ReceivedAt","External","Hit_Count","Port" "2010-03-31 00:28:56","92.47.196.147","868","445" "2010-03-31 09:27:29","98.104.149.153","12","135" "2010-03-31 10:08:56","87.237.140.163","12","5900" "2010-03-31 02:49:33","221.195.73.86","12","8000" "2010-03-31 04:22:26","59.53.88.70","8","2967

I have a bash script running a select statement to pull syslog data from a mysql database but I would like to format it in HTML or make it e-mail friendly Smile | :)

I'm using the following base code from a Microsoft link to e-mail a daily list of denies from a .csv file.

'--------------------------------------------------------------------
'
' Mailout using CDONTS.NewMail
'
'--------------------------------------------------------------------

' Declare all variables.
Option Explicit
Dim objSendMail
Dim strTo, strFrom
Dim strSubject, strBody

' Mail constants (some are for reference).
Const CdoBodyFormatHTML = 0 ' Body property is HTML
Const CdoBodyFormatText = 1 ' Body property is plain text (default)
Const CdoMailFormatMime = 0 ' NewMail object is in MIME format
Const CdoMailFormatText = 1 ' NewMail object is plain text (default)
Const CdoLow    = 0         ' Low importance
Const CdoNormal = 1         ' Normal importance (default)
Const CdoHigh   = 2         ' High importance

strFrom    = "MySQL@test.com"  ' Change to your e-mail address.
strTo      = "test@gmail.com"  ' Change to the recipient address.
strSubject = "Total Report"          ' Change to your subject.

' This line calls the ReadFile() function to read the page contents.
strBody = ReadFile("C:\e-mail\daily-totals.csv")

' This line calls the MakePage() function to format the page as HTML.
strBody = MakePage(strSubject,strBody)

' The following section creates the mail object and sends the mail.
Set objSendMail = CreateObject("CDONTS.NewMail")
	objSendMail.From    = strFrom
	objSendMail.To      = strTo
	objSendMail.Subject = strSubject & " (" & Date() & ")"
	objSendMail.Body    = strBody

	objSendMail.BodyFormat = CdoBodyFormatHTML
	objSendMail.MailFormat = CdoMailFormatMime
	objSendMail.Importance = CdoNormal

	objSendMail.Send
Set objSendMail = Nothing

' This function returns a properly formatted HTML page.
Function MakePage(txtSubject, txtBody)
	Dim txtTemp
	txtTemp = "<HTML>" & vbCrLf
	txtTemp = txtTemp & "<HEAD><TITLE>"
	txtTemp = txtTemp & txtSubject
	txtTemp = txtTemp & "</TITLE></HEAD>" & vbCrLf
	txtTemp = txtTemp & "<BODY>" & vbCrLf
	txtTemp = txtTemp & "<H2>" & txtSubject & "</H2>" & vbCrLf
	txtTemp = txtTemp & txtBody & vbCrLf
	txtTemp = txtTemp & "</BODY>" & vbCrLf
	txtTemp = txtTemp & "</HTML>"
	MakePage = txtTemp
End Function

' This function opens a file and returns the contents of the file.
Function ReadFile(txtFile)
	Dim txtTemp, objFS, objFL
	Set objFS = CreateObject("Scripting.FileSystemObject")
	Set objFL = objFS.OpenTextFile(txtFile)
	Do While Not objFL.AtEndOfStream
		txtTemp = txtTemp & objFL.ReadLine
		txtTemp = txtTemp & vbCrLf
	Loop	
	objFL.Close
	Set objFS = Nothing
	ReadFile = txtTemp
End Function

Is there a way to alter the strbody during the "Function ReadFile(txtFile)" command to alter the .csv file to something that would look a little more pretty inside of an e-mail?

Any help would be much appreciated....

If anyone would like to know how I'm getting Cisco Syslog data into a MySQL database I would be willing to enlighten you. I thought is was a somewhat cool process. I would rather take the Syslog data and write it to a MS SQL database if anyone has any idea's or links.

-lo

modified on Friday, April 2, 2010 12:08 AM

Luc Pattyn1-Apr-10 17:49

Getting an article published on CodeProject should be easier and faster.

1-Apr-10 17:49

Hi,

1.
your code would be easier to read when presented inside PRE tags (instead of CODE tags); you could still edit your existing post.

2.
this looks like VB, not VB.NET; are you sure you want to use an ancient and obsolete language? converting it to VB.NET wouldn't take much effort and make implementing the new functionality a lot easier.

3.
you should decide for yourself how fancy a result you want.
Here are some possibilities:
- put all the body text in PRE tags, that would automatically result in a non-proportional font and somewhat more of a tabular look;
- or create a real table, using TABLE, TR and TD tags

4.
your ReadFile function is reading a text file line by line and concatenating all; your HTML formatting would probably need the individual lines, so you would have to break it up again, unless you:
- either merge the reading and the formatting code in one function;
- or have ReadFile return a list of text lines.

5.
In order to generate a perfect table, your code would have to split each text line into its fields, as indicated by your header line. That would require locating some comma's and spaces (outside quoted strings only). Not hard at all.

6.
the easiest first step would be changing

txtTemp = txtTemp & txtBody & vbCrLf

txtTemp = txtTemp & "<PRE>" & txtBody & "</PRE>" & vbCrLf

which adds the PRE tags.

Smile | :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

lodogg1-Apr-10 18:15

1-Apr-10 18:15

Wow.. one line of code... crazy!

"ReceivedAt","External","Hit_Count","Port"
"2010-04-01 00:02:36","211.119.148.217","820","445"
"2010-04-01 00:59:30","221.195.73.86","16","1080"
"2010-04-01 09:49:17","98.105.130.138","14","135"
"2010-04-01 00:59:19","221.195.73.86","14","8000"
"2010-04-01 01:19:57","61.233.103.34","6","1434"

"your ReadFile function is reading a text file line by line and concatenating all; your HTML formatting would probably need the individual lines, so you would have to break it up again, unless you:
- either merge the reading and the formatting code in one function;
- or have ReadFile return a list of text lines."

Not hard for you but very hard for me Smile | :)

I know the basics.. I wouldn't even know how to convert the code to a newer .NET version. Would I use Visual Studio?

-lo

Luc Pattyn1-Apr-10 18:42

Getting an article published on CodeProject should be easier and faster.

1-Apr-10 18:42

lodogg wrote:
Would I use Visual Studio?

Yes, that would be wise.
If you barely know VB, changing to VB.NET is not just an upgrade, the language has evolved and it now uses classes for everything, not just for GUI stuff. I would switch if the code is getting a new lease on life.
You could always outsource.

Smile | :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

lodogg2-Apr-10 8:35

2-Apr-10 8:35

I would rather stay with my current old dilapidated code and rewrite it in the future. This is a home system and I outsource implementations all of the time at work but not for a home project.. Smile | :)

Any Ideas on how I can format the output into an HTML format?

I take it I would alter code under the

Function ReadFile(txtFile)

I would like to put each item in a column:

"ReceivedAt",		"External",		"Hit_Count",	"Port" 
"2010-03-31 00:28:56",	"92.47.196.147",	"868",		"445"

Thanks for all of the responses!!

-lo

Luc Pattyn2-Apr-10 15:31

Getting an article published on CodeProject should be easier and faster.

2-Apr-10 15:31

Here is one possibility for formatting the output:

When emitting this HTML code:

<table border='1'>
<tr align='center'><td><strong>title1</strong></td><td><strong>title2</strong></td><td><strong>title3</strong></td><td><strong>title4</strong></tf></tr>
<tr align='right'><td>value11</td><td>value12</td><td>value13</td><td>value14</td></tr>
<tr align='right'><td>value21</td><td>longvalue22</td><td>value23</td><td>value24</td></tr>
<tr align='right'><td>value91</td><td>value92</td><td>longvalue93</td><td>value94</td></tr>
</table>

the result, when looked at with a web browser, could be this (but many variations are possible, either by adding HTML tags, or, better yet, by defining some CSS styles):

title1	title2	title3	title4
value11	value12	value13	value14
value21	longvalue22	value23	value24
value91	value92	longvalue93	value94

Whatever you want, the first job of course is to split the input lines in their fields ("valueXY" in my example).

Smile | :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

Dalek Dave2-Apr-10 15:39

Dalek Dave

2-Apr-10 15:39

Luc Pattyn wrote:
better yet, by defining some CSS styles

Definitely agree.

Cut and paste from other parts of the code is lazy, but I have been doing it too, but setting up css styles is easier if you want global changes.

Certainly he should switch to VB.NET, much more functionality, and not terribly hard to learn.

------------------------------------

I will never again mention that I was the poster of the One Millionth Lounge Post, nor that it was complete drivel. Dalek Dave

Luc Pattyn2-Apr-10 15:50

Getting an article published on CodeProject should be easier and faster.

2-Apr-10 15:50

you're getting very good at acting sober on a friday evening/night...

Laugh | :laugh:

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

Dalek Dave2-Apr-10 15:58

Dalek Dave

2-Apr-10 15:58

Ah well you see yesterday was a pseudo-friday, and I was out last night, no work today because we get a holiday in remembrance of nailing the baby jesus to a tree.

------------------------------------

I will never again mention that I was the poster of the One Millionth Lounge Post, nor that it was complete drivel. Dalek Dave

Luc Pattyn2-Apr-10 16:20

Getting an article published on CodeProject should be easier and faster for Bronze and Silver authors.

2-Apr-10 16:20

So you cheated; at least you're honest about it!

Smile | :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

lodogg2-Apr-10 18:06

2-Apr-10 18:06

True but I want to start on the basics like Sub commands and simple variables and work my way up to the big leagues... he he That will never happen but understanding the code will be a big help.!

lodogg2-Apr-10 18:04

2-Apr-10 18:04

Yep I got the basics on HTML I just don't know how to say take the data between the " " and print the data to the following column, Title 3, Title 4 for example.

"ReceivedAt","External","Hit_Count","Port" 
"2010-03-31 00:28:56","92.47.196.147","868","445" 
"2010-03-31 09:27:29","98.104.149.153","12","135" 
"2010-03-31 10:08:56","87.237.140.163","12","5900" 
"2010-03-31 02:49:33","221.195.73.86","12","8000" 
"2010-03-31 04:22:26","59.53.88.70","8","2967

<table border='1'>
<tr align='center'><td><strong>ReceivedAt</strong></td><td><strong>External</strong></td><td><strong>Hit_Count</strong></td><td><strong>Port</strong></tf></tr>
<tr align='right'><td>2010-03-31 00:28:56</td><td>92.47.196.147</td><td>868</td><td>445</td></tr>
<tr align='right'><td>value21</td><td>longvalue22</td><td>value23</td><td>value24</td></tr>
<tr align='right'><td>value91</td><td>value92</td><td>longvalue93</td><td>value94</td></tr>
</table>

Good stuff so far.. Smile | :)

-lo

Luc Pattyn2-Apr-10 18:23

Getting an article published on CodeProject should be easier and faster for Bronze and Silver authors.

2-Apr-10 18:23

your input looks like a comma-separated-list, and furthermore each field starts and ends with a double quote (which you want removed). I don't know the easiest way to split a string in VB, it has been ages ago I last touched any VB.

Smile | :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

Pipes and Files - making it work

LurkingGryphon1-Apr-10 11:09

LurkingGryphon

1-Apr-10 11:09

What I'm working on is basically creating a new version of an old server program. The server service receives messages from an oracle database and distributes those messages to all the connected users, a bit like a chat program that only the administrator can talk on. (It's used to inform the client programs when something has changed in the DB so they can always display the most current data.)

Well, we need the new server to handle a whole new type of client that's running on .NET 3.5, but also be backward compatible with the old C++ client.

So the point I'm getting to here is that the system uses named pipes to communicate from server to clients. The way it was designed back in the day (long before I got involved!) had the server calling CreateNamedPipe to open \\.\pipe\serverrouter, and then the clients connected to it by calling CreateFile with \\<server computer>\pipe\serverrouter as an argument.

I've created a server that creates a NamedPipeServerStream and a client that can connect to it, but the old style client can't see that stream at all -- like it doesn't even exist.

As a smaller-scale test I created a new VB.net application and gave it one command:

Dim test As New System.IO.FileStream("\\<server computer>\pipe\serverrouter", IO.FileMode.OpenOrCreate)

Now, here's what brings up my question: If I point that at the computer that's running the old style server, it throws an exception saying "Filestream was asked to open a device that was not a file..." Which is all well and good, I get that -- but when I point it at the computer running the new server program, the exception is "Could not find file". This matches the behavior of the old style client.

What's going on here? Why can't I find the pipe with anything other than a NamedPipeClientStream? A pipe is a pipe, isn't it? How do I get it to at least see the thing? (Actually achieving a connection would be nice too, but I'll settle for being able to see it on the network first!)

Mycroft Holmes2-Apr-10 23:41

Mycroft Holmes

2-Apr-10 23:41

LurkingGryphon wrote:
but also be backward compatible with the old C++ client.

Somebody really does not like you. Going down this path is one of the reasons Crystal Reports is such a dog, the reason MS rebuilt some of their tools and completely broke backwards compatibility. I have the feeling that a lot of .net stuff is only consumable by .net. You could try wrapping it as com visible and see if that helps (probably not)

I'd seriously look at the design and the need for backwards compatibility.

Never underestimate the power of human stupidity
RAH

LurkingGryphon6-Apr-10 3:41

LurkingGryphon

6-Apr-10 3:41

I got it figured out finally.

It's a naming convention issue: in the old style named pipe calls (which are in kernel32, I believe), you have to specify the full path: \\.\pipe\pipename (and the users connect to \\servername\pipe\pipename)

But in .net, you only specify the "pipename" part. It automatically prepends the "\\.\pipe" internally on the server pipe, and the client pipe's New takes either a computer name argument or not (in which case it presumes the pipe is local), and prepends either "\\computername\pipe\" or "\\.\pipe\" as appropriate.

So if you use the old style address specification with the .net NamedPipeServerStream constructor, it actually ends up putting your pipe at \\.\pipe\pipe\pipename! That's fine if the other end is a NamedPipeClientStream, which will do the same manipulation and not cause a problem, but if you try to create a stream with one and connect with the other, one of them will be looking in \pipe\pipe\ and be unable to find it.

Judging from the examples I've seen of how to use .net's named pipe classes, this is a very common error.

Mycroft Holmes6-Apr-10 11:52

Mycroft Holmes

6-Apr-10 11:52

Put i a tip & trick, make sure there are the right key word and save it for posterity, or some other poor bugger running up against it.

Never underestimate the power of human stupidity
RAH

Gideon Engelberth3-Apr-10 6:09

Gideon Engelberth

3-Apr-10 6:09

We have a pair of programs using named pipes with a .NET server app and a native (Delphi, not C++, but still native) client app, so I know this is possible. During development, the programs ran on different machines and the required step to let the programs talk across different users was to use the NamedPipeServerStream overload that took a PipeSecurity. It ended up something like this (namespaces avoided for brevity):

Dim ps As PipeSecurity
ps.AddAccessRule(New PipeAccessRule("Everyone", PipeAccessRights.FullControl,
                                    AccessControlType.Allow))
Dim New NamedPipeServerStream("PipeName", ..., ps)

As I recall, the problem was configuring the security of the pipe to allow users from another computer to connect.