Structuring a Query

Results 1 to 2 of 2

Thread: Structuring a Query

  1. #1
    Join Date
    Dec 1969

    Default Structuring a Query

    I have a table that contains every event log item on every computer in our firm (there&#039;s only 100 computers, but it&#039;s still a pretty big table). I&#039;m trying to write a report that tells me what the biggest event log &#039;issues&#039; are. In the table are the following field:<BR><BR>prpty_SourceName<BR><BR>Now I understand the functions of SQL, but I don&#039;t know how to combine them. For example, and please don&#039;t laugh, I have the following code to begin the process:<BR><BR>While not oRS.eof<BR> Response.Write oRS("prpty_SourceName") & "<BR>"<BR> Set oRS2 = CreateObject("ADODB.RecordSet")<BR> strSQL = "SELECT COUNT(prpty_SourceName) As MyCount FROM Win32_NTLogEvent2 WHERE prpty_SourceName = &#039;" & oRS("prpty_SourceName") & "&#039;"<BR> Response.Write " " & strSQL & "<BR>"<BR> oRS2.Open strSQL, oConnection, 3, 3, 1<BR> For Each FieldItem in oRS2.Fields<BR> Response.Write "<BR>" & oRS("prpty_SourceName") & " = " & oRS2(FieldItem.Name) & "&#060;p&#062;"<BR> Next<BR> oRS.MoveNext<BR> Set oRS2 = Nothing<BR>Wend<BR><BR>This is probably the worst way to go about getting my results. Essentially, the oRS table contains all the distinct event log items. Then it takes each oRS event log source and runs ANOTHER query to find out the COUNT() of that log source. The next step is to order the count results, take the top ten, and print them out. I&#039;m sure that using the right SQL form I could get my results with just one query to the database. But I&#039;m clueless as to what that query might even begin to look like. Any help?? It would save me, and my server, hours of computation and headaches.

  2. #2
    Join Date
    Dec 1969

    Default Almost identical answer... a thread not far below:<BR><BR><BR><BR>Be sure to read my "Mea Culpa" message about the good I made.<BR><BR>And, yes, what you are doing probably is one of the worst possible ways.<BR><BR>Ehhh...I just noticed that you wanted to order the queries by the counts, and get just top 10. Okay. I guess we need to help you more.<BR><BR>Ummmm....I just realized. You didn&#039;t tell us the name of your second table, the one you are using to get the oRS recordset.<BR><BR>Are you saying that all of this is in ONE TABLE???<BR><BR>If it&#039;s one table, it&#039;s even simpler:<BR><BR>SELECT TOP 10 prpty_SourceName, COUNT(*) AS theCount<BR>FROM Win32_NTLogEvent2<BR>GROUP BY prpty_SourceName<BR>ORDER BY theCount DESC<BR><BR>If this is an Access/Jet DB, then the last line has to be<BR><BR>ORDER BY 2 DESC<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts