/build/static/layout/Breadcrumb_cap_w.png
02/25/2019 218 views

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
Answer Summary:
0 Comments   [ + ] Show comments

Comments


Answer Chosen by the Author

0

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
Answered 02/26/2019 by: tbingeman
Senior White Belt

All Answers

0

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

Answered 02/26/2019 by: Ziggi
Blue Belt

  • 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