How to execute a sql query to export data in a query based Policy ?

Products

• CA Client Automation - IT Client Manager
• CA Client Automation

Issue/Introduction

In a query based policy, some actions could be added. How to execute a SQL Query to export some data related to violator agents ? For example Software inventory

Environment

Client Automation - All Version

Resolution

Here is an example to export the list of AM Software inventory of the violator agents in file.

1- In the Query Based Policy add an action of type "Launch Application"

 

2- Fill the fields like this :

Executable : sqlcmd.exe

Parameters : 

-d mdb -S <SQLServerName> -Q "SET NOCOUNT ON ; PRINT '$DATE$ $TIME$ - Violator $NAME$' ; select a.agent_name, s.name, s.sw_version_label, dateadd ( ss, d.creation_date + datediff(ss,getutcdate(),getdate()), convert(datetime,'19700101')) from ca_discovered_software d LEFT JOIN ca_agent a ON a.object_uuid=d.asset_source_uuid LEFT JOIN ca_software_def s ON s.sw_def_uuid=d.sw_def_uuid WHERE a.agent_name='$NAME$' ORDER BY d.creation_date DESC" -U ca_itrm -P <OT_changedR11 -W -h-1 -o c:\temp\test_$NAME$.txt

 

Remarks :

- Replace -S <SQLServerName> by the sql servername with instancename : -S sqlservername\instancename

 

- Replace -P NOT_changedR11 by the correct password for ca_itrm it the default value has been changed.

 

The problem is that ca_itrm password appears in clear in the command line.

A solution could be to replace "-U ca_itrm -P NOT_changedR11" by -E

But SYSTEM Account should be added in SQL permissions. See Additional Information

 

-  -o c:\temp\test_$NAME$.txt

generates a different file for every computers. Use the same file for all violators does not work because some sqlcmd commands are running at the same time and one is failing with "The process cannot access the file because it is being used by another process." error.

 

 

 

 

3- When the policy is evaluated some sqlcmd.exe commands are executed and some files are generated 

 

The file contains the computer name with the discovered software on the computer :

 

 

 

Additional Information

Remarks :

- Use this if the number of violators is not very high. Otherwise this will generate a lot of sqlcmd.exe commands and generate a lot of files.

 

- SYSTEM Account could be added in SQL permissions like this :

 

Create a new user in mdb database with name SYSTEM and linked with login name "NT AUTHORITY\SYSTEM"

 

 

Put this user in ca_itrm_group :