Possible to run a report for uptime > 10 days and email the user?
We have a big problem with users never rebooting their computers. We cannot do a force reboot once a week (which i'd love to do!). I can create a report that has the users with uptime > X amount of days. Is there a way to get this report to also email the users that they have not restarted and need to do so? I know this is probably a long shot but thought i'd check.
thanks!
-
This chosen answer seems to no longer work in 6.4. Whenever I put this query or another similar one I wrote into the SQL Query box for the rule I click on "View Ticket Search Results" and get a SQL error. It seems the new version of this is encapsulating the SQL query within a larger SELECT that is specific to whichever queue you're building the ticket rule in. Other than using another server to do the query and do the emailing, can this still be achieved using the K1000? - tylerpenn 8 years ago
Answers (2)
Here is another suggestion, if your usernames match an email address (e.g. username = tsmtih and email is tsmith@SomeDomain.com) you can combine a SQL query with the service desk ticket rules.
Using this SQL (or something like it)
Select
concat(ORG1.MACHINE.USER,'@someDomain.com') As EMAILCC,
ORG1.MACHINE.USER_FULLNAME As USER_FULLNAME,
Unix_Timestamp(Now()) - Unix_Timestamp(ORG1.MACHINE.LAST_SYNC) As
LAST_SYNC_TIME,
Unix_Timestamp(ORG1.MACHINE.LAST_SYNC) As LAST_SYNC_SECONDS,
ORG1.MACHINE.NAME as machineName
From
ORG1.MACHINE Left Join
KBSYS.KUID_ORGANIZATION On KBSYS.KUID_ORGANIZATION.KUID = ORG1.MACHINE.KUID
Left Join
KBSYS.SMMP_CONNECTION On KBSYS.SMMP_CONNECTION.KUID = ORG1.MACHINE.KUID And
KBSYS.KUID_ORGANIZATION.ORGANIZATION_ID = 1
Where
ORG1.MACHINE.LAST_REBOOT < Date_Sub(Now(), Interval 10 Day)
Head over to the Service Desk, custom ticket rules. Create a new ticket rule that runs daily using the SQL above.
Check the checkbox that reads, "Send an email for each result row"
Subject field = "Please reboot your computer" or whatever you want it to say
Email column = "EMAILCC" without the quotes
Email Body = can say whatever you want. If you want to use values from the query above you have to prepend them with a $ (like php variables)
____________________________
If your usernames do not match email address the above is still possible, just a little more complicated. I haven't tested this, but I think it will work.
Basically you need to create a custom asset for your usernames that contains their email address. That is, two columns in the custom asset to contain the username in one that matches what Kace finds in the above query to an email address. For the custom asset you created, you can do a data import from some other system to get the usernames and email addresses in. You might also be able to just use the "Users" tab within the service desk, but I am uncertain about how to import data into "Users." Using the example above, if your username is tsmith and the email is tom.smith@somedomain.com then the record would look like this within the custom asset:
tsmith, tom.smith@somedomain.com
Now your SQL query needs to be modified above to join the machine table to the custom asset on the machine.user field and whatever is cooresponds to in your custom asset (perhaps ASSET_DATA_12.FIELD_46).
From there you can head over to create the custom ticket rule as defined above.
Good luck!
You can run this in a report.
SELECT NAME, LAST_REBOOT FROM MACHINE M WHERE LAST_REBOOT < DATE_SUB(NOW(), INTERVAL 10 DAY)
I'm not sure about the email. You can however turn this into a label, and accompany it with a script. Have that script pop up a message window every "X" minutes to constantly remind them during the day.
Comments:
-
Here's the label just in case.
select *, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS
from ORG1.MACHINE
LEFT JOIN KBSYS.KUID_ORGANIZATION ON KUID_ORGANIZATION.KUID=MACHINE.KUID LEFT JOIN KBSYS.SMMP_CONNECTION ON SMMP_CONNECTION.KUID = MACHINE.KUID AND KUID_ORGANIZATION.ORGANIZATION_ID = 1
where (MACHINE.LAST_REBOOT < DATE_SUB(NOW(), INTERVAL 10 DAY)) - dugullett 11 years ago -
In lieu of email you can set up an alert. Go to Reporting > Alerts > and target it to your label. Hopefully your pop up will annoy the user enough to reboot...although we have a message pop up every hour indefinitely after we send out patches and that doesn't always seem to help! - jegolf 11 years ago