Symantec Management Platform (Notification Server)

 View Only
  • 1.  SQL Report - As statement to rename column

    Posted Feb 03, 2016 02:18 PM

    I have created a custom SQL query that I want to build a report out of, but I am having issues getting this to work:

    vci.name as _CompName

    We have done an alias on a column name using the query builder feature, but never as raw sql. When I add the "as" to the raw sql, the column i add it to stops showing up on the report.

    Here is my raw sql without the colunn name alias':

    DECLARE @v1_ComputerName nvarchar(max)
       SET @v1_ComputerName = N'%ComputerName%'
    SELECT DISTINCT
        vci.Name,
        ifd.Name,
        ifd2.Name,
        ifd3.Name,
        ifd4.Name,
        ifd5.Name,
        ifd6.Name,
        arp.DisplayName,
        arp2.DisplayName
            
    FROM vrm_computer_item vci

    LEFT OUTER JOIN Inv_Installed_File_Details ifd on vci.Guid = ifd._ResourceGuid and ifd.Name like '%vncserver%'
    LEFT OUTER JOIN Inv_Installed_File_Details ifd2 on vci.Guid = ifd2._ResourceGuid and ifd2.Name like '%smc.exe%' and (ifd2.Path like 'C:\Program Files (x86)\Symantec\Symantec Endpoint Protection\12.1.6168.6000.105\Bin' or ifd2.Path like 'C:\Program Files\Symantec\Symantec Endpoint Protection\12.1.6168.6000.105\Bin')
    LEFT OUTER JOIN Inv_Installed_File_Details ifd3 on vci.Guid = ifd3._ResourceGuid and ifd3.Name like '%cmgshieldsvc.exe%'
    LEFT OUTER JOIN Inv_Installed_File_Details ifd4 on vci.Guid = ifd4._ResourceGuid and ifd4.Name like 'chrome.exe%' and (ifd4.Path like 'C:\Program Files\Google\Chrome\Application' or ifd4.Path like 'C:\Program Files (x86)\Google\Chrome\Application')
    LEFT OUTER JOIN Inv_Installed_File_Details ifd5 on vci.Guid = ifd5._ResourceGuid and ifd5.Name like 'printerinstallerclient.exe%' and (ifd4.Path like 'C:\Program Files (x86)\Printer Properties Pro\Printer Installer Client' or ifd4.Path like 'C:\Program Files\Printer Properties Pro\Printer Installer Client')
    LEFT OUTER JOIN Inv_Installed_File_Details ifd6 on vci.Guid = ifd6._ResourceGuid and ifd6.Name like 'sllauncher.exe%' and (ifd6.Path like 'C:\Program Files\Microsoft Silverlight')
    LEFT OUTER JOIN Inv_AddRemoveProgram arp on vci.Guid = arp._ResourceGuid and (arp.DisplayName like 'Adobe Flash Player 20 ActiveX' or arp.DisplayName like 'Adobe Flash Player 18 ActiveX')
    LEFT OUTER JOIN Inv_AddRemoveProgram arp2 on vci.Guid = arp2._ResourceGuid and (arp2.DisplayName like 'microsoft .net framework 4.5.2' or arp2.DisplayName like 'microsoft .net framework 4.5.1')

    WHERE 
        (vci.Name like @v1_ComputerName) and
        vci.IsManaged = 1
        
    ORDER BY vci.Name



  • 2.  RE: SQL Report - As statement to rename column
    Best Answer

    Posted Feb 03, 2016 02:55 PM

    Take out the '_' underscore before CompName. The Reporting engine treats column names that start with an underscore as hidden.



  • 3.  RE: SQL Report - As statement to rename column

    Posted Feb 03, 2016 03:14 PM

    You are a godsend!!