Building custom reports - Need help with sql query
Björn Axéll
I would like to build my own report but I need some help to build the right
SQL query. What I like to create a report showning me:
"All Updates that is not approved but needed by computers"
Anyone know how to create this is the best way.
Björn Axéll MVP - Advisec AB
http://blog.advisec.com
Lawrence Garvin (MVP)
"Björn Axéll" <netn...@hotmail.com> wrote in message
news:u5tRu5Jh...@TK2MSFTNGP05.phx.gbl...
> Hi all,
> I would like to build my own report but I need some help to build the
> right SQL query. What I like to create a report showning me:
> "All Updates that is not approved but needed by computers"
Björn, it is not necessary to create a SQL query to obtain this information.
All you need to do is apply the filters in the "All Updates" view to select
updates that are "Unapproved" and "Needed".
Then, if you want it in "report" format: Select all listed updates, right
click, select Status Report.
--
Lawrence Garvin, M.S., MCITP(x2), MCTS(x5), MCP(x7), MCBMSP
Principal/CTO, Onsite Technology Solutions, Houston, Texas
Microsoft MVP - Software Distribution (2005-2009)
MS WSUS Website: http://www.microsoft.com/wsus
My Websites: http://www.onsitechsolutions.com;
http://wsusinfo.onsitechsolutions.com
My MVP Profile: http://mvp.support.microsoft.com/profile/Lawrence.Garvin
Björn - Advisec
Thanks for your answer. I know I can get that report in the console but the
problem is it only show updates from the MicrosoftUpdate "source". I need to
have a report that show other updates (other sources) that has been imported
by WSUS API.
So - I still interested if someone can help me with a SQL query. I have
looked at the public views but I haven't realy got it to work (I'm not a SQL
guru)
Björn
Lawrence Garvin (MVP)
news:C0E88DB6-3779-4A63...@microsoft.com...
> Hi Lawrence,
> Thanks for your answer. I know I can get that report in the console but
> the
> problem is it only show updates from the MicrosoftUpdate "source". I need
> to
> have a report that show other updates (other sources) that has been
> imported
> by WSUS API.
Then it's not a SQL query you need.. it's the appropriate calls to the WSUS
API to retrieve that information.
Querying the database directly is not a supported function.
Björn - Advisec
this would be unsupported (teher is even sample queries on the WSUS blog).
So - if someone has knowlege in the public view, please help me with this
query.
Björn
Lawrence Garvin (MVP)
> I'm talking about building a query against the public views
There are no "public views". The =database= is not designed to be accessible
to the user level.
> I can see why
> this would be unsupported (teher is even sample queries on the WSUS blog).
There are queries against the =API=, but if there are any queries published
against the =database=, please share the links you found them at so that I
can help Microsoft reconcile their positions on support.
Björn - Advisec
http://msdn.microsoft.com/en-us/library/bb410149(VS.85).aspx. Since it state
"WSUS database views are useful for generating custom reports" I thought it
was the way to use them.
As for other examples, here is from the WSUS team blog:
So, with this information - can someone help we with the SQL query for
finding "All updates needed but not approved"
Thanks
Björn
![Harry Johnston [MVP]'s profile photo](http://lh3.googleusercontent.com/a-/ALV-UjWpkBAPqvc839nzAtvH7xG03OwcxExyoyKpBgg3G7rKJeLEHQ=s40-c)
Harry Johnston [MVP]
> "Björn - Advisec" <BjrnA...@discussions.microsoft.com> wrote in
> message news:A0E0CC9A-9F44-46B4...@microsoft.com...
>> I'm talking about building a query against the public views
>
> There are no "public views". The =database= is not designed to be
> accessible to the user level.
I think you're out of date on this:
<http://msdn.microsoft.com/en-us/library/bb410149(VS.85).aspx>
Harry.
Lawrence Garvin (MVP)
> This is what I mean with publich views
> http://msdn.microsoft.com/en-us/library/bb410149(VS.85).aspx.
SonOfAB.....
> So, with this information - can someone help we with the SQL query for
> finding "All updates needed but not approved"
Well, essentially, you're going to have to join together
PUBLIC_VIEWS.vUpdateApproval -- to get the list of not approved updates
[Returns one row with approval information for each update and computer
group if the update is approved to that computer group.]
=and=
PUBLIC_VIEWS.vUpdate -- to get metadata about each UpdateID
[Returns one row for the latest revision of each update. The values of
UpdateId are unique.]
=and=
PUBLIC_VIEWS.vUpdateInstallationInfoBasic -- to get the list of updates
reported as needed
[Returns one row for each update and computer if the computer has reported
status for that update with the reported status information.]
SELECT ua.UpdateID, u.DefaultTitle, --and any other fields you'd like to see
in the report
FROM PUBLIC_VIEWS.vUpdateApproval ua
JOIN PUBLIC_VIEWS.vUpdate u ON u.UpdateID = ua.UpdateID
JOIN PUBLIC_VIEWS.vUpdateInstallationInfoBasic uiib ON uiib.UpdateID =
ua.UpdateID
AND uiib.State = <whatever value(s) is(are) appropriate for the data
you want>
Lawrence Garvin (MVP)
news:e2%23R3BZh...@TK2MSFTNGP04.phx.gbl...
Actually, I'm *very* annoyed.... for years the product team has been telling
us "we don't support direct access to the database", and *today*, I find out
they've put a special set of objects, in a namespace "PUBLIC_VIEWS", and
documented them in the API.
Obviously the statement "we don't support direct access to the database" is
now pure B.S.
Björn Axéll
Thanks for trying to help now that you found the views:-)
The last part of te script, you wrote "whatever value(s) is(are) appropriate
for the data
> you want>"
What type of states is valid? I can't find thtis documented!
Björn
"Lawrence Garvin (MVP)" <lawr...@news.postalias> wrote in message
news:uxTDY2bh...@TK2MSFTNGP04.phx.gbl...
Lawrence Garvin (MVP)
> What type of states is valid? I can't find thtis documented!
I couldn't either -- but I'm sure it's documented somewhere.
Björn Axéll
I have now played a bit with the SQL query but I really don't get to show
what I want:-( The thing I can't figure out is:
*What and how do I find the expression for "Needed". This isn't something I
have in the db. The state values I find is Unknown(0), NotApplicable(1),
Downloaded(2), NotInstalled(3), Installed(4), Failed(5),
InstalledPendingReboot(6)
Really need some more help here
Björn
"Lawrence Garvin (MVP)" <lawr...@news.postalias> wrote in message
news:uGrPoYu...@TK2MSFTNGP06.phx.gbl...
Lawrence Garvin (MVP)
> HI again,
> I have now played a bit with the SQL query but I really don't get to show
> what I want:-( The thing I can't figure out is:
> *What and how do I find the expression for "Needed". This isn't something
> I have in the db. The state values I find is Unknown(0), NotApplicable(1),
> Downloaded(2), NotInstalled(3), Installed(4), Failed(5),
> InstalledPendingReboot(6)
>
>
> Really need some more help here
Björn, I cannot read and interpret the API documentatation for you, nor can
I teach you how to program SQL or VB/VC# via a newsgroup. Might I suggest
some good books on .NET programming fundamentals, and a few hours reading
the entire API package cover-to-cover?
I say this because --- the *datatype* of the value I provided you in the
WHERE filter is INTEGER. You've found the enumerations for that value.
You're looking for systems that are NEEDED. "Needed" is not an enumerated
value, it's a consolidation of more discrete values that are provided.
You'll need to apply some logic and thought processes here. Which of the
seven states provided would imply that the update is still Needed? Test for
those values.
Björn Axéll
I didn't ask you (or anyone) to tech me, I ask if someone could write a SQL
query that would work for me. If you don't have it, it is ok !
Björn
"Lawrence Garvin (MVP)" <lawr...@news.postalias> wrote in message
news:OAFNU76...@TK2MSFTNGP02.phx.gbl...
mikejones
0 - Unknown
1 - Not Applicable
2 - Needed
3 - Downloaded (but not installed)
4 - Installed
5 - Failed
6 - Pending Reboot
So you're looking for state 2 !
:)
netnet_4 wrote:
HI again,I have now played a bit with the SQL query but I really don't get to
04-Feb-09
HI again,
I have now played a bit with the SQL query but I really don't get to show
what I want:-( The thing I can't figure out is:
*What and how do I find the expression for "Needed". This isn't something I
have in the db. The state values I find is Unknown(0), NotApplicable(1),
Downloaded(2), NotInstalled(3), Installed(4), Failed(5),
InstalledPendingReboot(6)
Really need some more help here
Bj??rn
"Lawrence Garvin (MVP)" <lawr...@news.postalias> wrote in message
news:uGrPoYu...@TK2MSFTNGP06.phx.gbl...
Previous Posts In This Thread:
On Sunday, February 01, 2009 1:53 PM
netnet_4 wrote:
Building custom reports - Need help with sql query
Hi all,
I would like to build my own report but I need some help to build the right
SQL query. What I like to create a report showning me:
"All Updates that is not approved but needed by computers"
Anyone know how to create this is the best way.
Bj?rn Ax?ll MVP - Advisec AB
http://blog.advisec.com
On Monday, February 02, 2009 11:37 AM
Lawrence Garvin \(MVP\) wrote:
Re: Building custom reports - Need help with sql query
"Bj?rn Ax?ll" <netn...@hotmail.com> wrote in message
news:u5tRu5Jh...@TK2MSFTNGP05.phx.gbl...
Bj?rn, it is not necessary to create a SQL query to obtain this information.
All you need to do is apply the filters in the "All Updates" view to select
updates that are "Unapproved" and "Needed".
Then, if you want it in "report" format: Select all listed updates, right
click, select Status Report.
--
Lawrence Garvin, M.S., MCITP(x2), MCTS(x5), MCP(x7), MCBMSP
Principal/CTO, Onsite Technology Solutions, Houston, Texas
Microsoft MVP - Software Distribution (2005-2009)
MS WSUS Website: http://www.microsoft.com/wsus
My Websites: http://www.onsitechsolutions.com;
http://wsusinfo.onsitechsolutions.com
My MVP Profile: http://mvp.support.microsoft.com/profile/Lawrence.Garvin
On Monday, February 02, 2009 12:42 PM
BjrnAdvise wrote:
Hi Lawrence,Thanks for your answer.
Hi Lawrence,
Thanks for your answer. I know I can get that report in the console but the
problem is it only show updates from the MicrosoftUpdate "source". I need to
have a report that show other updates (other sources) that has been imported
by WSUS API.
So - I still interested if someone can help me with a SQL query. I have
looked at the public views but I haven't realy got it to work (I'm not a SQL
guru)
Bj??rn
"Lawrence Garvin (MVP)" wrote:
On Monday, February 02, 2009 3:13 PM
Lawrence Garvin \(MVP\) wrote:
Re: Building custom reports - Need help with sql query
"Bj??rn - Advisec" <BjrnA...@discussions.microsoft.com> wrote in message
news:C0E88DB6-3779-4A63...@microsoft.com...
Then it's not a SQL query you need.. it's the appropriate calls to the WSUS
API to retrieve that information.
Querying the database directly is not a supported function.
--
Lawrence Garvin, M.S., MCITP(x2), MCTS(x5), MCP(x7), MCBMSP
Principal/CTO, Onsite Technology Solutions, Houston, Texas
Microsoft MVP - Software Distribution (2005-2009)
MS WSUS Website: http://www.microsoft.com/wsus
My Websites: http://www.onsitechsolutions.com;
http://wsusinfo.onsitechsolutions.com
My MVP Profile: http://mvp.support.microsoft.com/profile/Lawrence.Garvin
On Monday, February 02, 2009 3:23 PM
BjrnAdvise wrote:
I'm talking about building a query against the public views - I can see why
I'm talking about building a query against the public views - I can see why
this would be unsupported (teher is even sample queries on the WSUS blog).
So - if someone has knowlege in the public view, please help me with this
query.
Bj??rn
"Lawrence Garvin (MVP)" wrote:
On Monday, February 02, 2009 5:32 PM
Lawrence Garvin \(MVP\) wrote:
Re: Building custom reports - Need help with sql query
"Bj??rn - Advisec" <BjrnA...@discussions.microsoft.com> wrote in message
news:A0E0CC9A-9F44-46B4...@microsoft.com...
There are no "public views". The =database= is not designed to be accessible
to the user level.
There are queries against the =API=, but if there are any queries published
against the =database=, please share the links you found them at so that I
can help Microsoft reconcile their positions on support.
--
Lawrence Garvin, M.S., MCITP(x2), MCTS(x5), MCP(x7), MCBMSP
Principal/CTO, Onsite Technology Solutions, Houston, Texas
Microsoft MVP - Software Distribution (2005-2009)
MS WSUS Website: http://www.microsoft.com/wsus
My Websites: http://www.onsitechsolutions.com;
http://wsusinfo.onsitechsolutions.com
My MVP Profile: http://mvp.support.microsoft.com/profile/Lawrence.Garvin
On Monday, February 02, 2009 5:44 PM
BjrnAdvise wrote:
Re: Building custom reports - Need help with sql query
This is what I mean with publich views
http://msdn.microsoft.com/en-us/library/bb410149(VS.85).aspx. Since it state
"WSUS database views are useful for generating custom reports" I thought it
was the way to use them.
As for other examples, here is from the WSUS team blog:
So, with this information - can someone help we with the SQL query for
finding "All updates needed but not approved"
Thanks
Bj??rn
"Lawrence Garvin (MVP)" wrote:
On Monday, February 02, 2009 6:46 PM
Harry Johnston [MVP] wrote:
Re: Building custom reports - Need help with sql query
Lawrence Garvin (MVP) wrote:
I think you are out of date on this:
<http://msdn.microsoft.com/en-us/library/bb410149(VS.85).aspx>
Harry.
On Tuesday, February 03, 2009 12:09 AM
Lawrence Garvin \(MVP\) wrote:
Re: Building custom reports - Need help with sql query
"Bj??rn - Advisec" <BjrnA...@discussions.microsoft.com> wrote in message
news:0A49A778-3F98-4D79...@microsoft.com...
SonOfAB.....
Well, essentially, you're going to have to join together
PUBLIC_VIEWS.vUpdateApproval -- to get the list of not approved updates
[Returns one row with approval information for each update and computer
group if the update is approved to that computer group.]
=and=
PUBLIC_VIEWS.vUpdate -- to get metadata about each UpdateID
[Returns one row for the latest revision of each update. The values of
UpdateId are unique.]
=and=
PUBLIC_VIEWS.vUpdateInstallationInfoBasic -- to get the list of updates
reported as needed
[Returns one row for each update and computer if the computer has reported
status for that update with the reported status information.]
SELECT ua.UpdateID, u.DefaultTitle, --and any other fields you'd like to see
in the report
FROM PUBLIC_VIEWS.vUpdateApproval ua
JOIN PUBLIC_VIEWS.vUpdate u ON u.UpdateID = ua.UpdateID
JOIN PUBLIC_VIEWS.vUpdateInstallationInfoBasic uiib ON uiib.UpdateID =
ua.UpdateID
AND uiib.State = <whatever value(s) is(are) appropriate for the data
you want>
--
Lawrence Garvin, M.S., MCITP(x2), MCTS(x5), MCP(x7), MCBMSP
Principal/CTO, Onsite Technology Solutions, Houston, Texas
Microsoft MVP - Software Distribution (2005-2009)
MS WSUS Website: http://www.microsoft.com/wsus
My Websites: http://www.onsitechsolutions.com;
http://wsusinfo.onsitechsolutions.com
My MVP Profile: http://mvp.support.microsoft.com/profile/Lawrence.Garvin
On Tuesday, February 03, 2009 12:11 AM
Lawrence Garvin \(MVP\) wrote:
Re: Building custom reports - Need help with sql query
"Harry Johnston [MVP]" <ha...@scms.waikato.ac.nz> wrote in message
news:e2%23R3BZh...@TK2MSFTNGP04.phx.gbl...
Actually, I'm *very* annoyed.... for years the product team has been telling
us "we don't support direct access to the database", and *today*, I find out
they've put a special set of objects, in a namespace "PUBLIC_VIEWS", and
documented them in the API.
Obviously the statement "we don't support direct access to the database" is
now pure B.S.
--
Lawrence Garvin, M.S., MCITP(x2), MCTS(x5), MCP(x7), MCBMSP
Principal/CTO, Onsite Technology Solutions, Houston, Texas
Microsoft MVP - Software Distribution (2005-2009)
MS WSUS Website: http://www.microsoft.com/wsus
My Websites: http://www.onsitechsolutions.com;
http://wsusinfo.onsitechsolutions.com
My MVP Profile: http://mvp.support.microsoft.com/profile/Lawrence.Garvin
On Tuesday, February 03, 2009 6:09 PM
netnet_4 wrote:
Re: Building custom reports - Need help with sql query
Hi,
Thanks for trying to help now that you found the views:-)
The last part of te script, you wrote "whatever value(s) is(are) appropriate
for the data
What type of states is valid? I can't find thtis documented!
Bj??rn
"Lawrence Garvin (MVP)" <lawr...@news.postalias> wrote in message
news:uxTDY2bh...@TK2MSFTNGP04.phx.gbl...
On Wednesday, February 04, 2009 11:32 AM
Lawrence Garvin \(MVP\) wrote:
Re: Building custom reports - Need help with sql query
"Bj??rn Ax??ll" <netn...@hotmail.com> wrote in message
news:usRDASlh...@TK2MSFTNGP03.phx.gbl...
I couldn't either -- but I'm sure it's documented somewhere.
--
Lawrence Garvin, M.S., MCITP(x2), MCTS(x5), MCP(x7), MCBMSP
Principal/CTO, Onsite Technology Solutions, Houston, Texas
Microsoft MVP - Software Distribution (2005-2009)
MS WSUS Website: http://www.microsoft.com/wsus
My Websites: http://www.onsitechsolutions.com;
http://wsusinfo.onsitechsolutions.com
My MVP Profile: http://mvp.support.microsoft.com/profile/Lawrence.Garvin
On Wednesday, February 04, 2009 3:59 PM
netnet_4 wrote:
HI again,I have now played a bit with the SQL query but I really don't get to
HI again,
I have now played a bit with the SQL query but I really don't get to show
what I want:-( The thing I can't figure out is:
*What and how do I find the expression for "Needed". This isn't something I
have in the db. The state values I find is Unknown(0), NotApplicable(1),
Downloaded(2), NotInstalled(3), Installed(4), Failed(5),
InstalledPendingReboot(6)
Really need some more help here
Bj??rn
"Lawrence Garvin (MVP)" <lawr...@news.postalias> wrote in message
news:uGrPoYu...@TK2MSFTNGP06.phx.gbl...
On Thursday, February 05, 2009 11:28 AM
Lawrence Garvin \(MVP\) wrote:
Re: Building custom reports - Need help with sql query
"Bj??rn Ax??ll" <netn...@hotmail.com> wrote in message
news:e4GA%23twhJ...@TK2MSFTNGP06.phx.gbl...
Bj??rn, I cannot read and interpret the API documentatation for you, nor can
I teach you how to program SQL or VB/VC# via a newsgroup. Might I suggest
some good books on .NET programming fundamentals, and a few hours reading
the entire API package cover-to-cover?
I say this because --- the *datatype* of the value I provided you in the
WHERE filter is INTEGER. You've found the enumerations for that value.
You're looking for systems that are NEEDED. "Needed" is not an enumerated
value, it's a consolidation of more discrete values that are provided.
You'll need to apply some logic and thought processes here. Which of the
seven states provided would imply that the update is still Needed? Test for
those values.
--
Lawrence Garvin, M.S., MCITP(x2), MCTS(x5), MCP(x7), MCBMSP
Principal/CTO, Onsite Technology Solutions, Houston, Texas
Microsoft MVP - Software Distribution (2005-2009)
MS WSUS Website: http://www.microsoft.com/wsus
My Websites: http://www.onsitechsolutions.com;
http://wsusinfo.onsitechsolutions.com
My MVP Profile: http://mvp.support.microsoft.com/profile/Lawrence.Garvin
On Thursday, February 05, 2009 1:52 PM
netnet_4 wrote:
Hi,I didn't ask you (or anyone) to tech me, I ask if someone could write a SQL
Hi,
I did not ask you (or anyone) to tech me, I ask if someone could write a SQL
query that would work for me. If you do not have it, it is ok !
Bj??rn
Submitted via EggHeadCafe - Software Developer Portal of Choice
Making Silverlight Emulate Synchronous Requests
http://www.eggheadcafe.com/tutorials/aspnet/91f69224-3da5-4959-9901-c5c717c9b184/making-silverlight-emulat.aspx