/build/static/layout/Breadcrumb_cap_w.png

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!


1 Comment   [ + ] Show comment
  • 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)

Answer Summary:
Posted by: Jbr32 10 years ago
10th Degree Black Belt
1

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!

Posted by: dugullett 10 years ago
Red Belt
0

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 10 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 10 years ago
 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