Kaseya Community

SQL View question

  • Since I can't seem to get back into the old forum today to dig around for an answer, I'll ask anew:

    I'm trying to use the audit data to return the location of BlahBlah.EXE, and supposedly that data is in a SQL view (vCurrApplicationInfo.DirectoryPath). Great, but how do I get the information for the EXE that I want? The online help is... vague, at best, unless you're some sort of SQL guru, I wager. I tried #vCurrApplicationInfo.DirectoryPath/blahblah.exe# but that threw an error. Rather than spend umpteen hours on trial-and-error... here I am.

    Thanks, all.

  • You could create a custom view. For example:

    SELECT     m.machName + '.' + m.groupName AS Machine_GroupID, m.agentGuid, m.machName, m.groupName, a.productName, a.version AS ProductVersion,

                         a.applicationName, a.manufacturer, a.description AS ApplicationDesc, a.lastModifiedDate, a.size AS ApplicationSize, a.dirPath AS DirectoryPath

    FROM         dbo.auditRsltApps AS a LEFT OUTER JOIN

                         dbo.machNameTab AS m ON a.agentGuid = m.agentGuid

    WHERE     (a.lastAudit = 1) AND (m.machName IS NOT NULL) AND (a.applicationName = 'YOUREXEHERE.exe')

    Then you can simply use the variable #yourViewName.DirectoryPath# in your script.

    Also, for your knowledge, I got most of that code from the vCurrApplicationInfo view. I merely added the WHERE clause for the executable to search for. Machine name is implied when you call the variable later.

  • example.txt

    Formatting came out screwy, so I will attach a text file here instead.

  • I can sort of see where you're going with that, but doesn't that involve me poking around in the database?

    Or, put another way: The only real SQL guy on the payroll is the owner of the company, who refuses to get involved with mundane things like Doing Stuff To The Kaseya Database. Poking around in there with next-to-zero knowledge isn't my idea of a safe thing to do...

  • Hmm... it may be possible with Get Variable somehow. I'm not sure what the proper formatting would be in the script, but a very basic SQL query would look like this:

    SELECT dirPath FROM dbo.auditRsltApps WHERE (applicationName = 'CHANGEME.exe')

  • I sent in a request in to our development team a few weeks ago to extend view usage within agent procedures to allow for this -- right now you just can't do it.

    [edited by: Ben at 4:47 PM (GMT -8) on 12-1-2010] .
  • @GD; how do you intend to use the information? The reason I'm asking is there are several ways to get this information. For instance I know that the machines I'm targeting have Outlook installed on them but I don't know what version so I found that many applications (not all) actually gets published to the following Registry Key path;

     HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths 

    Windows XP and up. From here if I look under the OUTLOOK.exe key the (Default) value has the executable name and path and "Path" value has the path only.