Jump to content
DarrenWhite99

Computer Status and User Idle Time dataview

Recommended Posts

This dataview is basically the same as the "Computer Status" dataview, but I have added a column for Agent Idle Time. I find it helpful when I need to see quickly which users are on their systems, and which machines are not being used or have long idle times so that I can work without disrupting an active user. For only 1 column difference, I have found it to be a very useful dataview and refer to it often.

2017-06-29 - I have updated the SQL query to be compatible with Control Center. No SQLYog Needed!.

2017-08-01 - I have added a column, `Agent Additional Users`. This shows any other logins reported by LabTech, such as on a Terminal Server. The import will rename the original Dataview, so you can remove it when you are ready.

To import, just extract the .sql file from the zip. In Control Center select Tools->Import->SQL File. It will ask if you want to import 3 statements. This is normal.

When finished, select Tools->Reload Cache.

59ec944326fb1_Dataview-ComputerStatusandIdleTIme.png.19b47306e7630992b259f3dc43ebbffa.png

The attachment has been moved. See https://www.labtechgeek.com/files/file/25-agent-status-and-user-idle-time-dataview/

 

Edited by DarrenWhite99
Update to attachment location
  • Thanks 1

Share this post


Link to post
Share on other sites

There is no Field for the Agent Idle Time value (computers.idletime) in the Field Chooser for any stock Dataview. (Why I customized a Dataview to have it, and why I am sharing it). The only way to add a Field to a Dataview is to manipulate the FieldList column in the dataviews table or use the hidden/buggy LT Dataview Creator. You can find out about the Dataview Creator at https://www.labtechgeek.com/topic/90-dataview-creator/

 

