Kaseya Community

sqlRead help please

  • I have an SQL query that returns all machines name in a Kaseya Group. Someone helped me with this here: http://community.kaseya.com/xsp/f/28/t/22094.aspx

    <?xml version="1.0" encoding="utf-8" ?>

    <queryList>
    <queryDef label="AgentsInVariableByGroup" sql="SELECT Stuff((SELECT N', ' + DisplayName FROM vAgentName WHERE GroupName in (Select GroupName From vAgentName where agentGuid = #vMachine.agentGuid#) FOR XML PATH(''),TYPE).value('text()[1]','nvarchar(max)'),1,2,N'')" />
    </queryList>

    It's super-useful for some of our automation.

    If I query this group: contoso-com.london

    .,.it returns all machine names. Perfect!

    However, I now need to query the top-level group: contoso-com

    I need the machines names from contoso-com.london, contoso-com.nyc, AND contoso-com.milan.

    I don't have a clue how to do this. Can anyone help?

  • Hello Lothario,

    I am assuming we are still talking about Organizations here and that your issue is to be able to select all children after a selected "root".

    The script becomes a bit more complicated (it could theoretically be simplified by creating a view inside kaseya and/or a function for the CTE) but for now I will just write an SQL that does what you need:

    The below will give

    WITH CTE AS (

       SELECT

           m.id          

           ,m.parentOrgFK

           ,m.orgName

       FROM kasadmin.org m

       WHERE m.parentOrgFK IS NULL AND orgName = 'MyRootOrganizationName'

       UNION ALL

       SELECT

           b.id

           ,b.parentOrgFK

           ,b.orgName

       FROM CTE t

       JOIN kasadmin.org b ON b.parentOrgFK = t.id

    )

    SELECT aSOrgs.OrganizationName, vA.Machine_GroupID

    FROM vComputedDenormalizedOrgToMach aMachOrgs, vSystemOrganizations aSOrgs, vAgentLabel vA, CTE c

    where aMachOrgs.OrgId = aSOrgs.id and aMachOrgs.AgentGuid = vA.agentGuid

    and aSorgs.id = c.id

    You just need to replace:

    orgName = 'MyRootOrganizationName'

    with the root name of your organization where you want to start "navigating" and it will take all of the children

    for you.

    Hope it helps.

    Best Regards

  • if I'm reading correctly your original query basically could be run using an agent procedure against a single machine and it ends up returning a comma separated list of all agents that are in the same exact machine group.  Now you would like to go back up to the root group.  

    If all of that is correct then you can modify the existing query with a couple of minor changes.

    FIrst change the "in" part of the first WHERE statement to like, and then replace the section where you are getting the group name of the machine with this:

    (Select LEFT(GroupName,CHARINDEX('.', GroupName) -1) + '%' From vAgentName where agentGuid = #vMachine.agentGuid#)

    so your final xml file would end up looking something like this:

    <?xml version="1.0" encoding="utf-8" ?>

    <queryList>
    <queryDef label="AgentsInVariableByRootGroup" sql="SELECT Stuff((SELECT N', ' + DisplayName FROM vAgentName WHERE GroupName like (Select LEFT(GroupName,CharIndex('.',GroupName) -1) + '%' From vAgentName where agentGuid = #vMachine.agentGuid#) FOR XML PATH(''),TYPE).value('text()[1]','nvarchar(max)'),1,2,N'')" />
    </queryList>

  • Guys, thanks a lot! I am going to use this query in a machine naming Agent Procedure - it will ensure that we don't allocate a duplicate name.

    I hope it's useful to someone else too.

  • Hello Lothario,

    I think Jonathan query is what you are looking for. when I read the forum link you referenced there were 2 issues in there (organization finder and the group selector)...

    I gave you an answer based on "organization finder" problem but probably you wanted to expand on group selector.

    Anyway, you got both :-)