Help with Auto Notifications

We currently have SMS dumping data into sql, and what we are trying do is
when sms does an inventory it captures the amount of RAM and puts it in a
sql table, it also keeps a history in another table. We have an issue were
RAM is starting to be remove, so we are wanted to have a scheduled task that
will compare the 2 values in the table if the current inventoried RAM is
less than the Historical inventoried RAM, an email notification would be
sent. Is this possible, if so how would go about creating this task.
thanks - mikeWhat version of SQL Server?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mike" <mlehman@.osc.uscg.mil> wrote in message news:OLr54f$gGHA.1612@.TK2MSFTNGP04.phx.gbl..
.
> We currently have SMS dumping data into sql, and what we are trying do is
when sms does an
> inventory it captures the amount of RAM and puts it in a sql table, it als
o keeps a history in
> another table. We have an issue were RAM is starting to be remove, so we
are wanted to have a
> scheduled task that will compare the 2 values in the table if the current
inventoried RAM is less
> than the Historical inventoried RAM, an email notification would be sent.
Is this possible, if so
> how would go about creating this task.
> thanks - mike
>|||SQL 2000 SP 4
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uiLjDv$gGHA.3424@.TK2MSFTNGP05.phx.gbl...
> What version of SQL Server?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Mike" <mlehman@.osc.uscg.mil> wrote in message
> news:OLr54f$gGHA.1612@.TK2MSFTNGP04.phx.gbl...
>

Help with a select statement

---

Greetings,
I am an SMS administrator and use SQL to create reports. My SQL skills are junior at best. I am trying to create an SQL select statement that shows me all computers that do not Java 1.5.0_04 installed. It is easy for me to search for all machines that have 'J2SE Runtime Environment 5.0 Update 4' but what I want is all computers minus the computers with Java 1.5.0_04.
Posted below is my attempt, but it does not work. Can someone lend a hand and direct me onto the correct path?
Thanks
_________________
select SMS_G_System_SYSTEM.Name, SMS_R_System.LastLogonUserName,
SMS_R_System.OperatingSystemNameandVersion, SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName,
SMS_R_System.ADSiteName
from SMS_R_System
inner join SMS_G_System_SYSTEM
on SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId
inner join SMS_G_System_ADD_REMOVE_PROGRAMS
on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId
where SMS_R_System.OperatingSystemNameandVersion like "%Workstation 5.1%"
and SMS_G_System_SYSTEM.Name
not in (select SMS_G_System_SYSTEM.Name
from SMS_R_System
inner join SMS_G_System_SYSTEM
on SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId
inner join SMS_G_System_ADD_REMOVE_PROGRAMS
on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId
where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "J2SE Runtime Environment 5.0 Update 4")

Sorry, I had to reformat your SQL so that it was comfortable for me to deal with:
SELECT
G.Name,
R.LastLogonUserName,
R.OperatingSystemNameandVersion,
G_ARP.DisplayName,
R.ADSiteName
FROM
SMS_R_System R
INNER JOIN
SMS_G_System_SYSTEM G ON R.ResourceId = G.ResourceID
INNER JOIN
SMS_G_System_ADD_REMOVE_PROGRAMS G_ARP ON R.ResourceId = G_ARP.ResourceID
WHERE
R.OperatingSystemNameandVersion LIKE '%Workstation 5.1%' AND
G.Name NOT IN
(
SELECT
G.Name
FROM
SMS_R_System R
INNER JOIN
SMS_G_System_SYSTEM G ON R.ResourceId =G.ResourceID
INNER JOIN
SMS_G_System_ADD_REMOVE_PROGRAMS G_ARP ONR.ResourceId = G_ARP.ResourceID
WHERE
G_ARP.DisplayName = 'J2SE Runtime Environment 5.0Update 4'
)
What you have there will return you all of the comuters where theOperatingSystemNameandVersion contains 'Workstation 5.1', but 'J2SERuntime Environment 5.0 Update 4' is not found in the DisplayName ofthe related Add_Remove_Programs table.
You should be using ResourceID, not Name, in your NOT IN clause,shouldn't you? Isn't that the field used to join the tablestogether? Like this:
SELECT
G.Name,
R.LastLogonUserName,
R.OperatingSystemNameandVersion,
G_ARP.DisplayName,
R.ADSiteName
FROM
SMS_R_System R
INNER JOIN
SMS_G_System_SYSTEM G ON R.ResourceId = G.ResourceID
INNER JOIN
SMS_G_System_ADD_REMOVE_PROGRAMS G_ARP ON R.ResourceId = G_ARP.ResourceID
WHERE
R.OperatingSystemNameandVersion LIKE '%Workstation 5.1%' AND
G.ResourceID NOT IN
(
SELECT
G.ResourceID
FROM
SMS_R_System R
INNER JOIN
SMS_G_System_SYSTEM G ON R.ResourceId =G.ResourceID
INNER JOIN
SMS_G_System_ADD_REMOVE_PROGRAMS G_ARP ONR.ResourceId = G_ARP.ResourceID
WHERE
G_ARP.DisplayName = 'J2SE Runtime Environment 5.0Update 4'
)
|||Acutally now that I look over the resulting data I think it was working. I was looking at one idividual who does have the software that was showing up on the list, but he just installed it this week and I only inventory weekly... duh!
Thanks for your help

Help w/ query

I need help typing to pull info out of a SMS database. We are selecting info
from three table. we inner join the tables to get selected info out. where
we are having trouble is by limiting what is returned. is there a way to say
"if the row has a specific character(s) then the row will be ignored". the
way it is now gives us about ten time the data, because the machine name is
associated with multiple collections within sms.
On Tue, 1 Feb 2005 19:51:01 -0800, Greg wrote:
(snip)
>is there a way to say
>"if the row has a specific character(s) then the row will be ignored".
Hi Greg,
Yes, there is.
Unfortunately, I can't give more specific help, since you didn't post any
specific information about your requirements. In order to help you, I need
to know the structure of your tables (as CREATE TABLE statements, omitting
irrelevant columns but including all constraints and properties), some
sample data (as INSERT statements) and expected output.
Consult www.aspfaq.com/5006, then post back with more details if you need
further help.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)