How to reboot a remote computer

strComputer = "<computername>"
Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate,(Shutdown)}!\\" & _       strComputer & "\root\cimv2")

Set colOperatingSystems = objWMIService.ExecQuery _("Select * from Win32_OperatingSystem")

For Each objOperatingSystem in colOperatingSystems
    objOperatingSystem.Reboot()
Next

How to copy file using Vbscript

 Const OverwriteExisting = TRUE

Set objFSO = CreateObject("Scripting.FileSystemObject")
objFSO.CopyFile "C:\Source\Filename.txt" , "D:\destination\", OverwriteExisting

How to set user password in Active Direcotyr(AD)

Set objUser = GetObject("LDAP://cn=cn,ou=ou,dc=dc,dc=com")
objUser.SetPassword "password"

How to find column name in all objects in a specific Database

SELECT * FROM INFORMATION_SCHEMA.[COLUMNS] c
WHERE c.COLUMN_NAME ='@COLUMN-NAME'

See who is connected to SQL Database

SELECT SPID,STATUS,PROGRAM_NAME,LOGINAME=RTRIM(LOGINAME),
HOSTNAME,CMD
FROM  MASTER.DBO.SYSPROCESSES
WHERE DB_NAME(DBID) = '<DATABASE NAME>' 
AND DBID != 0

How to get Local Machine Groups using WMI in VB.NET

Dim search As New ManagementObjectSearcher("SELECT * FROM Win32_Group Where LocalAccount = True")
For Each info In search.Get()
 Dim objGroup = GetObject("WinNT://./" & info("Name").ToString &  ",group")
  For Each objMember In objGroup.Members
     Dim info As ManagementObject
     Msgbox(info("Name").ToString)
     Msgbox(objMember.Name) 
  Next 
Next

How to Get List of Softwares installed on a machine

Dim search As New ManagementObjectSearcher("SELECT * FROM Win32Reg_AddRemovePrograms")
Dim info As ManagementObject 
For Each info In search.Get()  
  Msgbox(info("InstallDate"))
  Msgbox(info("DisplayName").ToString)  
  Msgbox(info("Publisher").ToString)
Next

How to start Windows Service on Remote Computer using VB.NET

Dim co As ConnectionOptions = New ConnectionOptions()
'CREDENTIALS
With co
 .Username = "username" 
 .Password = "password"
End With
Dim strComputer As String = "computername"
Dim scope As New System.Management.ManagementScope
scope=New System.Management.ManagementScope("\\" & strComputer & "\root\cimv2", co)
'CONNECT TO THE BOX
scope.Connect()
Dim query As ObjectQuery = New ObjectQuery("Select * from Win32_Service Where Name ='SERVICENAME' ")
Dim searcher As ManagementObjectSearcher = New ManagementObjectSearcher(scope, query)
Dim observer As ManagementOperationObserver = New ManagementOperationObserver()
Dim queryCollection As ManagementObjectCollection=searcher.Get()Dim m As ManagementObject
Dim args As Object
'START THE SERVICE

For Each m In queryCollection
 m.InvokeMethod("StartService", args)
Next

WMI Script to get BIOS details

strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\CIMV2")
Set colItems = objWMIService.ExecQuery( _
"SELECT * FROM Win32_BIOS",,48)
For Each objItem in colItems
  Msgbox "Win32_BIOS instance"
   If isNull(objItem.BIOSVersion) Then
     Msgbox "BIOSVersion: "
   Else
     Msgbox "BIOSVersion: " & Join(objItem.BIOSVersion, ",")
   End If
  Msgbox "Caption: " & objItem.Caption
  Msgbox "Description: " & objItem.Description
  Msgbox "InstallDate: " & objItem.InstallDate
  Msgbox "Manufacturer: " & objItem.Manufacturer
  Msgbox "Name: " & objItem.Name
Next

How to display Enabled Network Adapter and their IP Address in .NET using WMI

Imports System.Management

Dim search As New ManagementObjectSearcher("SELECT * FROM Win32_NetworkAdapterConfiguration Where IPEnabled=True")
Dim info As ManagementObject
Dim strIPAddress As String = Nothing
For Each info In search.Get     
If Not (info("IPAddress")) Is Nothing Then
       Dim i As Integer
       With GrdView
         .Rows.Add()
         i = GrdView.Rows.Count - 1
         Rows(i).Cells(1).Value = info("Description").ToString
       End With


'GET THE IP ADDRESS FOR SPECIFIC NETWORK ADAPTER
Dim sql As String = "SELECT * FROM Win32_NetworkAdapterConfiguration Where IPEnabled=True
And Description='" & info("Description").ToString & "'"
  Dim searchIP As New ManagementObjectSearcher(sql)
  Dim infoIP As ManagementObject
  For Each infoIP In searchIP.Get
    strIPAddress = Join(infoIP("IPAddress"), ",")
    strIPAddress = Mid(strIPAddress, 1, InStr(strIPAddress, ",") - 1)
    GrdView.Rows(i).Cells(2).Value = strIPAddress
  Next
End If
Next

WMI Script to get details about local Computer System

Below script will give you the computer system details of a remote computer

strComputer = "FullComputerName"
strDomain = "DOMAIN"
strUser = "username"
strPassword = "password"
Set objSWbemLocator = CreateObject("WbemScripting.SWbemLocator")
Set objWMIService = objSWbemLocator.ConnectServer(strComputer, _
    "root\CIMV2", _
    strUser, _
    strPassword, _
    "MS_409", _
    "ntlmdomain:" + strDomain)
Set colItems = objWMIService.ExecQuery( _
    "SELECT * FROM Win32_ComputerSystem",,48)
For Each objItem in colItems
    Wscript.Echo "Win32_ComputerSystem instance"
    Wscript.Echo "Caption: " & objItem.Caption
    Wscript.Echo "Description: " & objItem.Description
    Wscript.Echo "Domain: " & objItem.Domain
    Wscript.Echo "InstallDate: " & objItem.InstallDate
    Wscript.Echo "Manufacturer: " & objItem.Manufacturer
    Wscript.Echo "Model: " & objItem.Model
Next

WMI Script to get details about local Computer System

Below script will give you details about local computer system using WMI.

strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\CIMV2")
Set colItems = objWMIService.ExecQuery( _
    "SELECT * FROM Win32_ComputerSystem",,48)
For Each objItem in colItems
    Wscript.Echo "Win32_ComputerSystem instance"
    Wscript.Echo "Caption: " & objItem.Caption
    Wscript.Echo "Description: " & objItem.Description
    Wscript.Echo "Domain: " & objItem.Domain
    Wscript.Echo "Manufacturer: " & objItem.Manufacturer
    Wscript.Echo "Model: " & objItem.Model
Next

.NET function to get user groups from Active Directory

Below .NET function is used to get the user groups from Active Directory.

To use the below function in your project, you need to add the reference for System.DirectoryServices


Public Function GetUserGroupMembership(ByVal strUser As String) As StringCollection  Dim groups As New StringCollection()
Try
  Dim obEntry As New DirectoryServices.DirectoryEntry("LDAP://domain/DC=domain,DC=com")
  Dim srch As New DirectoryServices.DirectorySearcher(obEntry, "(sAMAccountName=" & strUser & ")")
  Dim res As DirectoryServices.SearchResult = srch.FindOne()
      If res IsNot Nothing Then
        Dim obUser As New DirectoryServices.DirectoryEntry(res.Path)
        ' Invoke Groups method.
        Dim obGroups As Object = obUser.Invoke("Groups")
        For Each ob As Object In DirectCast(obGroups, IEnumerable)
        ' Create object for each group.
        Dim obGpEntry As New DirectoryServices.DirectoryEntry(ob)
                    groups.Add(obGpEntry.Name)
        Next
      End If
Catch ex As Exception            'Trace.Write(ex.Message)
End Try
        Return groups
End Function


Above function can be called as :
Dim Groups As StringCollection = GetUserGroupMembership(username.text)

.NET function to get user information from Active Directory

Below script is a .NET function to get information about a user from AD. This information can be stored in a variable and used in your project/application.

To use the below function in your project, you need to add the reference for System.DirectoryServices


