Connecting to Database using VB Script

Query = "Select count(*) as ab from item"



ConnectionString = "DRIVER={SQL SERVER};SERVER=servername;Database=dbname;User Id=user123;Password=pass123;"



Set DBConnection = CreateObject("ADODB.Connection")

Set DBRows = CreateObject("ADODB.Recordset")





'* Open connection

DBConnection.Open connectionString



if err.number <> 0 then

msgbox "Connection Failed"& vbcrlf & err.description

end if





'* Execute SQL

Set DBRows = DBconnection.Execute(Query)



if err.number<> 0 then

msgbox "Querying Failed"& vbcrlf & err.description

end if



msgbox DBRows("ab")



'While Not DBRows.EOF



'Msgbox DBRows.Fields.Value



'DBrows.MoveNext

'Wend



DBconnection.Close

Set DBRows = Nothing

Set DBconnection = Nothing

Get the Drivers List in a PC - VBS

strLog = Replace(Wscript.ScriptFullname,Wscript.Scriptname,"")&"GetDrivers.log"



set FSO = CreateObject("Scripting.Filesystemobject")



set Logs = FSO.createTextFile(strLog)



Const HKEY_LOCAL_MACHINE = &H80000002



strComputer = "."



Set objRegistry = GetObject("winmgmts:\\" & strComputer & "\root\default:StdRegProv")



strKeyPath = "SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers"

objRegistry.EnumValues HKEY_LOCAL_MACHINE, strKeyPath, arrValueNames, arrValueTypes



For i = 0 to UBound(arrValueNames)

strValueName = arrValueNames(i)

objRegistry.GetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue

Logs.writeline arrValueNames(i) & " -- " & strValue

Next



Logs.close

set Logs = Nothing

set FSO = Nothing

set objRegistry = Nothing

Monitor the services of a remote server - VBS

'************************************************************************************************


'File Name : Services_Checker.vbs

'Description : Connects to a Server and checks if the XXX services are running

'Author : Arumugam
'Date : 09/07/2008

'Version : 1.0

'************************************************************************************************

on error resume next

wscript.echo

wscript.echo vbcrlf & " Services Checker"

wscript.echo "***********************"



logFile=replace(wscript.scriptfullname,wscript.scriptname,"")&"\Services.log"



set FSO = createobject("scripting.filesystemobject")

set objLog = FSO.createtextfile(logfile)



'Get the User ID

Wscript.StdOut.Write "Please enter your user name ( eg: user123):"

strUser = Wscript.StdIn.ReadLine

strUser = "DOMAIN\"&strUser



'Get the Password

Set objPassword = CreateObject("ScriptPW.Password")

Wscript.StdOut.Write "Please enter your password(corp domain) :"

strPassword = objPassword.GetPassword()

Wscript.Echo



if len(struser)<>12 or len(strPassword)<>8 then

msgbox"Check your Login Credentials"

wscript.quit

end if



objLog.writeline "################################################################################"

objLog.writeline "EXECUTION STARTED AT : " & now & " BY : " & strUser & vbcrlf



wscript.echo "Checking  Services on the Servers.Please wait..."&vbclrf



MonitorGAPBEE ("servername")


objLog.writeline "EXECUTION COMPLETED AT : " & now

objLog.writeline "################################################################################"

objLog.close

set objLog = Nothing

set FSO = Nothing



Public Function MonitorGAPBEE(strServer)



on error resume next



objLog.writeline "SERVER NAME:"& strServer

objLog.writeline "========================="



'Server to be checked

strComputer =strServer



'Connect to WMI of the Remote server

Set objSWbemLocator = CreateObject("WbemScripting.SWbemLocator")

Set objSWbemServices = objSWbemLocator.ConnectServer(strComputer,"root\cimv2",strUser,strPassword)



'Check if connection is successful

if err.number <> 0 then

objLog.writeline "Not connected"

objLog.writeline "********************************************"

objLog.writeline "Error occurred" &vbcrlf& err.description

objLog.writeline "********************************************"

objLog.writeline "SCRIPT IS ENDING NOW"

objLog.writeline "################################################################################"

wscript.echo "Error occurred" &vbcrlf& err.description

wscript.quit

else

objLog.writeline "connected"

end if



wscript.echo "*****************************************************"

wscript.echo strComputer & " Connected"

wscript.echo "============="



'Query the WMI for services

Set colDisks = objSWbemServices.ExecQuery("Select * from Win32_Service")



if err.number <> 0 then

objLog.writeline "WMI Query Failed" & vbcrlf& err.description&vbcrlf

else

objLog.writeline "WMI Query - Success"&vbcrlf

end if





'Set the Flag

Service_Flag = TRUE





objLog.writeline "Checking for abc services"

objLog.writeline "========================="&vbcrlf





'Check each of the services

For Each objDisk in colDisks



Service_Name = objDisk.displayname

Service_Status = objDisk.state





'Check the status of abc services

if instr(Service_Name,"abc") <> 0 and Service_Status<>"Running"then



strService = strService&vbcrlf&Service_Name

Service_Flag = FALSE



do until len(Service_Name) = 45

Service_Name = Service_Name&" "



loop



wscript.echo Service_Name & vbtab & Service_Status

objLog.writeline Service_Name & vbtab & Service_Status



elseif instr(Service_Name,"ULZ") <> 0 and Service_Status="Running"then



do until len(Service_Name) = 45

Service_Name = Service_Name&" "



loop

wscript.echo Service_Name & vbtab & Service_Status

objLog.writeline Service_Name & vbtab & Service_Status



end if





Next



'Check the Flag and display the msg



