Are you attending Connect IT Global? Make sure to book your hotel rooms before the special conference rates end TODAY. https://www.connectit.com/global/

Add Computer Description From AD

patjaysam
patjaysam USAMember, Managed Service Provider CHOCOLATE MILK
edited January 11 in Request

Would it be possible to have the computer description from Active Directory automatically added to the Agent in the Machine Summary? Also have it included in the available columns would be great.

1
1 votes

Shipped · Last Updated

CAmori provided solution to submitter.

Comments

  • CAmori
    CAmori Washington DC areaMember CHOCOLATE MILK

    @patjaysam We wrote this script for you, please PM me and I will send it to you.

    For everyone else, we released it on our https://ClubMSP.com site: https://clubmsp.com/msp/scripts/ad-description-to-custom-field/

    Full Disclosure: ClubMSP is a paid membership site. We feel the cost is minuscule compared to the time that we put into maintaining these scripts. Members can also request custom scripts.

  • Alessandro Di Marco
    Alessandro Di Marco Member CHOCOLATE MILK

    Hello Patjaysam,

    If one is on premise and feels adventurous, below is a simple way without having to run a script for each agent (which is slow / expensive).

    This one only needs to be executed one time on ONE server only that has access to the Kaseya DB Server (and the AD) and it will do the update for every single agent where there's a match update the description in a custom field. A big save if you have thousands of agents.

    It works by making direct updates in the Kaseya Database, so careful (no warranties :-))

    You first create the custom field, then you change below where it is highlighted and with one execution on one server all of your agents will have the associated AD Description.

    If you have multiple AD / Multi-Tenant / you are an MSP, you will need a different approach (your AD and your Kaseya DB may be on different networks) but for someone that use Kaseya to manage its own assets, this is probably the fastest approach to do this.

    In fact, if you use the approach of running the script on each agent it is technically easier (grab the description, update systeminfo step in the procedure editor, done).

    Alex

    ------

    Clear-Host

    $computerNames = Get-ADComputer -filter * -Prop description | select Name,Description


    #Change this text to the Variable Name you created. This is an example: ADComputerDescription

    $fieldName = "ADComputerDescription"

    $sqlConn = New-Object System.Data.SqlClient.SqlConnection


    #Change below with the correct database parameters

    $sqlConn.ConnectionString = "Server=SERVERNAME;Database=ksubscribers;User Id=YOURUSER;Password=YOURPASSWORD;"

    $sqlConn.Open()

    $sqlcmd = New-Object System.Data.SqlClient.SqlCommand

    $sqlcmd.Connection = $sqlConn

    $query = "select m.agentGuid, m.ComputerName, (select id from [dbo].[auditRsltManualFields] f where f.fieldName = '" + $fieldName + "') FieldID, FValues.fieldNameFK, FValues.fieldValue from [dbo].[vAuditMachineSummary] m left outer join (select fv.agentGuid, fv.fieldNameFK, fv.fieldValue from [dbo].[auditRsltManualFieldValues] fv, [dbo].[auditRsltManualFields] mf where fv.FieldNameFK = mf.id and mf.fieldName = '" + $fieldName + "') FValues on m.agentGuid = FValues.agentGuid"

    $sqlcmd.CommandText = $query

    $adp.SelectCommand = $sqlcmd

    $data = New-Object System.Data.DataSet

    $adp.Fill($data, "AgentsComputers") | Out-Null

    $sqlConn.Close()


    Write-Host $data.Tables["AgentsComputers"].Rows.Count


    foreach ($r in $data.Tables["AgentsComputers"].Rows)

    {

       $found = $computerNames | where Name -eq $r.ComputerName

       #If Computer Name matches

       if ($found){

          #If Record Exists in [auditRsltManualFieldValues]

          if ($r.fieldNameFK -eq $r.fieldID)

          {

               #Write-Host "Found And Record Exists for agentGuid" $r.agentGuid

               $sqlConn.Open()

               $query = "UPDATE [dbo].[auditRsltManualFieldValues] Set fieldValue = @fieldValue WHERE agentGuid = @aGuid and fieldNameFK = @fnFK";

               $sqlcmd = New-Object System.Data.SqlClient.SqlCommand

               $sqlcmd.Connection = $sqlConn

               $sqlcmd.CommandText = $query

               $sqlcmd.Parameters.AddWithValue("@fieldValue", $computerNames.Description) | Out-null

               $sqlcmd.Parameters.AddWithValue("@aGuid", $r.agentGuid) | Out-null

               $sqlcmd.Parameters.AddWithValue("@fnFK", $r.FieldID) | Out-null

               $sqlcmd.ExecuteNonQuery()

               $sqlConn.Close()

          } else {

               #Write-Host "Found And Record Does NOT exists for agentGuid" $r.agentGuid

               $sqlConn.Open()

               $sqlcmd = New-Object System.Data.SqlClient.SqlCommand

               $sqlcmd.Connection = $sqlConn

               $query = "INSERT INTO [dbo].[auditRsltManualFieldValues] (agentGuid, fieldNameFK, fieldValue) VALUES(@aGuid, @fnFK, @fieldValue)";

               $sqlcmd.CommandText = $query

               $sqlcmd.Parameters.AddWithValue("@fieldValue", $computerNames.Description) | Out-null

               $sqlcmd.Parameters.AddWithValue("@aGuid", $r.agentGuid) | Out-null

               $sqlcmd.Parameters.AddWithValue("@fnFK", $r.FieldID) | Out-null

               $sqlcmd.ExecuteNonQuery()

               $sqlConn.Close()

          }

       }

    }

This discussion has been closed.