Action script to check SQL database Status

Hi All,

I am using below command to fetch the status of single database like "Online/Offline/Suspect.

SELECT name FROM master.sys.databases
WHERE name = N’abc AND state_desc = ‘ONLINE’

Where “abc” is the name of SQL Database.

Getting an error while executing in action script.

Can experts help me on this to execute this in bigfix action script to create new fixlet.

Thanks

@PrashantK – you would want to do put that script in a .sql file and then use sqlcmd to run it.

Your would have to make sure you execute under a user that has access to the SQL server and can run this query.

Your script could look something like this (please keep in mind that I didn’t test this as this is just a “sample layout” for your code):

delete __createfile
delete dbtest.sql
createfile until __END 
SELECT name FROM master.sys.databases
WHERE name = N’abc AND state_desc = ‘ONLINE’
__END
move __createfile dbtest.sql
run sqlcmd -S myServer\instanceName -i dbtest.sql -o dbtest.txt

You will probably have to add an override command to this as your BigFix Agent runs as localsystem which most likely doesn’t have access to the database.

Also, please keep in mind that you don’t want to execute this all the time but rather just on demand. There are better SQL tools that can help you monitor databases continuously.

More on the override command here:
https://developer.bigfix.com/action-script/reference/execution/override.html

More on sqlcmd:

Hope that helps.

1 Like