Public Function GetUserInfo(ByVal inAD As String, ByVal inType As String) As String
Try
    Dim sPath As String= "LDAP://domain/DC=domain,DC=com"
    Dim SamAccount As String = Right(inAD, Len(inAD) - InStr(inAD, "\"))
    Dim myDirectory As New DirectoryServices.DirectoryEntry(sPath, "username", "Password")
    Dim mySearcher As New DirectoryServices.DirectorySearcher(myDirectory)
    Dim mySearchResultColl As DirectoryServices.SearchResultCollection
    Dim mySearchResult As DirectoryServices.SearchResult
    Dim myResultPropColl As DirectoryServices.ResultPropertyCollection
    Dim myResultPropValueColl As DirectoryServices.ResultPropertyValueCollection
    'Build LDAP query
    mySearcher.Filter = ("(&(objectClass=user)(samaccountname=" & SamAccount & "))")
    mySearchResultColl = mySearcher.FindAll()
    'only one user from search result
            Select Case mySearchResultColl.Count
                Case 0
                    Return "0"
                    Exit Function
                Case Is > 1
                    Return "1"
                    Exit Function
            End Select
            'Search result from the collection
            mySearchResult = mySearchResultColl.Item(0)
           
     'Properites, they contain the usefull info
            myResultPropColl = mySearchResult.Properties
            myResultPropValueColl = myResultPropColl.Item(inType)
            Return CStr(myResultPropValueColl.Item(0))

Catch ex As System.Exception           
    Return 0
End Try
End Function

Above function can be called as below:
Variablename = GetUserInfo(usernametexbox.text, "displayname")
where displayname is the property name in Active Directory

How to PING a specific website and send email, if site is down

This little Vbscript allows you to ping a site and send email, if the site is down

Dim strWebsite
strWebsite = "sitename.com"
If PingSite( strWebsite ) Then
    
Else
     Set myMail=CreateObject("CDO.Message")
            myMail.Subject="SITE is Down"
            myMail.From = fromemailaddress                
            myMail.To = toemailaddress
            myMail.HTMLBody= strWebsite
            myMail.Send
            set myMail=nothing
End If

Function PingSite( myWebsite )    Dim siteStatus, objHTTP
    Set objHTTP = CreateObject( "WinHttp.WinHttpRequest.5.1" )
    objHTTP.Open "GET", "http://" & myWebsite & "/", False
    objHTTP.SetRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MyApp 1.0; Windows NT 5.1)"
    On Error Resume Next
    objHTTP.Send
    siteStatus = objHTTP.Status
    On Error Goto 0
    If siteStatus = 200 Then
        PingSite = True
    Else
        PingSite = False
    End If
    Set objHTTP = Nothing
End Function

Update Computer Description in Active Directory

Below VB script allows you to update the Computer description from CSV file in Active directiory


set Input_Connection = createobject("adodb.connection")
set Input_RecordSet = createobject("adodb.recordset")
Comp_Location ="C:\AD\"
    'CONNECT TO CSV file
    Input_Connection.open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & _
    Comp_Location & ";Extended Properties=""text;HDR=YES;FMT=Delimited"""
    Input_RecordSet.open "SELECT * FROM Computers.csv",Input_Connection
strDomain= DOMAIN_NAME
Set objRootLDAP = GetObject("LDAP://RootDSE")
strDNSDomain = objRootLDAP.Get("DefaultNamingContext")
strOU = OU PATH
set objRootDSE = GetObject("LDAP://" & strDomain & "/RootDSE")
set objContainer = GetObject("LDAP://cn=Computers," & objRootDSE.Get("defaultNamingContext"))
set oLogObject = CreateObject("Scripting.FileSystemObject")
set oLogOutput = oLogObject.CreateTextFile("C:\UsersComputer.log")
oLogOutput.WriteLine Now & ": Log started"
On Error Resume Next
do until Input_RecordSet.EOF
    oLogOutput.WriteLine "A:" & strComputer & strOU & strDNSDomain
    strDescr =Input_RecordSet.Fields.Item(1).Value
    strComputer = "CN=" & Input_RecordSet.Fields.Item(0).Value & ","
    set objComputer = GetObject("LDAP://"& strComputer & strOU & strDNSDomain)
    objComputer.Put "description", strDescr
    objComputer.SetInfo
Input_RecordSet.movenext
Loop   

Get Free Space for particular drive and send email

Below script gives you the free space for D drive. You can run this on any machine using SSMS.
The threshold of 300 can be changed as per your requirement

Use tempdb
Declare
@Free_space  as varchar(max),
@Body as varchar(255)
If OBJECT_ID('#Fixeddrives') is not null
BEGIN
 Drop Table #Fixeddrives
 Create Table #Fixeddrives
  (
   drives varchar(2),
   FreeSpace bigint
  )

 INSERT INTO #Fixeddrives
 Exec sys.xp_fixeddrives
 Select @Free_space =  FreeSpace/1024
 From #Fixeddrives
 Where drives='D' and (FreeSpace)/1024< = 300
 Drop Table #Fixeddrives
END
if @Free_space < 300
 BEGIN
  Select @Body = 'SERVER NAME - Total Free space on D drive is: ' + @Free_space + ' GB'
  EXEC msdb.dbo.sp_send_dbmail
  @profile_name = 'PROFILE NAME',
  @recipients ='TO EMAIL ADDRESS',
  @body = @Body,
  @subject = 'SERVER NAME - Low disk space',
  @Importance = 'HIGH'
 END

Monitor File size for a specific file and send email

This VBScrpit allows you to monitor the file size of a specific file and send email.

const bytesToKb = 1024
strFile = "C:\ABC.mdf"
set objFSO = createobject("Scripting.FileSystemObject")
set objFile = objFSO.GetFile(strFile)
strFileSize =  cint(objFile.Size / bytesToKb/ bytesToKb) & "mb"
strMsg = "Total Filesize for " & strFile & " is : " & strFileSize
'SEND EMAIL
Set myMail=CreateObject("CDO.Message")
myMail.Subject = "File Size"
myMail.From = "FROM EMAIL ADDRESS"
myMail.To = "TO EMAIL ADDRESS"
myMail.HTMLBody = strMsg
myMail.Configuration.Fields.Item _            ("http://schemas.microsoft.com/cdo/configuration/sendusing")=2
myMail.Configuration.Fields.Item _            ("http://schemas.microsoft.com/cdo/configuration/smtpserver") _
= "SMTP SERVER"
myMail.Configuration.Fields.Item _            ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") _=25
myMail.Configuration.Fields.Update
myMail.Send
set myMail=nothing

Get Users from a specific Group in Active Directory

This script prompts for Group name and export the details into Excel

Option Explicit
Dim objExcel : Set objExcel = CreateObject("Excel.Application")
With objExcel
    .Visible = True
    .DisplayAlerts = False
    Dim objWorkBook : Set objWorkBook = .WorkBooks.Add
    Dim objSheet : Set objSheet = .Worksheets(1)
End With
Create_Excel_File()
Dim intRow : intRow = 2
Dim strGroup : strGroup = InputBox("Enter the Group Name")
On Error Resume Next
    Dim objGroup : Set objGroup = GetObject("LDAP://" & GetDN(strGroup))
if err.number <> 0 then
    Msgbox err.number & " : Error Occured during process." & vbcrlf & "The Group " & strGroup & " does not exist."
    err.Clear
End If
Enum_Members(objGroup)
Format_Excel_File()

Sub Create_Excel_File()
    With objSheet
        '.Name = "Data"
        .Cells(1, 1).Value = "UserName"
        .Cells(1, 2).Value = "First Name"
        .Cells(1, 3).Value = "Last Name"
        .Cells(1, 4).Value = "Title"
        .Cells(1, 5).Value = "Work Phone"
        .Cells(1, 6).Value = "Mobile Phone"
        .Cells(1, 7).Value = "Pager"
        .Cells(1, 8).Value = "Home Phone"
 .Cells(1, 9).Value = "Dept"
 .Cells(1, 10).Value = "City"
 .Cells(1, 11).Value = "Country"
 .Cells(1, 12).Value = "Date Created"
    End With
    Dim objRange : Set objRange = objSheet.UsedRange
    objRange.Select
    With objRange
        .Font.Bold = True
        .Font.Name = "Arial"
        .Font.Size = 10
        .WrapText = False
        .HorizontalAlignment = -4108
        .Interior.ColorIndex = 37
        .Cells.RowHeight = 25       
    End With
End Sub
Sub Format_Excel_File()
    Const xlsEdgeBottom = 9
    Const xlsEdgeLeft = 7
    Const xlsEdgeRight = 10
    Const xlsEdgeTop = 8
    Const xlsInsideHorizontal = 12
    Const xlsInsideVertical = 11
    Const xlsContinuous = 1
    Const xlsAutomatic = -4105
    Const xlsMedium = -4138
    Dim objRange : Set objRange = objSheet.UsedRange
    objRange.Select
   
    objRange.Columns.AutoFit
    Dim arrBorders : arrBorders = Array(xlsEdgeLeft, xlsEdgeTop, xlsEdgeBottom, xlsEdgeRight, xlsInsideVertical, xlsInsideHorizontal)
    Dim intBorder
    For Each intBorder in arrBorders
        With objRange.Borders(intBorder)
            .LineStyle = xlsContinuous
            .Weight = xlsMedium
            .ColorIndex = xlsAutomatic
        End With
    Next
End Sub

Sub Enum_Members(group)
    Dim arrAttributes : arrAttributes = Array("samaccountName", "GivenName", "sn", "Title", "telephonenumber", "Mobile", "Pager", "homePhone", "department", "l", "co", "whencreated")
    Dim objItem
    For Each objItem in group.Members
        If objItem.Class = "user" Then
            Dim intColumn : intColumn = 1
            Dim objUser : Set objUser = GetObject(objItem.AdsPath)
            Dim strAttrib
            For Each strAttrib in arrAttributes
                On Error Resume Next
                objSheet.Cells(intRow, intColumn).Value = objItem.Get(strAttrib)
                On Error GoTo 0
                intColumn = intColumn + 1
            Next
        End If           
        intRow = intRow + 1
    Next
    For Each objItem in group.Members
        If objItem.Class = "group" Then
            Call Enum_Members(objItem)
        End If
    Next
End Sub
Function GetDN(samAccount)
On Error Resume Next
      If Not IsObject(objWSHNetwork) Then
        Dim objWSHNetwork : Set objWSHNetwork = WScript.CreateObject("WScript.Network")
      End If
      Dim NT : Set NT= CreateObject("NameTranslate")
      NT.Init 3, ""
      NT.Set 3, objWSHNetwork.UserDomain & "\" & samAccount
      GetDN = NT.Get(1)
if err.number <> 0 then
    Msgbox err.number & " : Error Occured during process." & vbcrlf & "The Group " & strGroup & " does not exist."
    err.Clear
End If
End Function

Get groups of a specific users from Active Directory

This scrpit will prompt to enter Domain and User name and will export the User groups from Active Directory in Excel File

Option Explicit
Dim objExcel : Set objExcel = CreateObject("Excel.Application")
With objExcel
    .Visible = True
    .DisplayAlerts = False
    Dim objWorkBook : Set objWorkBook = .WorkBooks.Add
    Dim objSheet : Set objSheet = .Worksheets(1)
End With
Dim objNetwork, strDomain, strUser, objUser, objGroup, strGroupMemberships
'Get the domain and username from the WScript.Network object
Set objNetwork = CreateObject("WScript.Network")
strDomain = InputBox("Enter the Domain Name")
strUser = InputBox("Enter the User Name")
if strUSer <> "" then
'Instanciate the user object from the data above
Set objUser = GetObject("WinNT://" & strDomain & "/" & strUser)
'Run through the users groups and put them in the string
Create_Excel_Header(strUser)
Dim intRow : intRow = 2
Dim intColumn : intColumn = 1
For Each objGroup In objUser.Groups
    objSheet.Cells(intRow, 1 ).Value = objGroup.Name
    intRow = intRow  + 1
Next
else
 MSgbox "Error Processing your request",vbCritical,"Preetech Software Solutions"
End If
Format_Excel_File()
Sub Create_Excel_Header(strUser)
    With objSheet
        .Cells(1, 1).Value = "Group(s) of User:" & strUser
    End With
    Dim objRange : Set objRange = objSheet.UsedRange
    objRange.Select
    With objRange
        .Font.Bold = True
        .Font.Name = "Arial"
        .Font.Size = 10
        .WrapText = False
        .HorizontalAlignment = -4108
        .Interior.ColorIndex = 37
        .Cells.RowHeight = 25       
    End With
End Sub
Sub Format_Excel_File()
    Const xlsEdgeBottom = 9
    Const xlsEdgeLeft = 7
    Const xlsEdgeRight = 10
    Const xlsEdgeTop = 8
    Const xlsInsideHorizontal = 12
    Const xlsInsideVertical = 11
    Const xlsContinuous = 1
    Const xlsAutomatic = -4105
    Const xlsMedium = -4138
   
    Dim objRange : Set objRange = objSheet.UsedRange
    objRange.Select
   
    objRange.Columns.AutoFit
   
    Dim arrBorders : arrBorders = Array(xlsEdgeLeft, xlsEdgeTop, xlsEdgeBottom, xlsEdgeRight, xlsInsideVertical, xlsInsideHorizontal)
    Dim intBorder
   
    For Each intBorder in arrBorders
        With objRange.Borders(intBorder)
            .LineStyle = xlsContinuous
            .Weight = xlsMedium
            .ColorIndex = xlsAutomatic
        End With
    Next
End Sub

WMI Script to get data from Eventviewer

The below script will give you the details from EventViewer for sourcetype "Application" where there is an Error


strComputer = "."

Set objSWbemLocator = CreateObject("WbemScripting.SWbemLocator")

Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\CIMV2")

Set colItems = objWMIService.ExecQuery( _
    "SELECT * FROM Win32_NTLogEvent Where Logfile ='Application' And Type= 'Error' ",,48)

For Each objItem in colItems
     Msgbox (objItem.TimeGenerated)
     Msgbox (objItem.Message)
     Msgbox(objItem.SourceName)
     Msgbox(objItem.Type)
     Msgbox(objItem.User)
     Msgbox(objItem.Category)
     MsgboxobjItem.ComputerName)
