Automating the World

We have migrated servers, if you encounter any issues, please let us know on Slack, or post it.


We have also disabled registration as we migrate to the new forum systems. The slack (slack.labtechgeek.com) will always be accepting new users.
 
User avatar
DarrenWhite99
Geek
Topic Author
Posts: 514
Joined: Wed Jun 04, 2014 11:17 pm
Current LT Agent Count: 1500+
Location: Redding, California, US

Building Filenames with DateStamps using SQL

Wed Jun 07, 2017 4:55 pm

The SQL QUERY function can be used to perform nested REPLACE operations, generate a formatted date/time value, and put them all together to prepare a filename. This can be done with PowerShell or other agent side scripts, or by using multiple LabTech String Replace function steps, but it's clean to do it all within the script engine, using only two steps. These functions are useable in computer script and network scripts, where agent side commands are not available.

The important concepts:
  1. Do not introduce any variables in the SQL QUERY, unless they are guaranteed to not have any characters that would interfere with MYSQL. %computerid% is safe for instance, but %clientname% is not. Place those variables in the string processed by MYSQLEncode.
  2. Continue Nesting REPLACE() commands as needed to perform any character or string substitutions you want.
  3. Use CONCAT() to assemble one or more prepared variables, fixed string sections, output from SQL functions like DATE_FORMAT, RANDOM(), etc.
  4. If possible, only process the filename section. As long as you are only handling the filename, and not the extension or folder path, use LEFT() around the CONCAT to ensure the string is not too long.
  5. Wrap it in TRIM() to make sure any leading/trailing spaces are removed.
  6. It is easy to include the variable as part of a complete file path as shown at the end of my example, but you can even use an outer CONCAT() to stick the folder path in front and the file extension onto the end, and then the whole filename and path would be in the returned variable.

In this example script, I am building a hypothetical filename for a device configuration backup to be stored in, which references the ClientName, LocationName, DeviceName, and the DeviceID.
Script-UseSQLToGenerateFilename.PNG


NOTE - This is a Network Device Script, but could be switched to a Computer Script and all of the functions remain the same.
You do not have the required permissions to view the files attached to this post.