Community discussions

MikroTik App
 
bwigham
newbie
Topic Author
Posts: 33
Joined: Sun Oct 10, 2010 11:31 pm
Location: UK
Contact:

Export user - email script

Tue Dec 03, 2013 10:23 pm

Hi,

Thought i'd be better posting this under scripting..

I've setup a hotspot that supplies free internet using the trial option and I would like to get some info (like how many users have logged on daily), but because they are trial accounts (and not saved in Userman) I can't run any reports. I would like to run a script every evening that would export the hotspot user list and email it.

Can anyone help? If you can script this, I can pay for your time.

Thanks,

Barry.
 
User avatar
nick3dos
Member Candidate
Member Candidate
Posts: 189
Joined: Fri Apr 29, 2011 11:03 pm
Location: Greece

Re: Export user - email script

Wed Dec 04, 2013 11:26 am

In my setup i give free internet for 1:30 hour with trial method.
I keep a file "connections.txt" witch keeps the total number of users logged in.
I use an other program to download the file with ftp and get my daily and my total reports
You do not have the required permissions to view the files attached to this post.
 
bwigham
newbie
Topic Author
Posts: 33
Joined: Sun Oct 10, 2010 11:31 pm
Location: UK
Contact:

Re: Export user - email script

Wed Dec 04, 2013 5:11 pm

Hi nick3dos,

Thanks for the reply.

If it's possible I'd rather have the text file with the user list emailed, so I can send it directly to the site owner and they can then import to excel for their reports. They wouldn't know how to use FTP and I would have to do it for them.

Do you think this is possible.?

Barry.
 
User avatar
nick3dos
Member Candidate
Member Candidate
Posts: 189
Joined: Fri Apr 29, 2011 11:03 pm
Location: Greece

Re: Export user - email script

Wed Dec 04, 2013 5:49 pm

use the settings below (ofcourse you have to change username and password of your email account):
You do not have the required permissions to view the files attached to this post.
 
bwigham
newbie
Topic Author
Posts: 33
Joined: Sun Oct 10, 2010 11:31 pm
Location: UK
Contact:

Re: Export user - email script

Wed Dec 04, 2013 7:57 pm

Thanks nick3dos,

I copied your connections script, but I don't know if I'm missing something from the picture as it's running and not creating the .txt
 
User avatar
nick3dos
Member Candidate
Member Candidate
Posts: 189
Joined: Fri Apr 29, 2011 11:03 pm
Location: Greece

Re: Export user - email script

Wed Dec 04, 2013 8:49 pm

Thanks nick3dos,

I copied your connections script, but I don't know if I'm missing something from the picture as it's running and not creating the .txt
I forgot to tell you...
You have to make the starting file, so...
just create the text file "connections.txt" in your windows pc and just write the number 0 in the file and save it.
finally drag and drop it in your mikrotik files...
 
bwigham
newbie
Topic Author
Posts: 33
Joined: Sun Oct 10, 2010 11:31 pm
Location: UK
Contact:

Re: Export user - email script

Wed Dec 04, 2013 9:33 pm

I worked that out myself after replying last :)

So if I understand correctly now, I run this script every time someone logs in and it increments the number in the txt?
 
User avatar
nick3dos
Member Candidate
Member Candidate
Posts: 189
Joined: Fri Apr 29, 2011 11:03 pm
Location: Greece

Re: Export user - email script

Wed Dec 04, 2013 9:52 pm

yes, everytime someone logs in, the number in the file goes +1
 
bwigham
newbie
Topic Author
Posts: 33
Joined: Sun Oct 10, 2010 11:31 pm
Location: UK
Contact:

Re: Export user - email script

Wed Dec 04, 2013 11:34 pm

Do you know if there's anyway of running the print command under users and saving that to txt?

Thanks.
 
User avatar
nick3dos
Member Candidate
Member Candidate
Posts: 189
Joined: Fri Apr 29, 2011 11:03 pm
Location: Greece

Re: Export user - email script

Thu Dec 05, 2013 12:26 am

what do you want to print?
usernames that have logged in during the day?

if so, you have to make a new script to keep it in a text file, but you have to check for duplicate usernames and looged out usernames.

It is more complicated script....
 
