Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Shell script - Asterisk logs report

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Shell script - Asterisk logs report
# 1  
Old 03-17-2016
Shell script - Asterisk logs report
Dear all,
I start to build script(s) for few tasks, and I'll use log files to complete the following:


Code:
1) when ringnoanswer for a particular operator hits count 10 for waittime > 14000 send mail alert with summary of calls
2) per queue - exitwithtimout > 1 in any hour, then send mail alert.
3)  7am each morning mail report showing count for each operator that was  logged in 
the previous day (i.e. midnight to midnight from day before)   with total ringnowanswer > 14000 wait time.


So I have plan to use cron tasks, and bash scripts with queue_log grep for EXITEMPTY, EXITWITHTIMEOUT, RINGNOANSWER, operator name and queue number.
Well, that is just plan for now. Please if you have any ideas, could you please share.. ? May be there is easier way to do this.. I have zabbix already, and data needed is there in sql database. May be there is a way to pull the needed data from there?
thanks in advance
OS: CentOS
Asterisk 11.18.0

---------- Post updated at 12:56 PM ---------- Previous update was at 04:35 AM ----------

My first step is created user "select" for mysql and the following:
Code:
mysql -u select -D zabbix -e "select clock,value from history_log" > /tmp/text.log

The output is:

Code:
1458182176      1458182148|1458182143.44543|1111|NAME NAME|RINGNOANSWER|4000
1458183289      1458183261|1458183252.44657|1111|NAME NAME|RINGNOANSWER|9000
1458183410      1458183393|1458183388.44682|1111|NAME NAME|RINGNOANSWER|4000
1458183621      1458183596|1458183592.44700|1111|NAME NAME|RINGNOANSWER|4000

So I believe my next step should be to make unix time into readable, may be using:
awk '{ print strftime("%c", $0); }'

I have no idea how to grep only with waittime > 14000 (this is last value after RINGNOANSWER), and how to add them into new text file (may be using cron and update text file with new results instead to rewrite the file every time). After that to check if there is NAME > 10, and if true to send mail with output. This may be will lead to new problem - duplicate emails, so may be when NAME > 10 is true, on email to clear the text file.

Please .. someone.. give idea
Last edited by jim mcnamara; 03-17-2016 at 11:06 PM..
# 2  
Old 03-17-2016
strftime("%c",$1) should use column #1.

Could you please show us what your expected final output should be based on your sample?
This User Gave Thanks to jim mcnamara For This Post:
bigbrobg
# 3  
Old 03-18-2016
For RINGNOANSWER:
current output (first column is zabbix alert time - not needed) :
Code:
1458182148      1458182148|1458182143.44543|1111|NAME NAME|RINGNOANSWER|4000

it should look like:
Code:
 Date time start|date time end|1111|NAME NAME|RINGNOANSWER| only > 14000

For EXITEMPTY current output is (fist column is zabbix time - not needed):
Code:
1457841440     1457841440|1457841419.11419|1111|NONE|EXITEMPTY|1|1|20
1457841440     1457841440|1457841419.11419|1111|NONE|EXITEMPTY|1|1|20

it should look like:
Code:
date time start| date time end|1111-name1|exitempty|1|1|20
date time start| date time end|1112-name2|exitempty|1|1|20

for EXITWITHTIMEOUT current output is (first column - zabbix time, not needed):

Code:
1457844081    1457844081|1457844020.11685|1111|NONE|EXITWITHTIMEOUT|1|1|60
1457844081    1457844081|1457844020.11685|1112|NONE|EXITWITHTIMEOUT|1|1|60

it should look like:
Code:
date time start| date time end|1111-name|exitwithtimeout|1|1|60
date time start| date time end|1112-name|exitwithtimeout|1|1|60

-----
what i did so far for exitwithtimeout:

- pull data from zabbix mysql using user "select" (have only select rights, no password):

Code:
mysql -u select -D zabbix -e "select clock,value from history_log;" | 
     grep -v -e endpoint -e ADDMEMBER -e REMOVEMEMBER -e '|02' |  grep EXITWITHTIMEOUT > "/tmp/exitwithtimeout.txt"

- rename queues:
sed -i -- 's/|1111|/|1111-Name_1|/g; s/|1112|/|1112-Name_2|/g' /tmp/exitwithtimeout.txt

- removed |NONE|:
Code:
sed -i -- 's/|NONE|/|/g' /tmp/exitwithtimeout.txt

- removed first column:
Code:
cat /tmp/exitwithtimeout.txt | awk '{print $2,$3,$4,$5;}' > /tmp/exitwithtimeout_strp.txt

