Kaseya Community

SQL Queries - #1 Get Organisation / #2 Get Machine Names in Group

  • Hi, I don't have much idea about SQL queries, and I'm wondering if anyone has got the following:

    1. Get the Organisation name from the Kasaya Group.

    2. Get a list of machine names in the Kaseya Group. I want to use this to automatically name new machines.

    Both of these will be used in Agent Procedures.

    I'm sure that these will both be useful for many of us.

  • Hello Lothario,

    1.

    If the need is to have the Organization Name of a machine where you run the Agent Procedure to one way
    could be to add a step in your procedure as follows:

    GetVariable -> SQL View Data

    and use this one:

    (SELECT aSOrgs.OrganizationName, vA.Machine_GroupID FROM vComputedDenormalizedOrgToMach aMachOrgs, vSystemOrganizations aSOrgs, vAgentLabel vA where aMachOrgs.OrgId = aSOrgs.id and aMachOrgs.AgentGuid = vA.agentGuid) MyView/OrganizationName

    In this Procedure Example I just write in the procedure log the organizational name.

    Procedure ExampleOrg.xml


    2. I am not sure I understand what you want to do here (name a machine or a kaseya agent?).
    If it's the latter the Agent Deployment package has its own rule of naming (among the various choices).

    If you want to name the Machine as "Computer Name" then you don't want a "list" but a single (unique) value for each machine.

    In any case here is a sample SQL to get a list of "Agent" names and the group where they belong:

    SELECT * FROM vAgentName

    In a procedure this could be referred directly as #vAgentName.groupName#


    Best Regards

  • Hi  

    For question 1:

    Below is an example SQLRead I built to grab the machine group name only from the agent running the procedure:

    https://dl.dropboxusercontent.com/u/58075471/Consulting/Custom%20Scripts/MachGroup.xml

    The SQL inside of it is this:

    sql="SELECT LEFT(reverseName, CHARINDEX('.', reverseName) - 1) AS [Group] FROM Inventory.asset"/>

    However, you can alter it to get what you're looking for -- below is a screenshot which shows some tweaks to just get the Organization name as opposed to the machine group name:

    Here is more information on SQLRead step in Agent Procedures.

    For Question 2:

    Are you trying to get a list of all machines in a single group every time the procedure runs?

    You can query this information with the following:

    select machname from machNameTab where groupName like '%hq%'

  • Thanks for this.

    Just to clarify, for #1, I am looking for the organisation.

    So the Kaseya group name is: microsoft-inc.redmond

    The organisation will be Microsoft Inc.

    I'm trying to get the organisation from the Kaseya Group, so the result of the SQL query will be: Microsoft Inc. 

    I think you have answered this one.

    For #2, I just want a list of all machine names in the group as a variable. Then I can check to see what name I can automatically assign to my new machine (i.e. the proposed name will not be in the variable.

    Nicholas, in your query, what is the %hq%?

  • Alessandro, your Org finder is perfect. Thanks so much!

  • Hello Lothario,

    Glad you liked the script for problem 1.

    Regarding 2, I am not really sure we are taking this correctly as this may hit some limits of string or datatype size.

    But if you REALLY need it, this is a possible way:

    Add the following into the approved SQL Scripts

    \\yourkaseyaserver\xml\procedures\AgentProcSQL\0\SQLRead\

    GetAgentListsByGroup.xml

    Now add an SQLRead step on your procedure as follows.


    You can now check the #AgentNamesInGroup# Variable and it will contain all agents in the group of the agent where you run the procedure.

    Best Regards



    Added all necessary files.
    [edited by: Alessandro Di Marco at 10:28 AM (GMT -7) on May 24, 2016]
  • Just to confirm that I only want the machine names from the current Kaseya group - not all agents :-)

    I'm assuming that I use a SQLread step? Can you please confirm exactly what I must paste in the field (sorry, I am dumb with SQL).

    I will post my final Procedures here for others to use if they want.

  • Hello Lothario,

    I have edited my previous answer (because it contained quotes that SQLView does not like).

    And yes indeed I have done it now using SQLRead.

    I tested it on my lab and it works here.

    Best Regards

  • Fantastic! All of my problems are solved (until I get home).

    Thanks very much for this.