DOS batch file – Counting Lines

Background

Needing to count lines in a file.

Script

DOS / Command Line

Find Command

syntax

find /v /c "" [filename]

sample

find /v /c "" modules.txt

findstr

findstr – type / findstr/find

syntax
findstr /R "." [filename] | find /c /v ""
sample
findstr /R "." products.txt | find /c /v ""

findstr – type / findstr/find

syntax
type [filename] | findstr /r /n "^"| find /c ":"
sample
type products.txt | findstr /r /n "^"| find /c ":"

Win OS :- Remove Temporary Internet Files

Background

Wanted to remove left behind temporary Internet files.

Folder Stats

C:\Users

WinDirStat

Images

Image – WinDirStat

winDirStat.20181218.0249PM.PNG

Explanation
  1. 16 GB

Windows Explorer

Images

Image – Windows Explorer

folderExplorer.TemporaryInternetFiles.20181218.0228PM.PNG

Explanation
  1. 15.9 GB

Script

Script- Dos/Batch

@echo off

rem *************************************************************************************************

    rem https://community.f-secure.com/t5/Common-topics/Cleaning-temporary-Internet/ta-p/18280

    rem In Windows XP, the folder is located here:

        rem C:\Documents and Settings\\Local Settings\Temporary Internet Files\Content.IE5
        rem Note: If you only have one user account on Windows XP, use Administrator as the username.

    rem In Windows Vista and 7, the folder is located here:

        rem C:\Users\\AppData\Local\Microsoft\Windows\Temporary Internet Files\Content.IE5
        rem C:\Users\\AppData\Local\Microsoft\Windows\Temporary Internet Files\Low\Content.IE5

    rem In Windows 8 and 8.1, the folder is located here:
        rem C:\Users\\AppData\Local\Microsoft\Windows\INetCache

    rem *************************************************************************************************

setlocal enabledelayedexpansion

IF "%OS%"=="" GOTO WIN9X

REM Clear Local Variables
rem set "_folderSuffix="
rem set "_folderLowSuffix="

set _folderSuffix=
set _folderLowSuffix=

if exist "%SystemDrive%\Users\%username%\AppData\Local\Microsoft\Windows\Temporary Internet Files\Content.IE5"  (

	set "_folderSuffix=AppData\Local\Microsoft\Windows\Temporary Internet Files\Content.IE5"
	set "_folderLowSuffix=AppData\Local\Microsoft\Windows\Temporary Internet Files\Low\Content.IE5"

)

if exist "%SystemDrive%\Users\%username%\AppData\Local\Microsoft\Windows\INetCache"  (

	set "_folderSuffix=AppData\Local\Microsoft\Windows\INetCache"

)

set "_users=%SystemDrive%\Users"

FOR /D %%F in ("%_users%\*") do (

     set _folder=%%F

     set "_folderContent=%%F\%_folderSuffix%"    

	 if not "%_folderLowSuffix%"=="" (
		set "_folderLowContent=%%F\%_folderLowSuffix%"

		)

     echo Folder Base is !_folder!

     echo Folder Content Regular is !_folderContent!

     dir "!_folderContent!"\* /s 2>nul | find /v /c "::"

     if exist "!%_folderLowContent%" (

		echo Folder Content Low is !_folderLowContent!

		dir "!_folderLowContent!"\* /s 2>nul | find /v /c "::"

	)		

     echo *******************************************************************

     rem dir
     if "%1"=="dir" dir "!_folderContent!"\* /s /Q 2>nul 

     if exist "%%F\%_folderLowSuffix%" (

		if "%1"=="dir" dir "!_folderLowContent!"\* /s /Q 2>nul      

	 )		

     rem del
     if "%1"=="del" del "!_folderContent!"\* /s /Q 2>nul 

     if exist "%%F\%_folderLowSuffix%" (

		if "%1"=="del" del "!_folderLowContent!"\* /s /Q 2>nul      

	)	

)

:GOTO END

:WIN9X

REM - WIN9X systems are currently not supported.

:END

endlocal

Source Code Control

GitHub

DanielAdeniji/winOSInternetTempfilesCleanup
Link

 

Dos Batch File – Special Characters

Background

If you end up having special characters as in the case of nice passwords and also needing to use delayed expansions in your batch file, you might have to escape those passwords.

Batch File

Deploy


@echo off

if not exist log mkdir log

SETLOCAL DISABLEDELAYEDEXPANSION

set "_errorlevelSaved="

REM Batch files - number of command line arguments ( nimrodm )
rem https://stackoverflow.com/questions/1291941/batch-files-number-of-command-line-arguments