- added date (do not know how to convert second column ..):
Code:
cat /tmp/exitwithtimeout_strp.txt | while read f1 f2; do echo $(date -d @$f1) $f2; done

-----

Thank you so much for your time helping me!
Last edited by jim mcnamara; 03-18-2016 at 09:30 AM..
# 4  
Old 03-18-2016
You need awk. -F and the OFS variable are helpful here
You can set the characters you want to be field separators:

Code:
awk -F '[ |]'  '{ awk script here }'

You can set the output field separator with OFS to the the pipe symbol:
Code:
awk -F '[ |]'  'BEGIN {OFS="|"} {rest of awk script here }'

You can print to different output files on the fly, plus you can eliminate the first field. And NONE records.
So:
Code:
awk -F '[ |]'  'BEGIN {OFS="|"} 
                   /NONE/ {next}   # skip the NONE record
                  {print $2,$3,$4,$5 > "$6"}' inputfile

The output files will be named: EXITEMPTY, RINGNOANSWER and so on.
I do not know what "1111-name" means - what is name in this context?

What I am trying to get you to do is to use one tool for a file transform - when you cram multiple commands together on single lines over and over again your code becomes un-maintainable and error prone. Trying tinkering with the awk code we have so far.
Last edited by jim mcnamara; 03-18-2016 at 09:32 AM..
This User Gave Thanks to jim mcnamara For This Post:
bigbrobg
# 5  
Old 03-18-2016
Thanks Jim. I'll try with AWK and will reply with results.

May be someone will ask why I'm not using logs directly from Asterisk. Well the pbx server is on different machine, and it's not possible to do so. At least for now. That's why I'm using zabbix mysql base.

@Jim - quotes like 1111 are for queues. Each queue have his own name, but sadly in logs I have only queue number, so I have to add name. In logs I have 1111, and I need to change it to 1111-name... like 1111-queue_support_team.. and so on.
I know it's not good the way I did it. I'm newbie with this things...., that's why I'm asking you guys... Smilie

---------- Post updated at 02:12 PM ---------- Previous update was at 08:58 AM ----------

Thanks to Jim for pointing me to right direction.. now I have this for exitwithtimeout:

1. mysql pull data
Code:
mysql -u select -D zabbix -e "select clock,value from history_log;" | grep -v -e endpoint -e ADDMEMBER -e REMOVEMEMBER -e '|02' |  grep EXITWITHTIMEOUT > "/tmp/exitwithtimeout.txt"

before text format:

Code:
1458314962      1458314937|1458314907.591|1111|NONE|EXITWITHTIMEOUT|1|1|30

first column is zabbix time, not needed. Second column is end time (why, don't ask, asterisk thing I think), third column is start time.

2. text format:
Code:
cat /tmp/exitwithtimeout.txt | awk -F '[ |]'  'BEGIN {OFS="|"} { print strftime("%c|", $2) strftime("%c|", $1) $3,$5,$8; }' | sed 's/|1111|/|1111-Support_team_queue|/g' > exitwithtimeout.log

final result:
Code:
Fri 18 Mar 2016 11:28:27 AM EDT|Fri 18 Mar 2016 11:29:22 AM EDT|1111-Support_team_queue|EXITWITHTIMEOUT|30

Good thing - first step is done. I have data and formatted text, and I will do that for other two log files.

Now I have to think how to include only waittime > 14000 into final result, and how to track particular
queue for exitwithtimout > 1 in any hour. WAITTIME and EXITWITHTIMEOUT are part from different files - WAITTIME is from RINGNOANSWER, and queues are in all three,
but I need only from EXITWITHTIMEOUT.

WAITTIME is last one in this output (raw, before text format):
Code:
1458220872 1458220872|1458220852.47158|1111|John Wright|RINGNOANSWER|20000

Queue in EXITWITHTIMEOUT is 4th in log file (raw output before text formatting):

Code:
1458314937  1458314937|1458314907.591|1111|NONE|EXITWITHTIMEOUT|1|1|30

---------- Post updated at 04:54 PM ---------- Previous update was at 02:12 PM ----------

Well.. the answer how to include only > 14000 maybe is :
Code:
awk -F '[|]' -v x=14000 '$6 > x' /tmp/ringnoanswer.txt

and included with things above:
Code:
cat /tmp/ringnoanswer.txt | awk -F '[|]'  'BEGIN {OFS="|" } {print strftime("%c|", $2) strftime("%c|", $1) $3,$4,$5,$6;'} | 
                    awk -F '[|]' -v x=14000 '$6 > x' | sed 's/|1111|/|1111-Support_team_queue|/g'  > ringnoanswer.log

