June 26, 2012 at 2:50 pm
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.
June 26, 2012 at 5:11 pm
... 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.Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply