1 Reply Latest reply on Mar 13, 2008 5:33 PM by metalhead

    Excel SQL Query

      Hi,

      Im trying todo a comparison of PCs reporting in WSUS to EPO but I have a problem...

      Previously I was using a slow copy and paste method of comparing the data in Excel, but I have been playing with Excel SQL queries to pull the data and automate the comparison for me.
      I am using McAfees 'All connecting computers' query to find out the PCs last contact date; When you run the query you get a list of every PC and its last contact date to EPO and some other info.

      Heres the original McAfee SQL Query for 'All connecting computers':


      select
      ComputerName as 'Computer Name',
      (BranchNode.NodeName + '/' + LeafNode.NodeName) as 'ePO Node Name',
      UserName as 'User Name',
      AsciDateTime as 'Last Contact',
      AgentVersion as 'Agent Verson',
      DefaultLangID as 'Language ID',
      DomainName as 'Domain Name',
      IPAddress as 'IP Address',
      TimeZone as 'Time Zone',
      OSType as 'OS Type',
      OSPlatform as 'OS Platform',
      OSVersion as 'OS Version',
      OSServicePackVer as 'OS Service Pack',
      OSBuildNum as 'OS Build Number',
      CPUType as 'CPU Type',
      CPUSpeed as 'CPU Speed',
      NumOfCPU as 'Number of CPU',
      TotalPhysicalMemory as 'Total Physical Memory',
      FreeMemory as 'Free Memory',
      FreeDiskSpace as 'Free Disk Space',
      TotalDiskSpace as 'Total Disk Space',
      IPHostName as 'IP HostName',
      IPXAddress as 'IPX Address',
      NetAddress as 'Net Address',
      OSOEMID as 'OSOEMID',
      CPUSerialNum as 'CPU Serial Number',
      cast(LeafNode.AgentGUID as varchar(50)) as 'Agent GUID'
      from ComputerProperties, LeafNode, BranchNode
      WHERE ComputerProperties.ParentID = LeafNode.AutoID AND
      LeafNode.ParentID = BranchNode.AutoID
      and LeafNode.type != 24
      ORDER BY ComputerName


      Heres my modified version for Excel:
      (Im pulling Computer Name, IP, Domain, and Last Contact Date')


      SELECT
      ComputerProperties.ComputerName AS 'Computer Name',
      ComputerProperties.IPAddress AS 'IP Address',
      ComputerProperties.DomainName AS 'Domain Name',
      AVIView_LeafNode.ASCIdatetime AS 'Last Contact'

      FROM
      ePO_EPOSRV.dbo.AVIView_LeafNode AVIView_LeafNode,
      ePO_EPOSRV.dbo.BranchNode BranchNode,
      ePO_EPOSRV.dbo.ComputerProperties ComputerProperties,
      ePO_EPOSRV.dbo.LeafNode LeafNode

      WHERE
      ComputerProperties.ParentID = AVIView_LeafNode.ParentID AND
      ComputerProperties.ParentID = LeafNode.AutoID AND
      LeafNode.ParentID = BranchNode.AutoID AND ((LeafNode.Type<>24))

      ORDER BY ComputerProperties.ComputerName


      The McAfee Query uses the tables: 'ComputerProperties', 'LeafNode', and 'BranchNode'.
      In none of these tables could I find 'AsciDateTime'. I had to get this from the 'AVIView_LeafNode table'.
      This is not really much of a problem because I get data, but this leads onto the main issue im having.

      Running my customised query above I get duplicate results for each PC :confused:

      Example Result: (Ive modified for privacy)


      Computer Name IP Address Domain Name Last Contact
      COMPUTER1 192.168.X.XX WORKGROUP 20070627070916
      COMPUTER1 192.168.X.XX WORKGROUP 20080312135422
      COMPUTER1 192.168.X.XX WORKGROUP 20080312152014
      COMPUTER1 192.168.X.XX WORKGROUP 20080312142527
      COMPUTER1 192.168.X.XX WORKGROUP 20080312150004
      COMPUTER2 10.157.XX.XX DOMAIN1 20070731073659
      COMPUTER2 10.157.XX.XX DOMAIN1 20070721095848
      COMPUTER2 10.157.XX.XX DOMAIN1 20071115025530
      COMPUTER2 10.157.XX.XX DOMAIN1 20071114115324
      COMPUTER2 10.157.XX.XX DOMAIN1 20070728104152
      COMPUTER3 10.40.XX.XX DOMAIN2 20080312034110
      COMPUTER3 10.40.XX.XX DOMAIN2 20080312033952
      COMPUTER3 10.40.XX.XX DOMAIN2 20070218093801


      I can only assume theres a problem with my SQL query, im no SQL Programmer so not sure what the problem is? :confused:
      Im using Excel 2002, EPO 3.6.1.

      Any help/advice would be great!

      Thanks

      John