dynamic query

• achen9291

  SSC-Addicted

  Points: 460

  More actions

  June 26, 2012 at 2:50 pm

  #276851

  I am trying to do a dynamic query, right now it is static because i have to manually input the clientuserid to get the results. how can i get the data to be pulled automatically from the top 3 clientuserid?

  --算上網時間Count internet usage in Minutes

  --1. 抓出要算的retrieve useful record olny

  Select * into #ProxyLog_record from WebProxyLog Where

  GmtLogTime between (select dbo.F_START_OF_WEEK(getdate(),default)) and (Select Dateadd(Day, 6, (select dbo.F_START_OF_WEEK(getdate(),default)))) and

  convert(char(12), Logtime, 108) not between '12:00:00' and '13:00:00' and

  ClientUserName not in (

  'iec\IHC070705',

  'iec\IHC070202',

  'iec\IHC061108',

  'iec\IHC060809',

  'iec\IHC060808',

  'iec\ihc041019',

  'iec\hit080102',

  'iec\IHC070305',

  'iec\IHC061202',

  'iec\ihc030302',

  'iec\IHC050907',

  'iec\IHC080102',

  'iec\IHC090801',

  'iec\IHC100601',

  'iec\IHC100601',

  'iec\IHC010501',

  'IEC\IEC880329',

  'IEC\IEC780762',

  'IEC\ihc110502',

  'IEC\hit110502',

  'iec\HIT010501',

  'anonymous') and

  DestHost not like '%.microsoft%' and

  DestHost not like '%.citibank%' and

  DestHost not like '%.windowsupdate%' and

  DestHost not like '%.inventec%' and

  DestHost not like '%.trendmicro.com%' and

  DestHost not like '%.google%' and

  DestHost not like '%.symantec%' and

  DestHost not like '10.%' and

  DestHost not like '16.%' and

  DestHost not like '%.gov' and

  DestHost not like '%.hp.com%' and

  DestHost not like '%.dell.com%' and

  DestHost not like '%.ups%' and

  DestHost not like '%.usps.com%' and

  DestHost not like '%.fedex.com%' and

  DestHost not like '%.ibm.com%' and

  DestHost not like '%toshiba%' and

  DestHost not like '%.intel.com%' and

  DestHost not like '%.seagate.com%' and

  DestHost not like '%.hp-ww.com%' and

  DestHost not like '%.ta-america.com%' and

  DestHost not like '%.conexis.com%' and

  DestHost not like '%.www.employeebenefitswebsite.com%' and

  DestHost not like '%.secure.ucci.com%' and

  DestHost not like '%.ajg.adobeconnect.com%' and

  DestHost not like '%.jpfic.com%' and

  DestHost not like '%.avesis.com%' and

  DestHost not like '%.aetna.com%' and

  DestHost not like '%.cbs-companies.com%' and

  DestHost not like '%.sre.gob.mx%' and

  DestHost not like '%.800notes.com%' and

  DestHost not like '%.chinahouston.org%' and

  DestHost not like '%.n-o-v-a.com%' and

  DestHost not like '%.openonline.com%' and

  DestHost not like '%.weather.com%' and

  DestHost not like '%.weatherbug.com%' and

  DestHost not like '%.myfoxhouston.com%' and

  DestHost not like '%.andadvantus.com%' and

  DestHost not like '%.1800flowers.com%' and

  DestHost not like '%.nhc.noaa.gov%' and

  DestHost not like '%.pbconferences.com%' and

  DestHost not like '%.amazon.com%' and

  DestHost not like '%.wxbug.com%' and

  DestHost not like '%.kwe.com%' and

  DestHost not like '%.fedex.com%' and

  DestHost not like '%.forwarding.ups-scs.com%' and

  DestHost not like '%.usps.com%' and

  DestHost not like '%.yml.com.tw%' and

  DestHost not like '%.dhl-usa.com%' and

  DestHost not like '%.expeditors.com%' and

  DestHost not like '%.morrisonexpress.com%' and

  DestHost not like '%.nykline.com%' and

  DestHost not like '%.pandora.com%' and

  DestHost not like '%.ob10.com%' and

  DestHost not like '%.skype.com%' and

  DestHost not like '%.citibank.com%' and

  DestHost not like '%.auo.com%' and

  DestHost not like '%.clocklink.com%' and

  DestHost not like '%.fujitsu.com%' and

  DestHost not like '%.hitachigst.com%' and

  DestHost not like '%.ta-retirement.com%' and

  DestHost not like '%.businessaccess.citibank.citigroup.com%'and

  DestHost not like '%dell.webportal.eaglegl.com%' and

  DestHost not like '%.citibank.com/us/citibusinessonline/%' and

  DestHost not like '207.46.%' and

  DestHost not like '207.114.197.%' and

  DestHost not like '64.4.34.%' and

  DestHost not like '64.4.36.%' and

  DestHost not like '65.54.228.%' and

  DestHost not like '65.54.171.%' and

  DestHost not like '65.54.189.%' and

  DestHost not like '64.4.16.%' and

  DestHost not like '68.142.201.%' and

  DestHost not like '68.180.219.%' and

  DestHost not like '72.2.117.%' and

  DestHost not like '76.13.6.%' and

  DestHost not like '98.136.113.%' and

  DestHost not like '72.246.30.199%' and

  DestHost not like '68.142.122.198%' and

  DestHost not like '98.136.112.%' and

  DestHost not like '216.155.194.%' and

  DestHost not like '98.136.112.%' and

  DestHost not like '209.239.112.%' and

  DestHost not like '71.244.82.%' and

  DestHost not like '88.49.113.%' and

  DestHost not like '210.7.74.%' and

  DestHost not like 'ihs-12345%' and

  DestHost not like 'ms_proxy_auth_query' and

  DestHost not in ('msnbcmedia.msn.com', 'www.projectreader.com') and

  uri not like '%.gif'

  --2. 抓需要的Columns and Convert Time into (MM/DD/YYYY hh:mm)

  Select ClientIP, ClientUserName, Convert(nvarchar(30), logTime, 100) as logTime, servername into #ProxyLog_info from #ProxyLog_record

  --3. 同一個人同日期時間(Hour:Minute)只算一次 the same person

  Select distinct logTime, ClientUserName, ClientIP into #ProxyLog_count from #ProxyLog_info order by ClientUserName

  --4. 算上網總時間Count total internet usage minutes

  Select ClientUserName, count(ClientUserName) counts from #ProxyLog_count Group by ClientUserName order by Counts desc

  --5. Top 3 user internet usage

  Select ClientUserName, DestHost, count(DestHost) counts from #ProxyLog_record

  where ClientUserName ='IEC\IHC010202' Group by ClientUserName, DestHost order by counts desc

  --6. 算瀏覽網頁次數 Count website access times

  Select DestHost, count(DestHost) counts from #ProxyLog_record Group by DestHost order by counts desc

  Select (530/60)

  --drop Table #ProxyLog_record

  --drop Table #ProxyLog_info

  --drop Table #ProxyLog_count

  as you can see in section 5. i have to search for a specific one. but how can i get it to pull the top 3 data from the proxylog automatically?

  what im trying to do is get the top 3 users from the log, who uses the most internet so i need it to display the websites they use, along with the amount of times they visited the website.

• PaulB-TheOneAndOnly

  SSC Guru

  Points: 66773

  More actions

  June 26, 2012 at 5:11 pm

  #1505916

  ... select top 3?

  _____________________________________
  Pablo (Paul) Berzukov

  Author of Understanding Database Administration available at Amazon and other bookstores.

  Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.