1. BrightGauge Support
  2. Community
  3. Community - Community Queries for SQL based Datasets

Kaseya R8+ On Premise - Remote Control Log (BG4)

Avatar
Thomas Bresse

Since Kaseya R8, there's a new dataset with the new Kaseya remote control logs.

It allows you to list number of remote session you've made, time spent in remote sessions by technician or customer. And with the new BG4 quick refresh, you can also see in real time current remote sessions.

Here's the query for BG4, my first one :

SELECT DISTINCT

NEWID() AS id,

krc.agentGuid,

krc.MachineId,

krc.ComputerName,

krc.ReverseGroupName,

krc.GroupName,

krc.OperatingSystem,

krc.OSInformation,

krc.CurrentUser,

krc.LastLoggedOnUser,

krc.startTime,

krc.adminName,

krc.lastActiveTime,

krc.sessionType,

krc.status,

CAST((datepart(hh, krc.duration)+datepart(mi, krc.duration)/60.0) as float) as 'Duration',

krc.date,

CASE

WHEN ((krc.OperatingSystem = 'Windows 2000' and krc.OSInformation not like 'Professional%') OR krc.OperatingSystem = 'Windows 2003' OR krc.OperatingSystem = 'Windows 2008' OR krc.OperatingSystem = 'Windows 2012') THEN 'Server'

ELSE 'Workstation'

END AS 'Machine Type'

FROM [KaseyaRemoteControl].[vRemoteControlLog] krc INNER JOIN vMachine vm ON krc.agentGuid = vm.agentGuid

INNER JOIN machGroup mg ON mg.reverseName = vm.groupName

INNER JOIN kasadmin.org k ON k.id = mg.orgFK

I have made it for 3.1 as well if anyone needs.

Thomas BRESSE

BeMSP

Didn't find what you were looking for?

New post
Would you like to vote for this feature?
0 out of 0 found this helpful

Comments

1 comment

Sort by Date Votes
  • Avatar
    Integrations Team

    So it looks like for some kaseya has changed the duration field. Here's a query that works if you get a datetime (or other error)

    SELECT DISTINCT
    NEWID() AS id,
    krc.agentGuid,
    krc.MachineId,
    krc.ComputerName,
    krc.ReverseGroupName,
    krc.GroupName,
    krc.OperatingSystem,
    krc.OSInformation,
    krc.CurrentUser,
    krc.LastLoggedOnUser,
    krc.startTime,
    krc.adminName,
    krc.lastActiveTime,
    krc.sessionType,
    krc.status,
    CAST(LEFT(krc.duration, CHARINDEX(':', krc.duration) - 1) + Right(LEFT(krc.duration, CHARINDEX(':', krc.duration) + 2),2)/60.00 as decimal(18,2)) as 'Duration',
    krc.date,
    CASE
    WHEN ((krc.OperatingSystem = 'Windows 2000' and krc.OSInformation not like 'Professional%') OR krc.OperatingSystem = 'Windows 2003' OR krc.OperatingSystem = 'Windows 2008' OR krc.OperatingSystem = 'Windows 2012') THEN 'Server'
    ELSE 'Workstation'
    END AS 'Machine Type'
    FROM [KaseyaRemoteControl].[vRemoteControlLog] krc
    INNER JOIN vMachine vm ON krc.agentGuid = vm.agentGuid
    INNER JOIN machGroup mg ON mg.reverseName = vm.groupName
    INNER JOIN kasadmin.org k ON k.id = mg.orgFK

    0