set argC=0

for %%x in (%*) do Set /A argC+=1

echo Argument Count is %argC%

set "_app=sqlcmd.exe"

set _dbHost=%1

IF NOT "%2"=="" set "_dbUsername=%2"

IF NOT "%3"=="" set "_dbPassword=%3"

set "_appOption=-S %_dbHost% -b "

IF NOT "%_dbUsername%"=="" set "_appOption=%_appOption% -U%_dbUsername% "

IF NOT "%_dbPassword%"=="" set _appOption=%_appOption% -P"%_dbPassword%"

set "_filename=modules.txt"

echo appOption is %_appOption%

rem goto skipped

SETLOCAL ENABLEDELAYEDEXPANSION

set _fileCount=0

for /f "tokens=1,2 delims=," %%a in ('type %_filename%') do (

	set /A _fileCount+=1

	set _folder=%%a

	set _file=%%b

	set "_fileFull=!_folder!\!_file!"

	echo Processsing file !_fileCount!  - !_fileFull!

	%_app% %_appOption% -i !_fileFull!	

	set "_errorlevelSaved=%errorlevel%"

	rem echo _errorlevelSaved is %_errorlevelSaved%

	IF NOT _errorlevelSaved NEQ 0 GOTO exception

)

:skipped

goto completed

:exception

echo "Error Level %_errorlevelSaved%"

:completed

SETLOCAL DISABLEDELAYEDEXPANSION

endlocal

Invoke

Outline

  1. Server Name :- 118.90.10.1
  2. User :- dadeniji
  3. Password :- paul.winters

Actual Invocation


deploy 118.90.10.1 john paul.winters

Output

Output – Textual

Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login failed for user 'dadeniji'..

Output – Image

sqlcmd.specialCharacters.inbatchfile.(dadeniji)20181209.0700AM

 

Workaround

In our case we have special characters in our password and we are getting failed logins, and so we have to escape each special character.

Outline

In this sample we escaped the period in our password.

  1. The actual password is paul.winters.
  2. The only character that needs to be escaped is the period.
  3. And, so we ended up with paul^.winters.

 

Actual Invocation


deploy 118.90.10.1 john paul^.winters

Output

sqlcmd.specialCharacters.inbatchfile.(john.smith)20181209.0707AM

Dedicated

Dedicating to Rob van der Woude’s.

Rob van der Woude’s Scripting Pages
Escape Characters
Link

youtube-dl :- Convert Downloaded Video to Audio

Background

By default artifacts downloaded from YouTube comes across as video.

Lineage

  1. YouTube – Upload – “Stuck at Processing 95%”
    Date Published :- 2016-Jan-29th
    Link
  2. ClipConverter is not available for videos containing music
    Date Published :- 2018-Nov-18th
    Link

youtube-dl

For example, youtube-dl downloads comes across as mp4.

Let us convert our downloaded video to audio.

Better yet, let us have youtube-dl perform a conversion post the download.

Prerequisites

Please download the following:-

  1. FFMpeg
    • Original author(s) :-
      • Fabrice Bellard
    • Definition
      • Wikipedia
        • Link
        • FFmpeg is a free software project consisting of a vast software suite of libraries and programs for handling video, audio, and other multimedia files and streams. At its core is the FFmpeg program itself, designed for command-line-based processing of video and audio files, and widely used for format transcoding, basic editing (trimming and concatenation), video scaling, video post-production effects, and standards compliance (SMPTE, ITU). FFmpeg includes libavcodec, an audio/video codec library used by many commercial and free software products, libavformat (Lavf), an audio/video container mux and demux library, and the core ffmpeg command line program for transcoding multimedia files.
        • FFmpeg is part of the workflow of hundreds of other software projects, and its libraries are a core part of software media players such as VLC, and has been included in core processing for YouTube and the iTunes inventory of files. Codecs for the encoding and/or decoding of most of all known audio and video file formats is included, making it highly useful for the transcoding of common and uncommon media files into a single common format.
    • Download Sites

Script

Download Singular

Script – dlvideo.cmd


set "_app=youtube-dl.exe"
set "_format=mp3"
set "_quality=0"

set "_option=--extract-audio --audio-format %_format% --audio-quality %_quality% --keep-video "

%_app% %_option% %1

Sample Invocation


dlvideo https://youtu.be/csmart

Output

convert_20181119_0654AM.PNG

Download List

Outline

  1. Prepare text file
    • urllist.txt
      • Lists URL
  2. Prepare Script
    • dlvideoInList.cmd
      • for ( Commands )

Script – dlvideoInList.cmd

@echo off

