Hello,

Does anyone have a template or could provide a query to create a report of all scheduled advertisements, by date or range of dates?

Thank you for your help.
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
The below SQL will pull all advertisements on your SCCM site, and sort them by the advertisement start time. If you're familiar with the database at all, there are a few other columns you can add into the query under v_advertisement, but I believe I hit the ones you'll care about.

Select adv.advertisementname "Advertisement Name", adv.advertisementID "Advertisement ID", adv.programname "Program name", adv.presenttime "Advertisement Start time" from
v_Advertisement Adv
order by adv.presenttime
Answered 09/22/2010 by: Jsaylor
Second Degree Blue Belt

Please log in to comment
0
Thank you very much Jsaylor!

I'd like to create a report which lists all mandatory assignments within advertisements for a given day.
Answered 09/23/2010 by: august606
Senior Yellow Belt

Please log in to comment
0
Unfortunately, the mandatory assignment dates aren't stored in a way that is really human-readable. They're stored as "schedule tokens" that look something like: 9149B9C000080000

There's a way to decode them, but it's not something you could get running in a web report. You would have to use a WMI call to decode the information with the SMS_Scheduletoken class, here's the official explanation page. If you're interested in looking at the raw data, check out dbo.programoffers in your database, in column MandatorySched.
Answered 09/23/2010 by: Jsaylor
Second Degree Blue Belt

Please log in to comment
2
Yes, but you would have to tell it what you want to know about.
Three obvious options: Option A: using the built-in alerts:
[ol][*]Reporting->email alerts->add new computer notification[*]choose a threshold the wizard can handle[*]you will now get a notification when a machine meets that criteria on its checkin
[/ol]Option B: extend the notification to use a custom query -- this time on ASSET HISTORY
Look at the asset history of a machine. Whatever is in that you could report on.
[ol][*]open the notification you created above (reports->email alerts->click on existing one)
[*]change the query to something like below
[/ol]E.g.
[code]
Select MACHINE.ID
from MACHINE
join ASSET A on MAPPED_ID=MACHINE.ID and ASSET_TYPE_ID=5
join ASSET_HISTORY AH on AH.ASSET_ID=A.ID
where
AH.DESCRIPTION like '%ram change%' -- note that i do not know what this would be for RAM
and AH.TIME > date_sub(now(), interval 1 DAY)[/code]
Option C: run a scheduled report so you get the alert on many machines at one time (e.g. daily ). Benefit of this is you can choose which columns to return including the asset history
[ol][*]Reporting->add new sql report[*]use this SQL (note the SQL here is almost same as above but you can tweak it)
[/ol][code]Select MACHINE.ID, MACHINE.NAME,
AH.DESCRIPTION -- description can be lengthy so you might want to substring it, etc
from MACHINE
join ASSET A on MAPPED_ID=MACHINE.ID and ASSET_TYPE_ID=5
join ASSET_HISTORY AH on AH.ASSET_ID=A.ID
where
AH.DESCRIPTION like '%ram change%' -- note that i do not know what this would be for RAM
and AH.TIME > date_sub(now(), interval 1 DAY)[/code]
A Less obvious option would be automatically creating a ticket when a threshold is exceeded and leveraging all the benefits that go with that (emails, audit trail, etc). The basic idea is that you could have your alert above send the ticket to the queue. see http://www.kace.com/support/kb/index.php?action=artikel&cat=8&id=719&artlang=en in step 3.2 you would use a query like the above. Inour helpdesk we get notifications from the system into our queue that tell us about certain things like the status of last nights maintenance run.
Answered 01/12/2012 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
excellent! thanks for the quick respones. I am not sure what route I will go down but I will let you know how I get on. Thanks again
Answered 01/12/2012 by: mac456
Purple Belt

Please log in to comment
0
Good luck! When you like the answers please rate the posts!
Answered 01/12/2012 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
rated :)
p.s If anyone has done this before, please help (not that big on SQL)
cheers
Answered 01/12/2012 by: mac456
Purple Belt

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