|
I've attached the VBS Script in the zip file. This
will get the information into an SQL database (assuming the DSN & user
credentials are correct. You'll need to add it, as I removed my info :).
You should be able to use any database (mySQL, Access, etc…) as long as
you have a ODBC connection and the DSN setup. I’ve commented the
code, but if you have a question about something go ahead and shoot me an
email. As for processing the information out of the SQL DB, I had
to use two ugly looking SQL queries. The first one simply calculates the
amount of downtime & total time for each device, for each map, for each
month. The second one does a little math, to convert minutes down to
percent available. The logging table (see the script for details) is used to
track which line on which file is being imported. It’s designed
such that the script can be stopped(break/crash) in the middle of a run, and minimize
the amount of false data imported into the database. This table can get
large, quickly so it wouldn’t hurt to trim out records whose DateTime
value is older than, say 30 days. Your call. Pass1: vwNetworkUptime6Mo1 SELECT TOP 100 PERCENT DeviceName,
CAST(YEAR(DateTime) AS nvarchar(4)) + N'/' + CAST(MONTH(DateTime) AS
nvarchar(2)) AS YrMo, AVG(RTT) AS aRTT, AVG(MaxRTT) AS aMaxRTT, AVG(MinRTT) AS
aMinRTT, Map, SUM(CONVERT(decimal(7, 3), DownTime) / 100 * 60) AS MinutesDown,
COUNT(DownTime) * 60 AS TotalTime FROM
dbo.tblWhatsUpStats GROUP BY Map, DeviceName, CAST(YEAR(DateTime) AS nvarchar(4)) + N'/' +
CAST(MONTH(DateTime) AS nvarchar(2)) ORDER BY DeviceName, CAST(YEAR(DateTime) AS nvarchar(4)) + N'/' +
CAST(MONTH(DateTime) AS nvarchar(2)) Pass2: vwNetworkUptime6Mo2 SELECT TOP 100 PERCENT YrMo, DeviceName, SUM(1
- MinutesDown / TotalTime) AS Uptime, Map FROM
dbo.vwNetworkUptime6Mo1 GROUP BY DeviceName, YrMo, Map ORDER BY Map, YrMo, DeviceName Jason M. Bohreer, MCSA Network Administrator Nastech Pharmaceutical (425) 908-3657 --------------------------- This email or fax and any attachments thereto may contain
private, confidential, and privileged material for the sole use of the intended
recipient. Any review, copying, or distribution of this email (or any
attachments thereto) by others is strictly prohibited. If you are not the
intended recipient, please contact the sender immediately and permanently
delete the original and any copies of this email and any attachments thereto. -----Original Message----- Jason That is perfect - it is something we can give to the end users too (We
very much like being transparent / visible to our user community here). I would very much appreciate your script, I do like the idea of storing
the info in SQL. Christian p.s. I also automatically added a read receipt to my original e-mail
(sorry) and I can see it also added our default footer. I will try and get that removed in future postings. -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Sent: 30 December 2003 17:33 To: [EMAIL PROTECTED] Subject: RE: [WhatsUp Forum] New user and question on reporting While I don't have nearly the level of sites that you do, we have quite a few devices that we monitor in multiple sites. I was looking
for a method to compare a devices availability to itself over months (as well as relate to the other devices), but couldn't find a way to do it without manually compiling the information. So I spent a little
time examining the format of the ST*.tab files and decided to import them into an SQL database for better post processing. In a nutshell, I have a script that imports the values of the ST files into an SQL table. I then can use a web page & SQL query to
determine the availability of the systems over months. I've attached an
example of my reports page. If this is kinda what your looking for, let
me know and I'll forward over the code for the import & ASP reporting. -jb Jason M. Bohreer, MCSA Network Administrator Nastech Pharmaceutical (425) 908-3657 --------------------------- This email or fax and any attachments thereto may contain private, confidential, and privileged material for the sole use of the intended recipient. Any review, copying, or distribution of this email (or any attachments thereto) by others is strictly prohibited. If you are not the intended recipient, please contact the sender immediately and permanently delete the original and any copies of this email and any attachments thereto. -------------------------------------------------------------------------------- The Salvation Army is an international Christian church working in 109 countries worldwide. As a registered charity, The Salvation Army
demonstrates its Christian principles through social action and is one of the
largest, most diverse providers of social welfare in the world. Visit our ever-growing website to find out what The Salvation Army is
doing across the -------------------------------------------------------------------------------- Attention: The information contained in this message and or attachments is
intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review,
retransmission, dissemination or other use of, or taking of any action in reliance
upon, this information by persons or entities other than the intended
recipient is prohibited. If you received this in error, please contact the sender
and delete the material from any system and destroy any copies. Thank You. -------------------------------------------------------------------------------- Please visit http://www.ipswitch.com/support/mailing-lists.html to be removed from this list. An Archive of this list is available at: http://www.mail-archive.com/whatsup_forum%40list.ipswitch.com/ |
STImportScript.zip
Description: STImportScript.zip