setlocal enabledelayedexpansion

set "_filename=youtubeURLList.txt"

for /f "tokens=*" %%a in ('type %_filename%') do (

set line=%%a

echo !line!

call dlvideo.cmd !line!

)

endlocal

Sample Invocation


dlvideoInList.cmd

 

Quotes

VideoHelp

Home / Forum / Video / Video Conversion / Need help with ffmpeg

Link
Sorry, no clue what the other folks in this forum do.
Personally I would probably post in the ffmpeg developer mailing list and ask if there’s someone interested.
(I know that Fiona Glaser did consultant jobs a while back, but I’m not sure if she is still doing that. Side note: Don’t get confused DarkShakiri, Jason Garrett-Glaser, Fiona Glaser are all the same person.)

Dedicated

Dedicated to Fiona Glaser.

For the pretentious, Girls code and they do it well too.

WinOS – Restart Computer through Script

Background

Needing to restart a computer that is not accessible via Remote Desktop.

Script

DOS Batch

Here is a Dos Script that can help.

Outline

  1. Expects lone argument passed in to be computer name of host to restart
  2. Initiates shutdown.exe
    • /r :- Restart
    • /d p:0:0 :- Reason is a planned restart
    • /m :- Computer that is target of restart
    • /c :- Reason
      • User that initiated restart and host restarted from

Code


@echo on

setlocal

rem /r
rem restart computer

rem /d 
rem   p = planned restart

if [%1]==[] (
	echo Please pass along computer name as first parameter
	echo Exiting Script
	goto :endofScript
	)

set "_usernamefull=%USERDOMAIN%\%USERNAME%"

set "_computer=%1"

set "_reason=Planned restart by %_usernamefull% from host %COMPUTERNAME%"

shutdown.exe /r /d p:0:0 /m \\%_computer% /c "%_reason%"

:endofScript

endlocal

Output

Output #1

Image

Textual

LABDB: The entered computer name is not valid or remote shutdown is not supported on the target computer. Check the name and then try again or contact your system administrator.(53)

Output #2

Image

Textual

You're about to be signed out
Windows will shut down in less than a minute.

MS Windows – Create timestamp copy of original file ( using batch file )

Background

There is a batch file that I have that fails occasionally.

And, yes it creates a log file.

But, unfortunately by the time that I go look, it has re-attempted and worked well.

And, so I am losing the error message.

 

Clone File

Let us clone the file and name the new file based on our current timestamp.

Outline

  1. Building Block
    • _getTimeStamp.cmd
    • _fileBackup.cmd
  2. Use case
    • fileBackupInvoke.cmd

Building Block

_getTimeStamp.cmd


@echo off
@setlocal enableextensions enabledelayedexpansion
 
rem Creating a file name as a timestamp in a batch job
rem BeConcise
rem http://stackoverflow.com/questions/1064557/creating-a-file-name-as-a-timestamp-in-a-batch-job
rem copy "{path}\example.txt" "{path}\_%date:~10,4%%date:~4,2%%date:~7,2%_%time:~0,2%%time:~3,2%%time:~6,2%.txt"
rem set "_timestampSaved=%date:~10,4%%date:~4,2%%date:~7,2%_%time:~0,2%%time:~3,2%%time:~6,2%"
 
rem Need leading zero for batch script using %time% variable
rem http://serverfault.com/questions/147515/need-leading-zero-for-batch-script-using-time-variable
 
:prepare time stamp 
set year=%date:~10,4%
set month=%date:~4,2%
set day=%date:~7,2%
set hour=%time:~0,2%
:replace leading space with 0 for hours < 10
if "%hour:~0,1%" == " "  set hour=0%hour:~1,1%
set minute=%time:~3,2%
set second=%time:~6,2%
set _timeStamp=%year%%month%%day%_%hour%%minute%%second%
  
(
    endlocal
 
    rem SET _timestamp=%_timestamp%
    SET _timestampSaved=%_timestamp%
)

 

 

_fileBackup.cmd


@ECHO OFF
REM Reset local variables
set _DATEANDTIME=
set _DATEANDTIME_INTERNAL=
set _timestampSaved=

REM Validate arguments passed in through command line
REM Argument List
REM 	1) File Name 
REM 	2) File Extension
IF %1.==. GOTO No1
IF %2.==. GOTO No2
 
call _getTimeStamp.cmd

rem set "_DATEANDTIME=%_timestampSaved%"
rem echo "TimestampSave is %_DATEANDTIME%"


REM The originating file is filename and file ext
set "_fileSource=%1%2"

REM The destination file is filename, timestamp, and file ext
set "_fileDestination=%1_%_timestampSaved%%2"

REM Display file name source & destination
echo file Source is %_fileSource%
echo file Destination is %_fileDestination%

REM make backup of file ( file Source -> file destination )
if exist %_fileSource% echo "f" | xcopy %_fileSource% %_fileDestination% /D

REM Go to end
GOTO End1

:No1
    ECHO Please pass in file name as param 1
	GOTO End1

:No2
    ECHO Please pass in file extension as param 2
	GOTO End1

:End1

Use Case

fileBackupInvoke.cmd

Script


@echo off
@setlocal

if not exist log mkdir log

set "_fileSource=log\refreshData"
set "_fileExtension=.log"

call _fileBackup.cmd %_fileSource% %_fileExtension%

@endlocal

Output

Source Code Control

GitHub

DanielAdeniji/cloneFileNowUsingMSDOS
Link

 

Summary

As always, nothing here.

Unfortunately logic for getting timestamp is often a hit and miss.

Especially when one considers zero (0) offset that is often amiss.

 

 

 

Scheduled Task – Xcopy does not copy file

Background

We have a scheduled script that was not working as new files were not being copied over.

But, also there were no errors logged.

 

Script – Backup

Here is what the original script looks like.

Script – Backup ( Original )


set "app=xcopy"

set "_HOME_DIR=%CD%"

set "_DATABASE=DBBackup"
set "_srcFolder=\\DBPROD\SQLBackups\%_DATABASE%\%_DATABASE%.bak"
set "_destFolder=F:\Microsoft\SQLServer\SQLRestore"

@rem if Folder Log does not exist, please create it
if not exist log mkdir log

@rem Initiate xcopy
%app% %_srcFolder% %_destFolder% /D /Y /J


Script – Backup ( Revision )

Added code to capture and expose ERRORLEVEL.


set "app=xcopy"
set "_DATABASE=HRDB"
set "_srcFolder=\\DBPROD\SQLBackups\%_DATABASE%\%_DATABASE%.bak"
set "_destFolder=F:\Microsoft\SQLServer\SQLRestore"

@rem if Folder Log does not exist, please create it
if not exist log mkdir log

@rem Initiate xcopy
%app% %_srcFolder% %_destFolder% /D /Y /J

set _errLevel=%errorlevel%

if _errLevel neq 0 (
	echo _errLevel %_errLevel% 
	echo ErrorLevel %errorLevel% 
	exit /b %_errLevel%		 
)	

Output

Image

xcopyDoesNotCopyNewFiles

Text


D:\Scripts\RestoreDB>xcopy \\DBPROD\SQLBackups\HRDB\HRDB.bak F:\Microsoft\SQLServer\SQLRestore /D /Y /J
\\DBPROD\SQLBackups\HRDB\HRDB.bak
Sharing violation

D:\Scripts\RestoreDB>set _errLevel=4

D:\Scripts\RestoreDB>if _errLevel NEQ 0 (
echo _errLevel 4
 echo ErrorLevel 0
 exit /b 4
)
_errLevel 4
ErrorLevel 0

D:\Scripts\RestoreDB>set _errLevel=4

D:\Scripts\RestoreDB>if _errLevel NEQ 0 (
echo restoreDB_DBBackup::_errLevel 4
 echo restoreDB_DBBackup::ErrorLevel 0
 exit /b 4
)


Image

We are experiencing a sharing violation.

Diagnostics

Who is using the file?

Resource Monitor

In use files

RestoreMonitor-InuseFiles

 

SQL Server

sp_whoIsActive

sp_whoIsActive

 

sys.dm_exec_requests

Is there a SQL script that I can use to determine the progress of a SQL Server backup or restore process?
Answered By – Veldmuis
Link

Code


SELECT 
		 r.session_id
		,r.command
		,[Percent Complete]
			= CONVERT(NUMERIC(6,2),r.percent_complete)
		, [ETA Completion Time]
			= CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) 
		, [Elapsed Min]
			= CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) 
		, [ETA Min]
			= CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) 
		, [ETA Hours]
			= CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) 
		, [sqlText]
			= CONVERT(VARCHAR(1000)
				,(
					SELECT SUBSTRING(text,r.statement_start_offset/2,
						CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)
						FROM sys.dm_exec_sql_text(sql_handle))
				  )
FROM sys.dm_exec_requests r 
WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')

 

Output

sys-dm-exec-requests

Summary

Our problem is that a prior run has the file we are trying to copy via xcopy locked.

We can wait for the database restore to complete, or terminate it.

Also, xcopy with /D works fine … just make sure that you check the ERRORLEVEL.