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, partitionIdFROM dbo.machNameTabWHERE (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.
HardKnoX 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, macAddrFROM dbo.vAuditMachineSummaryJOIN (SELECT macAddr as dupMacs FROM dbo.vAuditMachineSummary GROUP BY macADDR having count(agentGuid) > 1) Dups on dupMacs = macAddr ORDER by macAddr
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!
Thanks, so simple I can kick myself, here is the complete view query for anybody that wants it;
SELECT agentGuid, agentGuidStr, machName, groupName, displayName, partitionIdFROM dbo.machNameTab AS aWHERE (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.
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, partitionIdFROM dbo.machNameTab AS aWHERE (partitionId = 1) AND (agentGuid IN (SELECT agentGuid FROM dbo.vAuditMachineSummary AS va WHERE (MachineId NOT LIKE ComputerName + '%')))
HardKnoX - 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,
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?
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?