/build/static/layout/Breadcrumb_cap_w.png

Help creating report

So I built a report that tells me what laptops have not connected since a certain date.  What I would like to do is get it to show me what laptops have not connected within a certain time period such as 7, 14, 21, 30 days.  Can anyone help me figure out what I need to put in within my WHERE statement to allow for this to work.


SELECT MACHINE.NAME AS SYSTEM_NAME, MACHINE.USER_NAME, LAST_SYNC  FROM MACHINE  WHERE ((LAST_SYNC < '2019-02-11 00:00:00') AND (CHASSIS_TYPE = 'laptop'))  ORDER BY SYSTEM_NAME, LAST_SYNC

0 Comments   [ + ] Show comments

Answers (2)

Answer Summary:
Posted by: Ziggi 5 years ago
Blue Belt
1

Hope this helps.


SELECT LAST_SYNC, MACHINE.NAME AS SYSTEM_NAME, MACHINE.USER_NAME  FROM MACHINE  WHERE ((CHASSIS_TYPE = 'Laptop') AND ((TIMESTAMP(LAST_SYNC) > NOW() OR TIMESTAMP(LAST_SYNC) <= DATE_SUB(NOW(),INTERVAL 30 DAY))) AND ((TIMESTAMP(LAST_SYNC) > NOW() OR TIMESTAMP(LAST_SYNC) <= DATE_SUB(NOW(),INTERVAL 21 DAY))) AND ((TIMESTAMP(LAST_SYNC) > NOW() OR TIMESTAMP(LAST_SYNC) <= DATE_SUB(NOW(),INTERVAL 14 DAY))) AND ((TIMESTAMP(LAST_SYNC) > NOW() OR TIMESTAMP(LAST_SYNC) <= DATE_SUB(NOW(),INTERVAL 7 DAY))))  ORDER BY LAST_SYNC


Comments:
  • I am trying to turn this into 4 reports not just 1. I need to figure out how to get it to show just 1 of the 7 time frames - tbingeman 5 years ago
Posted by: tbingeman 5 years ago
Senior White Belt
1

Top Answer

Ziggi using your query as something to work with, I was able to figure it out.  Here is the way I have it looking and running for the 30 day query.


SELECT
LAST_SYNC,
MACHINE.NAME AS SYSTEM_NAME,
MACHINE.USER_NAME
FROM
MACHINE
WHERE
(
(CHASSIS_TYPE = 'Laptop')
AND (
(
TIMESTAMP(LAST_SYNC) > NOW()
OR TIMESTAMP(LAST_SYNC) <= DATE_SUB(NOW(), INTERVAL 30 DAY)
)
)
)
ORDER BY
LAST_SYNC
 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