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