Requests for Automation

Finding Duplicate Agent throguh Script

Customer want a script for find the duplicate agents based on Mac ID. I know that there is sql query to find the duplicate agent based on mac id but customer don't want to login sql server and get the results so they want to automate this through script. We have tried with sql read option in agent procedures but unable to get the output in logs or any other format. Please help us to get this done. SQL Query SELECT macAddr as 'MAC Address' , ComputerName AS 'Computer Name' , SystemSerialNumber as 'Serial Number' , ChassisAssetTag as 'Asset Tag' , connectionGatewayIp as 'Gateway IP' , MachineId as 'Machind and Group' FROM dbo.vAuditMachineSummary WHERE macAddr IN ( SELECT macAddr FROM dbo.vAuditMachineSummary GROUP BY macAddr HAVING COUNT(*)>1 ) ORDER BY 'MAC Address'

Discussion
Gravatar for Iranna
Iranna over 2 years ago

Hi Onno,
Thanks for your update.
SQLCMD command through Kaseya Scripting is blocked however i have created batch file using sql cmd and placed in sql sql server in c:\working directory and used execute file as system account from Kaseya scripting in first step and second step get file from machine so it's works for me.

find the below batch file command.

sqlcmd -S\instancename -d ksubscribers -E -s, -W -Q "SELECT macAddr as 'MAC Address' , ComputerName AS 'Computer Name' , SystemSerialNumber as 'Serial Number' , ChassisAssetTag as 'Asset Tag' , connectionGatewayIp as 'Gateway IP' , MachineId as 'Machind and Group' FROM dbo.vAuditMachineSummary WHERE macAddr IN ( SELECT macAddr FROM dbo.vAuditMachineSummary GROUP BY macAddr HAVING COUNT(*)>1 ) ORDER BY 'MAC Address'" > C:\duplicateagents.csv

Gravatar for Onno de Vries
Onno de Vries over 2 years ago

Hi Iranna, I'm sorry to hear that you have found the SQLCMD to be blocked by Kaseya Scripting, I can assure you that this is not the intended behaviour of the Kaseya Scripting. I have been using that very frequently and lastly little over a week ago on a 9.3 server with the latest patches.
Anyway, the looks of your solution you really did a good job and wasn't really needing help.. ;-)

Gravatar for Onno de Vries
Onno de Vries over 2 years ago

Hi Iranna, there is a way of doing this actually. If you'd run the sql command not through the SQRead option but simply as a ExecuteShellCommand statement in an Agent Procedure ón the server that runs SQL for your Kaseya application.
If you'd output that command to a file, you can fi upload that to the kserver using GetFile or have it made part of a log entry using Write ProcedureLogEntry, you could even email that as well (You'd have to read the content of the output file into a variable (GetVariable).
The command to use for executing the SQL statement from the command line is called 'SQLCMD'.

Details

  • 5 Votes
  • 3 Comments
  • Open for over 2 years
  • Created by Iranna