Kaseya Community

Creating a View for machines with active remote control sessions

  • Hello - I thought I'd share this as it was a quick but very handy little trick.

    1) I created a new view named Agent - Active Remote Control Session.

    2) I queried the dbo.ViewDef table to identify the view ID using the query: select viewid from viewDef where viewname = 'Agent - Active Remote Control Session'

    It returned 44041164.

    3) I then modified the dbo.fvFilterView44041164 to use the following query:

    SELECT agentGuid, agentGuidStr, machName, groupName, displayName, partitionId
    FROM dbo.machNameTab
    WHERE (agentGuid IN
    (SELECT agentGuid
    FROM KaseyaRemoteControl.[Log]
    WHERE (completed = 0) AND (lastActiveTime >= DATEADD(n, - 1, GETDATE()))))

    And, tada! we now have a view that shows us all machines with active remote control sessions.

  • Thats cool :)

    I made one that shows all agents that are "out of compliance" in Policy Management.

    SELECT DISTINCT a.agentGuid, a.agentGuidStr, a.machName, a.groupName, a.displayName, a.partitionId

    FROM         dbo.machNameTab AS a INNER JOIN

                         policy.vPolicyObjectAgentStatus AS f ON a.agentGuid = f.agentGuid LEFT OUTER JOIN

                         dbo.agentState AS b ON a.agentGuid = b.agentGuid INNER JOIN

                         dbo.users AS su ON a.agentGuid = su.agentGuid

    WHERE     (a.partitionId = 1) AND (ISNULL(b.online, 199) = 1) AND (ISNULL(su.suspendAgent, 0) = 0) AND (f.PolicyObjectStatus LIKE 'Override') OR (a.partitionId = 1) AND (ISNULL(su.suspendAgent, 0) = 0) AND (b.offlineTime > DATEADD(MINUTE, - 43200, CURRENT_TIMESTAMP)) AND (f.PolicyObjectStatus LIKE 'Override')

    I'm struggling to create one to show agents with duplicate MAC addresses at the moment.

  • Ah that is a clever one also! Out of curiosity, why the need for duplicate MAC addresses?

    Also have you had any trouble with techs attempting to edit the views? We only have a handful of Masters, and I was pretty clear about leaving this alone, but the power of SQL views is tempting and I imagine we'll expand the use of these.

  • I'm trying to isolate and display only duplicate agents to make it easier to remove them.

    Editing these custom views in Kaseya is problematic because the view editor uses a form that essentially overwrites the query used by the view every time you use the save or save as button. The Kaseya export feature also can't export the custom part of these views.

    I'm thinking of configuring the initial view permissions so that nobody can accidentally change them.

  •  I haven't taken the time to try and pull a custom view yet, but here's a quick SQL query that will just get you the machine names with duplicate Mac Addresses.

    Select
    MachineId, macAddr
    FROM dbo.vAuditMachineSummary
    JOIN
    (SELECT macAddr as dupMacs
    FROM dbo.vAuditMachineSummary
    GROUP BY macADDR having count(agentGuid) > 1) Dups on dupMacs = macAddr
    ORDER by macAddr



    Works better if you include the macADDR field in the query so you can see which ones are dupes of what...
    [edited by: Jonathan at 11:51 AM (GMT -8) on Mar 4, 2015]
  • One of my oft-meant but never executed projects, is a view that will find machines that have a VSA agent name not equal to the machine's name....I have a client that is wont to rename workstations as staff change, and so the machine that was "ABC" yesterday becomes "DEF" today - which tends to cause no end of confusion.

    ..In the mean time, there are heaps of good SQL queries in my Kaseya NOC project - see community.kaseya.com/.../17555.aspx just have a fish through the PHP files to find plenty of SQL; for example there is code for the "active remote control sessions" that works correctly for both KRC and classic RC, on any VSA version.

  • Jonathan You are a Legend! Smile

    Thanks, so simple I can kick myself, here is the complete view query for anybody that wants it;

    SELECT     agentGuid, agentGuidStr, machName, groupName, displayName, partitionId
    FROM         dbo.machNameTab AS a
    WHERE     (partitionId = 1) AND (agentGuid IN
                              (SELECT     dbo.vAuditMachineSummary.agentGuid
                                FROM          dbo.vAuditMachineSummary INNER JOIN
                                                           (SELECT     macAddr AS dupMacs
                                                             FROM          dbo.vAuditMachineSummary AS vAuditMachineSummary_1
                                                             WHERE      (macAddr LIKE '%-%')
                                                             GROUP BY macAddr
                                                             HAVING      (COUNT(agentGuid) > 1)) AS Dups ON Dups.dupMacs = dbo.vAuditMachineSummary.macAddr))

    I tweaked it a bit to only show agents with MAC addresses using this "WHERE (macAddr LIKE '%-%')".

    I also have noticed that on some rare instances virtual machines in different networks will have the same MAC address possibly because somebody set the MAC address manually or copied the virtual machine as a template.

    Thanks again Yes



    typo
    [edited by: HardKnoX at 2:35 PM (GMT -8) on Mar 4, 2015]
  • Hi Craig

    I gave it a quick try as I thought I could use a view that could filter computers where the computer name and agent name does not match and I came up with this;

    SELECT     agentGuid, agentGuidStr, machName, groupName, displayName, partitionId
    FROM         dbo.machNameTab AS a
    WHERE     (partitionId = 1) AND (agentGuid IN
                              (SELECT     agentGuid
                                FROM          dbo.vAuditMachineSummary AS va
                                WHERE      (MachineId NOT LIKE ComputerName + '%')))

  • - Glad to see I could help you out for a change, I can't tell you how many of your posts have been helpful to me :)  

  • This is an excellent thread, thank you.

  • Hi Ryan or HardKnoX,

    Seems awesome.

    Could I ask a rookie question?

    The view you are talking about is a "SQL view" or "Machine Filter View" based on agent properties?

    Is it possible to transform the former into the later? How?

    Thanks,

    Jose

  • I suspect you won't be able to do a direct copy from a "SQL view" to "Machine view filter" but you could use some of the elements(table/view and fields) from the "SQL views" to give you an idea how to make more advance "Machine view filters".

  • I assume we'll need to re-build these after VSA updates?  Ours aren't working and we have updated several times since they were first installed and working.

  • Anytime you do a Reapply Schema you have to go back into the database and update the view, this happens every time you install an update or upgrade your VSA. Its a bit annoying but that is the risk of customizations like this, the only alternative is to put it in as a feature request and hope that they add it in, in a future release.

  • Hi all, these are great. However, while I'm pretty handy at procedures and policies, this is a bit over my head.

    How would I go about using one of these views? I have access to our SQL server, but am not that experienced with SQL.

    Could someone point my in the right direction?