Using Workflow Solution to Update Time Spent in Altiris HelpDesk
Updated: 21 May 2010 | 4 comments
This issue has been solved. See solution.
Has anyone been able to update time spent in Altiris Helpdesk using a component from workflow solution?
Using custom components (like SQL) perhaps?
Any idea will be greatly appreciated...
Thanks,
discussion Filed Under:
Comments
If you're talking about
If you're talking about updating the time spent on a ticket, yes, I've helped someone do exactly that via a generated SQL component.
Some important things to know: ticket info is stored in the WORKITEM table. The table is historical (i.e. a ticket could have many rows), and the is_last column = 1 indicates the latest and greatest update.
A previous customer used the Update Ticket component, but noticed that the "total time spent" on the ticket wasn't getting updated. We solved the problem by using a generated SQL component immed. after the Update Ticket component that executed the following:
UPDATE WORKITEM
SET minutes_spent = CAST(@MINUTES_SPENT AS INT),
Total_minutes_spent = ISNULL(total_minutes_spent, 0) + CAST(@MINUTES_SPENT AS INT)
WHERE Number = CAST(@NUMBER AS INT) AND is_last = 1
The generated comp took two input parameters: @NUMBER being the ticket ID, and @MINUTES_SPENT being the number of minutes worked on the ticket in that go-around.
Hope this helps... I can provide more info if contacted.
Thanks for posting, I still need more help!
Thanks for posting. I'm kind of new to SQL but have been building some components to use in workflow. Can you provide the complete script that would point to [my SQL table]?
For example, I see that it will:
SET minutes_spent = CAST(@MINUTES_SPENT AS INT)
But how does the script know which table to use?
Thanks a bunch!
Sure, when you start a new
Sure, when you start a new generated script component, first provide a valid connection string pointing to the appropriate Altiris_Incidents database on your NS.
Next, where it asks for SQL, insert the following exactly:
UPDATE WORKITEM
SET minutes_spent = CAST(@MINUTES_SPENT AS INT),
Total_minutes_spent = ISNULL(total_minutes_spent, 0) + CAST(@MINUTES_SPENT AS INT)
WHERE Number = CAST(@NUMBER AS INT) AND is_last = 1
Note: the WORKITEM is the table we're modifiying, specifically the two columns "minutes_spent" and "total_minutes_spent". The WHERE statement isolates the "latest" row in the history of rows for the ticket.
Then, click Find Input Parameters. The generator should detect @MINUTES_SPENT and @NUMBER. These will be the inputs to this generated components: the former will be the # of minutes to update the ticket by, the latter is the ticket ID.
You should be able to click through the remaining screens until you have to name it. What's important to note is that this generated SQL component returns NO data... it modifies data already existing in the database.
A generated SQL component can execute any SQL provided to it, as long as it's well-formed, and the user your connecting as has sufficient priveleges.
Ah, Got it...
I missed the part where WORKITEM was the actual table.
It works flawlessly. Thanks a lot, this really helped..
Andy
Would you like to reply?
Login or Register to post your comment.