bwigham
newbie
Topic Author
Posts: 33
Joined: Sun Oct 10, 2010 11:31 pm
Location: UK
Contact:

Re: Export user - email script

Thu Dec 05, 2013 12:35 am

Hi nick3dos,

Thanks for your time on this.

I would like to save the output of ip hotspot users print, but the file would need reset after sending the email. The hotspot is in a shopping mall and it's set to give an hour free reset after 12hrs.

thanks.
 
User avatar
nick3dos
Member Candidate
Member Candidate
Posts: 189
Joined: Fri Apr 29, 2011 11:03 pm
Location: Greece

Re: Export user - email script

Thu Dec 05, 2013 9:31 am

As i understand you need something like that:

For saving the online users to file
/ip hotspot active print file=connections.txt
For saving the hotspot users to file
/ip hotspot user print file=connections.txt
To reset the file
/file set connections.txt contents=""
Use this commands in scripts before and after sending the email.
 
bwigham
newbie
Topic Author
Posts: 33
Joined: Sun Oct 10, 2010 11:31 pm
Location: UK
Contact:

Re: Export user - email script

Fri Dec 06, 2013 12:30 am

Hi nick3dos,

Could you post the code from the email script, not sure if I'm spacing things correctly as it's not running.

Thanks,

Barry.
 
bwigham
newbie
Topic Author
Posts: 33
Joined: Sun Oct 10, 2010 11:31 pm
Location: UK
Contact:

Re: Export user - email script

Fri Dec 06, 2013 12:45 am

Got it, I didn't have a space right :)

Again, thanks for your help with this.
 
User avatar
nick3dos
Member Candidate
Member Candidate
Posts: 189
Joined: Fri Apr 29, 2011 11:03 pm
Location: Greece

Re: Export user - email script

Fri Dec 06, 2013 12:47 am

/system script
add name=email policy=ftp,reboot,read,write,policy,test,winbox,password,sniff,sensitive,api source="#get name date and time\r\
    \n:local name [/system identity get name];\r\
    \n:local date [/system clock get date];\r\
    \n:local time [/system clock get time];\r\
    \n#make subject message\r\
    \n:local sub (\$name . \" - \" . \$time . \" - \" . \$date);\r\
    \n#make body message\r\
    \n:local bod (\"See attached file\");\r\
    \n#send e-mail\r\
    \n/tool e-mail send to=\"user@gmail.com\" subject=\$sub body=\$bod  server=[:resolve smtp.gmail.com] tls=yes file=connections.txt;\r\
    \n\r\
    \n"
 
User avatar
nick3dos
Member Candidate
Member Candidate
Posts: 189
Joined: Fri Apr 29, 2011 11:03 pm
Location: Greece

Re: Export user - email script

Fri Dec 06, 2013 12:49 am

Got it, I didn't have a space right :)

Again, thanks for your help with this.
:D
 
obadaabdullah
Frequent Visitor
Frequent Visitor
Posts: 71
Joined: Wed Jan 08, 2014 7:41 pm

Re: Export user - email script

Wed Jan 29, 2014 11:12 pm

/system script
add name=email policy=ftp,reboot,read,write,policy,test,winbox,password,sniff,sensitive,api source="#get name date and time\r\
    \n:local name [/system identity get name];\r\
    \n:local date [/system clock get date];\r\
    \n:local time [/system clock get time];\r\
    \n#make subject message\r\
    \n:local sub (\$name . \" - \" . \$time . \" - \" . \$date);\r\
    \n#make body message\r\
    \n:local bod (\"See attached file\");\r\
    \n#send e-mail\r\
    \n/tool e-mail send to=\"user@gmail.com\" subject=\$sub body=\$bod  server=[:resolve smtp.gmail.com] tls=yes file=connections.txt;\r\
    \n\r\
    \n"

how can i include my users bytes in and out and limit bytes total in this file, can i have it easier to read????
thanks alot
 
bwigham
newbie
Topic Author
Posts: 33
Joined: Sun Oct 10, 2010 11:31 pm
Location: UK
Contact:

Re: Export user - email script

Wed Jan 29, 2014 11:30 pm

This is the script I use for sending the email.

/ip hotspot user print detail file=connections.txt

