I am trying to understand how to use name value parts to run custom SQL queries in our infocenter reports. I have a good sample that I want to start with. I created an SQL statement to search for a machine, and then tell me what version of office is listed. Relatively simple query. The problem I have is I cannot grasp how to convert this over to be used inside of the module.
I've read the report contexts section of help, so I know that I need to use the MachineFilter context, but again I'm not sure I understand how to integrate it with my query.
Would anyone be able to point me in a direction to learn some more ? Thank you !
Here is the query I am working with right now:
SELECT vMachine.machName, vAddRemoveList.applicationNameFROM ksubscribers.dbo.vAddRemoveList join vMachine on vMachine.agentGuid = vAddRemoveList.agentGuid where (applicationName like '%Microsoft office%'AND applicationName NOT like '%update%' ANDapplicationName NOT like '%validat%' ANDapplicationName NOT like '%component%' AND applicationName NOT like '%meeting%' ANDapplicationName NOT like '%outlook%' AND applicationName NOT like '%pack%' AND applicationName NOT like '%access%' AND applicationName NOT like '%viewer%'
Wow. Not ONE response to a critical area of Kaseya, and in the only area where support is even remotely helpful.
I apologize that no one got back to you on this. I am reading this for the first time and since it was posted so long ago I never saw the original post. Nevertheless, I want to say that your query is working and returns multiple rows with the machine name (machName) and the office application suite (applicationName). A Name Value Part (NVP) is designed to return a single value not multiple values and rows, and so I am not sure what you are looking to do here. You can convert this SQL to a NVP but then when you add it to a report you will need to specify which machine you want to know the Office version on and it will return the office version for that one machine. This is nit very useful, which makes me think you really don't want a NVP, but rather want your own Data Set. Please reply back with what the overall objective is here.
Kaseya Professional Services
No need. No one would help me so I figured it out 5 months ago on my own. For anyone looking, Instead of using a name value part I created a script using an SQLRead variable and then put this code in the XML file:
<?xml version="1.0" encoding="utf-8" ?> <queryList> <queryDef label="Get Office" sql="SELECT vAddRemoveList.applicationName FROM ksubscribers.dbo.vAddRemoveList join vMachine on vMachine.agentGuid = vAddRemoveList.agentGuid where ( vmachine.agentGuid = #vMachine.agentGuid# ANDapplicationName like '%Microsoft office%'AND applicationName NOT like '%update%' AND applicationName NOT like '%validat%' AND applicationName NOT like '%component%' AND applicationName NOT like '%meeting%' AND applicationName NOT like '%outlook%' AND applicationName NOT like '%pack%' AND applicationName NOT like '%access%' AND applicationName NOT like '%viewer%' AND applicationName NOT like '%interop%' AND applicationName NOT like '%Add%'AND applicationName NOT like '%Click%'AND applicationName NOT like '%Activation%'AND applicationName NOT like '%OneNote%' AND applicationName NOT like '%assistant%')" /></queryList>
I then use a second line in the script to publish the returned value into a custom kaseya field, and I ran it against all of the workstations. Now I have a field with what version of office is installed on the workstation (For licensing report purposes)
I am brand new to SQL and Kaseya scripting, and I have been tasked with auditing our clients machine and software information. I am trying to do EXACTLY what is listed above but additionally with version of AV, Office, Adobe Reader, and a few other things like that. I can work out the coding on my own, but I was wondering where I need to load the SQL script on the Kaseya server and what would be a good test environment for testing scripts going forward. Our server is not on-premise and I couldn't use the KaseyaViews user with a local DB connection since it is remote.
Most of Application reporting you can do with the supplied "Audit > Add/Remove Programs" or "Application" report parts without the need to create custom SQL queries.
If you need application version information, I recommend using the "Audit > Application" report part as the "Audit > Add/Remove Programs" do not contain this information. You will need to know what the primary application executable file name is and you will also need consider filtering by "Directory Path" to filter out backup copies of files that could give false positives, here is an example of what filters I use to create an Antivirus Version report part;
As per what Matt Warburton said, the Name Value Part was only designed for single values, to create custom report parts is actually a bit more difficult. I only recently figured out how to do this myself and it took a few days to get it to work.
See Etienne Deneuve's post for more information on how to make custom report parts community.kaseya.com/.../19083.aspx
Thanks for answering me so quickly. I have done quite a bit of work with custom report parts and I love them. However, I couldn't find a dataset that had all of the columns we needed for a single report. For instance, some have application options and some have machine information. We are looking for a way to get everything we need to know about a machine in a line by line single report that we paste into a pre-formatted excel template. One of the reports includes all custom fields, so getting the exact data we want filled in the custom fields we create would be huge. For example, each machine has its own line, and each line has a ton of columns that excel rules will parse and color format all of the problems. I have recently gotten some agent procedures to extract the data from SQL and put the values in the correct custom field, however they wont run on offline machines. So, over time that is useful but it doesn't help me for the many audits in the next couple weeks. I did just get read access to the Kaseya DBs on our SLQ server so I will be able to test queries and everything, but I would love a way to populate the custom fields directly without using agent procedures.
The problem that I can see with what you are trying to do is that;
1) The new reporting only allows for up to 40 custom fields, I'm unsure if they have changed or will change this in R9 or future versions.
2) Taking data from the Kaseya database and putting it in custom fields is kind of redundant.
3) Creating reports with too many fields make them hard to read and puts a lot of strain on the database server that can impact performance.
I have run into these issue myself and had to learned that the only solution is to learn how to make your own database views, this will allow you to combine fields from different tables and make reports from them.