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?

Comments

Answers

0

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:

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?

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.

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.

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.

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.

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.

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.

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.

For those who may stumble upon this thread. I finally managed to get to sql server, final result seems to work and is this: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)