Report Center Date Format

[Aenabs 0]

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.

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 ()

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

I changed the expression for the field ComputerDateLastContact

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

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;

 

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 November 12, 2018 by Aenabs

[Gavsto 103]

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

 

DATEDIFF(NOW(), ComputerDateLastContact) > 30)

 

[Aenabs 0]

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