Tuesday, October 29, 2019

Powershell - FTP download a file

This Powershell code will connect to an FTP site and download a file and store it in a location.

Method #1 LONG method with a bunch of error checking, mostly code i've found in other locations.
(method 2 below is far simpler).  I've included both for comparison and help with this

function Get-FTPfiles ($url,$credentials) 
        {
        write-host Connecting to FTP....
        $FTPrequest = [Net.WebRequest]::Create($url)
        $FTPrequest.Method = [System.Net.WebRequestMethods+FTP]::ListDirectory
        
        if ($credentials) 
            { 
            $FTPrequest.Credentials = $credentials 
            }
            
        $FTPresponse = $FTPrequest.GetResponse()
        
            if(!$FTPresponse)
            {   
            #if the response comes back as a 'null' it could mean that the FTP server might be down
            #the file wasn't found, something errored.  when this flag goes high (1) it cause the "UNTIL" statement to activate and break
            #out of this function loop
            $FTPState = 1
            break
            }
        
        $FTPfiles = New-Object IO.StreamReader $FTPresponse.GetResponseStream() 
                    
        while(-not $FTPfiles.EndOfStream) 
            {
            $FTPfiles.ReadLine()   
            }
    
        $FTPfiles.Close()
        $FTPresponse.Close()      
        }

$FTPserver = "ftp://123.123.123.123/" 
$FTPuser = 'FTPuserName' 
$FTPpassword = 'FTPpassword'
$FTPsourcefolder = '\'  #FTP subfolder if required
$FTPtargetfolder = "C:\downloads\" #local PC destination
$FTPfilename ="file to get"
$FTPState = 0

$credentials = new-object System.Net.NetworkCredential($FTPuser, $FTPpassword)
    
$folderPath= $FTPserver + "/" + $FTPsourcefolder + "/"
    
#the Do-Until loop below basically works in conjuction with the "get-FTPfiles" function.  In order to break out of the function,
#it runs 'until' the variable is not equal to 0, which causes the function to break out and continue with the program.
    do
        {
        $files = Get-FTPfiles -url $folderPath -credentials $credentials    
        $webclient = New-Object System.Net.WebClient 
        $webclient.Credentials = New-Object System.Net.NetworkCredential($FTPuser,$FTPpassword) 
   
        $FTPsourceFullPath=$folderPath + $FTPfilename  
        $ftpDestinationLocation = $FTPtargetfolder + $FTPfilename 
        $webclient.DownloadFile($FTPsourceFullPath, $FTPtargetfolder+$FTPfilename)
        $FTPState = 1
        }

    until ($FTPState -ne 0)
        {
        write-host FTP process flagged as completed
        }

$FileExists = Test-Path $ftpDestinationLocation

    If ($FileExists -eq $True) 
        {
        write-host File Downloaded Successfully
        
        }

METHOD 2
Script for DOWNLOADING from FTP

$FTPserverIP = "123.123.123.123"
$FTPuser = 'username'
$FTPpassword = 'password'
$FTPtargetfolder = "C:\download\"
$FTPfilename ="testfile.csv"

$client = New-Object System.Net.WebClient
$client.Credentials = New-Object System.Net.NetworkCredential("$FTPuser", "$FTPpassword")

$client.DownloadFile("ftp://$FTPserverIP/$FTPfilename", "$FTPtargetfolder\$FTPfilename")


Script for UPLOADING to FTP

$FTPserverIP = "123.123.123.123"
$FTPuser = 'username'
$FTPpassword = 'password'
$FTPtargetfolder = "C:\download\"
$FTPfilename ="testfile.csv"

$client = New-Object System.Net.WebClient
$client.Credentials = New-Object System.Net.NetworkCredential("$FTPuser", "$FTPpassword")
$client.UploadFile("ftp://$FTPserverIP/$FTPfilename", "$FTPtargetfolder\$FTPfilename")

Wednesday, October 9, 2019

Find the largest files in your linux file system


This will give you the top largest 20 files in a linux system collectively (directory of VAR in the example)

du -a /var/ | sort -n -r | head -n 20


du will estimate file space usage.
sort will sort out the output of du command.
head will only show top 20 largest file in /dir/

Tuesday, October 8, 2019

SQL parsing a carriage return delimited column

I've encountered this in the past and coworker helped me figure out a way to do it.  I'm positing this for my own memory but maybe it will help someone else too!

