Report "All advertisements for a specific computer"
Hi
We recently restructured both SCCM and AD (not our idea) and also started running native mode, now all advertisements done based on AD group membership. Easiest way to move a computer to another part of organization, is to remove it from all the groups, and add it to other groups, wait or force the updates and then run it through OSD. It can add up to be a total of 40 or more groups. The problem I am facing is with the report, Since we check it to see if the comp has got all the programs. "All advertisements for a specific computer" shows even the advertisements that are no longer valid. I need a report that only shows valid advertisements, Any suggestions?
We recently restructured both SCCM and AD (not our idea) and also started running native mode, now all advertisements done based on AD group membership. Easiest way to move a computer to another part of organization, is to remove it from all the groups, and add it to other groups, wait or force the updates and then run it through OSD. It can add up to be a total of 40 or more groups. The problem I am facing is with the report, Since we check it to see if the comp has got all the programs. "All advertisements for a specific computer" shows even the advertisements that are no longer valid. I need a report that only shows valid advertisements, Any suggestions?
0 Comments
[ + ] Show comments
Answers (14)
Please log in to answer
Posted by:
Jsaylor
13 years ago
You could try using the laststatustime column from v_clientadvertisementstatus. I'm not sure exactly how accurate it will be though. To use it, just make a copy of All Advertisements for a specific computer, and use the following SQL instead of the SQL that's in there already:
If you'll notice, all I added was stat.laststatustime (stat is the arbitrary label given to v_ClientAdvertisementStatus in this report) just after the advertisementID line in the select statement. If that doesn't seem to work, there are some other columns in that view that may be promising, but try that one first.
SELECT adv.AdvertisementName,
stat.LastStateName,
adv.Comment AS C072,
pkg.Name AS C062,
adv.ProgramName AS C071,
adv.SourceSite,
adv.AdvertisementID,
stat.laststatustime
FROM v_Advertisement adv
JOIN v_Package pkg ON adv.PackageID = pkg.PackageID
JOIN v_ClientAdvertisementStatus stat ON stat.AdvertisementID = adv.AdvertisementID
JOIN v_R_System sys ON stat.ResourceID=sys.ResourceID
WHERE sys.Netbios_Name0=@ComputerNameIf you'll notice, all I added was stat.laststatustime (stat is the arbitrary label given to v_ClientAdvertisementStatus in this report) just after the advertisementID line in the select statement. If that doesn't seem to work, there are some other columns in that view that may be promising, but try that one first.
Posted by:
Jsaylor
13 years ago
It looks like that's just too many parameters for SCCM to interpret the way we want it to without returning 100k rows (or some serious rework using some T-SQL, which SCCM doesn't always support.) You can still use the report to discover all currently assigned advertisements though, it just won't display last status time along with the advertisements. I went ahead and edited my previous post to remove the status time column.
Posted by:
admaai
13 years ago
For those who may stumble upon this thread. I finally managed to get to sql server, final result seems to work and is this:
Do not forget the trigger @computername
SELECT adv.AdvertisementName AS [Advertisement name], adv.AdvertisementID AS [Advertisement ID], v_ClientAdvertisementStatus.LastStateName,
v_ClientAdvertisementStatus.LastStatusTime
FROM v_R_System AS SYS INNER JOIN
v_FullCollectionMembership AS FCM ON SYS.ResourceID = FCM.ResourceID LEFT OUTER JOIN
v_Collection AS COL ON FCM.CollectionID = COL.CollectionID INNER JOIN
v_Advertisement AS adv ON COL.CollectionID = adv.CollectionID INNER JOIN
v_ClientAdvertisementStatus ON SYS.ResourceID = v_ClientAdvertisementStatus.ResourceID AND
adv.AdvertisementID = v_ClientAdvertisementStatus.AdvertisementID
WHERE (SYS.Netbios_Name0 = @computername)Do not forget the trigger @computername
Posted by:
Jsaylor
13 years ago
Unfortunately, that's not how SCCM stores its data. All advertisements that have been run on a computer will continue to display, because the computer will report them as successfully executed.
Your best bet is to use the computer's current collection membership, which will give you everything that particular machine is assigned to, and hopefully your naming convention for collections is easily understandable. Of course, there's no default report that will allow you to look at that kind of data, but I've put one together that should do what you're looking for. Check out the third post down:
http://itninja.com/question/silent-uninstall61&mpage=1
Your best bet is to use the computer's current collection membership, which will give you everything that particular machine is assigned to, and hopefully your naming convention for collections is easily understandable. Of course, there's no default report that will allow you to look at that kind of data, but I've put one together that should do what you're looking for. Check out the third post down:
http://itninja.com/question/silent-uninstall61&mpage=1
Posted by:
admaai
13 years ago
Yup seen it, downloaded it and never thanked for it until now:). Actually it has helped in other areas. Ok back to original question, there should be a way to get at least timestamps on those status messages?
Posted by:
admaai
13 years ago
Thank you . It helped, the computer I looked at had around 3 months time gaps between the recent groups and old groups status messages. A bit intrigued though, it might be possible to use your other report, "the collections a computer belongs to" (btw an excellent addition to right click tools), as a base for a new report for valid advetisements.
Posted by:
Jsaylor
13 years ago
Oh, yes, I hadn't actually thought that far around the problem. It should be pretty trivial to mesh everything we have together into one report... In fact, try this:
It will require the same prompt as the other collection query, I went ahead and attached a .mof to this post for ease of use. Feel free to download it, rename the extension from .txt to .mof, and import to your reports.
You'll get a lot of extra lines if you have multiple advertisements assigned to each collection, since each line will only report one advertisement, but it should get you where you need to be. If you don't care about the collection information, just remove the Col.name and FCM.CollectionID statements from the Select line.
SELECT COL.Name as "Collection name", FCM.CollectionID as "Collection ID", SYS.Netbios_Name0 as "Computer Name", adv.advertisementname as "Advertisement name", adv.advertisementID as "Advertisement ID"
FROM v_R_System SYS
JOIN v_FullCollectionMembership FCM on SYS.ResourceID = FCM.ResourceID
LEFT JOIN v_Collection COL on FCM.CollectionID=COL.CollectionID
Join v_Advertisement adv on COL.collectionID=adv.CollectionID
WHERE
SYS.Netbios_Name0 = @computernameIt will require the same prompt as the other collection query, I went ahead and attached a .mof to this post for ease of use. Feel free to download it, rename the extension from .txt to .mof, and import to your reports.
You'll get a lot of extra lines if you have multiple advertisements assigned to each collection, since each line will only report one advertisement, but it should get you where you need to be. If you don't care about the collection information, just remove the Col.name and FCM.CollectionID statements from the Select line.
Posted by:
admaai
13 years ago
Excellent, Thank you. I´ll give it a try tomorrow at work. I am not in habit of asking for stuff without doing some of the work first. But since the upgrade, and being burried under all the extra work, I do not even know where the SQL server is and even if I have an account on it.
Posted by:
anonymous_9363
13 years ago
If you're getting that from one report, you'll probably get it all for all: it's a web server issue, as the busy man Mr G might have advised.
Posted by:
admaai
13 years ago
LOL, some explanations leave the askee wondering "WHAT?". good for extra brain activity though, well I did check other reports and they are working fine. To adjust webservers config for just 1 report seems kinda too much.
http://systemsmanagement.spaces.live.com/blog/cns!E8AF746880BBD15E!231.entry helped to understand what is going on. So I removed the 2 suggested rows for collections, the report works, but it is not only for me. It is for support staff too and they have to have an easier report like the All advertisements for a specific computer, excluding non valid collections. Thanks for all the help so far.
http://systemsmanagement.spaces.live.com/blog/cns!E8AF746880BBD15E!231.entry helped to understand what is going on. So I removed the 2 suggested rows for collections, the report works, but it is not only for me. It is for support staff too and they have to have an easier report like the All advertisements for a specific computer, excluding non valid collections. Thanks for all the help so far.
Posted by:
Jsaylor
13 years ago
I guess I should have tested that out first, it looks like the last status time returns a large number of values for a given computer, so you get a ton of rows whenever you try to pull any data. There might be a better way to join that particular column.
Rating comments in this legacy AppDeploy message board thread won't reorder them,so that the conversation will remain readable.