In the following example, we’re going to create a dashboard which can be displayed on a dedicated monitor and will show the status of Helpdesk incidents in ‘relatively’ real-time with colors and links to the Altiris Helpdesk.
In this scenario, were going to assume that the helpdesk environment uses a three tier support system, assigns the owner of the incident to one of the support Tiers and schedules a due date to the incident which will change depending on the owner of the incident.
For example:
- Tier 1 has a 15 minute time to resolution period
- Tier 2 has a 60 minute time to resolution period
- Tier 3 has a 120 minute time to resolution period
(one way to automatically schedule and set start and due timers would be to use an incident rule)
It is important to know that the web parts utilized by the Altiris portal pages can interpret HTML. So how can you bring in HTML to the web part using the results from a report? Case Statements!
STEPS:
- Create your report
- Create a report using the following sql query:
USE Altiris_Incidents (
select 'Id' = '<a href="http://localhost/AexHD/worker/default.aspx?cmd=viewItem&id=' + CAST(wv.workitem_number AS varchar(5)) + '" target=_blank>' + '<font color="blue" size=3><b>' + CAST(wv.workitem_number AS varchar(5)) + '</b></font></a>',
wv.workitem_title AS Title,
wv.assigned_to_worker_name AS Worker,
CASE(wv.owned_by_worker_full_name)
WHEN 'Tier 1' THEN '<font size =3><b>Tier 1</b></font>'
WHEN 'Tier 2' THEN '<font color="blue" size=3><b>Tier 2</b></font>'
WHEN 'Tier 3' THEN '<font color="red" size=3><b>Tier 3</b></font>'
ELSE wv.owned_by_worker_full_name
END AS Owner,
wi.category_tree_value AS Category,
'minutes_remaining' = DATEDIFF(mi,GETDATE(),wi.due_on),
'tier status' = CASE(wv.owned_by_worker_full_name)
WHEN 'Tier 1' THEN '<font color="red" size=3><b>Tier 1 Expired</b></font>'
WHEN 'Tier 2' THEN '<font color="red" size=3><b>Tier 2 Expired</b></font>'
WHEN 'Tier 3' THEN '<font color="red" size=3><b>Tier 3 Expired</b></font>'
ELSE 'ERROR'
END
FROM altiris_incidents..workitem_detail_view wv
JOIN workitem wi
on wv.workitem_number = wi.number
WHERE (wv.workitem_is_last = 1
AND wi.is_last = 1)
AND (wv.workitem_status_lookup_value = 'open'
AND wv.owned_by_worker_full_name = 'Tier 1'
OR wv.owned_by_worker_full_name = 'Tier 2'
OR wv.owned_by_worker_full_name = 'Tier 3')
AND wv.workitem_is_scheduled = 1
AND DATEDIFF(mi,GETDATE(),wv.workitem_due_on) <= 0
UNION
select 'Id' = '<a href="http://localhost/AexHD/worker/default.aspx?cmd=viewItem&id=' + CAST(wv.workitem_number AS varchar(5)) + '" target=_blank>' + '<font color="blue" size=3><b>' + CAST(wv.workitem_number AS varchar(5)) + '</b></font></a>',
wv.workitem_title AS Title,
wv.assigned_to_worker_name AS Worker,
CASE(wv.owned_by_worker_full_name)
WHEN 'Tier 1' THEN '<font size =3><b>Tier 1</b></font>'
WHEN 'Tier 2' THEN '<font color="blue" size=3><b>Tier 2</b></font>'
WHEN 'Tier 3' THEN '<font color="red" size=3><b>Tier 3</b></font>'
ELSE wv.owned_by_worker_full_name
END AS Owner,
wi.category_tree_value AS Category,
'minutes_remaining' = DATEDIFF(mi,GETDATE(),wi.due_on),
'tier status' = CASE(wv.owned_by_worker_full_name)
WHEN 'Tier 1' THEN '<font color="green" size=3><b>Tier 1 In Progress</b></font>'
WHEN 'Tier 2' THEN '<font color="green" size=3><b>Tier 2 In Progress</b></font>'
WHEN 'Tier 3' THEN '<font color="green" size=3><b>Tier 3 In Progress</b></font>'
ELSE 'ERROR'
END
FROM altiris_incidents..workitem_detail_view wv
JOIN workitem wi
on wv.workitem_number = wi.number
WHERE (wv.workitem_is_last = 1
AND wi.is_last = 1)
AND (wv.workitem_status_lookup_value = 'open'
AND wv.owned_by_worker_full_name = 'Tier 1'
OR wv.owned_by_worker_full_name = 'Tier 2'
OR wv.owned_by_worker_full_name = 'Tier 3')
AND wv.workitem_is_scheduled = 1
AND DATEDIFF(mi,GETDATE(),wv.workitem_due_on) > DATEDIFF(mi,wv.workitem_start_on,wv.workitem_due_on) *.1
UNION
select 'Id' = '<a href="http://localhost/AexHD/worker/default.aspx?cmd=viewItem&id=' + CAST(wv.workitem_number AS varchar(5)) + '" target=_blank>' + '<font color="blue" size=3><b>' + CAST(wv.workitem_number AS varchar(5)) + '</b></font></a>',
wv.workitem_title AS Title,
wv.assigned_to_worker_name AS Worker,
CASE(wv.owned_by_worker_full_name)
WHEN 'Tier 1' THEN '<font size =3><b>Tier 1</b></font>'
WHEN 'Tier 2' THEN '<font color="blue" size=3><b>Tier 2</b></font>'
WHEN 'Tier 3' THEN '<font color="red" size=3><b>Tier 3</b></font>'
ELSE wv.owned_by_worker_full_name
END AS Owner,
wi.category_tree_value AS Category,
'minutes_remaining' = DATEDIFF(mi,GETDATE(),wi.due_on),
'tier status' = CASE(wv.owned_by_worker_full_name)
WHEN 'Tier 1' THEN '<font color="blue" size=3><b>Tier 1 Expiring</b></font>'
WHEN 'Tier 2' THEN '<font color="blue" size=3><b>Tier 2 Expiring</b></font>'
WHEN 'Tier 3' THEN '<font color="blue" size=3><b>Tier 3 Expiring</b></font>'
ELSE 'ERROR'
END
FROM altiris_incidents..workitem_detail_view wv
JOIN workitem wi
on wv.workitem_number = wi.number
WHERE (wv.workitem_is_last = 1
AND wi.is_last = 1)
AND (wv.workitem_status_lookup_value = 'open'
AND wv.owned_by_worker_full_name = 'Tier 1'
OR wv.owned_by_worker_full_name = 'Tier 2'
OR wv.owned_by_worker_full_name = 'Tier 3')
AND wv.workitem_is_scheduled = 1
AND (DATEDIFF(mi,GETDATE(),wv.workitem_due_on) >= 1
AND DATEDIFF(mi,wv.workitem_start_on,wv.workitem_due_on) *.1 >= DATEDIFF(mi,GETDATE(),wv.workitem_due_on)))
ORDER BY minutes_remaining
- Create your Web Part
- In the 6.5 console, go to Configure->Console->Portal Pages
- In the left pane right click the ‘Web Parts’ Folder and select ‘New Web Part’
- Name your Web Part
- Select ‘Results from report’ and choose the report you’ve created in Step 1.
- Check ‘Display in wide column’
- Check ‘Fixed height’ and set this to 800 (although we will override this later on)
- Now click on the ‘Show Preview’ button to display your formatted Web Part
- Now we need to format this Web Part so it will display using the entire browser window and refresh every 1 or 2 minutes, But first we’re going to need the URL and the GUID of this Web Part
- Right click within your Web Part preview and select ‘properties’
- Modify WebPartReportView.aspx
- Browse to C:\Program Files\Altiris\CustomConsole\Web\WebParts and copy WebPartReportView.aspx. Rename it customWebPartReportView.aspx
- Open customWebPartReportView.aspx with a text editor
i. Add the following within the <HEAD> tags: <meta http-equiv="refresh" content="120"> ( this will automatically refresh the page every 2 minutes)
ii. Modify the “PageSize” attribute to: PageSize="35" ( This may have to be adjusted depending on the resolution of your monitor)
iii. Save the file
- Create Shortcut
- Right click on your desktop and choose New->Shortcut
- Now add the url that was copied previously from your Web Part
Now name your shortcut and click finished.
That’s it! Click on your shortcut and you’ve got a ‘‘relatively’ real-time reporting dashboard with color and links*.
*Clicking on the Incident ID within the Web Part will bring up that specific ticket within the Altiris Helpdesk.
Just in case this site formats the html tags within the sql query, I have attached it.
ITS Delivers