The field definition I added is: IF(computers.`idletime`>0;;SEC_TO_TIME(computers.`idletime`);;\'Not Available\') as `Agent Idle Time`

 

To add this Dataview to your system you will need to execute the SQL query in a tool such as SQLYog, or in a script using the SQL EXECUTE function. You cannot import using the Control Center Tools->Import SQL command. Import using Control Center is now supported.

Edited by DarrenWhite99

Share this post


Link to post
Share on other sites

I imported the SQL, but it doesn't show up in my dataview, even after restarting the DB agent, and reloading the control panel.

Share this post


Link to post
Share on other sites

It should be under the Status folder in your Dataviews. That is where the stock "Computer Status" dataview is located, and where you should find the "Computer Status and Idle" dataview. It should only be dependent on your Control Center, so reloading cache (or re-opening it) should be all that you need to do after importing. You do not need to restart the DB Agent.

Share this post


Link to post
Share on other sites

If you still have problems, if you are able to maybe try putting the SQL query into SQLYog. The LabTech Import function doesn't provide any real feedback, so if it won't work maybe SQLYog can explain why not with an error message.

Share this post


Link to post
Share on other sites

ran:

/*
SQLyog Community v9.50 
MySQL - 5.5.31-log 
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;

INSERT INTO `dataviews` (`DataViewID`, `Name`, `FieldList`, `ColumnList`, `HiddenList`, `SQLBody`, `IDColumn`, `SQLWhere`, `FolderID`, `ClientLink`, `ComputerLink`, `SortField`, `GroupField`, `SortOrder`, `FilterControl`, `FormatControl`, `LocationLink`, `IconControl`, `SystemPermission`, `UserClassName`, `GUID`, `SystemPermissionHigh`) VALUES('716','Computer Status and Idle','Contacts.ContactID as `ContactID`, CONCAT(Contacts.FirstName;;\' \';;Contacts.LastName) as `Contact`, Contacts.Email as `Contact Email`, Contacts.Phone as `Contact Phone`, Contacts.Cell as `Contact Cell`, Contacts.Address1 as `Contact Address1`, Contacts.Address2 as `Contact Address2`, Contacts.City as `Contact City`, Contacts.State as `Contact State`, Contacts.Zip as `Contact Zipcode`, Contacts.Fax as `Contact Fax`, Contacts.Pager as `Contact Pager`, Contacts.MSN as `Contact MSN`, Contacts.AIM as `Contact AIM`, Contacts.ICQ as `Contact ICQ`, Contacts.NetBiosName as `Contact Netbios`, Clients.ClientID as `ClientID`, Clients.Name as `Client Name`, Clients.Address1 as `Client Address`, Clients.City as `Client City`, Clients.State as `Client State`, Clients.Zip as `Client Zipcode`, Clients.Address2 as `Client Address2`, CONVERT(IF(Clients.SupportMins>0;;Clients.SupportMins;;IF(Clients.SupportMins=0;;\'None\';;\'Unlimited\')) using UTF8) as `Client SupportMins`, Clients.Phone as `Clients Phone`, Clients.Fax as `Clients Fax`, Clients.Country as `Clients Country`, Locations.LocationID as `LocationID`, CONCAT(Clients.Name;;\'/\';;Locations.Name) as `Client Location`, Locations.Name as `Location Name`, Locations.Address as `Location Address`, Locations.City as `Location City`, Locations.State as `Location State`, Locations.Zip as `Location Zipcode`, Locations.Phone as `Location Phone`, Locations.Fax as `Location Fax`, Locations.Country as `Location Country`, Locations.Router as `Location Router`, Locations.RouterPort as `Location Router Port`, Computers.Version as `Agent OS Version`, Computers.ServiceVersion as `Agent Version`, computers.Comment as `Agent Comment`, Computers.LastContact as `Agent Last Contact Date`, Computers.LastInventory as `Agent Last Inventory Date`, Computers.CPUUsage as `Agent CPU Usage`, Computers.TotalMemory as `Agent Memory Total`, Computers.MemoryAvail as `Agent Memory Avail`, Computers.RouterAddress as `Agent Router Address`, Computers.Uptime as `Agent Uptime`, Computers.DataIn as `Agent Bytes IN`, Computers.DataOut as `Agent Bytes Out`, Computers.MAC as `Agent MAC`, Computers.DateAdded as `Agent Install Date`, Computers.BiosName as `Agent Mainboard`, Computers.BiosVer as `Agent Serial Number`, Computers.BiosFlash as `Agent Bios`, Computers.Name as `Agent Name`, Convert(IF(INSTR(Computers.Username;;\';;\')>0;;LEFT(Computers.Username;;Instr(Computers.UserName;;\';;\')-1);;Computers.Username) using utf8) as `Agent User`, Computers.OS as `Agent Operating System`, Computers.Domain as `Agent Windows Domain`, Computers.BiosMFG as `Agent Manufacturer`, Computers.LocalAddress as `Agent IP Address`, Computers.AssetTag as `Agent Asset Tag`, Computers.AssetDate as `Agent Asset Date`, CONVERT(IF(Computers.LastContact>DATE_ADD(NOW();;interval -7 Minute);;\'Online\';;\'Offline\') using utf8) as `Agent Status`, CONVERT(IF(Computers.OS like \'%darwin%\';;\'True\';;\'False\') using utf8) as `Agent Apple`, CONVERT(IF(Computers.OS like \'%windows ce%\';;\'True\';;\'False\') using utf8) as `Agent Mobile`, CONVERT(IF(Computers.OS like \'%nix%\';;\'True\';;\'False\') using utf8) as `Agent Unix`, CONVERT(IF(Computers.BiosFlash like \'%portable%\';;\'True\';;\'False\') using utf8) as `Agent Laptop`, CONVERT(IF((Computers.Flags & 1)=1;;\'True\';;\'False\') using utf8) as `Agent FasTalk`, CONVERT(IF((Computers.Flags & 16)=16;;\'True\';;\'False\') using utf8) as `Agent Master PC`, CONVERT(IF((Computers.Flags & 32)=32;;\'True\';;\'False\') using utf8) as `Agent Monitors Running`, CONVERT(IF((Computers.Flags & 128)=128;;\'True\';;\'False\') using utf8) as `Agent Probe PC`, CONVERT(IF((Computers.Flags & 256)=256;;\'True\';;\'False\') using utf8) as `Agent ShadowProtect Detected`, Convert(IF(Computers.OS like \'%server%\';;\'Server\';;IF(Computers.BiosFlash like \'%portable%\';;\'Laptop\';;\'WorkStation\')) using utf8) as `Agent Type`, AgentComputerData.Reliablity as `Agent Reliability`, Computers.WindowsUpdate as `Agent Windows Update`, Computers.Windir as `Agent Windows Directory`, IF(computers.`idletime`>0;;SEC_TO_TIME(computers.`idletime`);;\'Not Available\') as `Agent Idle Time`','Client Name,Agent Name,Agent Status,Agent User,Agent CPU Usage,Agent Memory Total,Agent Memory Avail,Agent Uptime,Agent Bytes Out,Agent Reliability,Agent Idle Time,Agent Last Contact Date','','FROM ((((computers JOIN locations ON computers.locationid=locations.locationid) JOIN clients ON computers.clientid=clients.clientid) LEFT JOIN contacts ON computers.contactid=contacts.contactid) LEFT JOIN agentcomputerdata ON computers.computerid=agentcomputerdata.computerid)','Computers.ComputerID','','7','Computers.ClientID','Computers.ComputerID','Computer','Client Name','0','Agent Apple,0,0,,1|Agent Mobile,0,0,,1|Agent Unix,0,0,,1|Agent Laptop,0,0,,1|Agent FasTalk,0,0,,1|Agent Master PC,0,0,,1|Agent Monitors Running,0,0,,1|Agent Probe PC,0,0,,1|Agent ShadowProtect Detected,0,0,,1|','Agent CPU Usage,A,,,2,,0,,%,3,90,1,FF0000,,1|Agent Memory Total,A,,,1,,0,,mb|Agent Memory Avail,A,,,1,,0,,mb|Agent Uptime,A,,,1,,0,, mins|Agent MAC,A,,,2,,0,,|Agent Status,1,FFFFFF,EE0000,2,,0,,,1,Online,0,FFFFFF,00EE00,1|','Computers.LocationID','54|Agent Status|1,Online,49|','0','','6baf6956-058c-4a2c-baf6-fbe7d3780b0f','0');

 

got:

No query(s) were executed. Please enter a query in the SQL window or place the cursor inside a query.

Share this post


Link to post
Share on other sites

Ok scythe000... I updated the Query so it now can be imported directly in Control Center.. Just extract the SQL, select Tools->Import->SQL, confirm you want to import the 2 statements to the database, and it should be good to go.

Share this post


Link to post
Share on other sites

That's odd, I believe I imported it directly into the control center before you updated it and it works fine.

Share this post


Link to post
Share on other sites

A huge thanks for this. Now if you figure out a way to screen connect from these types of views from machine line item context menu to screenconnect I love you forever. :)

Share this post


Link to post
Share on other sites

The right click menu in the Dataview has the LabVNC option, but not the updated ConnectWise Control option. Looks like a feature request is needed! Visit https://cp.labtechsoftware.com/#/forms-library/enhancements and request that this be added to Dataviews in Control Center.

 

Also, I updated the dataview today. See the first post (http://www.labtechgeek.com/forum/viewtopic.php?f=25&t=3418) to read about it and download the update.

Share this post


Link to post
Share on other sites

So agent idle time is how long the computer is not being used? And if it shows not available that means its actively used?

Share this post


Link to post
Share on other sites

If there is no user logged in it will have no idle time, and no user... But if it shows a user and no idle, then it's active so don't reboot them. Hahahaha....

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×