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?
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

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:

http://itninja.com/question/silent-uninstall61&mpage=1
Answered 10/05/2010 by: Jsaylor
Second Degree Blue Belt

Please log in to comment
0
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?
Answered 10/05/2010 by: admaai
Orange Senior Belt

Please log in to comment
2
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:

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=@ComputerName


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.
Answered 10/05/2010 by: Jsaylor
Second Degree Blue Belt

Please log in to comment
0
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.
Answered 10/06/2010 by: admaai
Orange Senior Belt

Please log in to comment
0
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:


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 = @computername


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.

Attachment

Answered 10/06/2010 by: Jsaylor
Second Degree Blue Belt

Please log in to comment
0
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.
Answered 10/06/2010 by: admaai
Orange Senior Belt

Please log in to comment
0
Hi Jsaylor
Imported the .mof but I get HTTP 500 when running it.
Answered 10/06/2010 by: admaai
Orange Senior Belt

Please log in to comment
0
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.
Answered 10/07/2010 by: VBScab
Red Belt

Please log in to comment
0
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.
Answered 10/07/2010 by: admaai
Orange Senior Belt

Please log in to comment
0
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.
Answered 10/07/2010 by: Jsaylor
Second Degree Blue Belt

Please log in to comment
2
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.
Answered 10/07/2010 by: Jsaylor
Second Degree Blue Belt

Please log in to comment
0
Yup, that seems to have fixed it, Thanks again.
Answered 10/08/2010 by: admaai
Orange Senior Belt

Please log in to comment
2
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)


Do not forget the trigger @computername
Answered 11/09/2010 by: admaai
Orange Senior Belt

Please log in to comment
0
Good on you for following-up. I'm sure it'll be appreciated.
Answered 11/09/2010 by: VBScab
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity