cesinha87 Posted July 5, 2018 Share Posted July 5, 2018 (edited) Hello everyone, I have a txt file with 1k systems hostname (1ksystems.txt) and I have to run a SQL query searching for these systems. I have the query but I don't want manually add 1 thousand lines to the query and change the string Hostname for every hostname, so I am trying to find a way to do this through AutoIt. Help please SQL query: select * from [dbo].[OrionAuditLogMT] where Message like '%Hostname%' or Message like '%Hostname%' I need AutoIt script looking into 1ksystems.txt file and replace %Hostname% with the hostname. For every hostname in the 1ksystems.txt there will be a new line in the SQL query like or Message like '%Hostname%'. Eventually my SQL query would have 1 thousand entry Hope I made some sense and you guys can help me. Thanks in advanced Edited July 5, 2018 by cesinha87 Link to comment Share on other sites More sharing options...
Moderators JLogan3o13 Posted July 5, 2018 Moderators Share Posted July 5, 2018 If you format your host text file by surrounding each host name with a single quote, comma delimited, like so: 'hostname1', 'hostname2', 'hostname3', 'hostname4', 'hostname5', 'hostname6' I would think you would be able to read the file in as a string in AutoIt: #include <File.au3> Local $sString = FileRead(@DesktopDir & "\1khostnames.txt") And then run your query with an IN statement (untested): select * from [dbo].[OrionAuditLogMT] where Message IN ($sString) "Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball How to get your question answered on this forum! Link to comment Share on other sites More sharing options...
cesinha87 Posted July 5, 2018 Author Share Posted July 5, 2018 I apologized for my stupidity but how SQL Managment Studio Express will read the variable $sString? I was hoping to do something with AutoIt and eventually have this huge SQL query with a bunch or Message like '%Hostname%' then copy and paste in the SQL Managment Studio Express Link to comment Share on other sites More sharing options...
Moderators JLogan3o13 Posted July 5, 2018 Moderators Share Posted July 5, 2018 My misunderstanding, you didn't mention SQL Management Studio in your OP; I thought you were connecting to and running your query directly from AutoIt. If you search the forum, you will find a number of threads on running sql queries directly from AutoIt. "Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball How to get your question answered on this forum! Link to comment Share on other sites More sharing options...
cesinha87 Posted July 5, 2018 Author Share Posted July 5, 2018 I don't really care connecting with SQL to be honest. I was just trying to find a way through AutoIt to read a text file and new line and replace Hostname string with the hostname found in the text file. That's all Can that be done? Link to comment Share on other sites More sharing options...
careca Posted July 5, 2018 Share Posted July 5, 2018 Consider this, if i understood right, it changes all instances in 1.txt of: "%Hostname%" by @Computername Macro. $File=@ScriptDir&"\1.txt" $FileContent=FileRead($File,FileGetSize($File)) $Find="%Hostname%" $Replace=@ComputerName $FileContent=StringReplace($FileContent,$Find,$Replace) FileDelete($File) FileWrite($File,$FileContent) Spoiler Renamer - Rename files and folders, remove portions of text from the filename etc. GPO Tool - Export/Import Group policy settings. MirrorDir - Synchronize/Backup/Mirror Folders BeatsPlayer - Music player. Params Tool - Right click an exe to see it's parameters or execute them. String Trigger - Triggers pasting text or applications or internet links on specific strings. Inconspicuous - Hide files in plain sight, not fully encrypted. Regedit Control - Registry browsing history, quickly jump into any saved key. Time4Shutdown - Write the time for shutdown in minutes. Power Profiles Tool - Set a profile as active, delete, duplicate, export and import. Finished Task Shutdown - Shuts down pc when specified window/Wndl/process closes. NetworkSpeedShutdown - Shuts down pc if download speed goes under "X" Kb/s. IUIAutomation - Topic with framework and examples Au3Record.exe Link to comment Share on other sites More sharing options...
Moderators JLogan3o13 Posted July 5, 2018 Moderators Share Posted July 5, 2018 @careca Unless I am completely misinterpreting what the OP stated, I seriously doubt he has a text file with lines of the literal string "%Hostname%". I also highly doubt that he wants all 1000 lines in his text file to be converted to the @ComputerName. "Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball How to get your question answered on this forum! Link to comment Share on other sites More sharing options...
careca Posted July 5, 2018 Share Posted July 5, 2018 Well, can you clarify then what the op is looking for? Im a bit confused at this point, there's sql, txt files, and i worked all day and am not 100% clear thinking. Spoiler Renamer - Rename files and folders, remove portions of text from the filename etc. GPO Tool - Export/Import Group policy settings. MirrorDir - Synchronize/Backup/Mirror Folders BeatsPlayer - Music player. Params Tool - Right click an exe to see it's parameters or execute them. String Trigger - Triggers pasting text or applications or internet links on specific strings. Inconspicuous - Hide files in plain sight, not fully encrypted. Regedit Control - Registry browsing history, quickly jump into any saved key. Time4Shutdown - Write the time for shutdown in minutes. Power Profiles Tool - Set a profile as active, delete, duplicate, export and import. Finished Task Shutdown - Shuts down pc when specified window/Wndl/process closes. NetworkSpeedShutdown - Shuts down pc if download speed goes under "X" Kb/s. IUIAutomation - Topic with framework and examples Au3Record.exe Link to comment Share on other sites More sharing options...
Moderators JLogan3o13 Posted July 5, 2018 Moderators Share Posted July 5, 2018 I will let the OP clarify for you, so as not to take over the post. My take on it was he has a text file with 1000 server names, and is looking for AutoIt to help him write out the SQL query, plugging in the server names. @cesinha87 while I would still, personally, do it all from AutoIt, a quick and dirty way to get what you want would be something like this: #include <file.au3> Local $aArray, $sString = "select * from [dbo].[OrionAuditLogMT] Where Message like '%" _FileReadToArray(@DesktopDir & "\servers.txt", $aArray) For $a = 1 To ($aArray[0] - 1) $sString &= $aArray[$a] & "%' or Message like '%" Next $sString &= $aArray[$aArray[0]] & "%'" ConsoleWrite($sString & @CRLF) "Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball How to get your question answered on this forum! Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now