Moderator's Comments:
Mod Comment Please use CODE tags (not ICODE tags) for full line and multi-line sample input, sample output, and code segments.
Last edited by bigbrobg; 03-19-2016 at 07:30 PM.. Reason: Change ICODE tags to CODE tags.
# 6  
Old 03-21-2016
These are my three scripts-like running every hour with cron.


RINGNOANSWER:

Code:
mysql -u select -D zabbix -e "select clock,value from history_log where itemid=25143;" |
        grep -v -e endpoint -e ADDMEMBER -e REMOVEMEMBER -e '|0' | 
        grep RINGNOANSWER > "/tmp/ringnoanswer.txt"

wait

cat /tmp/ringnoanswer.txt | awk -F '[|]'  'BEGIN {OFS="|" } {print strftime("%c|", $2) strftime("%c|", $1) $3,$4,$5,$6;'} |
awk -F '[|]' -v x=14000 '$6 > x' |       
        sed 's/|1111|/|1111-NOC_queue|/g;
        s/|1112|/|1112-Support_level_1|/g;
        s/|1113|/|1113-Support_level_2|/g' > /var/log/asterisk_agents_log/ringnoanswer.log

-------
Output:
Sun 20 Mar 2016 10:12:57 PM EDT|Sun 20 Mar 2016 10:13:21 PM EDT|1111-NOC_queue|John Inlall|RINGNOANSWER|20000
Sun 20 Mar 2016 10:40:38 PM EDT|Sun 20 Mar 2016 10:41:23 PM EDT|1111-NOC_queue|Ethan Milyon|RINGNOANSWER|20000
Mon 21 Mar 2016 07:45:18 AM EDT|Mon 21 Mar 2016 07:45:39 AM EDT|1111-NOC_queue|Peter Hill|RINGNOANSWER|20000
------
*waittime is the latest one (20000)

EXITWITHTIMEOUT:

Code:
mysql -u select -D zabbix -e "select clock,value from history_log where itemid=25152;" |
        grep -v -e endpoint -e ADDMEMBER -e REMOVEMEMBER -e '|02' | 
        grep EXITWITHTIMEOUT > "/tmp/exitwithtimeout.txt"

wait

cat /tmp/exitwithtimeout.txt | awk -F '[ |]'  'BEGIN {OFS="|"} { print strftime("%c|", $2) strftime("%c|", $1) $3,$5,$8; }' |
        sed 's/|1111|/|1111-NOC_queue|/g;
        s/|1112|/|1112-Support_level_1|/g;
        s/|1113|/|1113-Support_level_2|/g' > /var/log/asterisk_agents_log/exitwithtimeout.log

-----
Output:
Mon 21 Mar 2016 07:45:18 AM EDT|Mon 21 Mar 2016 07:46:08 AM EDT|1113-Support_level_2|EXITWITHTIMEOUT|30
Mon 21 Mar 2016 08:25:23 AM EDT|Mon 21 Mar 2016 08:26:11 AM EDT|1113-Support_level_2|EXITWITHTIMEOUT|30
-----

EXITEMPTY:

Code:
mysql -u select -D zabbix -e "select clock,value from history_log where itemid=25161;" |
        grep -v -e endpoint -e ADDMEMBER -e REMOVEMEMBER -e '|02' | 
        grep EXITEMPTY > "/tmp/exitempty.txt"

wait

cat /tmp/exitempty.txt | awk -F '[ |]'  'BEGIN {OFS="|"} { print strftime("%c|", $2) strftime("%c|", $1) $3,$5; }' |
        sed 's/|1111|/|1111-NOC_queue|/g;
        s/|1112|/|1112-Support_level_1|/g;
        s/|1113|/|1113-Support_level_2|/g' > /var/log/asterisk_agents_log/exitempty.log

-----
Output:
Wed 16 Mar 2016 07:53:17 PM EDT|Wed 16 Mar 2016 07:53:31 PM EDT|1112-Support_level_1|EXITEMPTY
Sat 19 Mar 2016 09:43:26 PM EDT|Sat 19 Mar 2016 09:44:38 PM EDT|1112-Support_level_1|EXITEMPTY
-----

Now I'm wondering how to improve them, and how to do the following:

