I have created a report where you can get a overview of the status of every advertisement in the environment. This can be very useful for getting an overview on how well software distribution is working.
When you launch the report you will get the options to choose what status you want to view, for example Failed, Reboot Pending or any other status in your environment.
For example you can choose to view all failed advertisements, then the report can look like this:
To build this report:
1. Create a new report and give it a good name.
2. Edit the query statement, parse the following code:
SELECT
AdvState.AdvertisementID, AdvName.AdvertisementName, AdvState.LastStateName, AdvState.number AS 'Number of clients with this Status',
ROUND(100.0*AdvState.number/SUM(CASE AdvTotal.LastState WHEN 0 THEN 0 ELSE 1 END),1) AS 'Percent with this Status',
SUM(CASE AdvTotal.LastState WHEN 0 THEN 0 ELSE 1 END) AS 'Total # Clients with Accepted Status'
FROM v_ClientAdvertisementStatus AdvTotal, v_Advertisement AdvName,
(SELECT a.AdvertisementID,a.LastStateName, count(*) as 'number'
FROM v_ClientAdvertisementStatus a, v_AdvertisementStatusInformation b
WHERE a.LastStatusMessageID = b.MessageID
AND b.MessageStateName = @status
GROUP BY a.AdvertisementID, a.LastStateName
) AS AdvState
WHERE AdvState.AdvertisementID = AdvTotal.AdvertisementID
AND AdvState.AdvertisementID = AdvName.AdvertisementID
GROUP BY AdvState.AdvertisementID, AdvName.AdvertisementName, AdvState.LastStateName, AdvState.number
ORDER BY AdvName.AdvertisementName
3. Click on the "Prompts Button"
4. Create a new prompt with the following Name: "status"
5. Give it a prompt text
6. Provide the following sql statement to the prompt:
SELECT DISTINCT
a.MessageStateName
FROM
v_AdvertisementStatusInformation a,
v_ClientAdvertisementStatus b
WHERE a.MessageID = b.LastStatusMessageID
ORDER BY MessageStateName
7. Press Ok in all windows
8. Now rightclick on your report and choose properties
9. Choose the "Links" tab
10. Choose link Type: "link to another report"
11. Choose Report: "Software Distribution - Advertisement Status - All system resources for a specific advertisement in a specific state"
StateName should be column 3
AdvertID should be column 1
12. Press OK
And you are all done, have fun with your newly created report.
We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.
I have created a report where you can get a overview of the status of every advertisement in the environment. This can be very useful for getting an overview on how well software distribution is working.
When you launch the report you will get the options to choose what status you want to view, for example Failed, Reboot Pending or any other status in your environment.
For example you can choose to view all failed advertisements, then the report can look like this:
To build this report:
1. Create a new report and give it a good name.
2. Edit the query statement, parse the following code:
SELECT
AdvState.AdvertisementID, AdvName.AdvertisementName, AdvState.LastStateName, AdvState.number AS 'Number of clients with this Status',
ROUND(100.0*AdvState.number/SUM(CASE AdvTotal.LastState WHEN 0 THEN 0 ELSE 1 END),1) AS 'Percent with this Status',
SUM(CASE AdvTotal.LastState WHEN 0 THEN 0 ELSE 1 END) AS 'Total # Clients with Accepted Status'
FROM v_ClientAdvertisementStatus AdvTotal, v_Advertisement AdvName,
(SELECT a.AdvertisementID,a.LastStateName, count(*) as 'number'
FROM v_ClientAdvertisementStatus a, v_AdvertisementStatusInformation b
WHERE a.LastStatusMessageID = b.MessageID
AND b.MessageStateName = @status
GROUP BY a.AdvertisementID, a.LastStateName
) AS AdvState
WHERE AdvState.AdvertisementID = AdvTotal.AdvertisementID
AND AdvState.AdvertisementID = AdvName.AdvertisementID
GROUP BY AdvState.AdvertisementID, AdvName.AdvertisementName, AdvState.LastStateName, AdvState.number
ORDER BY AdvName.AdvertisementName
3. Click on the "Prompts Button"
4. Create a new prompt with the following Name: "status"
5. Give it a prompt text
6. Provide the following sql statement to the prompt:
SELECT DISTINCT
a.MessageStateName
FROM
v_AdvertisementStatusInformation a,
v_ClientAdvertisementStatus b
WHERE a.MessageID = b.LastStatusMessageID
ORDER BY MessageStateName
7. Press Ok in all windows
8. Now rightclick on your report and choose properties
9. Choose the "Links" tab
10. Choose link Type: "link to another report"
11. Choose Report: "Software Distribution - Advertisement Status - All system resources for a specific advertisement in a specific state"
StateName should be column 3
AdvertID should be column 1
12. Press OK
And you are all done, have fun with your newly created report.
Cheers
Marcus
Share this post
Link to post
Share on other sites