if Service_Flag = TRUE then



objLog.writeline "All the ULZ services are running fine"



else

WSCRIPT.ECHO

WSCRIPT.ECHO "!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!"

wscript.echo "Please check the following services"& strService

WSCRIPT.ECHO "!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!"

WSCRIPT.ECHO



objLog.writeline "Following abc services are not running"

objLog.writeline "======================================"&strService



end if







'Destruct the used objects

Set colDisks=nothing

Set objSWbemServices = nothing

Set objSWbemLocator = nothing



objLog.writeline "**************************************************************"



end function



wscript.echo "Completed. Press any key to exit"

strUser = Wscript.StdIn.ReadLine

Checking the Local Groups& Users - VBS

set objShell = createobject("wscript.shell")

set objNetwork = createobject("Wscript.network")



ComputerName = objNetwork.computername



set objNetwork = Nothing

set objShell = Nothing



strOutputFile = replace(wscript.scriptfullname,wscript.scriptname,"")&"Logs.txt"





strComputer = "."



'create output file

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objTextFile = objFSO.CreateTextFile(strOutputFile)



objTextFile.writeline "##########################################################"&vbcrlf

objTextFile.writeline "COMPUTER NAME :"& ComputerName&vbcrlf&"Local Group Details "&vbcrlf

objTextFile.writeline "##########################################################"



objTextFile.writeline

'objTextFile.writeline "Group_Name" & vbtab& vbtab & "Configured Users"



Set colGroups = GetObject("WinNT://" & strComputer & "")





if err.number <> 0 then

wscript.echo "Connection Failed"&vbcrlf&err.description

wscript.quit

end if

colGroups.Filter = Array("group")

big=0

For Each objGroup In colGroups



objTextFile.WriteLine "*****************************************************"

objTextFile.WriteLine

Users_Flag = FALSE



do until i=17

tmp=tmp&" "

i=i+1



loop



User_Count=0

For Each objUser in objGroup.Members

GroupUsers = GroupUsers&vbcrlf&tmp&objUser.Name

User_Count = User_Count + 1

Users_Flag = TRUE



Next



GroupName = objGroup.Name



do until len(GroupName) = 65

GroupName=GroupName&" "

Loop







if Users_Flag = TRUE then

objTextFile.WriteLine "GROUP NAME :"& GroupName &vbcrlf&vbcrlf &_

"TOTAL USERS :" & User_Count & vbcrlf&vbcrlf &"USERS CONFIGURED:" & GroupUsers

else



objTextFile.WriteLine "GROUP NAME :"& GroupName &vbcrlf&vbcrlf &_

"TOTAL USERS :"& User_Count &vbcrlf&vbcrlf &"USERS CONFIGURED:" &"NONE"

end if



objTextFile.WriteLine





Next



objTextFile.WriteLine "*****************************************************"



objTextFile.Close

msgbox "done"

send mail using vbs

'************************************************************************************************

'Function Name : SendMail(strFile)

'Purpose : To send a mail with an attachment which is passed as input value to this fn

'Input : File to be attached in the mail

'************************************************************************************************



Public Function SendMail(strFile)



On error Resume Next



'Get the Email ID from the user

strEmailID = InputBox ("Please enter your Email ID:")



'Confirm if the Email id is correct

strOption = msgbox ("Your ID :"&strEmailID,3,"Confirm Mail ID")



'Send mail If it is a correct email ID

if strOption = 6 then



Set objMessage = CreateObject("CDO.Message")



objMessage.Subject = "Report"

objMessage.TextBody = "Attached the Report generated on " & date





'Sender & receiver Email IDs

'===========================

objMessage.From = strEmailID

objMessage.To = strEmailID



'Set the Mail configurations

objMessage.Configuration.Fields.Item _

("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

objMessage.Configuration.Fields.Item _

("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "mail.example.com"
objMessage.Configuration.Fields.Item _

("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1



' Note : Change XXXXXX with your userid
'======================================



objMessage.Configuration.Fields.Item _

("http://schemas.microsoft.com/cdo/configuration/sendusername") = strUser



'Note: Give the corp password - used for SMTP server authentication

'===================================================================

objMessage.Configuration.Fields.Item _

("http://schemas.microsoft.com/cdo/configuration/sendpassword") = strPassword

objMessage.Configuration.Fields.Item _

("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25

objMessage.Configuration.Fields.Item _

("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = False



objMessage.Configuration.Fields.Item _

("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60

objMessage.Configuration.Fields.Update

objMessage.Send



if err.number = 0 then

Msgbox "Pulls report is successfully Sent"

else

msgbox "Report is Failed" & vbcrlf& err.description

end if

else

SendMail

end if



End Function



'################## END OF THE SCRIPT ####################################################################################

About Me

Hi,

Thanks for visiting my page. I feel happy to let you know about myself who is called by the name "Arumugam" @ "Aru"(Call out Name) , a software professional living in Chennai - tamilnadu-India.

I was born in KovilPatti near tuticorin district and i am amazed to see this world since 26 years. I was grown up (am not tallest anyway) in coimbatore where i was groomed,matured and learnt to live with any challenges. Graduated from GCE Tirunelveli from Majestic Mechanical Engg and worked for a compressor Manufacturing company at coimbatore for an year. Since then, i changed the gear to turn up in IT Sector and got relocated to Infosys as Software Engineer.
Have been working at infy for 3.5 years (till date - Nov 4 2009) and looking out for sharing information and knowledge with others which brought me up here to write up this.

Eagerly looking forward to have more buddies to spread up my wings on this blogspot there by making this globe better place to live in.

Happy blogging..

Regards,
Aru