Friday, October 12, 2012
VBScript to find Fileage and send an e-mail
This summary is not available. Please
click here to view the post.
Check_Churn script;This script will basically to see how much the log files are changing over a user defined period of time
Check_Churn script;This script will basically to see how much the log files are changing over a user defined period of time.
It's useful to check how if there are any delay in getting a logfile updated from the start of the file o the end of the file at some instance(depends on the schedule time of the script as well).
Syntax to follow: C:\>cscript /nologo "c:\Documents and Settings\bdhinakaran\Desktop\VbScript\chec
k_churn.vbs" -d "c:\\" -p "(iiCFDFXLIVE-SyslogHub-3.3.3-instance1\[(\d)\]).log"
-c 90
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'On Error Resume Next
adir = Wscript.Arguments.Item(0)
dir = Cstr(Wscript.Arguments.Item(1))
aPatternMatch = Wscript.Arguments.Item(2)
PatternMatch = Cstr(Wscript.Arguments.Item(3))
acri = Wscript.Arguments.Item(4)
cri = CLng(Wscript.Arguments.Item(5))
If adir = "-d" and aPatternMatch = "-p" and acri = "-c" Then
Const adVarChar = 200
Const MaxCharacters = 255
today = now()
Set DataList = CreateObject("ADOR.Recordset")
DataList.Fields.Append "FileName", adVarChar, MaxCharacters
DataList.Fields.Append "FileDateCreated", adVarChar, MaxCharacters
DataList.Open
Set fso = CreateObject("Scripting.FileSystemObject")
Set hash = CreateObject ("Scripting.Dictionary")
If fso.FolderExists(dir) Then
Set f = fso.GetFolder(dir)
Set fc = f.Files
For Each file in fc
's = s & file.name & " " & "DateCreated "& file.DateCreated
'hash.add file.name, file.DateCreated
's = s & VbNewLine
'count = count + 1
Set myRegExp = New RegExp
myRegExp.IgnoreCase = True
myRegExp.Global = True
myRegExp.Pattern = patternMatch
'Wscript.Echo "Pattern Match:" & patternMatch
Set myMatches = myRegExp.Execute(file.Name)
If myMatches.count > 0 Then
DataList.AddNew
DataList("FileName") = Cstr(file.name)
DataList("FileDateCreated") = file.DateCreated
DataList.Update
count = count + 1
'Wscript.Echo "Matched Pattern:" & patternMatch
End If
Next
'Wscript.Echo "No Pattern Match Found in the specified Directory:" &dir
'Wscript.Echo "Total Files:"&count
Else
Wscript.Echo "Folder doesn't exists. Please check the path"
Wscript.Quit(2)
End If
If count > 1 Then
DataList.Sort = "FileName asc" 'sort the disconnected recordset to asc by default it sorts to ascending if needed can sort to desc
DataList.MoveFirst
diff1 = DateDiff("s",DataList.Fields.Item("FileDateCreated"),today)
DataList.MoveLast
diff2 = DateDiff("s",DataList.Fields.Item("FileDateCreated"),today)
rdiff = diff1 - diff2
'Wscript.Echo "Real diff in Second for the FirstFile and LastFile:" & rdiff &"sec"
'Displays the overall fields
'DataList.MoveFirst
'Do Until DataList.EOF
' Wscript.Echo DataList.Fields.Item("FileName") & vbTab & DataList.Fields.Item("FileDateCreated")
' DataList.MoveNext
'Loop
If cri > rdiff Then
Wscript.Echo "Creation Time of the First Log file and Last LogFile is less than the user-defined time:" & rdiff &"sec" &"in the directory: " &dir
Wscript.quit(0)
Else
Wscript.Echo "Is more than the user-defined time: " & rdiff &" sec " & " in the directory: " &dir
Wscript.Quit(2)
End If
'keys = hash.Keys
'items = hash.Items
'for i = 0 to hash.Count - 1
'wscript.echo Keys(i) & " = " & hash.item(Keys(i))
'next
'diff1 = DateDiff("s",hash.item(Keys(0)),today)
'diff2 = DateDiff("s",hash.item(Keys(hash.Count - 1)),today)
'Wscript.Echo "The First File Difference with the today's date:" & diff1
'Wscript.Echo "The Last File Difference with the today's date:" & diff2
'rdiff = diff1 - diff2
'Wscript.Echo s
'Wscript.Echo "Real diff in Second for the FirstFile and LastFile:" & rdiff &"sec"
'Wscript.Echo "Total Files:" & hash.Count
Else
'DataList.MoveFirst
'Do Until DataList.EOF
'Wscript.Echo DataList.Fields.Item("FileName") & vbTab & DataList.Fields.Item("FileDateCreated")
'DataList.MoveNext
'Loop
Wscript.Echo "We have single record or file in the specified Directory or no files of the patten match specified: " & dir
Wscript.Quit(2)
End If
Else
Wscript.Echo "Please follow the pattern: check_churn.vbs -d <directory> -p <pattern> -c <error_sec> "
Wscript.echo "This script will basically to see how much the log files are changing over a user defined period of time"
Wscript.Quit(2)
End If
It's useful to check how if there are any delay in getting a logfile updated from the start of the file o the end of the file at some instance(depends on the schedule time of the script as well).
Syntax to follow: C:\>cscript /nologo "c:\Documents and Settings\bdhinakaran\Desktop\VbScript\chec
k_churn.vbs" -d "c:\\" -p "(iiCFDFXLIVE-SyslogHub-3.3.3-instance1\[(\d)\]).log"
-c 90
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'On Error Resume Next
adir = Wscript.Arguments.Item(0)
dir = Cstr(Wscript.Arguments.Item(1))
aPatternMatch = Wscript.Arguments.Item(2)
PatternMatch = Cstr(Wscript.Arguments.Item(3))
acri = Wscript.Arguments.Item(4)
cri = CLng(Wscript.Arguments.Item(5))
If adir = "-d" and aPatternMatch = "-p" and acri = "-c" Then
Const adVarChar = 200
Const MaxCharacters = 255
today = now()
Set DataList = CreateObject("ADOR.Recordset")
DataList.Fields.Append "FileName", adVarChar, MaxCharacters
DataList.Fields.Append "FileDateCreated", adVarChar, MaxCharacters
DataList.Open
Set fso = CreateObject("Scripting.FileSystemObject")
Set hash = CreateObject ("Scripting.Dictionary")
If fso.FolderExists(dir) Then
Set f = fso.GetFolder(dir)
Set fc = f.Files
For Each file in fc
's = s & file.name & " " & "DateCreated "& file.DateCreated
'hash.add file.name, file.DateCreated
's = s & VbNewLine
'count = count + 1
Set myRegExp = New RegExp
myRegExp.IgnoreCase = True
myRegExp.Global = True
myRegExp.Pattern = patternMatch
'Wscript.Echo "Pattern Match:" & patternMatch
Set myMatches = myRegExp.Execute(file.Name)
If myMatches.count > 0 Then
DataList.AddNew
DataList("FileName") = Cstr(file.name)
DataList("FileDateCreated") = file.DateCreated
DataList.Update
count = count + 1
'Wscript.Echo "Matched Pattern:" & patternMatch
End If
Next
'Wscript.Echo "No Pattern Match Found in the specified Directory:" &dir
'Wscript.Echo "Total Files:"&count
Else
Wscript.Echo "Folder doesn't exists. Please check the path"
Wscript.Quit(2)
End If
If count > 1 Then
DataList.Sort = "FileName asc" 'sort the disconnected recordset to asc by default it sorts to ascending if needed can sort to desc
DataList.MoveFirst
diff1 = DateDiff("s",DataList.Fields.Item("FileDateCreated"),today)
DataList.MoveLast
diff2 = DateDiff("s",DataList.Fields.Item("FileDateCreated"),today)
rdiff = diff1 - diff2
'Wscript.Echo "Real diff in Second for the FirstFile and LastFile:" & rdiff &"sec"
'Displays the overall fields
'DataList.MoveFirst
'Do Until DataList.EOF
' Wscript.Echo DataList.Fields.Item("FileName") & vbTab & DataList.Fields.Item("FileDateCreated")
' DataList.MoveNext
'Loop
If cri > rdiff Then
Wscript.Echo "Creation Time of the First Log file and Last LogFile is less than the user-defined time:" & rdiff &"sec" &"in the directory: " &dir
Wscript.quit(0)
Else
Wscript.Echo "Is more than the user-defined time: " & rdiff &" sec " & " in the directory: " &dir
Wscript.Quit(2)
End If
'keys = hash.Keys
'items = hash.Items
'for i = 0 to hash.Count - 1
'wscript.echo Keys(i) & " = " & hash.item(Keys(i))
'next
'diff1 = DateDiff("s",hash.item(Keys(0)),today)
'diff2 = DateDiff("s",hash.item(Keys(hash.Count - 1)),today)
'Wscript.Echo "The First File Difference with the today's date:" & diff1
'Wscript.Echo "The Last File Difference with the today's date:" & diff2
'rdiff = diff1 - diff2
'Wscript.Echo s
'Wscript.Echo "Real diff in Second for the FirstFile and LastFile:" & rdiff &"sec"
'Wscript.Echo "Total Files:" & hash.Count
Else
'DataList.MoveFirst
'Do Until DataList.EOF
'Wscript.Echo DataList.Fields.Item("FileName") & vbTab & DataList.Fields.Item("FileDateCreated")
'DataList.MoveNext
'Loop
Wscript.Echo "We have single record or file in the specified Directory or no files of the patten match specified: " & dir
Wscript.Quit(2)
End If
Else
Wscript.Echo "Please follow the pattern: check_churn.vbs -d <directory> -p <pattern> -c <error_sec> "
Wscript.echo "This script will basically to see how much the log files are changing over a user defined period of time"
Wscript.Quit(2)
End If
Wednesday, September 26, 2012
HOWTO – Add check_db Plug-in (check_db)
The plug-in can be found at nagios exchange bu this is a modified version which actually uses SERVICE_NAME instead of SID. This has been loaded with the new JDBC connectivity driver for the new Oracle server Connectivity.
Description:
Nagios plugin to check output of a Oracle sql query, matched
with a regular expression. This checks the numeric data to the matched regular
expression.
Remote oracle database check using JDBC drivers. Supports
custom SQL queries and regular expression match. Provides similar functionality
as SiteScope DB monitor. This plug-in can check almost every aspect of oracle
database
The plug-in uses JSAP command line parser from http://www.martiansoftware.com/jsap/
and a ojdbc6.jar class file for the connectivity.
This plug-in involves basically 4 component check_db ,
DbCheck.class/DbCheck.java , ojdbc6.jar and JSAP-2.0a.jar
You can get the completed doc and file at this location: https://skydrive.live.com/redir?resid=7109A4AA026A5AFB!112
Sunday, March 4, 2012
File Upload and Download Automation to SFTP
Set oFTPScriptFSO = CreateObject("Scripting.FileSystemObject")
Set oFTPScriptShell = CreateObject("WScript.Shell")
If (oFTPScriptFSO.FileExists("C:\Users\bdhinakaran\Desktop\sftpfile.txt")) Then
' do nothing
Else
Set writeObject= oFTPScriptFSO.CreateTextFile("C:\Users\bdhinakaran\Desktop\sftpfile.txt", True)
writeObject.WriteLine("to test sftp")
End If
sUpldCmd = """C:\Program Files (x86)\WinSCP\WinSCP.com"" -script=" & "C:\Users\bdhinakaran\Desktop\put.txt"
sDwnldCmd = """C:\Program Files (x86)\WinSCP\WinSCP.com"" -script=" & "C:\Users\bdhinakaran\Desktop\get.txt"
'WScript.Echo "run Command: " & sCmd
sReturn = oFTPScriptShell.Run(sUpldCmd,3,true)
'WScript.Echo "Run Return Value: " & sreturn
Wscript.Sleep 1000
'Delete the file once it is uploaded.
If ( sReturn = 0) Then
If (oFTPScriptFSO.FileExists("C:\Users\bdhinakaran\Desktop\sftpfile.txt")) Then
oFTPScriptFSO.DeleteFile("C:\sftpfile.txt")
Else
WScript.Echo "unable to delete the Uploaded File|0"
WScript.Quit (2)
End If
Else
Wscript.Echo "unable to login to SFTP Server"
Wscript.Quit (2)
End If
sReturn = 0
'procedures to download the file and delete after that
sReturn = oFTPScriptShell.Run(sDwnldCmd,3,true)
WScript.Sleep 1000
'WScript.Echo "Run Return Value: " & sreturn
If ( sReturn = 0) Then
If (oFTPScriptFSO.FileExists("C:\Users\bdhinakaran\Desktop\sftpfile.txt")) Then
WScript.Echo "SFTP is doing good | 1"
WScript.Quit (0)
Else
WScript.Echo "File sftpfile.txt has not downloaded, check manually|0"
WScript.Quit (2)
End If
Else
Wscript.Echo "unable to login to SFTP Server"
End If
Set oFTPScriptShell = Nothing
Set oFTPScriptFSO = Nothing
Set oFTPScriptShell = CreateObject("WScript.Shell")
If (oFTPScriptFSO.FileExists("C:\Users\bdhinakaran\Desktop\sftpfile.txt")) Then
' do nothing
Else
Set writeObject= oFTPScriptFSO.CreateTextFile("C:\Users\bdhinakaran\Desktop\sftpfile.txt", True)
writeObject.WriteLine("to test sftp")
End If
sUpldCmd = """C:\Program Files (x86)\WinSCP\WinSCP.com"" -script=" & "C:\Users\bdhinakaran\Desktop\put.txt"
sDwnldCmd = """C:\Program Files (x86)\WinSCP\WinSCP.com"" -script=" & "C:\Users\bdhinakaran\Desktop\get.txt"
'WScript.Echo "run Command: " & sCmd
sReturn = oFTPScriptShell.Run(sUpldCmd,3,true)
'WScript.Echo "Run Return Value: " & sreturn
Wscript.Sleep 1000
'Delete the file once it is uploaded.
If ( sReturn = 0) Then
If (oFTPScriptFSO.FileExists("C:\Users\bdhinakaran\Desktop\sftpfile.txt")) Then
oFTPScriptFSO.DeleteFile("C:\sftpfile.txt")
Else
WScript.Echo "unable to delete the Uploaded File|0"
WScript.Quit (2)
End If
Else
Wscript.Echo "unable to login to SFTP Server"
Wscript.Quit (2)
End If
sReturn = 0
'procedures to download the file and delete after that
sReturn = oFTPScriptShell.Run(sDwnldCmd,3,true)
WScript.Sleep 1000
'WScript.Echo "Run Return Value: " & sreturn
If ( sReturn = 0) Then
If (oFTPScriptFSO.FileExists("C:\Users\bdhinakaran\Desktop\sftpfile.txt")) Then
WScript.Echo "SFTP is doing good | 1"
WScript.Quit (0)
Else
WScript.Echo "File sftpfile.txt has not downloaded, check manually|0"
WScript.Quit (2)
End If
Else
Wscript.Echo "unable to login to SFTP Server"
End If
Set oFTPScriptShell = Nothing
Set oFTPScriptFSO = Nothing
Monday, November 7, 2011
VBScript to find Fileage and send an e-mail
'On Error Resume Next
Const ForReading = 1
today = now()
Set objMessage = CreateObject("CDO.Message")
objMessage.From = "babu.dhinakaran@jda.com"
objMessage.To = "babu.dhinakaran@jda.com"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO.OpenTextFile("c:\filelist.txt", ForReading)
Do Until objTextFile.AtEndOfStream
folderspec = objTextFile.Readline
'Wscript.Echo "File in the Folder:" & folderspec
objMessage.Subject = folderspec
Agecount = 0
Set folder = objFSO.GetFolder(folderspec)
Set fc = folder.Files
For Each f1 in fc
file = f1.name
filespec = folderspec & file
Set file = objFSO.GetFile(filespec)
ShowDateCreated = file.DateCreated
'Wscript.Echo "FileName: " & filespec
'Wscript.Echo "ShowDateCreated:" & ShowDateCreated
difference = DateDiff("n",ShowDateCreated,today)
If difference > 30 Then
Agecount = Agecount + 1
End If
'Wscript.Echo "File Age in minutes is:" & difference
Next
'Wscript.Echo "Total file older than 30 minutes at: " & folderspec & " are:" & Agecount
'mailbody = "Total file older than 30 minutes at: " & folderspec & " are:" & Agecount
'objMessage.TextBody = mailbody
'Wscript.Echo "-----------------------------------------------"
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "indiamailrelay.jda.corp.local"
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
objMessage.Configuration.Fields.Update
If Agecount > 0 Then
mailbody = "Total file older than 30 minutes at: " & folderspec & " are:" & Agecount
objMessage.TextBody = mailbody
objMessage.Send
'Wscript.Echo "Total file older than 30 minutes at: " & folderspec & " are:" & Agecount
End If
Loop
------------------------------------------------------------------------------------
The above code basically, check for a fileage > 30 minutes and send an email if it is true.
It uses a text file as a input to read folder path where files exists
eg:- c:\temp\
Note: Make sure to provide full path as said above, if provided c:\temp this will generate an error.
Const ForReading = 1
today = now()
Set objMessage = CreateObject("CDO.Message")
objMessage.From = "babu.dhinakaran@jda.com"
objMessage.To = "babu.dhinakaran@jda.com"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO.OpenTextFile("c:\filelist.txt", ForReading)
Do Until objTextFile.AtEndOfStream
folderspec = objTextFile.Readline
'Wscript.Echo "File in the Folder:" & folderspec
objMessage.Subject = folderspec
Agecount = 0
Set folder = objFSO.GetFolder(folderspec)
Set fc = folder.Files
For Each f1 in fc
file = f1.name
filespec = folderspec & file
Set file = objFSO.GetFile(filespec)
ShowDateCreated = file.DateCreated
'Wscript.Echo "FileName: " & filespec
'Wscript.Echo "ShowDateCreated:" & ShowDateCreated
difference = DateDiff("n",ShowDateCreated,today)
If difference > 30 Then
Agecount = Agecount + 1
End If
'Wscript.Echo "File Age in minutes is:" & difference
Next
'Wscript.Echo "Total file older than 30 minutes at: " & folderspec & " are:" & Agecount
'mailbody = "Total file older than 30 minutes at: " & folderspec & " are:" & Agecount
'objMessage.TextBody = mailbody
'Wscript.Echo "-----------------------------------------------"
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "indiamailrelay.jda.corp.local"
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
objMessage.Configuration.Fields.Update
If Agecount > 0 Then
mailbody = "Total file older than 30 minutes at: " & folderspec & " are:" & Agecount
objMessage.TextBody = mailbody
objMessage.Send
'Wscript.Echo "Total file older than 30 minutes at: " & folderspec & " are:" & Agecount
End If
Loop
------------------------------------------------------------------------------------
The above code basically, check for a fileage > 30 minutes and send an email if it is true.
It uses a text file as a input to read folder path where files exists
eg:- c:\temp\
Note: Make sure to provide full path as said above, if provided c:\temp this will generate an error.
Friday, September 16, 2011
diskinfo from the Domain using LDAP to query AD for Servers
on error resume next
' Determine DNS domain name from RootDSE object.
Set objRootDSE = GetObject("LDAP://RootDSE")
strDNSDomain = objRootDSE.Get("defaultNamingContext")
'wscript.echo "defaultNamingContext (Domain Name System)" & strDNSDomain
' Use ADO to search Active Directory for all computers.
Set adoCommand = CreateObject("ADODB.Command")
Set adoConnection = CreateObject("ADODB.Connection")
adoConnection.Provider = "ADsDSOObject"
adoConnection.Open "ADs Provider"
adoCommand.ActiveConnection = adoConnection
' Search entire domain.
strBase = ""
' Filter on computer objects with server operating system.
strFilter = "(&(objectCategory=computer)(operatingSystem=*server*))"
' Comma delimited list of attribute values to retrieve.
strAttributes = "cn"
' Construct the LDAP syntax query.
strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"
adoCommand.CommandText = strQuery
'adoCommand.Properties("Page Size") = 100
'adoCommand.Properties("Timeout") = 30
'adoCommand.Properties("Cache Results") = False
Set adoRecordset = adoCommand.Execute
strComputerDN = Array()
'arrSortOut = Array()
' cnsd = Array()
Dim cnsdstring
counter = 0
'step = 0
count = 0
' Enumerate computer objects with server operating systems.
Do Until adoRecordset.EOF
ReDim Preserve strComputerDN(counter)
strComputerDN(counter) = adoRecordset.Fields("cn").value
'Wscript.Echo strComputerDN(counter)
adoRecordset.MoveNext
counter = counter + 1
Loop
'wscript.echo "defaultNamingContext" & strDNSDomain
'wscript.echo counter
'Clean up.
adoRecordset.Close
adoConnection.Close
Wscript.Echo "Disk Info"
for each strComputer in strComputerDN
'wscript.echo strComputer
cnsdstring = ""
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" _
& strComputer & "\root\cimv2")
Set colDisks = objWMIService.ExecQuery _
("Select * from Win32_LogicalDisk where DriveType=3")
For Each objDisk in colDisks
ReDim Preserve cnsd(count)
cnsd(count) = objDisk.DeviceID
cnsdstring = cnsdstring &"," &cnsd(count)
count=count+1
'wscript.echo cnsd(count)
'wscript.echo objDisk.DeviceID
next
wscript.echo "" &strComputer &","&cnsdstring
count = 0
Next
' Determine DNS domain name from RootDSE object.
Set objRootDSE = GetObject("LDAP://RootDSE")
strDNSDomain = objRootDSE.Get("defaultNamingContext")
'wscript.echo "defaultNamingContext (Domain Name System)" & strDNSDomain
' Use ADO to search Active Directory for all computers.
Set adoCommand = CreateObject("ADODB.Command")
Set adoConnection = CreateObject("ADODB.Connection")
adoConnection.Provider = "ADsDSOObject"
adoConnection.Open "ADs Provider"
adoCommand.ActiveConnection = adoConnection
' Search entire domain.
strBase = "
' Filter on computer objects with server operating system.
strFilter = "(&(objectCategory=computer)(operatingSystem=*server*))"
' Comma delimited list of attribute values to retrieve.
strAttributes = "cn"
' Construct the LDAP syntax query.
strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"
adoCommand.CommandText = strQuery
'adoCommand.Properties("Page Size") = 100
'adoCommand.Properties("Timeout") = 30
'adoCommand.Properties("Cache Results") = False
Set adoRecordset = adoCommand.Execute
strComputerDN = Array()
'arrSortOut = Array()
' cnsd = Array()
Dim cnsdstring
counter = 0
'step = 0
count = 0
' Enumerate computer objects with server operating systems.
Do Until adoRecordset.EOF
ReDim Preserve strComputerDN(counter)
strComputerDN(counter) = adoRecordset.Fields("cn").value
'Wscript.Echo strComputerDN(counter)
adoRecordset.MoveNext
counter = counter + 1
Loop
'wscript.echo "defaultNamingContext" & strDNSDomain
'wscript.echo counter
'Clean up.
adoRecordset.Close
adoConnection.Close
Wscript.Echo "Disk Info"
for each strComputer in strComputerDN
'wscript.echo strComputer
cnsdstring = ""
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" _
& strComputer & "\root\cimv2")
Set colDisks = objWMIService.ExecQuery _
("Select * from Win32_LogicalDisk where DriveType=3")
For Each objDisk in colDisks
ReDim Preserve cnsd(count)
cnsd(count) = objDisk.DeviceID
cnsdstring = cnsdstring &"," &cnsd(count)
count=count+1
'wscript.echo cnsd(count)
'wscript.echo objDisk.DeviceID
next
wscript.echo "" &strComputer &","&cnsdstring
count = 0
Next
Tuesday, September 6, 2011
Linux using DBD::Sybase to access MSSQL
Recently, I made yet another attempt to get Perl to access Microsoft SQL Server using DBD. Usually, when I want to connect to a Microsoft SQL Server, it is from Perl on Windows. So I take the easy route and use DBD::ODBC and use an ODBC connection. This time though, I wanted to connect to Microsoft SQL Server 2000 from a Linux box. Having no ODBC to fall back on, I looked for native DBD driver of some sort.
It took me several hours of struggling to make it work. I almost gave up several times, so I am writing outline to help anyone else trying to accomplish this same task.
In the end, we will use the DBD::Sybase perl module from CPAN to access the Microsoft SQL Server. Before we can do that however, we must first compile the freetds library.Note: From now on I will refer to Microsoft SQL Server as SQL Server. Please do not confuse this with a generic sql server. We can all now pause to gripe about the lack of imagination in product naming at Microsoft.
Compiling Freetds
Download and compile freetds from http://www.freetds.org/.once you unzip and untar it, enter the directory and run:
./configure --prefix=/usr/local/freetds --with-tdsver=7.0makemake install
Configuring Freetds
Now we have the freetds compiled, but we still have configure it. This is the part that threw me off and is so different from other DBD drivers. The DBD::Sybase driver will ultimately be affected by the contents of the /usr/local/freetds/etc/freetds.conf file. If that file is not configured correctly, your DBD::Sybase connection will fail.
Okay, now that we have established there is a relationship between the freetds.conf file and the DBD::Sybase module, let's edit the freetds.conf file.
The strategic modifications I made to the freetds.conf file were:
1) uncomment the following lines and modify if necessary:
try server login = yestry domain login = no
Note: this forces the module to attempt a database login instead of a domain login. I could not get domain login to work, though I will admit I did not try very hard.
2) uncomment the following line and modify if necessary:
tds version = 7.0
This supposedly sets the default tds version to establish a connection with. I have only SQL Server 2000 servers, and they won't talk at any lower version. So I set it to 7.0. If for some reason you had older SQL Servers, you might leave it at the default 4.2.
3) create a server entry for my server sql1:
[sql1]
host = sql1
port = 1433
tds version = 8.0
[download]
Note: My server here is sql1. Ping sql1 worked, so I am sure I can resolve it using DNS. You can also specifcy an ip address instead of the host name. The sql1 in the brackets is just a descriptor. It could be 'superduperserver' and it would still work as long as my 'host =' is set correctly. I tried 'tds version 7.0' for my SQL Sever 2000 and it worked. Version 5.0 though resulted in an error. You might want to verify your SQL Server is listening on port 1433 with a 'netstat -a -n' run from the command line on the SQL Server.
At this point you can verify your configuration.
/usr/local/freetds/bin/tsql -S sql1 -U sqluser
You will then be prompted for a password and if everything is well, you will see a '1)' waiting for you to enter a command. If you can't get the 1) using tsql, I doubt your DBD::Sybase perl code is going to work. Please note that sqluser is not an Active Directory/Windows Domain user, but an SQL Server user.
Compiling DBD::Sybase
Now that we have the freetds library prerequisite for DBD::Sybase installed and configured, we can compile the DBD::Sybase perl module. Obtain it from http://www.cpan.org/ if you haven't already.
once you have untarred it and are in the directory, run:
export SYBASE=/usr/local/freetdsperl Makefile.PLmakemake install
Note: The export line is to let the compilation process know where to find the freetds libraries.Using DBD::Sybase
You are now ready to test your DBD::Sybase module.
#!/usr/bin/perluse DBI;$dsn = 'DBI:Sybase:server=sql1';my $dbh = DBI->connect($dsn, "sqluser", 'password');die "unable to connect to server $DBI::errstr" unless $dbh;$dbh->do("use mydatabase");$query = "SELECT * FROM MYTABLE";$sth = $dbh->prepare ($query) or die "prepare failed\n";$sth->execute( ) or die "unable to execute query $query error $DBI::errstr";$rows = $sth->rows ;print "$row rows returned by query\n";while ( @first = $sth->fetchrow_array ) { foreach $field (@first) { print "field: $field\n"; }}
Content extracted from: http://www.perlmonks.org/?node_id=392385
It took me several hours of struggling to make it work. I almost gave up several times, so I am writing outline to help anyone else trying to accomplish this same task.
In the end, we will use the DBD::Sybase perl module from CPAN to access the Microsoft SQL Server. Before we can do that however, we must first compile the freetds library.Note: From now on I will refer to Microsoft SQL Server as SQL Server. Please do not confuse this with a generic sql server. We can all now pause to gripe about the lack of imagination in product naming at Microsoft.
Compiling Freetds
Download and compile freetds from http://www.freetds.org/.once you unzip and untar it, enter the directory and run:
./configure --prefix=/usr/local/freetds --with-tdsver=7.0makemake install
Configuring Freetds
Now we have the freetds compiled, but we still have configure it. This is the part that threw me off and is so different from other DBD drivers. The DBD::Sybase driver will ultimately be affected by the contents of the /usr/local/freetds/etc/freetds.conf file. If that file is not configured correctly, your DBD::Sybase connection will fail.
Okay, now that we have established there is a relationship between the freetds.conf file and the DBD::Sybase module, let's edit the freetds.conf file.
The strategic modifications I made to the freetds.conf file were:
1) uncomment the following lines and modify if necessary:
try server login = yestry domain login = no
Note: this forces the module to attempt a database login instead of a domain login. I could not get domain login to work, though I will admit I did not try very hard.
2) uncomment the following line and modify if necessary:
tds version = 7.0
This supposedly sets the default tds version to establish a connection with. I have only SQL Server 2000 servers, and they won't talk at any lower version. So I set it to 7.0. If for some reason you had older SQL Servers, you might leave it at the default 4.2.
3) create a server entry for my server sql1:
[sql1]
host = sql1
port = 1433
tds version = 8.0
[download]
Note: My server here is sql1. Ping sql1 worked, so I am sure I can resolve it using DNS. You can also specifcy an ip address instead of the host name. The sql1 in the brackets is just a descriptor. It could be 'superduperserver' and it would still work as long as my 'host =' is set correctly. I tried 'tds version 7.0' for my SQL Sever 2000 and it worked. Version 5.0 though resulted in an error. You might want to verify your SQL Server is listening on port 1433 with a 'netstat -a -n' run from the command line on the SQL Server.
At this point you can verify your configuration.
/usr/local/freetds/bin/tsql -S sql1 -U sqluser
You will then be prompted for a password and if everything is well, you will see a '1)' waiting for you to enter a command. If you can't get the 1) using tsql, I doubt your DBD::Sybase perl code is going to work. Please note that sqluser is not an Active Directory/Windows Domain user, but an SQL Server user.
Compiling DBD::Sybase
Now that we have the freetds library prerequisite for DBD::Sybase installed and configured, we can compile the DBD::Sybase perl module. Obtain it from http://www.cpan.org/ if you haven't already.
once you have untarred it and are in the directory, run:
export SYBASE=/usr/local/freetdsperl Makefile.PLmakemake install
Note: The export line is to let the compilation process know where to find the freetds libraries.Using DBD::Sybase
You are now ready to test your DBD::Sybase module.
#!/usr/bin/perluse DBI;$dsn = 'DBI:Sybase:server=sql1';my $dbh = DBI->connect($dsn, "sqluser", 'password');die "unable to connect to server $DBI::errstr" unless $dbh;$dbh->do("use mydatabase");$query = "SELECT * FROM MYTABLE";$sth = $dbh->prepare ($query) or die "prepare failed\n";$sth->execute( ) or die "unable to execute query $query error $DBI::errstr";$rows = $sth->rows ;print "$row rows returned by query\n";while ( @first = $sth->fetchrow_array ) { foreach $field (@first) { print "field: $field\n"; }}
Content extracted from: http://www.perlmonks.org/?node_id=392385
Subscribe to:
Posts (Atom)