Contents |
How to extend the functionality of Microsoft Excel to aid in the management and monitoring of Novell Netware servers and volumes.
Version 1.0
By Thomas Roll (troll<at>propodean.co.uk)
The intention of this code is to provide a simple set of functions that, when included in an Excel spreadsheet, can be used to extract basic MIS information of Novell Netware environments based on basic Server and Volume parameters and values. This represents a simple and convenient way in which to extract basic information about disk space availability, server connection usage etc.
In most setups - it will not be required to authenticate/login to the NDS tree in order to get basic information such as disk space statistics or server uptime.
The individual functions have been put together based on information published on the Novell Developer Network about the use of the Novell Client API calls, and sample code published. A special thanks to Karl Durrance - without his work on the Novell clsNovellAPI VB 6 class, I would never have been able to put this library together.
The code will in time be added to in order to provide further functionality to aid in the monitoring and management of Novell Netware environments.
Testing has been done against the following systems:
Microsoft Windows 2000, Windows XP SP1 and SP2
Novell Client 4.9 and later
Novell Netware 5.x, 6.x
Microsoft Excel 97 - 2007
Note - No IPX specific testing has been carried out. I see no reason why it would not work, but Netware 3 and 4 releases and IPX can behave in unexpected manners now and again.
Option Explicit
' Name NWAddIn
' Version 1.0
' Author Thomas Roll
' The intention of this code is to provide a simple set of functions that, when included in an Excel spreadsheet, can
' be used to extract basic MIS information of Novell Netware environments.
' The individual functions have been put together based on information published on the Novell Developer Network,
' showing the use of Novell Client API calls
'
' A special thanks to Karl Durrance - without his work on the Novell clsNovellAPI VB 6 class, I would never have been able
' to put this library together.
'
Private Const ERR_SUCCESS = 0
Private Const NW_MAX_VOLUME_NAME_LEN = 17
Private Const NWCC_NAME_FORMAT_BIND = &H2
Private Const NWCC_OPEN_LICENSED = &H1
Private Const NWCC_TRAN_TYPE_WILD = &H8000
Private Type VERSION_INFO
serverName(47) As Byte
fileServiceVersion As Byte
fileServiceSubVersion As Byte
maximumServiceConnections As Integer
connectionsInUse As Integer
maxNumberVolumes As Integer
revision As Byte
SFTLevel As Byte
TTSLevel As Byte
maxConnectionsEverUsed As Integer
accountVersion As Byte
VAPVersion As Byte
queueVersion As Byte
printVersion As Byte
virtualConsoleVersion As Byte
restrictionLevel As Byte
internetBridge As Byte
reserved(59) As Byte
End Type
Private Type SERVER_AND_VCONSOLE_INFO
currentServerTime As Long
vconsoleVersion As Byte
vconsoleRevision As Byte
End Type
Private Type CPU_INFO
pageTableOwnerFlag As Long
CPUTypeFlag As Long
coProcessorFlag As Long
busTypeFlag As Long
IOEngineFlag As Long
FSEngineFlag As Long
nonDedicatedFlag As Long
End Type
Private Type NWFSE_CPU_INFO
serverTimeAndVConsoleInfo As SERVER_AND_VCONSOLE_INFO
reserved As Integer
numOfCPUs As Long
CPUInfo As CPU_INFO
End Type
Private Type DIR_SPACE_INFO
totalBlocks As Long
availableBlocks As Long
purgeableBlocks As Long
notYetPurgeableBlocks As Long
totalDirEntries As Long
availableDirEntries As Long
reserved As Long
sectorsPerBlock As Byte
volLen As Byte
volName(NW_MAX_VOLUME_NAME_LEN - 1) As Byte
End Type
Type NWFSE_OS_VERSION_INFO
serverTimeAndVConsoleInfo As SERVER_AND_VCONSOLE_INFO
reserved As Integer
OSMajorVersion As Byte
OSMinorVersion As Byte
OSRevisionNum As Byte
accountingVersion As Byte
VAPVersion As Byte
queueingVersion As Byte
securityRestrictionsLevel As Byte
bridgingSupport As Byte
maxNumOfVolumes As Long
numOfConnSlots As Long
maxLoggedInConns As Long
maxNumOfNameSpaces As Long
MaxNumOfLANs As Long
maxNumOfMediaTypes As Long
maxNumOfProtocols As Long
maxMaxSubdirTreeDepth As Long
maxNumOfDataStreams As Long
maxNumOfSpoolPrinters As Long
serialNum As Long
applicationNum As Integer
End Type
Type FSE_SERVER_INFO
replyCanceledCount As Long
writeHeldOffCount As Long
writeHeldOffWithDupRequest As Long
invalidRequestTypeCount As Long
beingAbortedCount As Long
alreadyDoingReallocCount As Long
deAllocInvalidSlotCount As Long
deAllocBeingProcessedCount As Long
deAllocForgedPacketCount As Long
deAllocStillTransmittingCount As Long
startStationErrorCount As Long
invalidSlotCount As Long
beingProcessedCount As Long
forgedPacketCount As Long
stillTransmittingCount As Long
reExecuteRequestCount As Long
invalidSequenceNumCount As Long
duplicateIsBeingSentAlreadyCnt As Long
sentPositiveAcknowledgeCount As Long
sentDuplicateReplyCount As Long
noMemForStationCtrlCount As Long
noAvailableConnsCount As Long
reallocSlotCount As Long
reallocSlotCameTooSoonCount As Long
End Type
Type FILE_SERVER_COUNTERS
tooManyHops As Integer
unknownNetwork As Integer
noSpaceForService As Integer
noReceiveBuffers As Integer
notMyNetwork As Integer
netBIOSProgatedCount As Long
totalPacketsServiced As Long
totalPacketsRouted As Long
End Type
Type NWFSE_FILE_SERVER_INFO
serverTimeAndVConsoleInfo As SERVER_AND_VCONSOLE_INFO
reserved As Integer
NCPStationsInUseCount As Long
NCPPeakStationsInUseCount As Long
numOfNCPRequests As Long
serverUtilization As Long
ServerInfo As FSE_SERVER_INFO
fileServerCounters As FILE_SERVER_COUNTERS
End Type
Private Declare Function NWCCCloseConn Lib "clxwin32" (ByVal connHandle As Long) As Long
Private Declare Function NWCCOpenConnByName Lib "clxwin32" (ByVal startConnHandle As Long, ByVal name As Long, ByVal nameFormat As Long, ByVal openState As Long, ByVal tranType As Long, pConnHandle As Long) As Long
Private Declare Function NWGetCPUInfo Lib "calwin32" (ByVal conn As Long, ByVal CPUNum As Long, ByVal CPUName As String, ByVal numCoprocessor As String, ByVal bus As String, fseCPUInfo As NWFSE_CPU_INFO) As Long
Private Declare Function NWGetDirSpaceInfo Lib "calwin32" (ByVal conn As Long, ByVal dirHandle As Byte, ByVal volNum As Integer, spaceInfo As DIR_SPACE_INFO) As Long
Private Declare Function NWGetFileServerInfo Lib "calwin32" (ByVal conn As Long, fseFileServerInfo As NWFSE_FILE_SERVER_INFO) As Long
Private Declare Function NWGetFileServerVersionInfo Lib "calwin32" (ByVal conn As Long, versBuffer As VERSION_INFO) As Long
Private Declare Function NWGetVolumeNumber Lib "calwin32" (ByVal conn As Long, ByVal volName As String, volNum As Integer) As Long
Private Declare Function NWGetOSVersionInfo Lib "calwin32" (ByVal conn As Long, fseOSVersionInfo As NWFSE_OS_VERSION_INFO) As Long
' ====================================================
' Function: NWSrvConnsInUse
'
' Input Server Name
' Returns Number of connections on the server
' On error Returns NULL
' ====================================================
Public Function NWSrvConnsInUse(ByVal Server As String) As Long
Dim lhConn As Long
Dim lRet As Long
Dim VI As VERSION_INFO
lhConn = OpenConnByName(Server)
If lhConn <> 0 Then
lRet = NWGetFileServerVersionInfo(lhConn, VI)
If lRet = ERR_SUCCESS Then
NWSrvConnsInUse = VI.connectionsInUse
Else
NWSrvConnsInUse = Null
End If
Call NWCCCloseConn(lhConn)
Else
NWSrvConnsInUse = Null
End If
End Function
' ====================================================
' Function: NWSrvNCPStations
'
' Input Server Name
' Returns Current number of NCP connections on the server
' On error Returns NULL
' ====================================================
Public Function NWSrvNCPStations(ByVal Server As String) As Long
Dim lhConn As Long
Dim lRet As Long
Dim NFSI As NWFSE_FILE_SERVER_INFO
lhConn = OpenConnByName(Server)
If lhConn <> 0 Then
lRet = NWGetFileServerInfo(lhConn, NFSI)
If lRet = ERR_SUCCESS Then
NWSrvNCPStations = NFSI.NCPStationsInUseCount
Else
NWSrvNCPStations = Null
End If
Call NWCCCloseConn(lhConn)
Else
NWSrvNCPStations = Null
End If
End Function
' ====================================================
' Function: NWSrvNCPStationsPeak
'
' Input Server Name
' Returns Highest number of NCP station connections
' reached on the server
' On error Returns NULL
' ====================================================
Public Function NWSrvNCPStationsPeak(ByVal Server As String) As Long
Dim lhConn As Long
Dim lRet As Long
Dim NFSI As NWFSE_FILE_SERVER_INFO
lhConn = OpenConnByName(Server)
If lhConn <> 0 Then
lRet = NWGetFileServerInfo(lhConn, NFSI)
If lRet = ERR_SUCCESS Then
NWSrvNCPStationsPeak = NFSI.NCPPeakStationsInUseCount
Else
NWSrvNCPStationsPeak = Null
End If
Call NWCCCloseConn(lhConn)
Else
NWSrvNCPStationsPeak = Null
End If
End Function
' ====================================================
' Function: NWSrvUptime
'
' Input Server Name
' Returns Date/Time stamp for last server boot
' On error Returns NULL
' ====================================================
Public Function NWSrvUptime(ByVal Server As String) As Date
Dim lhConn As Long
Dim lRet As Long
Dim CPUNum As Long
Dim CPUName As String
Dim COPRName As String
Dim BUSName As String
Dim CPUInfo As NWFSE_CPU_INFO
lhConn = OpenConnByName(Server)
If lhConn <> 0 Then
lRet = NWGetCPUInfo(lhConn, CPUNum, CPUName, COPRName, BUSName, CPUInfo)
If lRet = ERR_SUCCESS Then
NWSrvUptime = DateAdd("s", -(CPUInfo.serverTimeAndVConsoleInfo.currentServerTime / 18.2065), Now())
Else
NWSrvUptime = Null
End If
Call NWCCCloseConn(lhConn)
Else
NWSrvUptime = Null
End If
End Function
' ====================================================
' Function: NWSrvOSVersion
'
' Input Server Name
' Returns Version string for the Netware OS
' On error Returns NULL
' ====================================================
Public Function NWSrvOSVersion(ByVal Server As String) As String
Dim lhConn As Long
Dim lRet As Long
Dim VI As NWFSE_OS_VERSION_INFO
lhConn = OpenConnByName(Server)
If lhConn <> 0 Then
lRet = NWGetOSVersionInfo(lhConn, VI)
If lRet = ERR_SUCCESS Then
NWSrvOSVersion = VI.OSMajorVersion & "." & VI.OSMinorVersion & "." & VI.OSRevisionNum
Else
NWSrvOSVersion = Null
End If
Call NWCCCloseConn(lhConn)
Else
NWSrvOSVersion = Null
End If
End Function
' ====================================================
' Function: NWVolSize
'
' Input Server Name
' Volume Name
' Returns Size of volume in MB
' On error Returns NULL
' ====================================================
Public Function NWVolSize(ByVal Server As String, ByVal Volume As String) As Long
Dim lhConn As Long
Dim lRet As Long
Dim DSI As DIR_SPACE_INFO
Dim iVol As Integer
lhConn = OpenConnByName(Server)
If lhConn <> 0 Then
lRet = NWGetVolumeNumber(lhConn, UCase$(Volume) & vbNullChar, iVol)
If lRet = ERR_SUCCESS Then
lRet = NWGetDirSpaceInfo(lhConn, 0, iVol, DSI)
If lRet = ERR_SUCCESS Then
NWVolSize = (DSI.totalBlocks * DSI.sectorsPerBlock / 2) / 1024
Else
NWVolSize = Null
End If
Else
NWVolSize = Null
End If
Call NWCCCloseConn(lhConn)
Else
NWVolSize = Null
End If
End Function
' ====================================================
' Function: NWVolSpaceAvail
'
' Input Server Name
' Volume Name
' Returns Amount of available space of volume in MB
' On error Returns NULL
' ====================================================
Public Function NWVolSpaceAvail(ByVal Server As String, ByVal Volume As String) As Long
Dim lhConn As Long
Dim lRet As Long
Dim DSI As DIR_SPACE_INFO
Dim iVol As Integer
lhConn = OpenConnByName(Server)
If lhConn <> 0 Then
lRet = NWGetVolumeNumber(lhConn, UCase$(Volume) & vbNullChar, iVol)
If lRet = ERR_SUCCESS Then
lRet = NWGetDirSpaceInfo(lhConn, 0, iVol, DSI)
If lRet = ERR_SUCCESS Then
NWVolSpaceAvail = (DSI.availableBlocks * DSI.sectorsPerBlock / 2) / 1024
Else
NWVolSpaceAvail = Null
End If
Else
NWVolSpaceAvail = Null
End If
Call NWCCCloseConn(lhConn)
Else
NWVolSpaceAvail = Null
End If
End Function
' ====================================================
' Function: NWVolSpacePurgeable
'
' Input Server Name
' Volume Name
' Returns Amount of purgeable space on volume in MB
' On error Returns NULL
' ====================================================
Public Function NWVolSpacePurgeable(ByVal Server As String, ByVal Volume As String) As Long
Dim lhConn As Long
Dim lRet As Long
Dim DSI As DIR_SPACE_INFO
Dim iVol As Integer
lhConn = OpenConnByName(Server)
If lhConn <> 0 Then
lRet = NWGetVolumeNumber(lhConn, UCase$(Volume) & vbNullChar, iVol)
If lRet = ERR_SUCCESS Then
lRet = NWGetDirSpaceInfo(lhConn, 0, iVol, DSI)
If lRet = ERR_SUCCESS Then
NWVolSpacePurgeable = (DSI.purgeableBlocks * DSI.sectorsPerBlock / 2) / 1024
Else
NWVolSpacePurgeable = Null
End If
Else
NWVolSpacePurgeable = Null
End If
Call NWCCCloseConn(lhConn)
Else
NWVolSpacePurgeable = Null
End If
End Function
' =================
' Support Functions
'
' OpenConnByName - Connection handle to named server
' TrimNull - Truncate null terminated string (API string return value)
'
' =================
Private Function OpenConnByName(Server As String) As Long
Dim bytename() As Byte
Dim lhConn As Long
Dim lRet As Long
bytename = StrConv(UCase$(Server) & vbNullChar, vbFromUnicode)
lRet = NWCCOpenConnByName(0, VarPtr(bytename(0)), NWCC_NAME_FORMAT_BIND, NWCC_OPEN_LICENSED, NWCC_TRAN_TYPE_WILD, lhConn)
If lRet <> ERR_SUCCESS Then
lhConn = 0
End If
OpenConnByName = lhConn
End Function
Private Function TrimNull(CString As String) As String
CString = CString & vbNullChar
TrimNull = Left(CString, InStr(1, CString, vbNullChar, vbBinaryCompare) - 1)
End Function
To use this code - open a new Excel spreadsheet, open the VBA editor and copy the code into a new module.
All functions defined as public will now be available in the Excel spreadheet as a User Defined function. If the spreadheet is saved as an Excel AddIn, it is possible to use these functions in any spreadsheet as long as the Excel AddIn is loaded.
The following example require the customized MS Excel Add-in loaded or alternatively the required code embedded in a code module within the spreadsheet itself.
To access the functions, use the function (fx) button on the Excel toolbar.
The functions will be listed in the User Defined functions section - in this case the date/time stamp for when the server was last rebooted is extracted.
Supply the required parameters - in this case the Server Name.
The spreadsheet with the result. In this case, the cell has been formatted as a custom Date/Time format.
A convenient way to see if any server has been rebooted overnight.
The functions will be listed in the User Defined functions section - in this case the volume size.
Supply the required parameters - in this case the Server Name and Volume Name.
The spreadsheet with the result.
Combining this function with functions for available space and purgeable space, it is easy to get a quick overview of where you may be running a bit low.
Compiled Add-Ins for Microsoft Excel 97-2003 and Excel 2007 are available from coolsolutions on [1]
Happy reporting.
© 2009 Novell, Inc. All Rights Reserved.
