Query with machine name and get location and username.

Hi,

I want a script that can ask me for the machine name or take the machine names from a file and query with the ADS computer and get the location and query the same machine name which i have in the notes tab in the user's notes box.and get the Username,email,ntlogin,description of the user,office,extension,managername.
Any script that can do this...
Regards
Sharath
LVL 11
bsharathAsked:
Who is Participating?
 
RobSampsonConnect With a Mentor Commented:
Try this version.....I had forgotten to fill in the extra code for the File processing...

'===============
'Search_LDAP_Using_ADO_To_Display_User_Details_Based_On_Machine_Name.vbs
'http://www.experts-exchange.com/Programming/Languages/Scripting/Q_22891046.html?cid=238#a20136831

strOutputfile = "User_Information_By_Machine.csv"

'If Right(LCase(WScript.FullName), 11) = "wscript.exe" Then
'      Set objShell = CreateObject("WScript.Shell")
'      objShell.Run "cmd /k cscript """ & WScript.ScriptFullName & """", 1, False
'      Set objShell = Nothing
'      WScript.Quit
'End If

Set objFSO = CreateObject("Scripting.FileSystemObject")
Const intForReading = 1

strDetails = """Computer Name"",""Login Name"",""Display Name"",""Email"",""Description"",""Office"",""Extension"",""Manager"""

Set objNetwork = CreateObject("WScript.Network")

If WScript.Arguments.Count = 1 Then
      strMode = "File"
      strInputFile = WScript.Arguments.Item(0)
Else
      strMode = "Prompt"
End If

If strMode = "File" Then
      Set objInputFile = objFSO.OpenTextFile(strInputFile, intForReading, False)
      While Not objInputFile.AtEndOfStream
            strComputer = objInputFile.ReadLine
            strUserADsPath = Get_LDAP_User_Properties("user", "info", strComputer, "adsPath")
            If InStr(LCase(strUserADsPath), LCase("LDAP://")) > 0 Then
                  Set objUser = GetObject(strUserADsPath)
                  Get_Details objUser
            Else
                  strDetails = strDetails & VbCrLf & """Notes field does not match for any user account with computer: " & strComputer & """"
            End If
      Wend
Else
      strComputer = InputBox("Please enter the machine name to get information for:", "Machine Name")
      strUserADsPath = Get_LDAP_User_Properties("user", "info", strComputer, "adsPath")
      If InStr(LCase(strUserADsPath), LCase("LDAP://")) > 0 Then
            Set objUser = GetObject(strUserADsPath)
            Get_Details objUser
      Else
            strDetails = strDetails & VbCrLf & """Notes field does not match for any user account with computer: " & strComputer & """"
      End If
End If

Set objOutputFile = objFSO.CreateTextFile(Replace(wscript.ScriptFullName, wscript.ScriptName, "") & strOutputFile, True)
objOutputFile.Write strDetails
objOutputFile.Close
Set objOutputFile = Nothing

'WScript.Echo "Done"
MsgBox "Done"

Sub Get_Details(objUser)
      strLoginName = objUser.samAccountName
      strFullName = objUser.DisplayName
      strEmail = objUser.Mail
      strDescription = objUser.Description
      strOffice = objUser.physicalDeliveryOfficeName
      strExtension = objUser.TelephoneNumber
      strManager = objUser.Manager
      If strManager <> "" Then strManager = Mid(strManager, 4, InStr(strManager, ",") - 4)
      'WScript.Echo VbCrLf & "Computer Name: " & strComputer & VbCrLf & _
      '      "Login Name: " & strLoginName & VbCrLf & _
      '      "Display Name: " & strFullName & VbCrLf & _
      '      "Email: " & strEmail & VbCrLf & _
      '      "Description: " & strDescription & VbCrLf & _
      '      "Office: " & strOffice & VbCrLf & _
      '      "Extension: " & strExtension & VbCrLf & _
      '      "Manager: " & strManager
      strDetails = strDetails & VbCrLf & _
            """" & strComputer & """," & _
            """" & strLoginName & """," & _
            """" & strFullName & """," & _
            """" & strEmail & """," & _
            """" & strDescription & """," & _
            """" & strOffice & """," & _
            """" & strExtension & """," & _
            """" & strManager & """"
