K1000 Reporting - Help with a custom report
Guys
I've created a custom report for our workstations and last shutdown time. The report is pulling back all the infromation I need , however , I want to open pull back workstations that have a shutdown time greater than 7 days when the report is run.
Here's my report syntax.
select LABEL.NAME as LABEL_NAME, MACHINE.NAME as MACHINE_NAME, MACHINE.USER_LOGGED as USER_LOGGED
, ORG1.MACHINE.ID
, LAST_ShUTDOWN
from ORG1.MACHINE
left outer join ORG1.MACHINE_LABEL_JT on MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID
left outer join ORG1.LABEL on MACHINE_LABEL_JT.LABEL_ID = LABEL.ID
where LABEL.TYPE !='hidden' and LABEL.NAME = 'M_Desktop' or LABEL.NAME = 'M_Laptop'
I'm looking to add some line to open pull back machines with a greater shutdown that 7 days. Anybody provide any help?
Answers (2)
Add the following line to the where clause of your query:
and LAST_SHUTDOWN < date_sub(now(), interval 7 day)
This should pull those records with LAST_SHUTDOWN timestamps before 7 days ago.
Im not sure it's worked fully , as I can see machines with shutdown times for today and yesterday but the report it only pulling back 87 machines (without the line you gave me it was 148) - FergieMan 10 years ago
where
LABEL.TYPE !='hidden'
and
(LABEL.NAME = 'M_Desktop' or LABEL.NAME = 'M_Laptop')
and
LAST_SHUTDOWN < date_sub(now(), interval 7 day) - grayematter 10 years ago