SQL Query Execution in Action Script

Content Authoring
1

Hi All,

I new in bigfix, can anyone tell me that how can i execute below sql query in bigfix action script to fetch the SQL Database status?

Select DATABASEPROPERTYEX(‘db_name’,‘Status’) as DBStatus

Thanks

2

Relevance for MS SQL does not include ability for arbitrary queries directly.
https://developer.bigfix.com/relevance/reference/local-mssql-database.html

To run arbitrary SQL you will need to handle credentials to gain access.

Here is a very old example on BigFix.me
https://bigfix.me/fixlet/details/640

but you will probably need to combine that with the Secure Parameters to keep your DB username/password secure: https://bigfix.me/fixlet/details/3678

1 Like
3

Thanks for your reply.

I just want to try to capture message “Error” in Error.txt and “Success” in Output.txtif found.

I am using below action scriprt:

#######################################################################

// enter parameter for SQL DataBase Status
action parameter query “DB_Name” with description “Please enter the DataBase Name” with default value ""
action parameter query “Error_Folder” with description “Please enter the Folder Name and Path” with default value “”

// Disable wow64 redirection on x64 OSes
action uses wow64 redirection {not x64 of operating system}

// check if folder exists
if { not exists folder (parameter “Error_Folder”)}
folder create {parameter “Error_Folder”}
endif

// delete __creatfile and old sql.bat files
//delete __createfile
//delete c:\temp\sql.bat

// CREATEFILE
createfile until END_OF_FILE

REPLACE THE FOLLOWING WITH THE SCRIPT YOU WANT TO RUN ON THE ENDPOINT

delete __appendfile
appendfile set nocount on
appendfile Select DATABASEPROPERTYEX(‘bigfix’,‘Status’) as DBStatus
$? > {parameter “Error_Folder”}\output.txt
$Error > {parameter “Error_Folder”}\error.txt

END_OF_FILE

// rename __createfile to sql.bat
// move __createfile sql.ps1
// dos c:\temp\sql.bat

delete statement.sql
move __appendfile statement.sql
waithidden sqlcmd -E -n -w 1200 -W -h-1 -s “~” -i statement.sql -o "{(pathname of parent folder of client)}

// waithidden { pathname of file ((it as string) of value “Path” of key “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server” of native registry) } -ExecutionPolicy Bypass -File powershell.ps1
if {exists file “output.txt” whose (line of it as string contains “False”) of folder (parameter “Error_Folder”)}
Exit 100
endif
############################################################################

I am not able to capture message in output.txt in case of success and error.txt in case of failure.

Also what will be the syntax for below path:

waithidden { pathname of file ((it as string) of value “Path” of key “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server” of native registry) } -ExecutionPolicy Bypass -File powershell.ps1

Thanks