Hi,

I have the following SQL script and am stuck:


SELECT
v_R_System.Name0 AS MachineName,
DATEDIFF(day, v_MonthlyUsageSummary.LastUsage, GETDATE()) AS DaysSinceLastUsed,
v_MeteredProductRule.RuleID,
v_MeteredFiles.FileName
FROM v_R_System
INNER JOIN v_MonthlyUsageSummary ON v_R_System.ResourceID = v_MonthlyUsageSummary.ResourceID
INNER JOIN v_MeteredFiles
INNER JOIN v_MeteredProductRule ON v_MeteredFiles.RuleID = v_MeteredProductRule.RuleID ON v_MonthlyUsageSummary.FileID = v_MeteredFiles.MeteredFileID
WHERE (v_MeteredProductRule.RuleID = 173)
ORDER BY MachineName, DaysSinceLastUsed

This is an SCCM query and I am trying to get a list of machines with the LastUsage with the least value (fewest number of days). The problem I am having is that I am getting ALL the usage records for each machine???

I have tried using the MIN function but end up with all kinds of syntax, non-boolean, etc., errors and have NO clue what to do.

I cant imagine its that difficult... or is it?

Thanks for your help!

Lee
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

0
The WHERE clause is your filter - this is where you need to limit your results. I'm not familiar with the SCCM database, so I can't give you code.
Answered 03/02/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
Thanks Andy! I'll play with that....
Answered 03/02/2010 by: lsiegel
Senior Yellow Belt

Please log in to comment
0
I'm not entirely sure this is possible in a single SQL statement for a return result against multiple single column values (IE, computer names.) If you attempt to specify any number in the WHERE clause, you're going to only return results against all computers that have that number (E.G., if the minimum value of v_MonthlyUsageSummary.LastUsage is 6 days for any computer in your environment, then only computers with a value of 6 days will display.)

You might try digging through some SQL manuals and have a look at the available functions, and keep in mind that if you're going to use any aggregate clauses, you're going to have to use GROUP BY, and HAVING to keep the syntax correct. Try poking around here if you haven't already for ideas.
Answered 03/02/2010 by: Jsaylor
Second Degree Blue Belt

Please log in to comment
0
errrrm.....I don't use SCCM myself (so i don't know what v_MeteredProductRule.RuleID is....), but shouldn't the query look like this (note the second inner join)? Also, why are you ordering by the machine name?:
SELECT
v_R_System.Name0 AS MachineName,
DATEDIFF(day, v_MonthlyUsageSummary.LastUsage, GETDATE()) AS DaysSinceLastUsed,
v_MeteredProductRule.RuleID,
v_MeteredFiles.FileName
FROM v_R_System
INNER JOIN v_MonthlyUsageSummary ON v_R_System.ResourceID = v_MonthlyUsageSummary.ResourceID
INNER JOIN v_MeteredFiles ON v_MonthlyUsageSummary.FileID = v_MeteredFiles.MeteredFileID
INNER JOIN v_MeteredProductRule ON v_MeteredFiles.RuleID = v_MeteredProductRule.RuleID
WHERE (v_MeteredProductRule.RuleID = 173)
ORDER BY DaysSinceLastUsed
Answered 03/02/2010 by: captain_planet
Second Degree Brown Belt

Please log in to comment
0
He's ordering by the machine name because .LastUsage returns multiple results for a given machine, this allows him to at least see each machine result in individual result blocks per machine name.
Answered 03/02/2010 by: Jsaylor
Second Degree Blue Belt

Please log in to comment
Answer this question or Comment on this question for clarity