SQL query to get native computers but ignore proxy

Reporting
1

I use the below MSSSQL query via automation to get our Bigfix Computers. I would like to update it to exclude Proxy Agents. Does anyone know the sql query statement to do that?

SELECT LTRIM(RTRIM(bch.Value)) as 'Computer', bch.ComputerID,
       cp.LastReportTime,
       LTRIM(RTRIM(ip.ResultsText)) as 'IPAddress', fc.ResultsText as 'FirstCheckIn', udp.ResultsText as 'LastUDPping', ls.ResultsText as 'LockedStatus'
FROM dbo.COMPUTERS cp
         LEFT OUTER JOIN
     dbo.BES_COLUMN_HEADINGS bch ON bch.ComputerID = cp.ComputerID
         LEFT OUTER JOIN
     (select q.computerID, q.ResultsText
      from dbo.QUESTIONRESULTS q,
           propertyIDmap pim
      where q.AnalysisID = pim.AnalysisID
        and q.PropertyID = pim.PropertyID
        and q.SiteID = pim.SiteID
        and pim.propertyName = 'ip address') as ip ON ip.ComputerID = cp.ComputerID
         LEFT OUTER JOIN
     (select q.computerID, q.ResultsText
      from dbo.QUESTIONRESULTS q,
           propertyIDmap pim
      where q.AnalysisID = pim.AnalysisID
        and q.PropertyID = pim.PropertyID
        and q.SiteID = pim.SiteID
        and pim.propertyName = 'first check-in') as fc ON fc.ComputerID = cp.ComputerID
         LEFT OUTER JOIN
     (select q.computerID, q.ResultsText
      from dbo.QUESTIONRESULTS q,
           propertyIDmap pim
      where q.AnalysisID = pim.AnalysisID
        and q.PropertyID = pim.PropertyID
        and q.SiteID = pim.SiteID
        and pim.propertyName = 'lastudpping') as udp on udp.ComputerID = cp.ComputerID
         LEFT OUTER JOIN
     (select q.computerID, q.ResultsText
      from dbo.QUESTIONRESULTS q,
           propertyIDmap pim
      where q.AnalysisID = pim.AnalysisID
        and q.PropertyID = pim.PropertyID
        and q.SiteID = pim.SiteID
        and pim.propertyName = 'locked') as ls on ls.ComputerID = cp.ComputerID
WHERE bch.Name = 'Computer Name';
2

Can’t test it cause I don’t use Property mapping tool but give this a try - there is “AgentType” column in computers table, so just add it as a filter.

SELECT LTRIM(RTRIM(bch.Value)) as 'Computer', bch.ComputerID,
       cp.LastReportTime,
       LTRIM(RTRIM(ip.ResultsText)) as 'IPAddress', fc.ResultsText as 'FirstCheckIn', udp.ResultsText as 'LastUDPping', ls.ResultsText as 'LockedStatus'
FROM dbo.COMPUTERS cp
         LEFT OUTER JOIN
     dbo.BES_COLUMN_HEADINGS bch ON bch.ComputerID = cp.ComputerID
         LEFT OUTER JOIN
     (select q.computerID, q.ResultsText
      from dbo.QUESTIONRESULTS q,
           propertyIDmap pim
      where q.AnalysisID = pim.AnalysisID
        and q.PropertyID = pim.PropertyID
        and q.SiteID = pim.SiteID
        and pim.propertyName = 'ip address') as ip ON ip.ComputerID = cp.ComputerID
         LEFT OUTER JOIN
     (select q.computerID, q.ResultsText
      from dbo.QUESTIONRESULTS q,
           propertyIDmap pim
      where q.AnalysisID = pim.AnalysisID
        and q.PropertyID = pim.PropertyID
        and q.SiteID = pim.SiteID
        and pim.propertyName = 'BF: First Report Time') as fc ON fc.ComputerID = cp.ComputerID
         LEFT OUTER JOIN
     (select q.computerID, q.ResultsText
      from dbo.QUESTIONRESULTS q,
           propertyIDmap pim
      where q.AnalysisID = pim.AnalysisID
        and q.PropertyID = pim.PropertyID
        and q.SiteID = pim.SiteID
        and pim.propertyName = 'BF: Last UDP received') as udp on udp.ComputerID = cp.ComputerID
         LEFT OUTER JOIN
     (select q.computerID, q.ResultsText
      from dbo.QUESTIONRESULTS q,
           propertyIDmap pim
      where q.AnalysisID = pim.AnalysisID
        and q.PropertyID = pim.PropertyID
        and q.SiteID = pim.SiteID
        and pim.propertyName = 'locked') as ls on ls.ComputerID = cp.ComputerID
WHERE bch.Name = 'Computer Name' and cp.AgentType = 'Native';
1 Like
3

Yes, that worked great and only returns the native computers. Thank you!

1 Like