I was trying to pull a specific line from a database, but the column contained Carriage Delimited data.  As you can see "passwords" is not your normal database entry.  

In my situation, the first 4 digits are system generated, and the last 4 digits are manually generated.  So, if a password change was required, the only 4 digits i know are the first 4.

So I wanted to pull the exact line out of the system to obtain the password.

The problem is, doing a "select from passwords" would return the entire column into a single variable, treating every password as just one long huge single password.


> select * from pinsets where pinsets_id = 2
+------------+--------------------------+-------------+----------+----------+ | pinsets_id | passwords | description | addtocdr | deptname | +------------+--------------------------+-------------+----------+----------+ | 2 | 10001500 | testpins | 0 | | 10012400 10023400 10034400 10045400 +------------+--------------------------+-------------+----------+----------+ 1 row in set (0.00 sec)

I needed a way to search for the first 4 digits of the the password, and then have the system parse out the last 4 digits and give me just the line.

I want to search for "1000" and have the system return "10001500"

This was what I used

select MID((select passwords from pinsets where description = 'testpins'), INSTR ((select passwords from pinsets), "1000"),8);

The MID command pulls everything out of passwords into a single file, then I do an INSTR (instring) to find the first instance of 1000, and then pull all 8 characters (the first 4 characters '1000' followed by the last 4 characters '1500')

Running this command gave me the result I needed.

+---------------------------------------------------------------------------------------------------------------------------------+ | MID((select passwords from pinsets where description = 'testpins'), INSTR ((select passwords from pinsets), "1000"),8) | +---------------------------------------------------------------------------------------------------------------------------------+ | 10001500 | +---------------------------------------------------------------------------------------------------------------------------------+

This is not perfect, but it worked for my situation.  I had a degree of difficulty finding information on line for this situation, so I wanted to post what I found in case it helped someone else.

Monday, October 7, 2019

Asterisk - limit extension total usage duration

Here's how you an limit the extension duration based on values in a PINSET in the GUI.
This will use a PINSET with the description of "extenmaxduration" (system is hardcoded to this description for this example)
LIMITATION.
This code works with FOUR DIGIT extensions ONLY
Create a PINSET with the description "extensionduration"
In the pins put in pins using the SYNTAX:
Extension*Minutes
Example:
1005*00500
1006*01500
1103*99999
EXPLAINED:
PINSET values are stored in the database using "carriage return" delimitation within the actual column, so we need to parse the data returned by the SQL statement. This means the data MUST be a consistent length for my select statement..
This is why we have leading zero's ( 00 ) in the duration to maintain the length for the SQL script i have. I'm sure there are far smarter people than me who can work this out, but this is what I was able to make work.
In my example,
extension 1005 is allowed 500 minutes "1005*00500"
extension 1006 is allowed 1500 minutes "1006*01500"
extension 1103 is allowed 99999 minutes "1103*99999"
Extensions that are NOT in the list, the code will simply bypass them and allow those calls to continue.
So here is the entire code. Again the "outrt-X" heading will need to be altered to suit your environment.
[outrt-2] ; limit by extensions
include => outrt-2-custom
exten => _.,1,Macro(user-callerid,LIMIT,EXTERNAL,)
exten => _.,n,Set(MOHCLASS=${IF($["${MOHCLASS}"=""]?default:${MOHCLASS})})
exten => _.,n,Set(_NODEST=)
exten => _.,n,Gosub(sub-record-check,s,1(out,${EXTEN},))

;open connection to ASTERISK table to get the pinset data
exten => _.,n,MYSQL(Connect connid localhost root PBXPASS asterisk)
exten => _.,n,MYSQL(Query resultid ${connid} select MID((select passwords from pinsets where description = 'extensionduration'), INSTR ((select passwords from pinsets), "${ampuser}"),10) from pinsets)
exten => _.,n,MYSQL(Fetch fetchid ${resultid} extenmaxduration)
exten => _.,n,MYSQL(Clear ${resultid})
exten => _.,n,MYSQL(Disconnect ${connid})

;If we dont find a matching extension, we'll skip the rest of the checks and just let it dial out.
exten => _.,n,GotoIf($["${extenmaxduration}" = ""]?continue)

;parse the return using an * as the delimiter between extension and minute values
exten => _.,n,Set(extenmaxduration=${CUT(extenmaxduration,*,2)})

;change mins to seconds
exten => _.,n,Set(extenmaxduration=${MATH(${extenmaxduration}*60,int)})