#get name date and time
:local name [/system identity get name];
:local date [/system clock get date];
:local time [/system clock get time];

#make subject message
:local sub ($name . " - " . $time . " - " . $date);

#Make message body
:local bod ("Please find attached a text file containing todays user usage stats, this can be imported into excel");

/tool e-mail send to="you@yourdomain.com" subject=$sub body=$bod file="connections.txt";



I run this as a script to reset the connections.txt file

/file set connections.txt contents=""

And here's the content of the connections.txt file

1 D server=hotspot1 name="T-1A:2A:3A:4A:5A:6A" password=""
mac-address=A4:EB:D3:6B:0E:F7 profile=Freewifi limit-uptime=1h
uptime=6m45s bytes-in=241325 bytes-out=1061487 packets-in=1487
packets-out=1365

2 D server=hotspot1 name="T-1B:2B:3B:4B:5B:6B" password=""
mac-address=14:5A:05:44:B6:18 profile=Freewifi limit-uptime=1h
uptime=47m27s bytes-in=1767827 bytes-out=10917084 packets-in=12990
packets-out=12321

3 D server=hotspot1 name="T-1C:2C:3C:4C:5C:6C" password=""
mac-address=44:4C:0C:DA:00:BF profile=Freewifi limit-uptime=1h
uptime=9m3s bytes-in=1029893 bytes-out=4551726 packets-in=5534
packets-out=5210
 
obadaabdullah
Frequent Visitor
Frequent Visitor
Posts: 71
Joined: Wed Jan 08, 2014 7:41 pm

Re: Export user - email script

Thu Feb 06, 2014 10:04 am

This is the script I use for sending the email.

/ip hotspot user print detail file=connections.txt

#get name date and time
:local name [/system identity get name];
:local date [/system clock get date];
:local time [/system clock get time];

#make subject message
:local sub ($name . " - " . $time . " - " . $date);

#Make message body
:local bod ("Please find attached a text file containing todays user usage stats, this can be imported into excel");

/tool e-mail send to="you@yourdomain.com" subject=$sub body=$bod file="connections.txt";



I run this as a script to reset the connections.txt file

/file set connections.txt contents=""

And here's the content of the connections.txt file

1 D server=hotspot1 name="T-1A:2A:3A:4A:5A:6A" password=""
mac-address=A4:EB:D3:6B:0E:F7 profile=Freewifi limit-uptime=1h
uptime=6m45s bytes-in=241325 bytes-out=1061487 packets-in=1487
packets-out=1365

2 D server=hotspot1 name="T-1B:2B:3B:4B:5B:6B" password=""
mac-address=14:5A:05:44:B6:18 profile=Freewifi limit-uptime=1h
uptime=47m27s bytes-in=1767827 bytes-out=10917084 packets-in=12990
packets-out=12321

3 D server=hotspot1 name="T-1C:2C:3C:4C:5C:6C" password=""
mac-address=44:4C:0C:DA:00:BF profile=Freewifi limit-uptime=1h
uptime=9m3s bytes-in=1029893 bytes-out=4551726 packets-in=5534
packets-out=5210


thanks for responding
I've tried it but i didn't get the same result , I'm on 951 RB, any changes???
thanks
 
User avatar
nick3dos
Member Candidate
Member Candidate
Posts: 189
Joined: Fri Apr 29, 2011 11:03 pm
Location: Greece

Re: Export user - email script

Fri Feb 07, 2014 10:11 am

thanks for responding
I've tried it but i didn't get the same result , I'm on 951 RB, any changes???
thanks
What is your result in the file???
 
obadaabdullah
Frequent Visitor
Frequent Visitor
Posts: 71
Joined: Wed Jan 08, 2014 7:41 pm

Re: Export user - email script

Mon Feb 10, 2014 12:56 am

thanks for responding
I've tried it but i didn't get the same result , I'm on 951 RB, any changes???
thanks
What is your result in the file???

thanks it work great.
I'm sorry but I've asked many times with no respond:
i have a hotspot with (username & password) , users have a limit bytes (upload+download), is there any way to but the remaining amount of their (limit bytes total -(bytes in + bytes out)) in the statues page so i don't need to be asked by each and each user about his account???
thanks for responding

Who is online

Users browsing this forum: BoandlKramer and 18 guests