How to run a query on a regular schedule (using crontab) and send the output via email or to an SMB share? |
Discovery does not currently have a facility to do this. However, it is possible to run a query on a regular basis by using tw_query and scheduling it in a cron job. To enable this then connect to the appliance through ssh then: 1- TO SEND THE REPORT BY EMAIL: 1.1- [Optional] Create a folder to store the scripts:
If there's not already one then one may want to run the following command line, as an example: $ mkdir /usr/tideway/my_custom_scripts
$ cd /usr/tideway/my_custom_scripts 1.2- [Optional] Create a password file: This depends on: A- if the option "--passwordfile" will be used in the script (see next step)
B- if this file already exists or not If using --passwordfile and that the file doesn't exist yet then (for this example the file will be called "pwd" and be stored in /usr/tideway/my_custom_scripts):
1.2.1- Run the following command line:
$ vi /usr/tideway/my_custom_scripts/pwd
1.2.2- Type "i" to edit
1.2.3- Set the password in clear text then type on the key "Esc" to escape and finally save by typing ":wq" then press Enter 1.2.4- [Optional but recommended] To secure the password file, set the file permissions to owner-only, e.g by running the following command line: $ chmod 600 /usr/tideway/my_custom_scripts/pwd
1.3- Create the script: Run the following command line to create the script: $ vi check_on_consolidation.sh
Following is an example of a script that can execute a query and send the result by email. The dos2unix command is needed to prevent blank lines in the output Excel spreadsheet.
### First, remove previous output file, if it is there
rm -f _RESULTS_CSV_ ### Run the query /usr/tideway/bin/tw_query --username=_DISCOVERY_USER_ --passwordfile=_PWDFILE_ --csv --file _RESULTS_CSV_ "_QUERY_" ### Remove the additional blank lines from the output csv file dos2unix _RESULTS_CSV_ ### Email the results. Provide the relevant smtp info and email address ### This an example only. This command line may need to be edited, to match the company mail server configuration echo "_MAIL_BODY_" | mailx -v -r "_MAIL_SENDER_" -s "_MAIL_SUBJECT_" -a "_RESULTS_CSV_" -S smtp="_MAIL_SERVER_" -S smtp-auth=_AUTH_TYPE_ -S smtp-auth-user="_MAIL_USER_" -S smtp-auth-password="_USER_PWD_" _MAIL_RECIPIENT_ Where: _DISCOVERY_USER_ = Discovery user name which must be used to access to this report
_PWDFILE_ = the path to the file that hosts the password - This file must only contain the password in clear text This can be replaced by --password=_DISCOVERY_PWD_ where _DISCOVERY_PWD_ is the password for the Discovery user _RESULTS_CSV_ = the path to the file name in which the query results should be saved to _QUERY_ = the query that should be executed and saved in _RESULTS_CSV_ _MAIL_BODY_ _MAIL_SENDER_ _MAIL_SUBJECT_ _MAIL_RECIPIENT_ = Respectively: The message that should be displayed in the email body, The email address from which the email will appear to have been sent from, the email subject and the email address(es) to send this report to _MAIL_SERVER_ _AUTH_TYPE_ _MAIL_USER_ _USER_PWD_= Respectively: the company mail server, the type of auth supported by the mail server, the name of the user to log in the mail server and the mail user password Note: Please pay attention to the double quotes in the command line. Script example: # ================== /usr/tideway/check_on_consolidation.sh ====================
### First, remove previous output file, if it is there rm -f /tmp/results.csv ### Run the query /usr/tideway/bin/tw_query --username=discoveryuser --passwordfile=/usr/tideway/my_custom_scripts/pwd --csv --file /tmp/results.csv "SEARCH IN '_System' ScanningSystem SHOW name, formatTime(last_data_push)" ### Remove the additional blank lines from the output csv file dos2unix /tmp/results.csv ### Email the results. Provide the relevant smtp info and email address echo "My query results" | mailx -r "reports@bmc.com" -s "Daily report from consolidator" -a "/tmp/results.csv" -S smtp="mail.bmc.com" -S smtp-auth=login -S smtp-auth-user="mailuser" -S smtp-auth-password="mailpassword" reports_ml@bmc.com ======================================================================== 1.4- Make it executable: $ chmod +x check_on_consolidation.sh
1.5- Run it manually: $ ./check_on_consolidation.sh
Then go to section 3- in this KA to schedule it. 2- TO COPY THE REPORT TO AN SMB SHARE: Go through the same steps than for sending the query results by email but in the new script file replace the last line by something similar to this instead: $ smbclient //_SHARE_SRV_/_SHARE_NAME_ -c 'put _RESULTS_CSV_ _DISTANT_FILENAME_' -U '_DOMAIN_\_DOMAIN_USER_%_PASSWORD_'
Where: _DISTANT_FILENAME_ = The name the file will have on the target smb share. Script example: # ================== /usr/tideway/check_on_consolidation.sh ====================
### First, remove previous output file, if it is there rm -f /tmp/results.csv ### Run the query /usr/tideway/bin/tw_query --username=discoveryuser --passwordfile=/usr/tideway/my_custom_scripts/pwd --csv --file /tmp/results.csv "SEARCH IN '_System' ScanningSystem SHOW name, formatTime(last_data_push)" ### Remove the additional blank lines from the output csv file dos2unix /tmp/results.csv ### Copy the file to an SMB share: smbclient //10.12.4.1/Scripts -c 'put /tmp/results.csv results.csv' -U 'MyDomain\ShareUser%ShareUserPassword' ========================================================================= Notes: - The command line uses "//" as it's linux (and not "\\" as for windows) for the share address
- The symbol "%" is to be kept in "_DOMAIN_\_DOMAIN_USER_%_PASSWORD_". 3- SCHEDULE IT USING CRON: 3.1- Create the cron file using the following command line (it's probably relevant to name this file from the script name):
$ vi $TIDEWAY/etc/cron/check_on_consolidation.cron
And set the schedule at which the report should be sent by mail, or copied to an SMB share, e.g: #===========/usr/tideway/etc/cron/check_on_consolidation.cron ========
# # cron job to check on the consolildator # runs every day at 02:00 # 0 2 * * * /bin/sh /usr/tideway/check_on_consolidation.sh #=============================================================== 3.2- Run the following command line to update the system cron:
$ tw_cron_update
3.3: Verify that it was effectively added to the system cron: $ crontab -l
Notes: - full paths to files or executables are always required, else the script will fail. - More information on sending emails with Discovery here. The command line above probably may need to be updated with the available ssl options, depending on the mail server configuration. - More information on sending emails through a specific email server here, as an example. - More info about cron setting in Discovery here. Videos: |