End Sub

Function Get_LDAP_User_Properties(strObjectType, strSearchField, strObjectToGet, strCommaDelimProps)
     
      ' This is a custom function that connects to the Active Directory, and returns the specific
      ' Active Directory attribute value, of a specific Object.
      ' strObjectType: usually "User" or "Computer"
      ' strSearchField: the field by which to seach the AD by. This acts like an SQL Query's WHERE clause.
      '                        It filters the results by the value of strObjectToGet
      ' strObjectToGet: the value by which the results are filtered by, according the strSearchField.
      '                        For example, if you are searching based on the user account name, strSearchField
      '                        would be "samAccountName", and strObjectToGet would be that speicific account name,
      '                        such as "jsmith".  This equates to "WHERE 'samAccountName' = 'jsmith'"
      '      strCommaDelimProps: the field from the object to actually return.  For example, if you wanted
      '                        the home folder path, as defined by the AD, for a specific user, this would be
      '                        "homeDirectory".  If you want to return the ADsPath so that you can bind to that
      '                        user and get your own parameters from them, then use "ADsPath" as a return string,
      '                        then bind to the user: Set objUser = GetObject("LDAP://" & strReturnADsPath)
     
      ' Now we're checking if the user account passed may have a domain already specified,
      ' in which case we connect to that domain in AD, instead of the default one.
      If InStr(strObjectToGet, "\") > 0 Then
            arrGroupBits = Split(strObjectToGet, "\")
            strDC = arrGroupBits(0)
            strDNSDomain = strDC & "/" & "DC=" & Replace(Mid(strDC, InStr(strDC, ".") + 1), ".", ",DC=")
            strObjectToGet = arrGroupBits(1)
      Else
      ' Otherwise we just connect to the default domain
            Set objRootDSE = GetObject("LDAP://RootDSE")
            strDNSDomain = objRootDSE.Get("defaultNamingContext")
      End If

      strBase = "<LDAP://" & strDNSDomain & ">"
      ' Setup ADO objects.
      Set adoCommand = CreateObject("ADODB.Command")
      Set adoConnection = CreateObject("ADODB.Connection")
      adoConnection.Provider = "ADsDSOObject"
      adoConnection.Open "Active Directory Provider"
      adoCommand.ActiveConnection = adoConnection

 
      ' Filter on user objects.
      'strFilter = "(&(objectCategory=person)(objectClass=user))"
      strFilter = "(&(objectClass=" & strObjectType & ")(" & strSearchField & "=" & strObjectToGet & "))"

      ' Comma delimited list of attribute values to retrieve.
      strAttributes = strCommaDelimProps
      arrProperties = Split(strCommaDelimProps, ",")

      ' Construct the LDAP syntax query.
      strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"
      adoCommand.CommandText = strQuery
      ' Define the maximum records to return
      adoCommand.Properties("Page Size") = 100
      adoCommand.Properties("Timeout") = 30
      adoCommand.Properties("Cache Results") = False

      ' Run the query.
      Set adoRecordset = adoCommand.Execute
      ' Enumerate the resulting recordset.
      strReturnVal = ""
      Do Until adoRecordset.EOF
          ' Retrieve values and display.    
          For intCount = LBound(arrProperties) To UBound(arrProperties)
                If strReturnVal = "" Then
                      strReturnVal = adoRecordset.Fields(intCount).Value
                Else
                      strReturnVal = strReturnVal & VbCrLf & adoRecordset.Fields(intCount).Value
                End If
          Next
          ' Move to the next record in the recordset.
          adoRecordset.MoveNext
      Loop

      ' Clean up.
      adoRecordset.Close
      adoConnection.Close
      Get_LDAP_User_Properties = strReturnVal

End Function
'===============

Regards,

Rob.
0
 
davecorunCommented:
Microsoft has a whole TechNet center for scripts.

http://www.microsoft.com/technet/scriptcenter/default.mspx

I would download the Script Repository and grab one of their pre-built scripts.  I seem to remember an almost exact version of what you're asking for.

// Dave
0
 
RobSampsonCommented:
Sharath, this is very similar to:
http://www.experts-exchange.com/Programming/Languages/Scripting/Q_22891052.html

Are they different in any way?

Regards,

Rob.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
bsharathAuthor Commented:
Rob both are different...
0
 
RobSampsonCommented:
Sharath, try this script.....it has no command prompt output, but outputs to CSV.  You can either run it as is to have it prompt for one computer name, or you can drag a text file onto it, with computer names in it.

'============
strOutputfile = "User_Information_By_Machine.csv"

'If Right(LCase(WScript.FullName), 11) = "wscript.exe" Then
'      Set objShell = CreateObject("WScript.Shell")
'      objShell.Run "cmd /k cscript """ & WScript.ScriptFullName & """", 1, False
'      Set objShell = Nothing
'      WScript.Quit
'End If

Set objFSO = CreateObject("Scripting.FileSystemObject")
Const intForReading = 1

strDetails = """Computer Name"",""Login Name"",""Display Name"",""Email"",""Description"",""Office"",""Extension"",""Manager"""

Set objNetwork = CreateObject("WScript.Network")

If WScript.Arguments.Count = 1 Then
      strMode = "File"
      strInputFile = WScript.Arguments.Item(0)
Else
      strMode = "Prompt"
End If

If strMode = "File" Then
      Set objInputFile = objFSO.OpenTextFile(strInputFile, intForReading, False)
      While Not objInputFile.AtEndOfStream
            strComputer = objInputFile.ReadLine
            strUserADsPath = Get_LDAP_User_Properties("user", "info", strComputer, "adsPath")
            Set objUser = GetObject(strUserADsPath)
      Wend
Else
      strComputer = InputBox("Please enter the machine name to get information for:", "Machine Name")
      strUserADsPath = Get_LDAP_User_Properties("user", "info", strComputer, "adsPath")
      Set objUser = GetObject(strUserADsPath)
      strLoginName = objUser.samAccountName
      strFullName = objUser.DisplayName
      strEmail = objUser.Mail
      strDescription = objUser.Description
      strOffice = objUser.physicalDeliveryOfficeName
      strExtension = objUser.TelephoneNumber
      strManager = objUser.Manager
      If strManager <> "" Then strManager = Mid(strManager, 4, InStr(strManager, ",") - 4)
      'WScript.Echo VbCrLf & "Computer Name: " & strComputer & VbCrLf & _
      '      "Login Name: " & strLoginName & VbCrLf & _
      '      "Display Name: " & strFullName & VbCrLf & _
      '      "Email: " & strEmail & VbCrLf & _
      '      "Description: " & strDescription & VbCrLf & _
      '      "Office: " & strOffice & VbCrLf & _
      '      "Extension: " & strExtension & VbCrLf & _
      '      "Manager: " & strManager
      strDetails = strDetails & VbCrLf & _
            """" & strComputer & """," & _
            """" & strLoginName & """," & _
            """" & strFullName & """," & _
            """" & strEmail & """," & _
            """" & strDescription & """," & _
            """" & strOffice & """," & _
            """" & strExtension & """," & _
            """" & strManager & """"
End If

Set objOutputFile = objFSO.CreateTextFile(strOutputFile, True)
objOutputFile.Write strDetails
objOutputFile.Close
Set objOutputFile = Nothing

'WScript.Echo "Done"
MsgBox "Done"

Function Get_LDAP_User_Properties(strObjectType, strSearchField, strObjectToGet, strCommaDelimProps)
     
      ' This is a custom function that connects to the Active Directory, and returns the specific
      ' Active Directory attribute value, of a specific Object.
      ' strObjectType: usually "User" or "Computer"
      ' strSearchField: the field by which to seach the AD by. This acts like an SQL Query's WHERE clause.
      '                        It filters the results by the value of strObjectToGet
      ' strObjectToGet: the value by which the results are filtered by, according the strSearchField.
      '                        For example, if you are searching based on the user account name, strSearchField
      '                        would be "samAccountName", and strObjectToGet would be that speicific account name,
      '                        such as "jsmith".  This equates to "WHERE 'samAccountName' = 'jsmith'"
      '      strCommaDelimProps: the field from the object to actually return.  For example, if you wanted
      '                        the home folder path, as defined by the AD, for a specific user, this would be
      '                        "homeDirectory".  If you want to return the ADsPath so that you can bind to that
      '                        user and get your own parameters from them, then use "ADsPath" as a return string,
      '                        then bind to the user: Set objUser = GetObject("LDAP://" & strReturnADsPath)
     
      ' Now we're checking if the user account passed may have a domain already specified,
      ' in which case we connect to that domain in AD, instead of the default one.
      If InStr(strObjectToGet, "\") > 0 Then
            arrGroupBits = Split(strObjectToGet, "\")
            strDC = arrGroupBits(0)
            strDNSDomain = strDC & "/" & "DC=" & Replace(Mid(strDC, InStr(strDC, ".") + 1), ".", ",DC=")
            strObjectToGet = arrGroupBits(1)
      Else
      ' Otherwise we just connect to the default domain
            Set objRootDSE = GetObject("LDAP://RootDSE")
            strDNSDomain = objRootDSE.Get("defaultNamingContext")
      End If

      strBase = "<LDAP://" & strDNSDomain & ">"
      ' Setup ADO objects.
      Set adoCommand = CreateObject("ADODB.Command")
      Set adoConnection = CreateObject("ADODB.Connection")
      adoConnection.Provider = "ADsDSOObject"
      adoConnection.Open "Active Directory Provider"
      adoCommand.ActiveConnection = adoConnection

 
      ' Filter on user objects.
      'strFilter = "(&(objectCategory=person)(objectClass=user))"
      strFilter = "(&(objectClass=" & strObjectType & ")(" & strSearchField & "=" & strObjectToGet & "))"

      ' Comma delimited list of attribute values to retrieve.
      strAttributes = strCommaDelimProps
      arrProperties = Split(strCommaDelimProps, ",")

      ' Construct the LDAP syntax query.
      strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"
      adoCommand.CommandText = strQuery
      ' Define the maximum records to return
      adoCommand.Properties("Page Size") = 100
      adoCommand.Properties("Timeout") = 30
      adoCommand.Properties("Cache Results") = False

      ' Run the query.
      Set adoRecordset = adoCommand.Execute
      ' Enumerate the resulting recordset.
      strReturnVal = ""
      Do Until adoRecordset.EOF
          ' Retrieve values and display.    
          For intCount = LBound(arrProperties) To UBound(arrProperties)
                If strReturnVal = "" Then
                      strReturnVal = adoRecordset.Fields(intCount).Value
                Else
                      strReturnVal = strReturnVal & VbCrLf & adoRecordset.Fields(intCount).Value
                End If
          Next
          ' Move to the next record in the recordset.
          adoRecordset.MoveNext
      Loop

      ' Clean up.
      adoRecordset.Close
      adoConnection.Close
      Get_LDAP_User_Properties = strReturnVal

End Function
'============

Regards,

Rob.
0
 
bsharathAuthor Commented:
Rob i get this...

C:\>"Query with machine.vbs"
Microsoft (R) Windows Script Host Version 5.6
Copyright (C) Microsoft Corporation 1996-2001. All rights reserved.

C:\Query with machine.vbs(35, 7) Microsoft VBScript runtime error: Invalid proce
dure call or argument: 'GetObject'
0
 
RobSampsonCommented:
OK, try this, which accounts for the machine not being found in the Notes field:

'====================
strOutputfile = "User_Information_By_Machine.csv"

'If Right(LCase(WScript.FullName), 11) = "wscript.exe" Then
'      Set objShell = CreateObject("WScript.Shell")
'      objShell.Run "cmd /k cscript """ & WScript.ScriptFullName & """", 1, False
'      Set objShell = Nothing
'      WScript.Quit
'End If

Set objFSO = CreateObject("Scripting.FileSystemObject")
Const intForReading = 1

strDetails = """Computer Name"",""Login Name"",""Display Name"",""Email"",""Description"",""Office"",""Extension"",""Manager"""

Set objNetwork = CreateObject("WScript.Network")

If WScript.Arguments.Count = 1 Then
      strMode = "File"
      strInputFile = WScript.Arguments.Item(0)
Else
      strMode = "Prompt"
End If

If strMode = "File" Then
      Set objInputFile = objFSO.OpenTextFile(strInputFile, intForReading, False)
      While Not objInputFile.AtEndOfStream
            strComputer = objInputFile.ReadLine
            strUserADsPath = Get_LDAP_User_Properties("user", "info", strComputer, "adsPath")
            Set objUser = GetObject(strUserADsPath)
      Wend
Else
      strComputer = InputBox("Please enter the machine name to get information for:", "Machine Name")
      strUserADsPath = Get_LDAP_User_Properties("user", "info", strComputer, "adsPath")
      If strUserADsPath <> "" Then
            Set objUser = GetObject(strUserADsPath)
            strLoginName = objUser.samAccountName
            strFullName = objUser.DisplayName
            strEmail = objUser.Mail
            strDescription = objUser.Description
            strOffice = objUser.physicalDeliveryOfficeName
            strExtension = objUser.TelephoneNumber
            strManager = objUser.Manager
            If strManager <> "" Then strManager = Mid(strManager, 4, InStr(strManager, ",") - 4)
            'WScript.Echo VbCrLf & "Computer Name: " & strComputer & VbCrLf & _
            '      "Login Name: " & strLoginName & VbCrLf & _
            '      "Display Name: " & strFullName & VbCrLf & _
            '      "Email: " & strEmail & VbCrLf & _
            '      "Description: " & strDescription & VbCrLf & _
            '      "Office: " & strOffice & VbCrLf & _
            '      "Extension: " & strExtension & VbCrLf & _
            '      "Manager: " & strManager
            strDetails = strDetails & VbCrLf & _
                  """" & strComputer & """," & _
                  """" & strLoginName & """," & _
                  """" & strFullName & """," & _
                  """" & strEmail & """," & _
                  """" & strDescription & """," & _
                  """" & strOffice & """," & _
                  """" & strExtension & """," & _
                  """" & strManager & """"
      Else
            strDetails = strDetails & VbCrLf & _
                  """" & strComputer & " was not found."""
      End If
End If

Set objOutputFile = objFSO.CreateTextFile(strOutputFile, True)
objOutputFile.Write strDetails
objOutputFile.Close
Set objOutputFile = Nothing

'WScript.Echo "Done"
MsgBox "Done"

Function Get_LDAP_User_Properties(strObjectType, strSearchField, strObjectToGet, strCommaDelimProps)
     
      ' This is a custom function that connects to the Active Directory, and returns the specific
      ' Active Directory attribute value, of a specific Object.
      ' strObjectType: usually "User" or "Computer"
      ' strSearchField: the field by which to seach the AD by. This acts like an SQL Query's WHERE clause.
      '                        It filters the results by the value of strObjectToGet
      ' strObjectToGet: the value by which the results are filtered by, according the strSearchField.
      '                        For example, if you are searching based on the user account name, strSearchField
      '                        would be "samAccountName", and strObjectToGet would be that speicific account name,
      '                        such as "jsmith".  This equates to "WHERE 'samAccountName' = 'jsmith'"
      '      strCommaDelimProps: the field from the object to actually return.  For example, if you wanted
      '                        the home folder path, as defined by the AD, for a specific user, this would be
      '                        "homeDirectory".  If you want to return the ADsPath so that you can bind to that
      '                        user and get your own parameters from them, then use "ADsPath" as a return string,
      '                        then bind to the user: Set objUser = GetObject("LDAP://" & strReturnADsPath)
     
      ' Now we're checking if the user account passed may have a domain already specified,
      ' in which case we connect to that domain in AD, instead of the default one.
      If InStr(strObjectToGet, "\") > 0 Then
            arrGroupBits = Split(strObjectToGet, "\")
            strDC = arrGroupBits(0)
            strDNSDomain = strDC & "/" & "DC=" & Replace(Mid(strDC, InStr(strDC, ".") + 1), ".", ",DC=")
            strObjectToGet = arrGroupBits(1)
      Else
      ' Otherwise we just connect to the default domain
            Set objRootDSE = GetObject("LDAP://RootDSE")
            strDNSDomain = objRootDSE.Get("defaultNamingContext")
      End If

      strBase = "<LDAP://" & strDNSDomain & ">"
      ' Setup ADO objects.
      Set adoCommand = CreateObject("ADODB.Command")
      Set adoConnection = CreateObject("ADODB.Connection")
      adoConnection.Provider = "ADsDSOObject"
      adoConnection.Open "Active Directory Provider"
      adoCommand.ActiveConnection = adoConnection

 
      ' Filter on user objects.
      'strFilter = "(&(objectCategory=person)(objectClass=user))"
      strFilter = "(&(objectClass=" & strObjectType & ")(" & strSearchField & "=" & strObjectToGet & "))"

      ' Comma delimited list of attribute values to retrieve.
      strAttributes = strCommaDelimProps
      arrProperties = Split(strCommaDelimProps, ",")

      ' Construct the LDAP syntax query.
      strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"
      adoCommand.CommandText = strQuery
      ' Define the maximum records to return
      adoCommand.Properties("Page Size") = 100
      adoCommand.Properties("Timeout") = 30
      adoCommand.Properties("Cache Results") = False

      ' Run the query.
      Set adoRecordset = adoCommand.Execute
      ' Enumerate the resulting recordset.
      strReturnVal = ""
      Do Until adoRecordset.EOF
          ' Retrieve values and display.    
          For intCount = LBound(arrProperties) To UBound(arrProperties)
                If strReturnVal = "" Then
                      strReturnVal = adoRecordset.Fields(intCount).Value
                Else
                      strReturnVal = strReturnVal & VbCrLf & adoRecordset.Fields(intCount).Value
                End If
          Next
          ' Move to the next record in the recordset.
          adoRecordset.MoveNext
      Loop

      ' Clean up.
      adoRecordset.Close
      adoConnection.Close
      Get_LDAP_User_Properties = strReturnVal

End Function
'====================

Regards,

Rob.
0
 
bsharathAuthor Commented:
Rob excellent...
Just to confirm is this data captured from the ADS already mentioned data or does it get the username and email from the logged in user.

Can the script run on a file which has the machine names please...
0
 
RobSampsonCommented:
It gets the information from the AD, and does not matter who is logged in.

It is already configured to run against a file of computer names.  All you need to do is drag a file of computer names onto the script.

Regards,

Rob.
0
 
bsharathAuthor Commented:
In the csv file i get this when i take a file that has computer names in it...

 was not found.
0
 
bsharathAuthor Commented:
Rob i get this,

Computer Name
Notes field does not match for any user account with computer: Dev-chen-pc2007
Notes field does not match for any user account with computer: Dev-chen-pc2006

I think even here you need to omit  "Machine Name - "
0
 
RobSampsonCommented:
Oh yeah, forgot about that.....change the two lines that look like this:
strUserADsPath = Get_LDAP_User_Properties("user", "info", strComputer, "adsPath")

to this:
strUserADsPath = Get_LDAP_User_Properties("user", "info", "*" & strComputer, "adsPath")

Regards,

Rob.
0
 
bsharathAuthor Commented:
Still get the same...

We havent mentioned to omit this "Machine - "
0
 
bsharathAuthor Commented:
Sorry omit the last post...It works like a charm
0
 
bsharathAuthor Commented:
Thanks a lot Rob today we have succeeded on lot of Questions's Solution's
0
All Courses

From novice to tech pro — start learning today.