Archive for November 2011

Courion Tip – “Nag” emails

After a request has not been approved, we wanted to send out an email nagging the person saying that the request has, effectively, timed out and they need to do something. I could not find a suitable criteria inside of the courion workflows to accomplish this, so I did it inside of SQL server.

First off, set the time out comment inside the workflow to say “Timeout …..” (I just used the first 7 characters, so you can make the rest of it whatever you want it to say. I have blanked out the approval steps in the following screen shot.

Next, create a database trigger, so that when an approval step’s comment field is changed to “Timeout….”, a record gets created in another table with the information. This second table (MailsToBeSent – based on a article on the internet which I have now lost the link to) will then be used as a basis for emails going out via an SSIS process. Here is the trigger.

USE [ILM]
GO

CREATE TRIGGER [dbo].[SAP.Request.Add.Timeout]
ON [dbo].[ApprSteps]

AFTER UPDATE
AS
IF UPDATE (Comments)
Insert into [ILM_PROFILE].[dbo].[MailsToBeSent]
([MailID],[From],[TO],[CC],[BCC],[Subject],[Body],[IsHTMLFormat],[Priority],RequestID,DateTimeAdded)
VALUES
(
(SELECT MAX(MAILID)+1 from [ILM_PROFILE].[dbo].[MailsToBeSent]),
(SELECT [ParameterValue] FROM [ILM_PROFILE].[dbo].[ConfigurationParameter] where ParameterName =’NoReplyFromEmailAddress’),
‘noreply@company.com’,’noreply@company.com’,’noreply@company.com’
–‘subject’
–,(SELECT REQUESTID from INSERTED),
,’Request Escalation’
–‘body’
,’This is to inform you that request no. ‘ + CAST((SELECT REQUESTID from INSERTED) AS VARCHAR(10))
+ ‘ for user creation is still pending after ‘
,’True’,1
,(SELECT REQUESTID from INSERTED)
, GetDate())
GO

Advertisements

Posted November 22, 2011 by mmdmurphy in Courion Tip

Tagged with

Virtual Router – wifi sharing

http://virtualrouter.codeplex.com/

Posted November 11, 2011 by mmdmurphy in Uncategorized

Courion Tip – HTML ticketing results for the last hour

Depending on what your ticketing table looks like, here you go…

Select ‘<TR><TD>’ as EXR0, Date,'</TD><TD>’ AS EXR1,UserActedOn,'</TD><TD>’ AS EXR2,TargetID,'</TD><TD>’ AS EXR3,ActionReturnMessage,'</TD></TR>’ AS EXR6 from ticketing
where Date >CAST(SYSDATETIME() as datetime) -.04
order by Date desc

This will give you only the results for the last hour (0.04 days)

Posted November 10, 2011 by mmdmurphy in Courion Tip

Tagged with

How to Make Custom iPhone Ringtones for Free

http://www.ehow.com/how_2160460_custom-iphone-ringtones-free.html

Posted November 8, 2011 by mmdmurphy in Uncategorized

Courion Tip – Emails and Approvals

When a request is submitted, the email for the first approver should go out. It does.

However, the email for the SECOND approver step shouldn’t go out until the FIRST approval step is complete.

Here’s how….

First of all, if possible, make your Action Settings, Approval Community resolve to an active directory user name if possible. For instance, I have a custom macro called Manager_Approval. This resolves to the active directory name of the manager of the provisionee community.

SELECT AD_ACCOUNT_NAME FROM PROFILE WHERE EMP_ID = (SELECT MGR_ID FROM PROFILE WHERE EMP_ID = ‘%Provisionee Community.User%’)

Now, for the Global Settings, User Classifications, I have %Auth Step 1. Account Name%.

In other words, the first approver is their manager. When their manager logs in, it ‘matches it up’ and knows that this person needs to approve the request.

For the next approval community, I entered the text “SECURITY_APPROVAL”. Now, we need something that will return the phrase “SECURITY_APPROVAL” when someone in that AD group logs in.  I have a custom Macro called “Security_Approval” which is defined as

SELECT ‘SECURITY_APPROVAL’ FROM STAGING_AD WHERE (memberOf LIKE ‘%security_approvers%’) and sAMAccountName like ‘%Auth Step 1.Account Name%’

Now, when someone in the Security_Approvers  active directory group logs in, that text string will be generated, and match the approval step, and they will be able to approve that step. Yes, there are probably other ways to resolve this, but it works for me.

Finally, approval step 3…. This is the ‘owner’ of the system that access is being requested on, so I created another custom macro that resolves to the active directory name of the owner of that TargetID.  If you are wondering why I don’t need another user classification, you are on the right path. I ALREADY have a user classification that resolves to the user’s active directory name = Auth Step 1, so I don’t need another one. And that’s why I strongly suggest you resolve everything you can to a user’s active directory name.

Now, for detecting which steps have been completed. Sorry, Courion, but I have NO idea why you don’t make this easier. A simple set of ApprovalStep1Complete type triggers would have been nice. Instead I finally realized I needed to “roll my own”

My approver step 1 is manager approval, so I created this custom macro: Manager_Approved and set it to:

SELECT StepStatus from ApprStepsView where ApprovalStepID = (Select top 1 ApprovalStepID FROM ApprStepsView where RequestID = ‘%CURRENT_REQUEST_ID%’ order by ApprovalStepID asc)

and if you look at the ApprStepsView, you will see it change to “Approved” when this approval step is complete.

Now, we need to pair that with a condition, call it “Manager_Approved” (no conflict since one is a condition, and one is a macro). This condition is set as an Active Script Condition Evaluator, and is set to “%Custom Macro.SAP_Approval_Manager_Approved%”=”Approved”

Now, when the manager approves it, Approved=Approved, and the system knows the first step is approved.

Custom Macro 2 – Security_Approved:

SELECT StepStatus from ApprStepsView where ApprovalStepID = (Select top 1 ApprovalStepID FROM ApprStepsView where RequestID = ‘%CURRENT_REQUEST_ID%’ order by ApprovalStepID asc)+1

Condition 2:

“%Custom Macro.SAP_Approval_Security_Approved%” = “Approved”

We don’t need a custom macro/condition for Approval Step 3…

Now, on to the notifications:

Manager.Approval is set to “Request submitted for approval” event and not condition.

Security.Approval is set to “Request Pending Approval” and the Manager_Approved condition

System.Owner is set to “Request Pending Approval” and the “Security_Approved” condition.

Request.Completed is set to the Resource Success Event.

Posted November 1, 2011 by mmdmurphy in Courion Tip

Tagged with