RE: Query for Maxtor hard drive

No need to worry about being a newbie - we were all newbies once (even if
some people act like they weren't ;) - I've attempted to explain some of
what I changed and why.

Here's a query that should help (this is WQL for a console query, not SQL
for a web report):

select SMS_R_System.Name, SMS_R_System.OperatingSystemNameandVersion,
SMS_R_System.LastLogonUserDomain, SMS_R_System.LastLogonUserName,
SMS_R_System.NetbiosName, SMS_G_System_DISK.PNPDeviceID from SMS_R_System
inner join SMS_G_System_DISK on SMS_G_System_DISK.ResourceID =
SMS_R_System.ResourceId where SMS_G_System_DISK.PNPDeviceID like "%maxtor%"
order by SMS_G_System_DISK.PNPDeviceID

After you paste this into a new query in the console, look at the design
view and compare it to your original query - that will make it easier to see
what I've changed. While its good to get to know the WQL and SQL query
languages, the design view can be helpful for beginners trying to figure out
what's wrong with their queries.

A few things to help you in your query writing:

the "in" keyword will only match exact lists - for example, the
SMS_G_System_DISK.Manufacturer in ("%maxtor%") would only return a row if the
name of the manufacturer was %maxtor%. To use a wildcard, use LIKE instead
of IN. When using the sms console's query design tools, this is called a
"simple value", and the operator is "is like".

Be careful of "OR" in your where statements. If something matches either
criteria, then it is added to the results. In the case of the sample you
posted, the SMS_R_System.Client=1 would match all installed clients, so you
wouldn't want that in your results. (look at your original query in design
view in the SMS console and you'll see that it lists "or" between the two
criteria listed).

Also, avoid adding tables and/or columns you don't need - it just slows down
the query. The BIOS doesn't provide any details about the disk, so unless
you need a specific entry from the bios for some other reason, adding it to
the query just makes for more work for your server to process the request.

Hope this helps,
Shawn



"Jerry" wrote:

I'm a newbe & most of what I've learned was coppied from forums. I need
to query for Maxtor hard drives due to their high failure rate so I can
turn on Smart. I found it in Resource Explorer under
hardware/manufacture & also under PNP.
When I run this query (see below) I get standard disk drive on most of
the 1400 machines. I know there more because I see them in Resource
Explorer

select SMS_R_System.Name, SMS_R_System.OperatingSystemNameandVersion,
SMS_R_System.LastLogonUserDomain, SMS_R_System.LastLogonUserName,
SMS_R_System.NetbiosName, SMS_G_System_PC_BIOS.Description,
SMS_G_System_PC_BIOS.Manufacturer, SMS_G_System_PC_BIOS.SerialNumber,
SMS_G_System_PC_BIOS.*, SMS_G_System_DISK.PNPDeviceID,
SMS_GEH_System_DISK.Manufacturer from SMS_R_System inner join
SMS_G_System_PC_BIOS on SMS_G_System_PC_BIOS.ResourceID =
SMS_R_System.ResourceId inner join SMS_G_System_DISK on
SMS_G_System_DISK.ResourceID = SMS_R_System.ResourceId inner join
SMS_GEH_System_DISK on SMS_GEH_System_DISK.ResourceID =
SMS_R_System.ResourceId where SMS_R_System.Client = 1 or
SMS_G_System_DISK.Manufacturer in ( "%maxtor%" ) order by
SMS_G_System_DISK.PNPDeviceID, SMS_GEH_System_DISK.Manufacturer


.

Relevant Pages

  • RE: Multiply qty of filtered records
    ... If you should want to try doing this with a query (which is probably the more ... etc in the bottom part of the design view. ... I do know how to get this information in a Report, ... Dim rs As DAO.Recordset ...
    (microsoft.public.access.modulesdaovba)
  • Re: Query Design View is Slow to Open
    ... same query takes about 20 minutes to open in datasheet view. ... took only seconds to open in both design view and datasheet view. ... The troublesome queries are based entirely on local tables (i.e. no ... HOUSEHOLD_PersonalInfo opens slowly in design ...
    (comp.databases.ms-access)
  • Re: Is this join valid?
    ... > complex set of tables and queries, but I've boiled down the behavior ... you describe a problem with the Design View that really should not ... affect what happens when running a query from ASP. ... > the SQL view after creating the query in Design View. ...
    (microsoft.public.inetserver.asp.db)
  • Re: query to combine values if true
    ... What if you keep your data tables in a well-normalized form (to help Access ... you can give them a form and a way to query and they'd be ... i am open to suggestions of a better design. ...
    (microsoft.public.access.gettingstarted)
  • Re: query where end user puts in selection criteria
    ... I'd like for a user to be able to select specific competencies so ... am used to working in design view and building queries for myself but this is ... build their own query from a form. ... "Duane Hookom" wrote: ...
    (microsoft.public.access.gettingstarted)
Loading