K1000 Last Sync Reporting
Hi All,
Trying to develop a report for all computers that haven't synced back in the last 40 days or greater. Have the report working fine - however it displays ALL computers that haven't reported back, not just the ones that haven't reported in 40 days or more.
Below is the syntax i'm using at the moment, can someone please assist?
select DATE_FORMAT(LAST_SYNC,'%W %m-%d-%Y') as LAST_SYNC, MACHINE.NAME as MACHINE_NAME, SYSTEM_DESCRIPTION, IP, MAC, REPLACE(MACHINE.USER_LOGGED,'\\','\\\\') as USER_LOGGED, CS_DOMAIN, LAST_SYNC as LAST_SYNC_TIME from MACHINE where (CURDATE() - INTERVAL 40 DAY) > 40 order by LAST_SYNC_TIME
0 Comments
[ + ] Show comments
Answers (1)
Answer Summary:
Please log in to answer
Posted by:
dugullett
10 years ago
Try using this.
select DATE_FORMAT(LAST_SYNC,'%W %m-%d-%Y') as LAST_SYNC, MACHINE.NAME as MACHINE_NAME, SYSTEM_DESCRIPTION, IP, MAC, REPLACE(MACHINE.USER_LOGGED,'\\','\\\\') as USER_LOGGED, CS_DOMAIN, LAST_SYNC as LAST_SYNC_TIME from MACHINE where LAST_SYNC > DATE_SUB(NOW(), INTERVAL 40 DAY) order by LAST_SYNC_TIME
Comments:
-
Hi Dugullett - thanks for your help, however it didn't work... still listing all computers syncing, not just 40 days or older. I've attached a screenshot if it helps. Thanks http://postimg.org/image/r4yhyo8pd/ - mrjas89 10 years ago
-
It's working for me. I changed the INTERVAL to 1 MINUTE just to test. It only returned 25 rows out of 18,000 machines. I also cannot see your screenshot. It's blocked here at work.
where LAST_SYNC > DATE_SUB(NOW(), INTERVAL 1 MINUTE) - dugullett 10 years ago
-
Hi Dugullett, i'm using K1000 v5.4.76847,
The code i'm using is below:
select DATE_FORMAT(LAST_SYNC,'%W %m-%d-%Y') as LAST_SYNC, MACHINE.NAME as MACHINE_NAME, SYSTEM_DESCRIPTION, IP, MAC,
REPLACE(MACHINE.USER_LOGGED,'\\','\\\\') as USER_LOGGED, CS_DOMAIN, LAST_SYNC as LAST_SYNC_TIME
from MACHINE
where LAST_SYNC > DATE_SUB(NOW(), INTERVAL 40 DAY)
order by LAST_SYNC_TIME
Appreciate the assistance. - mrjas89 10 years ago-
Sorry I guess I misread your original question. To get the ones that have not synced in the past 40 days change this line.
where LAST_SYNC > DATE_SUB(NOW(), INTERVAL 40 DAY)
to
where LAST_SYNC < DATE_SUB(NOW(), INTERVAL 40 DAY) - dugullett 10 years ago-
Many thanks Dugullett! that worked great! - mrjas89 10 years ago
