| Author |
 Topic  |
|
|
dimepop
Starting Member
30 Posts |
 Posted - 07/12/2012 : 08:18:01
|
Hi,
Can i get some help with a query?
Having the below database
Which query will give me the % of Computers in Computername grouped by the first 4 letters (where they give me the site location) and where TimetoLogon over 60 and WindowsVersion Any of the XP versions?( SP1, SP2, SP3)
ComputerName TimeToLogon WindowsVersion
----------- ------------ ----------------
sfc1-080 43 Microsoft Windows XP Professional Service Pack 3
nyc1-188 12 Microsoft Windows 7 Enterprise Service Pack 1
lan1-323 16 Microsoft Windows XP Professional Service Pack 3
lan2-190 11 Microsoft Windows 7 Enterprise Service Pack 1
lan3-190 16 Microsoft Windows XP Professional Service Pack 3
hou1-559 98 Microsoft Windows XP Professional Service Pack 3
sea1-020 38 Microsoft Windows XP Professional Service Pack 3
I have this query that gets the % of Computers starting by lan with timetologon over 60 by now i would like to include all sites and include only the XP versions.
"SELECT LEFT (ComputerName, CHARINDEX('-', ComputerName) - 1) AS Expr1, COUNT(CASE WHEN TimeToLogon > 60 THEN 1 END) * 100.0 / NULLIF (COUNT(1), 0) AS [%Over60] FROM BootLog WHERE (ComputerName LIKE 'lan%') GROUP BY LEFT (ComputerName, CHARINDEX('-', ComputerName) - 1)"
So at the end i will end up with a result like for windows XP machines:
Site %Timetologonover60
sfc1 12
lan1 11
lan2 12
hou1 12
Thanks
|
|
|
dimepop
Starting Member
30 Posts |
Posted - 07/12/2012 : 08:21:10
|
I forgot to include something....
There is another row called DateAndTime with values like:
2012-03-14 09:37:12.000
2012-03-14 08:37:59.000
2012-03-14 08:38:30.000
2012-03-14 08:39:41.000
2012-03-14 08:39:56.000
2012-03-14 08:40:34.000
2012-03-14 08:41:25.000
the report ideally will only look at the last 30 days.
Thanks |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 07/12/2012 : 09:53:09
|
SELECT LEFT (ComputerName, CHARINDEX('-', ComputerName) - 1) AS Expr1,
COUNT(CASE WHEN TimeToLogon > 60 THEN 1 END) * 100.0 / NULLIF (COUNT(1), 0) AS [%Over60]
FROM BootLog
WHERE WindowsVersion LIKE 'Windows XP%'
and DateAndTime >= DATEADD(DD,dATEDIFF(dd,0,GETDATE())-30,0)
GROUP BY LEFT (ComputerName, CHARINDEX('-', ComputerName) - 1)
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
dimepop
Starting Member
30 Posts |
Posted - 07/12/2012 : 11:46:41
|
Hi Visakh, thanks for the query.
It runs perfectly. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 07/12/2012 : 17:24:41
|
wc
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
dimepop
Starting Member
30 Posts |
Posted - 07/13/2012 : 07:57:58
|
Hi Visakh16
Which query would display the following?
I would like to add the second row so i can display both in a combochart
Thanks
Site %XPTimetoLogonOver60 %Win7TimetoLogonOver60
sfc1 12 22
lan1 11 21
lan2 12 22
hou1 12 22 |
Edited by - dimepop on 07/13/2012 08:12:05 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 07/13/2012 : 10:35:34
|
SELECT LEFT (ComputerName, CHARINDEX('-', ComputerName) - 1) AS Expr1,
COUNT(CASE WHEN TimeToLogon > 60 AND WindowsVersion LIKE 'Windows XP%' THEN 1 END) * 100.0 / NULLIF (COUNT(1), 0) AS [%XPOver60],
COUNT(CASE WHEN TimeToLogon > 60 AND WindowsVersion LIKE 'Windows 7%' THEN 1 END) * 100.0 / NULLIF (COUNT(1), 0) AS [%Win7Over60]
FROM BootLog
WHERE (WindowsVersion LIKE 'Windows XP%' OR WindowsVersion LIKE 'Windows 7%')
and DateAndTime >= DATEADD(DD,dATEDIFF(dd,0,GETDATE())-30,0)
GROUP BY LEFT (ComputerName, CHARINDEX('-', ComputerName) - 1)
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
dimepop
Starting Member
30 Posts |
Posted - 07/13/2012 : 11:41:51
|
Thanks, I Owe you a drink!
:) |
|
|
|
dimepop
Starting Member
30 Posts |
Posted - 07/16/2012 : 07:33:09
|
Hi Visakh, sorry to bother you again, it seems that the last query doesn't work as expected.
The query returns the correct % of Win7 machines, but the incorrect % of XP machines.
If i run the query just to get the % of xp machines i get a different result as in the combined query.
Can you help?
Many thanks
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 07/16/2012 : 10:00:23
|
explain with sample data what issue you're facing. I cant make out exact issue without seeing sample output.
my only guess now is probably you're after this??
SELECT LEFT (ComputerName, CHARINDEX('-', ComputerName) - 1) AS Expr1,
COUNT(CASE WHEN TimeToLogon > 60 AND WindowsVersion LIKE 'Windows XP%' THEN 1 END) * 100.0 / NULLIF (COUNT(CASE WHEN WindowsVersion LIKE 'Windows XP%' THEN 1 END), 0) AS [%XPOver60],
COUNT(CASE WHEN TimeToLogon > 60 AND WindowsVersion LIKE 'Windows 7%' THEN 1 END) * 100.0 / NULLIF (COUNT(CASE WHEN WindowsVersion LIKE 'Windows 7%' THEN 1 END), 0) AS [%Win7Over60]
FROM BootLog
WHERE (WindowsVersion LIKE 'Windows XP%' OR WindowsVersion LIKE 'Windows 7%')
and DateAndTime >= DATEADD(DD,dATEDIFF(dd,0,GETDATE())-30,0)
GROUP BY LEFT (ComputerName, CHARINDEX('-', ComputerName) - 1)
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
dimepop
Starting Member
30 Posts |
Posted - 07/16/2012 : 11:03:58
|
Hi Visakh, now it works fine, but before the results for XP were wrong.
I noticed it because if i run the query to get the results for just XP then it gave me different results.
No the combines query shows the same results as the individual queries.
Many Thanks |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 07/16/2012 : 11:42:51
|
welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
T-SQL Query Help
