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%'