Next

Get Current SQL Job running on a server

Run the below script in your SQL Server:


msdb.dbo.sp_get_composite_job_info NULL, NULL, NULL, NULL, NULL, NULL, 1, NULL, NULL

Get the information about the restored database(s)

Below script gives you the details (DB Name, Restored Name, Restore Data, Back up Start Date, Back up Finish date and Username) of all the restored databases. To get the details for specific database, just add the "where" clause with bckup.database_name=''

SELECT bckup.database_name AS 'DB Name' ,Restored_To_DBName AS 'Restored Name' ,Last_Date_Restored AS 'Restored Date' ,bckup.backup_start_date AS 'Backup Start Date' ,bckup.backup_finish_date AS 'Backup Finish Date' ,bckup.user_name AS 'User Name' FROM msdb..backupset bckup INNER JOIN (SELECT backup_set_id ,Restored_To_DBName ,Last_Date_Restored FROM msdb..restorehistory INNER JOIN ( SELECT rh.destination_database_name Restored_To_DBName , MAX(rh.restore_date) Last_Date_Restored FROM msdb..restorehistory rh GROUP BY rh.destination_database_name ) AS InnerRest ON destination_database_name = Restored_To_DBName AND restore_date = Last_Date_Restored ) AS RestData ON bckup.backup_set_id = RestData.backup_set_id

How to Get SSIS Package name from SSIS Store where a specific table is used

Get the SSIS Package name from SSIS Package store where a specific object is used

SELECT a.name,
       CONVERT(XML, CONVERT(VARBINARY(MAX), a.packagedata)) AS ssispackagexml
FROM   msdb.dbo.sysssispackages a (nolock)
WHERE  CAST(CONVERT(XML, CONVERT(VARBINARY(MAX), a.packagedata)) AS
            NVARCHAR(MAX)) LIKE
       '%objectname%'