<[EMAIL PROTECTED]> wrote on 04/07/2005 03:42:45 AM:

> 
> Hi,
> 
> we are facing problem with the sql queries given below
> 
> query number one gives the output as workstation and total logon failure
> attempts from that workstation
> 
> which is fine
> 
> 1) select logonsvr,workstnid, count (username) from winadlogon where
> evtid="681" group by workstnid order by workstnid;
> 
> 
> query number 2 is generating list
> 
> username-workstnid-hits from the user
> 
> e.g.
> 
> userA-workstation A- 3
> 
> userB-Workstation A -2
> 
> userc-workstation B -1
> 
> 
> 2)select time,workstnid,username count (username) from winadlogon where
> evtid="681" group by username order by workstnid;
> 
> 
> as shown in above example we are getting the list.
> 
> If user A also has attempted login from workstation B the it is not
> displayed as we have done grouping by username.
> 
> 
> we need output such as
> 
> userA-workstation A -3
> 
> userA-workstation B -2
> 
> 
> if user is attempling log on from various workstations the this query
> should show all entries for the user for all workstations from where it
> has tried log on and hits from each workstation logon and while group by
> username is in place.
> 
> please suggest if there is any solution to this problem.
> 
> 
> Thanks,
> Narasimha
<big snip>

<FLAME ON>
1. You hijacked someone else's response to a question that wasn't yours to 
begin with to create your message. Not only can this be confusing but it's 
lazy and selfish. On top of it all you "top posted". Bad form all around.

2. Do you ever, or have you ever RTFM?!?! or search the archives?  This 
has been a FAQ. You claim to be proficient in Oracle but your SQL 
statements imply othewise. You have asked MANY questions to the list that 
cover some rather basic material. This is also one of them. 

We (I) don't mind answering questions on just about any topic HOWEVER in 
my opinion you should be getting to the point where this is familar to you 
(either through your use of Oracle or your exposure to MySQL, it's manual, 
the list and its archives, and what you have already asked) and it seems 
to me as though you have avoided learning too much because you seem to 
"run to the list" with questions I think you should probably be able to 
answer on your own. 
<flame off>

You have a serious problem with your query that the MySQL developers chose 
to ignore (silently) a long time ago. Unfortunately, you are paying the 
price for it now. 

Whenever you write a query that contains colums, aggregate funcions, and a 
GROUP BY clause you must list EVERY column in your GROUP BY clause or you 
will get random results. (
http://dev.mysql.com/doc/mysql/en/group-by-hidden-fields.html)

A better query would have been:
select logonsvr,workstnid, count(username) 
from winadlogon 
where evtid="681" 
group by workstnid, logonsvr order by workstnid

or 

select time,workstnid,username count(username) 
from winadlogon 
where evtid="681" 
group by username,workstnid, time order by workstnid

or 

select workstnid,username, count(username), min(time) as firstlogon, 
max(time) as lastlogon
from winadlogon 
where evtid="681" 
group by username,workstnid order by workstnid

NOTE: there is also NO spaces or tabs between a function and it's 
parameters in MySQL. You don't say "COUNT (*)" you say "COUNT(*)". (
http://dev.mysql.com/doc/mysql/en/functions.html)

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Reply via email to