With the advent of hierarchy in SMP 7.x, a centralized reporting server plays an even greater role. While we are still on 6.x due to our waiting for increased functionality before we move, I have already architected and deployed another NS to serve as a child NS to a Management NS once we move to the 7.x platform. We have always had a central reporting server that our two NS's forward data too on a defined schedule.
To increase efficiency of pulling data and ensuring it is as up-to-date as possible, we have began to create a few reports on the central reporting server that actually pull the data from both NS's.
In the example below, we are pulling the result of a three different software delivery jobs, it's Start Time, End Time, Status, Return Code, Execution Number, the Last Config Request time for that node, and the Domain designation of that server (which is unique to us as our prod, non-prod designation may vary from from your environment, but you can simply comment out those lines with a dash dash "--" or delete them from the query below).
We use the AdvertisementID as opposed to the AdvertisementName as we feel that it won't change as often as the name could and can easily exist across multiple NS's when we export and import the job. But if you don't have to worry about rogue job name changes like me, then feel free to use the AdvertisementName. :-)
As you can see, our SQL servers are being referenced directly for the Altiris database respective to its environment.
Also note that we are interested in the LAST time the job ran, hence the use of the 'max' function. The number of times the job has ran has also allowed us to see repeated failures. The collections are built to exclude those that successfully ran the job, so that use case is addressed. Then a simply union is used to combine the results from the two data sources. You could expand this to include other Altiris databases, but should also consider how often the report will need to be ran as well as network traffic. A DTS or SSIS package may suite you better.
BEGIN SQL:
(
select vcns101.[fully qualified domain name] as 'FQDN',
swdns101.AdvertisementName,
swdns101.start as 'Start Time',
swdns101.[end] as 'End Time',
swdns101.status as 'Status',
swdns101.ReturnCode,
swdns101.ExecutionNumber,
luns101.lastRequest as 'Last Config Request',
vCompns101.[OS Name],
ns101domain.domain
from [taspmosqlcs102\taspmosqlcs102].altiris.dbo.Inv_AeX_AC_Location vcns101 with (nolock)
--join [taspmosqlcs102\taspmosqlcs102].altiris.dbo.CollectionMembership cmns101 with (nolock)
--on cmns101.resourceguid = vcns101._resourceGuid
left join [taspmosqlcs102\taspmosqlcs102].altiris.dbo.vComputer vCompns101 with (nolock)
on vcns101._resourceGuid = vCompns101.guid
join [taspmosqlcs102\taspmosqlcs102].altiris.dbo.Inv_AeX_SWD_Execution_Summary swdns101 with (nolock)
on vcns101._resourceGuid = swdns101._resourceGuid
and (swdns101.AdvertisementID = '89800972-a3df-4c4a-91b4-e2532cb2e0d2'
or swdns101.AdvertisementID = '2ed1c7e5-9738-4acb-b47e-52528a58f654'
or swdns101.AdvertisementID = '095c5664-c92e-4345-a374-150f9fa3cbd8'
)
left join (
Select max(starttime) as LastRequest, resourceGuid
from [taspmosqlcs102\taspmosqlcs102].altiris.dbo.[Evt_NS_Client_Config_Request] with (nolock)
Group by resourceGuid
) luns101 on vcns101._resourceguid = luns101.resourceGuid
left join (
select _resourceGuid,max(
CASE
WHEN domain.[CWx_Domain_Type] like '%prod%' THEN 'PROD'
ELSE 'Non-prod'
END
) as 'Domain'
from [taspmosqlcs102\taspmosqlcs102].altiris.dbo.Inv_CWx_Remedy_CMDB_Domain_Info domain with (nolock)
group by _resourceGuid
) ns101domain on vcns101._resourceguid = ns101domain._resourceGuid
--where cmns101.collectionguid = '62dbf83a-57d0-4c24-8236-5c4eea12e93a'
)
union all
(
select vcns111.[fully qualified domain name] as 'FQDN',
swdns111.AdvertisementName,
swdns111.start as 'Start Time',
swdns111.[end] as 'End Time',
swdns111.status as 'Status',
swdns111.ReturnCode,
swdns111.ExecutionNumber,
luns111.lastRequest as 'Last Config Request',
vCompns111.[OS Name],
ns111domain.domain
from [taspmosqlcs111\taspmosqlcs111].altiris.dbo.Inv_AeX_AC_Location vcns111 with (nolock)
--join [taspmosqlcs102\taspmosqlcs102].altiris.dbo.CollectionMembership cmns101 with (nolock)
--on cmns101.resourceguid = vcns101._resourceGuid
left join [taspmosqlcs111\taspmosqlcs111].altiris.dbo.vComputer vCompns111 with (nolock)
on vcns111._resourceGuid = vCompns111.guid
join [taspmosqlcs111\taspmosqlcs111].altiris.dbo.Inv_AeX_SWD_Execution_Summary swdns111 with (nolock)
on vcns111._resourceGuid = swdns111._resourceGuid
and (swdns111.AdvertisementID = '89800972-a3df-4c4a-91b4-e2532cb2e0d2'
or swdns111.AdvertisementID = '2ed1c7e5-9738-4acb-b47e-52528a58f654'
or swdns111.AdvertisementID = '095c5664-c92e-4345-a374-150f9fa3cbd8'
)
left join (
Select max(starttime) as LastRequest, resourceGuid
from [taspmosqlcs111\taspmosqlcs111].altiris.dbo.[Evt_NS_Client_Config_Request] with (nolock)
Group by resourceGuid
) luns111 on vcns111._resourceguid = luns111.resourceGuid
left join (
select _resourceGuid,max(
CASE
WHEN [CWx_Domain_Type] like '%prod%' THEN 'PROD'
ELSE 'Non-prod'
END
) as 'Domain'
from [taspmosqlcs111\taspmosqlcs111].altiris.dbo.Inv_CWx_Remedy_CMDB_Domain_Info domain with (nolock)
group by _resourceGuid
) ns111domain on vcns111._resourceguid = ns111domain._resourceGuid
--where cmns111.collectionguid = '62dbf83a-57d0-4c24-8236-5c4eea12e93a'
)
order by [Start Time] desc
END SQL