Kaseya Community

Send and email based on a custom field value

This question has suggested answer(s)

is it possible to generate an email based on a value in a custom field? essentially i am using a powershell script to update  custom field with the last time  windows kb installed. ive been able to get this into a custom field to filter and report off of. the next step i'd like to alarm/email based on this field. is that possible to do?

All Replies
  • Hello ndaddona,

    If you are using Agent Procedures you can read the content of a Custom Field with something like that:

    Create a Step GetVariable

    Choose "SQL View Data" and use this as SQL.

    (SELECT CONCAT(m.machName,CHAR(46),m.groupName) AS Machine_GroupID, a.fieldName, c.fieldValue FROM auditRsltManualFields  a INNER JOIN auditRsltManualFieldValues  c ON a.id=c.fieldNameFK INNER JOIN machNameTab  m ON c.agentGuid = m.agentGuid WHERE m.machName IS NOT null and a.id = 914168169449161) MyView/FieldValue

    Replace the ID with the correct ID of the field you want to query.

    You can find which one it is by running this SQL first

    select * FROM auditRsltManualFields

    This will place the Custom Field Value into a Variable that you can name as you like.

    Once that's done, add a SendMail step and if you named your Variable: MyFieldValue (for example) you can just output it like that:

    #MyFieldValue#

    The reason why we are using the numeric ID instead of a more user friendly field name is because SQL View Data does not accepts quotes hence string parameters (same reasons we are using a CONCAT to add the "." with a CHAR(46) command..

    Best Regards

  • another (perhaps less elegant) way would be to:

    -Create a kaseya view based on the custom field value

    -Create a Policy based on the view you created (Policy Management module)

    -Run an Agent Procedure that sends the alert email as part of that policy. (or anything else you want to do)

  • Maybe I'm misunderstanding you but it sounds to me like you're putting a date into a custom field and you want to email if that date is older than you'd like? If so, Kaseya procedures don't currently provide any way to do date comparisons that I know about. What about just checking the date while in Powershell and, if it's older than you'd like, use Powershell to send the email? Or, what I do in my procedure, is to pass the date in my custom field to a vbscript and do a "datediff" and, if the result is not good, the vbscript sends the email. Easy peasy.

  • Hello Zippo,

    To have Kaseya do Date Comparison you can write the date in the custom field as follows:

    yyyy-mm-dd

    Then use SQLView and SQL Server function to compare dates.

    For example:

    (SELECT DATEDIFF(day, CAST(c.fieldValue as DATE), TODAY())

    FROM auditRsltManualFields  a INNER JOIN auditRsltManualFieldValues  c ON a.id=c.fieldNameFK INNER JOIN machNameTab  m ON c.agentGuid = m.agentGuid WHERE m.machName IS NOT null and a.id = 914168169449161)

    )

    This will return a number of day passed from the given date in the custom field.

    You could then use a Check Var step to verify the value is greater than x days to see if something is "overdue".

    To be absolutely sure of data types comparison you could move the check in SQL with something like

    (SELECT CASE WHEN DATEDIFF(day, CAST(c.fieldValue as DATE), TODAY()) > 30 THEN 1 ELSE 0 END

    FROM auditRsltManualFields  a INNER JOIN auditRsltManualFieldValues  c ON a.id=c.fieldNameFK INNER JOIN machNameTab  m ON c.agentGuid = m.agentGuid WHERE m.machName IS NOT null and a.id = 914168169449161)

    )

    And only check if the result is 1 or 0 inside the CheckVar.

    Best Regards

  • Ahhhh, Alessandro. Very cool. Thanks a lot for suggesting the SQL server functions. That had not occurred to me. Nice idea.

  • I think I would go about it a little bit different. If you are using Agent Procedures to run your powershell command and update a custom field, just add a line to compare it to a value using checkVar("#cSystemInfoManual.<Name_of_CF>) and compare it however you need to. Then, use send email, and include the agent label in the email so you know what machine this happened on ( that value is #vAgentLabel.machine_groupID#)

    I have an example of this if you would like something to go off of. I created this when we had software firewalls changing it's type from work to public, causing issues for users. I personally compare a custom field to the numbers that output in a text file from a cmd that is ran silently to output a status.

  • Hi ndaddona,

    I do this very thing with each maintenance cycle. One possibility is as follows:

    1. You create 2 custom fields

    2. Your kaseya procedure initiates your powershell script which updates the 1st custom field with the timestamp

    3. Create a second powershell script to read the value of the 1st custom field (your date) and perform compare then update the 2nd custom field with a 1 for true & 0 for false

    4. The next step in the kaseya procedure checks the vale of the second field, if 1 send an email & include the value of the 1st custom field. If 0 end procedure.

    Custom fields can be queried as follows: #vSystemInfoManual.customField#