how to export squid access.log file in readable format?

danialnaeem · 05-08-2014, 07:25 AM

Dear Experts?

Please tell me how to export squid access.log file in readable and good format.

Regards
Danial

Enindu · 05-08-2014, 07:35 AM

Can't open access.log in Geany as root?

danialnaeem · 05-08-2014, 07:39 AM

i can see logs in access.log file but i want to see it in good format in excel. is it possible ?

Enindu · 05-08-2014, 08:32 AM

No idea. Sorry.

bathory · 05-08-2014, 09:15 AM

Quote:

Originally Posted by danialnaeem

i can see logs in access.log file but i want to see it in good format in excel. is it possible ?

You can change the access_log format from "squid" to "common" in squid.conf. E.g:

Code:

access_log /var/log/squid/access.log common

danialnaeem · 05-14-2014, 01:27 AM

this line is not showing in my squid conf file.. when i open logs file in excel all data are showing in first column... i want to convert access log data in good and proper excel format please help

bathory · 05-14-2014, 03:01 AM

Quote:

Originally Posted by danialnaeem

this line is not showing in my squid conf file.. when i open logs file in excel all data are showing in first column... i want to convert access log data in good and proper excel format please help

You can copy and rename the squid access.log to squid.csv for example. Then you can import the csv file into excel by specifying as separator the "space".
Please note that instead of using excel, you can use sarg to generate reports based on squid logs.

Regards

eklavya · 05-14-2014, 06:53 AM

Quote:

# sed 's/ */,/g' /var/log/squid/access.log > /var/log/squid/access.csv

It will replace every space into comma.
Now open this csv file into spread sheet. Check comma check box under 'separated by' option.

danialnaeem · 05-15-2014, 01:25 AM

OK thank you .. very help full

i want to know one more thing can i see only TCP denied logs in squid access log report? any command ?

Thanks

bathory · 05-15-2014, 02:46 AM

Quote:

Originally Posted by danialnaeem

OK thank you .. very help full
i want to know one more thing can i see only TCP denied logs in squid access log report? any command ?
Thanks

You can grep for "DENIED" in squid access.log and redirect the output to a new file for processing:

Code:

grep DENIED /var/log/squid/access.log > squid_denied.log

danialnaeem · 05-15-2014, 04:07 AM

ok thank you so much ....

and if i need this denied logs with specific IP address?? then how i can see this?

Thanks

bathory · 05-15-2014, 04:38 AM

Quote:

Originally Posted by danialnaeem

ok thank you so much ....

and if i need this denied logs with specific IP address?? then how i can see this?

Thanks

Use this:

Code:

cat /var/log/squid/access.log |grep DENIED| grep x.x.x.x > squid_denied_ip.log

where x.x.x.x is the IP in question

danialnaeem · 05-16-2014, 02:11 AM

thank you very very much for you help bathory ... these two commands are very help full for me.. Sir can i generate these logs only TCP denied and with specific ip in readable date & time ???

Thanks

bathory · 05-16-2014, 03:16 AM

Quote:

Originally Posted by danialnaeem

thank you very very much for you help bathory ... these two commands are very help full for me.. Sir can i generate these logs only TCP denied and with specific ip in readable date & time ???

Thanks

Please have a look at post #5 to see the change you need to do in access_log format in order to display date in human readable format.

danialnaeem · 05-16-2014, 03:36 AM

Sir, I am using squid 3.1 and this line is not showing in my squid.conf file. when i add this line in squid.conf file and restart service nothing happenes. ???

access_log /var/log/squid/access.log common