<[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
