Kaseya Community

Populating custom field for multiple agents

  • I am trying to populate a custom field for about 5,000 workstations with what OU the computer object is in in AD.  I have tried a couple of methods of having each agent pull its OU info and populate its own custom field but ran into many obstacles. The biggest obstacle I ran into was that most of the computers were lacking the powershell AD modules I was trying to call.

    I have decided that it should be easier to export a dump of machines from Kaseya and then use that list to mass update all of the custom fields from one computers.  

    Does anyone know of a way to write to those fields from outside of a Kaseya Procedure, or have some tips as to how I could accomplish this with a procedure?

  • If you're familiar enough with vbs to edit this file to suit your needs, it will generate various csv text files you can use Kaseya to upload to a central location or Kaseya scripts to read and write to the custom fields...

    You can pretty much grab any useful field from AD by running on a PDC and changing the write command to the predefined variables.

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

    ' COMMENT: This script queries AD for User information and outputs

    ' the active user counts based on a number of variables

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

    Option Explicit

    On Error Resume Next

    Dim intActiveThreshold

    intActiveThreshold = 30 'This is the threshold by which accounts are considered active or not. You can edit this

    Dim objRootDSE 'As Object

    Dim objADODB 'As Object

    Dim objADODBcon 'As Object

    Dim objRecordSet 'As Object

    Dim objFileSys 'As Object

    Dim objTextFile 'As Object

    Dim strTextLine 'As String

    Dim strDomainDN 'As String

    Dim strLDAPQuery 'As String

    Dim strQuery 'As String

    Dim strUserName 'As String

    Dim strFirstName 'As String

    Dim strLastName 'As String

    Dim strOffice 'As String

    Dim strDept 'As String

    Dim strCompany 'As String

    Dim strDN 'As String

    Dim strDescription 'As String

    Dim strObjectClass 'As String

    Dim strLogonTime 'As String

    Dim strOU 'As String

    Dim intAccountDisabled 'As Integer

    Dim intLogonTime 'As DateTime

    Dim intDaysInactive

    Dim objUsercount

    Dim objTotalUsers

    Dim objQuestionCount

    Dim objTextFile2

    Dim objTextFile3

    Dim objTextFile4

    Dim objFileSys2

    Dim objFileSys3

    Dim objFileSys4

    objUsercount = 0

    objQuestionCount = 0

    objTotalUsers = 0

    Const ForWriting = 2

    Const strOutputFile = "C:\User_Count-Active_List.txt" ' Sets File Name of Active List

    Const ForWriting2 = 2

    Const strOutputFile2 = "C:\User_Count-Counter.txt" ' Counter file for use with Kaseya script

    Const ForWriting3 = 2

    Const strOutputFile3 = "C:\User_Count-Inactive_List.txt" ' Sets File Name of Inactive List

    Const ForWriting4 = 2

    Const strOutputFile4 = "C:\User_Count-Question_List.txt" ' Sets File Name of Inactive List

    Set objFileSys = CreateObject("Scripting.FileSystemObject")

    If objFileSys.FileExists(strOutputFile) Then

    Else

    objFileSys.CreateTextFile(strOutputFile)

    End If

    Set objTextFile = objFileSys.OpenTextFile(strOutputFile,ForWriting)

    objTextFile.WriteLine "Name - Last Logon Time"

    objTextFile.WriteLine

    Set objFileSys2 = CreateObject("Scripting.FileSystemObject")

    If objFileSys2.FileExists(strOutputFile2) Then

    Else

    objFileSys2.CreateTextFile(strOutputFile2)

    End If

    Set objTextFile2 = objFileSys2.OpenTextFile(strOutputFile2,ForWriting2)

    Set objFileSys3 = CreateObject("Scripting.FileSystemObject")

    If objFileSys3.FileExists(strOutputFile3) Then

    Else

    objFileSys3.CreateTextFile(strOutputFile3)

    End If

    Set objTextFile3 = objFileSys3.OpenTextFile(strOutputFile3,ForWriting3)

    objTextFile3.WriteLine "Name - Last Logon Time"

    objTextFile3.WriteLine

    Set objFileSys4 = CreateObject("Scripting.FileSystemObject")

    If objFileSys4.FileExists(strOutputFile4) Then

    Else

    objFileSys4.CreateTextFile(strOutputFile4)

    End If

    Set objTextFile4 = objFileSys4.OpenTextFile(strOutputFile4,ForWriting3)

    objTextFile4.WriteLine "Name - Last Logon Time"

    objTextFile4.WriteLine

    Set objRootDSE = GetObject("LDAP://rootDSE")

    strDomainDN = objRootDSE.Get("DefaultNamingContext")

    strQuery = "<LDAP://" & strDomainDN & ">;(&(ObjectCategory=Person)(ObjectClass=User));SAMaccountName,GivenName,sn,DistinguishedName,Description,physicalDeliveryOfficeName,department,company;SubTree"

    Set objADODB = CreateObject("ADODB.Command")

    Set objADODBcon = CreateObject("ADODB.Connection")

    objADODBcon.Provider = "ADsDSOObject"

    objADODBcon.Open = "Active Directory Provider"

    objADODB.ActiveConnection = objADODBcon

    objADODB.CommandText = strQuery

    objADODB.Properties("TimeOut") = 30

    objADODB.Properties("Cache Results") = False

    Set objRecordSet = objADODB.Execute

    Set objRootDSE = GetObject("LDAP://rootDSE")

    Do Until objRecordSet.EOF

    strUserName = UCase(objRecordSet.Fields("SAMaccountName"))

    strFirstName = UCase(objRecordSet.Fields("GivenName"))

    strLastName = UCase(objRecordSet.Fields("sn"))

    strDN = UCase(objRecordSet.Fields("DistinguishedName"))

    strOffice = Replace(UCase(objRecordSet.Fields("physicalDeliveryOfficeName")),","," ")

    strDept = UCase(objRecordSet.Fields("department"))

    strCompany = UCase(objecRecordSet.Fields("company"))

    LastLogon strDN

    'If strLogonTime = "Never Authenticated" Then

    ' LastLogon2 strDN

    'End If

    GetUserAttrib strDN

    If Len(strDN) > 0 Then

    strOU = Replace(Replace(Replace(Replace(strDN,",","/"),"CN=",""),"DC=",""),"OU=","")

    If Len(strFirstName) > 0 and Len(strLastName) > 0 Then

    strOU = Replace(Replace(Replace(Replace(strOU,strFirstName & " " & strLastName,""),strUserName,""),strFirstName,""),strLastName,"")

    End If

    If Len(strUserName) > 0 Then

    strOU = Replace(strOU,strUserName,"")

    End If

    Else

    strDN = "Empty"

    End If

    If intAccountDisabled = FALSE And intDaysInactive < intActiveThreshold And strLogonTime <> "Never Authenticated" And strFirstName <> "ADMIN" And strLastName <> "ADMIN" And strFirstName <> "" And strLastName <> "" And strUserName <> "BESADMIN" And strUserName <> "ADMIN" Then

    objUsercount = objUsercount + 1

    objTextFile.WriteLine strUserName & " - " & CStr(strLogonTime)

    Else

    objTextFile3.WriteLine strUserName & " - " & CStr(strLogonTime)

    End If

    If intAccountDisabled = FALSE And intDaysInactive < intActiveThreshold And strLogonTime <> "Never Authenticated" And sstrUserName <> "BESADMIN" And strUserName <> "ADMIN" Then

    if strFirstName <> "" And strLastName <> "" Then

    Else

    objTextFile4.WriteLine strUserName & " - " & CStr(strLogonTime)

    objQuestionCount = objQuestionCount + 1

    End If

    End If

    objTotalUsers = objTotalUsers + 1

    ClearVariables strUserName,strFirstName,strLastName, strDN,intAccountDisabled,strDescription,intLogonTime, strLogonTime, strOU,strOffice,strDept,strCompany,intDaysInactive

    objRecordSet.MoveNext

    Loop

    objTextFile.WriteLine

    objTextFile.WriteLine "Active User Count: " & objUsercount

    objTextFile.WriteLine

    objTextFile.WriteLine "Question Mark User Count: " & objQuestionCount

    objTextFile.WriteLine

    objTextFile.WriteLine "Total User Count: " & objTotalUsers

    objTextFile.WriteLine

    objTextFile2.WriteLine objUsercount

    objTextFile.Close

    objTextFile2.Close

    objTextFile3.Close

    Function GetUserAttrib(strDN)

    Dim objAccount 'As Object

    Set objAccount = GetObject("LDAP://" & strDN)

    intAccountDisabled = objAccount.AccountDisabled

    strDescription = Replace(objAccount.Description,","," ")

    strObjectClass = objAccount.Class

    End Function

    Function LastLogon(strDN)

    On Error Resume Next

    Dim objUser 'As Object

    Dim objLogon 'As Object

    set objUser = GetObject("LDAP://" & strDN)

    set objLogon = objUser.Get("lastLogonTimestamp")

    If Err.Number <> 0 Then

    LastLogon2 strDN

    Exit Function

    End If

    intLogonTime = objLogon.HighPart * (2^32) + objLogon.LowPart

    intLogonTime = intLogonTime / (60 * 10000000)

    intLogonTime = intLogonTime / 1440

    strLogonTime = intLogonTime + #1/1/1601#

    intDaysInactive = Fix(Now() - strLogonTime)

    End Function

    Function LastLogon2(strDN) ' Checks for 2000 or earlier functional level

    On Error Resume Next

    Dim objUser2 'As Object

    Dim objLogon2 'As Object

    set objUser2 = GetObject("LDAP://" & strDN)

    set objLogon2 = objUser2.Get("lastLogon")

    If Err.Number <> 0 Then

    strLogonTime = "Never Authenticated"

    Exit Function

    End If

    intLogonTime = objLogon2.HighPart * (2^32) + objLogon2.LowPart

    intLogonTime = intLogonTime / (60 * 10000000)

    intLogonTime = intLogonTime / 1440

    strLogonTime = intLogonTime + #1/1/1601#

    intDaysInactive = Fix(Now() - strLogonTime)

    End Function

    Function ClearVariables(strUserName,strFirstName,strLastName, strDN,intAccountDisabled,strDescription,intLogonTime,strLogonTime,strOU,strOffice,strDept,strCompany)

    strUserName = ""

    strFirstName = ""

    strLastName = ""

    strDN = ""

    intAccountDisabled = ""

    strDescription = ""

    intLogonTime = ""

    strLogonTime = ""

    strOU = ""

    strOffice = ""

    strDept = ""

    strCompany = ""

    End Function

  • As i look back this is more for users than computers -- but the basic format will still be the same, you'll just query the computer not the user objects.  I know it's not a perfect fit answer - but if you're avoiding PS you'll need to use vbs.  It should at least give you a starting point.

  • Hello Daniel, Chris above is correct, with vb script you can achieve this easily.

    Here is something for Computers instead of users (I made few examples in terms of output type, you can modify as needed)

    Set ad = CreateObject("ADSystemInfo")

    strComputer = ad.ComputerName

    Set objComputer = GetObject("LDAP://" & strComputer)

    'objComputer.Parent holds the distinguished name including LDAP://

    'distinguishedNameArr will only have in its array OU=value

    distinguishedNameArr = Split(Mid(objComputer.Parent, 8), ",")

    'output examples

    'full distinguished name

    Wscript.Echo(Mid(objComputer.Parent, 8))

    'each OU path printed individually

    For each ouStr in distinguishedNameArr

      strFullPath = Mid(ouStr, 4) + ">" + strFullPath

      Wscript.Echo ouStr

    Next

    Wscript.Echo strFullPath

  • Thank you guys, this is a way of doing it that i had not thought off.  Seems like I can use the VBS to export the data to a file and then my procedure can read that file to populate the custom field.  I almost feel like I was trying to make this more complicated that it needed to me.

    Again, thanks a lot!

  • Here is a script to do what you want to do.

    files.mymsp.com/.../Procedure%20OU%20that%20computer%20belongs%20to.xml

    By default it is just writing it out to the Agent Procedure Log, but I put a disabled line in you can use to update a custom field.

    Sending link, since I have never figured out how to post code here! :)   Enjoy!



    * I give up trying to put code tags... I just can't get it to work!
    [edited by: Chris Amori at 11:55 AM (GMT -7) on Jun 3, 2018]