Re: SMS 2003 Report question

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Sorry for jumping in... try this... in theory it should work ;) Substitute
the file you want to evaluate in both ='x.exe' statements

SELECT DISTINCT

CS.Name0,

(Case when SF.FileName='winword.exe' then 'Yes' else 'No' end) as 'WinFile'

FROM

dbo.v_GS_SoftwareFile SF

RIGHT OUTER JOIN dbo.v_GS_COMPUTER_SYSTEM CS ON SF.ResourceID =
CS.ResourceID

WHERE SF.FileName='winword.exe'

GROUP BY CS.Name0, SF.FileName

ORDER BY CS.Name0


"ant_techie" <anttechie@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:5E0B2163-A143-49AF-BC68-70A4CB15F763@xxxxxxxxxxxxxxxx
Hi Garth,

It sort of works :-)

SELECT DISTINCT
CS.Name0,
(Case when SF.FileName='File.exe' then 'Yes' else ' ' end) as 'File'
FROM
dbo.v_GS_SoftwareFile SF
Right OUTER JOIN dbo.v_GS_COMPUTER_SYSTEM CS ON SF.ResourceID =
CS.ResourceID
ORDER BY CS.Name0

If the file does not exist then I get one record showing that the file
does
not exist, but if the file does exist, then I get two records. One
showing
the file does not exist, and one showing that the file does exist.

Name File
COMPUTER1
COMPUTER1 Yes
COMPUTER2
COMPUTER3
COMPUTER3 Yes


Also, even though I select a specific collection, the results show all
computers from all the collections.

We can certainly use this as a report, but it would be nice to tidy it up.

Cheers,

Anthony.

"Garth" wrote:

I should have said report not query.

Report use SQL
Collection/query use WQL

And that is a SQL query.


"ant_techie" <anttechie@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:8B7AB69F-C3D9-4F60-9CD4-AE8D1E2449A2@xxxxxxxxxxxxxxxx
Hi Garth,

Thanks for the reply, but I get an error with that query

WBEM_E_INVALID_QUERY

Is it syntax or something else?

Anthony.

"Garth" wrote:

This query might help you..
http://smsug.ca/blogs/garth_jones/archive/2007/11/09/748.aspx


"ant_techie" <anttechie@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B4446D44-46D1-4830-815B-899FCFD6EC96@xxxxxxxxxxxxxxxx
Hi,

I am trying to create a report that will display all the computers
in a
collection and if they have a specific file (as per the inventory
info).

I have managed to create 2 queries, one that will display ONLY the
computers
in a collection that have the specific file in question, and another
query
that will display ONLY the computers that don't have the file.

However, I need a query or report that will show all the computers
whether
or not they have the file and if they have the file or not.

ie

COMPUTER1 Yes
COMPUTER2 Yes
COMPUTER3 No etc, etc...

Any Ideas as to the best way to approach this one?

Cheers,

Anthony.





.

Relevant Pages

  • Re: Print report based on value of 2 combo boxes
    ... What I need is the syntax for the rest of the DoCmd.Openreport..... ... The report "AllComputers" will be used for many different queries, ... the computers in the table" to a "particular computer" ... > boxes on the form to filter the query behind your report. ...
    (microsoft.public.access.reports)
  • Re: Distribute to user account objects in specific AD group in SCC
    ... For your second query, perhaps try using the subselect 'not in' ... This gave me a list of all the computers in the AD group. ... It does NOT work the same way as users in a usergroup. ... problem upon removing the computer account from the AD group and invoking ...
    (microsoft.public.sms.admin)
  • Re: Report problem - Access 2003
    ... "tina" wrote: ... a basic report bound to that query. ... Yes I have a table that links staff to computers. ...
    (microsoft.public.access.reports)
  • Re: Access to a custom table
    ... Create a collection based on this query for machines you want to include: ... "Tony" wrote: ... Let say I have computers named "A, B, C, D" in the database. ... > that has the NOT subselect query. ...
    (microsoft.public.sms.admin)
  • RE: How to combine existing collections to include or exclude...
    ... "Matthew Hudson" wrote: ... Servers you want minus the ones you don't want ... Add computers you don't want to show up in Collection B ... will see if I can't just send you the collection queries and the query ...
    (microsoft.public.sms.admin)