Jump to content

Recommended Posts

Hi Guys,

I have been using Automate for the last 6 months and so far I have managed to improve my Knowledge and usage of the system.

We recently got Report Center installed and I have been working on what I thought to be a simple solution to the Monitor - Agents no checkin for last 30days.

image.thumb.png.699caf716ba5250b4a9d16f7fd5b9e58.png

Objective: Create an automated report that queries the Computer data and returns data (Agents no checkin for last 30days)

I have configured my data source to use the following; Computer table, I can pull information from all clients but cannot restrict this information to only return devices that have not checked in online using the field ()

image.png.1f21f61c802424c8ff4e3b222116c3ad.png

I have also tried the following but it has failed to return the required results.

I changed the expression for the field ComputerDateLastContact

image.png.6b586c104452d3f04bcfce81afabc2c2.png

I have added the following ComputerName as a calculated field and set the expression to below;

image.png.09d82b67db78a0eb95b5327e9b971c3c.png

But still no results.

To my surprise there is no clear location to restrict this info using SQL codes such as (', Format(AddDays(Now(),-30), 'MMMM d - '), Format(Now(), 'MMMM d, yyyy)')).

I also tried to add this to the Where SQL statement in the Query Editor;

image.png.b25e62a07f3ebaeae3d7015de599a6ad.png

 

Please I need help, I have been in touch with ConnectWise support and their response was not great - they only assist with default reports not custom reports.

I believe in normal SQL application this would be as easy as - SELECT Computers FROM computers, location, client LEFT JOIN by client WHERE computer.ComputerDateLastContact > 30days

I am looking to restrict the data pulled from the database, which I believe is similar to the filter screen on connectwise Report list, see below. However no one in connectwise support is trained extensively to advise.

I feel this is a simple filter that can be applied to the report.

Any directions or help would be great

Edited by Aenabs

Share this post


Link to post
Share on other sites

Putting the following in the "Where SQL" in your last screenshot with no other modifications should work:

 

DATEDIFF(NOW(), ComputerDateLastContact) > 30)

 

  • Thanks 1

Share this post


Link to post
Share on other sites
15 hours ago, Gavsto said:

Putting the following in the "Where SQL" in your last screenshot with no other modifications should work:

  


DATEDIFF(NOW(), ComputerDateLastContact) > 30)

 

Thanks Gavsto - This worked a treat, however after posting this topic - I couldn't give up and keep trying various methods and got it work using this code "ComputerDatelastContact < DATE_SUB(NOW(),INTERVAL 30DAYS)" in the 'Where SQL' in my last screenshoot

 

Thanks

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

×