1

I need to use UNIX utilities that implement out-of-core divide-and-conquer algorithms so to decrease the amount of virtual memory used (cap of 1 mb). I have a large data set that is an apache log file. I am able to extract all the IP adresses and the date (converted to unix time (s)) and store it in a csv file called hits.

ip,timestamp
62.172.72.131,1041502001    
62.121.107.115,1041809792
163.28.16.1,1043258352
216.37.59.126,1044304131
68.104.188.224,1044472900
66.137.118.69,1044543502
62.172.72.131,1041502098
207.181.42.20,1044646708
205.156.184.254,1045670100
80.225.248.61,1045779615
200.44.24.157,1046214366
193.63.247.68,1046260886
66.176.248.72,1046317786
216.249.85.109,1046566502
68.44.222.12,1046638290
130.113.69.66,1048009156
143.43.220.157,1048026293

I now need to create a sessions file, which consists of the IP, time and number of hits. A session is 30 minutes long. For example, for hit 62.172.72.131 it must look through the file and find all hits with that IP address and determine whether it is in that session. The seventh hit has that IP address and 1041502097 - 1041502001 = 97 sec which is within 30 minutes so we have to write 62.172.72.131,97,2 to the sessions file. This needs to group by IP address and calculate sessions for that IP address i.e a session is 30 minutes, if an IP address outside that session (>1800 sec) than a new session line must be generated.

I have worked on the following code

awk 'BEGIN { FS=OFS=SUBSEP=","}{arr[$1]= $2 }END {for (i in arr) {...}}' hits.csv | sort -n 

Currently, I have no idea how to calculate the sessions and all the duplicates disappear when I print out i and arr[i]. I thought sorting it by IP address then all the hits from the same IP would appear sequentially.

closed as too broad by andcoz, cuonglm, Anthon, dr01, Jakuje Mar 9 '16 at 13:46

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    how about you sort your file first, than use uniq to see how many individual IPs you have (read them into an array). Then use the array to iterate over the IPs and for each IP over the timestamps doing the comparison. – Fiximan Mar 9 '16 at 11:34
  • I would prefer to store it in MySQL and leave the hard work to MySQL...in addition it will let you do stats overt time. – Rui F Ribeiro Mar 9 '16 at 11:36
  • I have to use either python and Unix, because of the restrictions on virtual memory. I cant use an array because this is a huge data file so the array will grow in size in memory. @Fiximan – Bryce Ramgovind Mar 10 '16 at 8:46
1

I am not sure what you really need... This is mainly a contribution, not an answer.

In the following we calculate a "session" number, and count the number of a[session][ip] hits

#!/usr/bin/gawk -f

BEGIN  { FS=OFS="," }
       { session = int($2/1800); a[session][$1] ++ }
END    { for(session in a){
           for(ip in a[session]){
               print a[session][ip], ip , session
           }
         }
       }

Notes:

  • 1800 = 30m * 60s
  • if useful print through sort: print a[session][ip], ip , session | "sort -Vr"
  • 1
    Would you mind to explain slightly what you are doing, and why the 1800, João? – Rui F Ribeiro Mar 9 '16 at 11:37
  • (I already got the 1800, 30 minutes) – Rui F Ribeiro Mar 9 '16 at 11:39

Not the answer you're looking for? Browse other questions tagged or ask your own question.