;open a new connection CDR table
exten => _.,n,MYSQL(Connect connid localhost root PBXPASS asteriskcdrdb)
exten => _.,n,MYSQL(Query resultid ${connid} SELECT if(sum(duration) < ${extenmaxduration}, 1, 0) FROM cdr WHERE (calldate between DATE_FORMAT(NOW() ,'%Y-%m-01') AND NOW() ) and src like '${AMPUSER}')
exten => _.,n,MYSQL(Fetch fetchid ${resultid} extenmaxduration)
exten => _.,n,MYSQL(Clear ${resultid})
exten => _.,n,MYSQL(Disconnect ${connid})

;if we dont find any value in pinset, we'll default allow this call to go through.
exten => _.,n(continue),execif($["${extenmaxduration}" = ""]?set(extenmaxduration=1))

;if montly duration is less than pinset duration, go out trunk, else say "time" and hangup
exten => _.,n,execif($["${extenmaxduration}" = "1"]?macro(dialout-trunk,1,${EXTEN},,off))
exten => _.,n,execif($["${extenmaxduration}" = "0"]?playback(time))
exten => _.,n,Macro(outisbusy,)
Save your changes and reload your configs.
Give it a try!

Remember that your values for your extensions need a leading zereo, and those values are stored in MINUTES. The script will do the calculation to change it to SECONDS which the CDR table uses.
In this example, if the extension has not exceed its maximum, the call will proceed out the trunk.
If it has exceeded the max, you will hear the voice "TIME" and then a "all circuits busy"

Thursday, October 3, 2019

Asterisk Balancing outbound calls across multiple random trunks

This code will send calls out trunks via a random generator

HOW IT WORKS
It adds a random generator and then will send calls across multiple trunks
(GUI) PBX -> Outbound Routes
Open the route you want to apply this random trunk
1: Under "Trunk Sequence" you want the first route to be the one if the time limit is NOT exceeded
2: The next route is the one ff the time IS exceeded. (you can have as many more as you want)
Make sure those changes are saved
Now open up /etc/asterisk/extensions_additional.conf
Find your route in the dialplan. it will look SOMETHING like the one below. Yours maybe different heading and number of trunks, so just use mine as an example only, modify your file.
This is mine, and i have TWO Destinations

[outrt-2] ; randomtrunk
include => outrt-2-custom
exten => _.,1,Macro(user-callerid,LIMIT,EXTERNAL,)
exten => _.,n,Set(MOHCLASS=${IF($["${MOHCLASS}"=""]?default:${MOHCLASS})})
exten => _.,n,Set(_NODEST=)
exten => _.,n,Gosub(sub-record-check,s,1(out,${EXTEN},))
exten => _.,n,Macro(dialout-trunk,1,${EXTEN},,off)
exten => _.,n,Macro(dialout-trunk,2,${EXTEN},,off)
exten => _.,n,Macro(outisbusy,)
;--== end of [outrt-2] ==--;

Copy and paste the entire section
open up /etc/asterisk/extensions_override_issabelpbx.conf  (if you are using a different deployment "extensions_custom.conf" should work as well
Go to the bottom of the file, and PASTE it

Now under the line with "GOSUB" put this value in

exten => _.,n,Set(randomtrunk=${RAND(1,2)});

The 1 and 2 signify the low and high number.  So if you have 2 trunks, it would be 1 & 2.  If you had 4, it would be 1 & 4.

These are the trunks are called. 
In my example two trunks are called, and if they do not work, "outisbusy" is run which is "all circuits are busy" message. You'll notice in my example it has "..trunk, 1, $ ..." and followed by "... trunk, 2, $ ...".
exten => _.,n,Macro(dialout-trunk,1,${EXTEN},,off)
exten => _.,n,Macro(dialout-trunk,2,${EXTEN},,off)
exten => _.,n,Macro(outisbusy,)
;--== end of [outrt-2] ==--;

It IS possible that, although mine are in numeric order, yours may not be. So just make note of that when you see the modification example
Change the first line to look like this instead. Just note if your 1 or 2 values ​​are in a different order and adjust accordingly.

execif($["${randomtrunk}" = "X"]?......

exten => _.,n,execif($["${randomtrunk}" = "1"]?macro(dialout-trunk,1,${EXTEN},,off))
exten => _.,n,execif($["${randomtrunk}" = "2"]?macro(dialout-trunk,2,${EXTEN},,off))


Just change the "X" value to be the match to one of your randomly generated values.

Heres what it will look like when you are done.

[outrt-2] ; randomtrunk
include => outrt-2-custom
exten => _.,1,Macro(user-callerid,LIMIT,EXTERNAL,)
exten => _.,n,Set(MOHCLASS=${IF($["${MOHCLASS}"=""]?default:${MOHCLASS})})
exten => _.,n,Set(_NODEST=)
exten => _.,n,Gosub(sub-record-check,s,1(out,${EXTEN},))

exten => _.,n,Set(randomtrunk=${RAND(1,2)});
exten => _.,n,execif($["${randomtrunk}" = "1"]?macro(dialout-trunk,1,${EXTEN},,off))
exten => _.,n,execif($["${randomtrunk}" = "2"]?macro(dialout-trunk,2,${EXTEN},,off))

exten => _.,n,Macro(outisbusy,)
;--== end of [outrt-2] ==--;

From the command line type
asterisk -rx 'core reload'
Test and try!
Please note that once this code is in place, you can't change the trunk order in the GUI for this outbound route. This code will over-ride the GUI for that. So if you DO need to make changes, you can make the change the GUI, but you will need to re-perform these custom modifications for it to work.
Anyone who is trying this, and you should only do this in a test server if you aren't familiar, but if you DO have problems and its messing up your PBX, in the "extensions_override_issabelpbx.conf" file. You can just erase these changes and save / reload the file and your pBX will revert to its original config for this rule.

Asterisk - limit total time usage of a trunk

Here's how you can limit the amount of time used on a particular trunk by looking up the time in SQL.
I tested this in the ISSABEL deployment of asterisk, but should work in any.

Once the time is exceeded on a trunk, the dialplan will skip to subsequent trunks.
(scroll to the bottom for advanced users for the completed code, the next bit I'm showing how to make the code change for users unfamiliar with dialplan changes)

HOW IT WORKS
The way this works is upon making a call that would use a particular trunk, the system does an SQL query on total outbound seconds used for the last 30 days.  It selects it by looking at the trunk name and adding up the values.
Then depending if you've reached your maximum or not, it will send the call to trunks accordingly.
(GUI) PBX -> Outbound Routes
Open the route you want to apply this "time limit" rule and verify at least these TWO conditions
1: Under "Trunk Sequence" you want the first route to be the one if the time limit is NOT exceeded
2: The next route is the one ff the time IS exceeded. (you can have as many more as you want)
Make sure those changes are saved
Now open up /etc/asterisk/extensions_additional.conf
Find your route in the dialplan. it will look SOMETHING like the one below. Yours maybe different heading and number of trunks, so just use mine as an example only, modify your file.
This is mine, and i have TWO Destinations
[outrt-2] ; 1000minlimitroute
include => outrt-2-custom
exten => _.,1,Macro(user-callerid,LIMIT,EXTERNAL,)
exten => _.,n,Set(MOHCLASS=${IF($["${MOHCLASS}"=""]?default:${MOHCLASS})})
exten => _.,n,Set(_NODEST=)
exten => _.,n,Gosub(sub-record-check,s,1(out,${EXTEN},))
exten => _.,n,Macro(dialout-trunk,1,${EXTEN},,off)
exten => _.,n,Macro(dialout-trunk,2,${EXTEN},,off)
exten => _.,n,Macro(outisbusy,)
;--== end of [outrt-2] ==--;
Copy and paste the entire section
open up /etc/asterisk/extensions_override_issabelpbx.conf  (if you are using a different deployment "extensions_custom.conf" should work as well
Go to the bottom of the file, and PASTE it
Now we'll modify this file.
First we'll add the database lookup, insert this code. Change USERNAME and PASSWORD to be your database:
Change TRUNKNAME to be the trunk name you want to check against.
Change "30" to be the number of days you want to look back on
Change 60000 to be the number of seconds you want to limit to
exten => _.,n,MYSQL(Connect connid localhost USERNAME PASSWORD asteriskcdrdb)
exten => _.,n,MYSQL(Query resultid ${connid} SELECT if(sum(duration) < 60000, 1, 0) FROM cdr WHERE DATE(calldate) >= DATE(NOW()) - INTERVAL 30 DAY and dstchannel like '%TRUNKNAME%')
exten => _.,n,MYSQL(Fetch fetchid ${resultid} dynroute)
exten => _.,n,MYSQL(Clear ${resultid})
exten => _.,n,MYSQL(Disconnect ${connid})
Put it under the GOSUB statement in the base dialplan code:
It will look like this:
[outrt-2] ; 1000minlimitroute
include => outrt-2-custom
exten => _.,1,Macro(user-callerid,LIMIT,EXTERNAL,)
exten => _.,n,Set(MOHCLASS=${IF($["${MOHCLASS}"=""]?default:${MOHCLASS})})
exten => _.,n,Set(_NODEST=)
exten => _.,n,Gosub(sub-record-check,s,1(out,${EXTEN},))

exten => _.,n,MYSQL(Connect connid localhost user password asteriskcdrdb)
exten => _.,n,MYSQL(Query resultid ${connid} SELECT if(sum(duration) < 60000, 1, 0) FROM cdr WHERE DATE(calldate) >= DATE(NOW()) - INTERVAL 30 DAY and dstchannel like '%trunkname%')
exten => _.,n,MYSQL(Fetch fetchid ${resultid} dynroute)
exten => _.,n,MYSQL(Clear ${resultid})
exten => _.,n,MYSQL(Disconnect ${connid})
Now, we'll modify the trunk to be dialed. Your code will look like something like this.
These are the trunks are called. In my example two trunks are called, and if they do not work, "outisbusy" is run which is "all circuits are busy" message. You'll notice in my example it has "..trunk, 1, $ ..." and followed by "... trunk, 2, $ ...".
exten => _.,n,Macro(dialout-trunk,1,${EXTEN},,off)
exten => _.,n,Macro(dialout-trunk,2,${EXTEN},,off)
exten => _.,n,Macro(outisbusy,)
;--== end of [outrt-2] ==--;
It IS possible that, although mine are in numeric order, yours may not be. So just make note of that when you see the modification example
Change the first line to look like this instead. Just note if your 1 or 2 values ​​are in a different order and adjust accordingly.
exten => _.,n,execif($["${dynroute}" = "0"]?macro(dialout-trunk,1,${EXTEN},,off))
This will tell the dialplan that IF the total time in SQL is NOT 1000 minutes (60000 seconds) then use the first trunk.
IF the dialplan is told by SQL IS more than 1000 minutes, the dialplan will execute the remaining trunk (s) in the order you have them.
Now your code will look something like this when completed:
username / password are you MYsql credentials
TRUNKNAME = trunk name in GUI (can be partial).
Change "30" to be the number of days you want to look back on
Change 60000 to be the number of seconds you want to limit to
[outrt-2] ; 1000minlimitroute
include => outrt-2-custom
exten => _.,1,Macro(user-callerid,LIMIT,EXTERNAL,)
exten => _.,n,Set(MOHCLASS=${IF($["${MOHCLASS}"=""]?default:${MOHCLASS})})
exten => _.,n,Set(_NODEST=)
exten => _.,n,Gosub(sub-record-check,s,1(out,${EXTEN},))

exten => _.,n,MYSQL(Connect connid localhost username password asteriskcdrdb)
exten => _.,n,MYSQL(Query resultid ${connid} SELECT if(sum(duration) < 60000, 1, 0) FROM cdr WHERE DATE(calldate) >= DATE(NOW()) - INTERVAL 30 DAY and dstchannel like '%trunkname%')
exten => _.,n,MYSQL(Fetch fetchid ${resultid} dynroute)
exten => _.,n,MYSQL(Clear ${resultid})
exten => _.,n,MYSQL(Disconnect ${connid})

exten => _.,n,execif($["${dynroute}" = "0"]?macro(dialout-trunk,1,${EXTEN},,off))

exten => _.,n,Macro(dialout-trunk,2,${EXTEN},,off)   
exten => _.,n,Macro(outisbusy,)
;--== end of [outrt-2] ==--;
From the command line type
asterisk -rx 'core reload'
Test and try!
Please note that once this code is in place, you can't change the trunk order in the GUI for this outbound route. This code will over-ride the GUI for that. So if you DO need to make changes, you can make the change the GUI, but you will need to re-perform these custom modifications for it to work.
Anyone who is trying this, and you should only do this in a test server if you aren't familiar, but if you DO have problems and its messing up your PBX, in the "extensions_override_issabelpbx.conf" file. You can just erase these changes and save / reload the file and your pBX will revert to its original config for this rule.