LabTech Support Forums

MySQL Query - Bios Date

This forum is for discussion of MySQL database related issues.

Moderator: jware.connectwise

MySQL Query - Bios Date

Postby csullivan.webitservices » Sat Oct 06, 2012 10:54 am

I have a MySQL question I wanted to know if you anyone can help me with. Any push in the right direction will help.

The query I want is to be able to see the BIOS date of the agent. I want to return all 2007 and 2008 pc’s. I got it to work by running two different queries then just copying that into a spreadsheet but my query does not return the computer agent name only the computer ID. Any way to also include in my query data from another table (the Computers Table I think) so I can get the computer name next to the Computer ID and maybe even the Domain?

Here is my current queries that only returns limited results (and see attached screenshot of what I would like with current results):

SELECT * FROM inv_bios
WHERE BIOSDate LIKE '%2007';

SELECT * FROM inv_bios
WHERE BIOSDate LIKE '%2008';

MySQL_Query_Results.png
An example of the current results my query returns and what I would like to see next to it.
(10.21 KiB) Downloaded 8 times
csullivan.webitservices
Newbie
Newbie
 
Posts: 8
Joined: Fri Jun 08, 2012 8:42 am

Re: MySQL Query - Bios Date

Postby janson.johnanson » Sat Oct 06, 2012 11:42 am

you may want to get a copy of SQLyog to build Queries

here is an example joining the the Inv_BIOS table to the Computer table then joining the computer table to the client table

SELECT
`clients`.`Name`
, `computers`.`Name`
, `computers`.`BiosName`
, `computers`.`BiosVer`
, `inv_bios`.`BIOSVersion`
, `inv_bios`.`BIOSDate`
FROM
`labtech`.`inv_bios`
INNER JOIN `labtech`.`computers`
ON (`inv_bios`.`ComputerID` = `computers`.`ComputerID`)
INNER JOIN `labtech`.`clients`
ON (`computers`.`ClientID` = `clients`.`ClientID`)
WHERE (`inv_bios`.`BIOSDate` LIKE '%2007')
OR (`inv_bios`.`BIOSDate` LIKE'%2008');
janson.johnanson
Sr. Member
Sr. Member
 
Posts: 261
Joined: Wed Dec 31, 1969 8:00 pm

Re: MySQL Query - Bios Date

Postby csullivan.webitservices » Sat Oct 06, 2012 3:30 pm

Janson,

Thank you so very much, that worked like a champ! :D

I do have SQLyog just need to learn the in's and out's of the program. With the help/info you gave I think I can build other queries now on my own using multiple tables.

Thanks again for the help!

Chad
csullivan.webitservices
Newbie
Newbie
 
Posts: 8
Joined: Fri Jun 08, 2012 8:42 am

Re: MySQL Query - Bios Date

Postby fredrik.arvidsson.digitalinformation » Wed Jul 24, 2013 4:33 am

An improved Query asking for computers with BIOS date older than 5 years.

SELECT
`clients`.`Name`
, `computers`.`Name`
, `computers`.`BiosName`
, `computers`.`BiosVer`
, `inv_bios`.`BIOSVersion`
, `inv_bios`.`BIOSDate`
FROM
`labtech`.`inv_bios`
INNER JOIN `labtech`.`computers`
ON (`inv_bios`.`ComputerID` = `computers`.`ComputerID`)
INNER JOIN `labtech`.`clients`
ON (`computers`.`ClientID` = `clients`.`ClientID`)
WHERE YEAR(CURDATE()) - YEAR(STR_TO_DATE(inv_bios.BIOSDate, '%m/%d/%Y')) >= 5
fredrik.arvidsson.digitalinformation
Newbie
Newbie
 
Posts: 5
Joined: Mon May 06, 2013 5:29 am
Location: Örebro/Sweden


Return to MySQL Database

Who is online

Users browsing this forum: No registered users and 2 guests