The state values equate to the following:
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 her
Bj??r
"Lawrence Garvin (MVP)" <***@news.postalias> wrote in message news:***@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 A
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" <***@hotmail.com> wrote in message news:***@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), MCBMS
Principal/CTO, Onsite Technology Solutions, Houston, Texa
Microsoft MVP - Software Distribution (2005-2009
MS WSUS Website: http://www.microsoft.com/wsu
My Websites: http://www.onsitechsolutions.com
http://wsusinfo.onsitechsolutions.co
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??r
"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" <***@discussions.microsoft.com> wrote in message news:C0E88DB6-3779-4A63-B9AD-***@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), MCBMS
Principal/CTO, Onsite Technology Solutions, Houston, Texa
Microsoft MVP - Software Distribution (2005-2009
MS WSUS Website: http://www.microsoft.com/wsu
My Websites: http://www.onsitechsolutions.com
http://wsusinfo.onsitechsolutions.co
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??r
"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" <***@discussions.microsoft.com> wrote in message news:A0E0CC9A-9F44-46B4-9AAD-***@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:
http://blogs.technet.com/wsus/archive/2008/06/20/baseline-compliance-report-using-public-wsus-views.aspx
http://blogs.technet.com/wsus/archive/2008/07/07/finding-machines-not-compliant-with-a-specific-security-bulletin.aspx
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" <***@discussions.microsoft.com> wrote in message news:0A49A778-3F98-4D79-BAFE-***@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]" <***@scms.waikato.ac.nz> wrote in message news:e2%***@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)" <***@news.postalias> wrote in message news:***@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" <***@hotmail.com> wrote in message news:***@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)" <***@news.postalias> wrote in message news:***@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" <***@hotmail.com> wrote in message news:e4GA%***@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