- when RINGNOANSWER for a particular operator hits count 10 for WAITTIME > 14000. alert with summary of calls
- if EXITWITHTIMEOUT > 1 per each queue, in any hour - alert with mail including queues with this issue
- at 7am each morning, send mail report showing count for each operator that was logged in the previous day (ie midnight to midnight from day before)
with total RINGNOANSWER > 14000 "waittime"


And I am stuck..
Last edited by bigbrobg; 03-21-2016 at 11:20 AM..
# 7  
Old 03-24-2016
Code:
print strftime("%c|", $2) strftime("%c|", $1)

is changed to

Code:
print strftime("%a %d %b %Y %H:%M:%S %Z|",$2) strftime("%a %d %b %Y %H:%M:%S %Z|",$1)

because when it's executed by cron, some mismatch date output happen
Last edited by bigbrobg; 03-24-2016 at 10:54 AM..
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

If I ran perl script again,old logs should move with today date and new logs should generate.

Appreciate help for the below issue. Im using below code.....I dont want to attach the logs when I ran the perl twice...I just want to take backup with today date and generate new logs...What I need to do for the below scirpt.............. 1)if logs exist it should move the logs with extention... (1 Reply)
Discussion started by: Sanjeev G
1 Replies

2. Shell Programming and Scripting

Shell script for capturing FTP logs

I have a script #!/bin/bash HOST=ftp.example.com USER=ftpuser PASSWORD=P@ssw0rd ftp -inv $HOST <<EOF user $USER $PASSWORD cd /path/to/file mput *.html bye EOF the script executes sucessfully I need to capture the FTP logs to a logfile should contain FTP Login successful ... (1 Reply)
Discussion started by: rajeshas83
1 Replies

3. Shell Programming and Scripting

Help with Shell Script to View Logs

Hi I'm very new to unix shell scripting. Im also new here in this forum. I'm a SQL Server DBA but I'm slowly learning Oracle and Sybase DB. Our Oracle and Sybase are on Unix platforms. Im slowly learning Linux Admin and Shell Scripting to automate tasks. I'm writing a script to view DB error... (4 Replies)
Discussion started by: Ricky777
4 Replies

4. Shell Programming and Scripting

Managing logs in shell script

Hi, I need write a shell script which should be executed from the crontab every day. This shell script is running several other shell scripts , and each one of them is writing to its log file. Few of the the shell script are also connecting using ssh to some other users on remote machine , do... (1 Reply)
Discussion started by: Yoav
1 Replies

5. Shell Programming and Scripting

Help with extract application logs through shell script in performance testing

Hi Experts, I am new to shell.How to extract logs (Web,APP,Database) using shell in performance testing? Need for webserver logs,app server logs and d/b logs code. Thanks in advance Sree (3 Replies)
Discussion started by: sree vasu
3 Replies

6. Shell Programming and Scripting

Shell Script for GC Logs

Hi, I have a strange situation here, I want to archive gc.logs file, generated by a java application, the strange thing about gc.log file is is doesn't have any time/date stamp appended to it unlike other logs (catalina/access/error) and one more strange thing is when ever the application is... (6 Replies)
Discussion started by: Neeryan
6 Replies

7. Shell Programming and Scripting

Need to develop a script to create a report reading multiple server logs

I am currently trying to develop a script to connect to mulltiple servers, reading specifc data from log files on the servers and append the data from each file into a single tab delimited row. So, at the end I am planning to have a report with all the extracted data with each row per server. I am... (5 Replies)
Discussion started by: scriptingnewbie
5 Replies

8. Shell Programming and Scripting

help with a shell script that greps an error from the logs

Hello everyone. I wrote the following script but the second part is not excecuting. It is not sending the notification by email if the error occurs. the send mail is working so i think the errorr should be in the if statement LOGDIR=/logs/out LOG=`date "+%Y%m%d"`.LOG-FILE.out #the log file ... (11 Replies)
Discussion started by: adak2010
11 Replies

9. Shell Programming and Scripting

Use asterisk in shell script bash

Hello, I am trying to save in a file a single "*" but its not working... look what i am doing... FILE="/home/teste/a.txt" ...BEGIN... ASTERISK="*" echo "STRING $ASTERISK STRING" >> $FILE ...END... when i do it, the result is a list of all files of the current... (4 Replies)
Discussion started by: diogooute
4 Replies

10. Shell Programming and Scripting

Shell script to view logs of a server

Please share a shell script to collect logs of a server (like cpu utilization, memory etc) for a perticular time interval by giving date, time and server name as input. (1 Reply)
Discussion started by: abhishek27
1 Replies
Login or Register to Ask a Question