In order to better understand our mail environment we wanted to ascertain approximately how much email was being held locally on our workstations in the form of local archives. I therefore wrote the script below in VBScript to be ran as a Computer Startup Script via Group Policy. The script performs a WMI query and then hands the result over to a Stored Proc in a SQL Server DB.
Option Explicit
On Error Resume Next
Dim strDBServerName, strDBName, strDBUser, strDBPass, strConnString, strComputerName, strNSFSize, strLine, strNSFMarkerPath
Dim wshNetwork, cmdObj, recObj, sysinfo, objFSO, objFileMarker, colFiles, objWMIService, objFileNSF
Set objFSO = CreateObject("Scripting.FileSystemObject")
strNSFMarkerPath = "C:\NSFSurvey.txt"
If NOT objFSO.FileExists(strNSFMarkerPath) Then
Set wshNetwork = WScript.CreateObject( "WScript.Network" )
Set sysInfo = CreateObject("ADSystemInfo")
strComputerName = wshNetwork.ComputerName & "." & sysInfo.DomainDNSName
strDBServerName = ""
strDBName = "NSFSize"
strDBUser = ""
strDBPass = ""
strConnString = "driver=sql server;server=" & strDBServerName & ";database=" & strDBName & ";uid=" & strDBUser & ";pwd=" & strDBPass
Set cmdObj=createobject("adodb.command")
Set recObj=createobject("adodb.recordset")
Set objWMIService = GetObject("winmgmts:\\" & wshNetwork.ComputerName & "\root\cimv2")
Set colFiles = objWMIService.ExecQuery _
("Select * from CIM_DataFile Where Extension = 'nsf' AND (Drive = 'C:')")
If colFiles.Count = 0 Then
Set objFileMarker = objFSO.CreateTextFile(strNSFMarkerPath)
Wscript.Quit
End If
For Each objFileNSF in colFiles
strNSFSize = CLng(strNSFSize) + objFileNSF.FileSize
Next
'Wscript.Echo strNSFSize
With cmdObj
.activeconnection=strConnString
.commandtype=4
.commandtext="dbo.USP_NSFSize"
.parameters.refresh
.parameters(1).value = strComputerName
.parameters(2).value = strNSFSize
.execute
End with
Set objFileMarker = objFSO.CreateTextFile(strNSFMarkerPath)
End If
The TSQL for the table is below;
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[NSFSizes](
[Timestamp] [datetime] NULL,
[computerFQDN] [varchar](55) NOT NULL,
[NSFSize] [bigint] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
And also the Stored Proc
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[USP_NSFSize]
@strComputerName varchar(55),
@strNSFSize bigint
AS
INSERT INTO dbo.NSFSizes (Timestamp, computerFQDN, NSFSize) VALUES (GETDATE(),@strComputerName, @strNSFSize)
