Thread Info
  • State Verified Answer
  • Locked Locked
  • Replies 4 replies
  • Answers 1 answer
  • Subscribers 2 subscribers
  • Views 9092 views
  • Users 0 members are here
Options
Suggested
This discussion has been locked.
You can no longer post new replies to this discussion. If you have a question you can start a new discussion

Exporting Computer Details From EM Console

Bluto

Hi all.

I am trying to find a method of exporting all of the details presented in the Computer Details tab from the Enterprise Console.  I would then like to manipulate that data within Excel so I can compare similar output from other Sophos ECs on other domains within our enterprise; basically so I can identify identical machine IDs across the domains.  I have tried outputting it as a report to a 'text only/generic printer' but that presents a report that is page formatted with headers etc.  A simple csv output woud suffice.  Any ideas?

Thank you in anticipation.

Stephen.

:3096


This thread was automatically locked due to age.
Parents
  • 0

    Hi,

    If you require the SQL to capture this information it would be something like:

    select c.Name, 
    c.Description, 
    CASE c.OperatingSystem
    WHEN 1 THEN 'Windows 95'
    WHEN 2 THEN 'Windows 98'
    WHEN 3 THEN 'Windows Me'
    WHEN 4 THEN 'Windows NT'
    WHEN 5 THEN 'Windows NT Server'
    WHEN 6 THEN 'Windows 2000'
    WHEN 7 THEN 'Windows 2000 Server'
    WHEN 8 THEN 'Windows XP'
    WHEN 9 THEN 'Windows 2003'
    WHEN 10 THEN 'Mac OS9'
    WHEN 11 THEN 'Mac OSX'
    WHEN 12 THEN 'Windows 95/98/Me'
    WHEN 13 THEN 'Mac OSX or UNIX'
    WHEN 14 THEN 'Unknown'
    WHEN 15 THEN 'Windows workstation'
    WHEN 16 THEN 'Windows server'                                 
    WHEN 17 THEN 'Vista'
    WHEN 18 THEN 'Windows 2008'
    WHEN 19 THEN 'Windows XP 64-Bit'
    WHEN 20 THEN 'LINUX'  
    WHEN 21 THEN 'UNIX' 
    WHEN 22 THEN 'UNIX' 
    WHEN 23 THEN 'UNIX' 
    WHEN 24 THEN 'UNIX' 
    WHEN 25 THEN 'UNIX' 
    WHEN 26 THEN 'UNIX' 
    WHEN 27 THEN 'UNIX' 
    WHEN 28 THEN 'UNIX' 
    WHEN 29 THEN 'UNIX' 						
    WHEN 30 THEN 'UNIX' 
    WHEN 31 THEN 'UNIX' 
    WHEN 32 THEN 'UNIX' 
    WHEN 33 THEN 'Windows 7' 	
    WHEN 34 THEN 'Windows Server 2008 R2'
    ELSE 'Unknown' END as OS, 
    c.Servicepack, 
    c.Domainname, 
    CAST(IPAddress / 256 / 256 / 256 % 256 as varchar) + '.'  +
       CAST(IPAddress / 256 / 256 % 256 as varchar) + '.' +
          CAST(IPAddress / 256 % 256 as varchar) + '.' +
             CAST(IPAddress % 256 as varchar) as IP, 
    fgpant.PathAndName as GroupPath 
FROM computersanddeletedcomputers as c with (nolock) 
	full join computergroupmapping as cgm with (nolock) on cgm.computerid = c.id
	full join dbo.GroupPathAndNameTable() as fgpant on fgpant.groupid = cgm.groupid 	
where c.managed = 1 and c.deleted=0

     It might also be worth mentioning that in Excel you can connect directly to SQL server to obtain data.  E.g.

    "Excel 2007" - "Data" - "From Other sources" - "From SQL server"

    You could then execute such a command directly from within Excel.

    Good luck,

    Jak

    :3110
Reply
  • 0

    Hi,

    If you require the SQL to capture this information it would be something like:

    select c.Name, 
    c.Description, 
    CASE c.OperatingSystem
    WHEN 1 THEN 'Windows 95'
    WHEN 2 THEN 'Windows 98'
    WHEN 3 THEN 'Windows Me'
    WHEN 4 THEN 'Windows NT'
    WHEN 5 THEN 'Windows NT Server'
    WHEN 6 THEN 'Windows 2000'
    WHEN 7 THEN 'Windows 2000 Server'
    WHEN 8 THEN 'Windows XP'
    WHEN 9 THEN 'Windows 2003'
    WHEN 10 THEN 'Mac OS9'
    WHEN 11 THEN 'Mac OSX'
    WHEN 12 THEN 'Windows 95/98/Me'
    WHEN 13 THEN 'Mac OSX or UNIX'
    WHEN 14 THEN 'Unknown'
    WHEN 15 THEN 'Windows workstation'
    WHEN 16 THEN 'Windows server'                                 
    WHEN 17 THEN 'Vista'
    WHEN 18 THEN 'Windows 2008'
    WHEN 19 THEN 'Windows XP 64-Bit'
    WHEN 20 THEN 'LINUX'  
    WHEN 21 THEN 'UNIX' 
    WHEN 22 THEN 'UNIX' 
    WHEN 23 THEN 'UNIX' 
    WHEN 24 THEN 'UNIX' 
    WHEN 25 THEN 'UNIX' 
    WHEN 26 THEN 'UNIX' 
    WHEN 27 THEN 'UNIX' 
    WHEN 28 THEN 'UNIX' 
    WHEN 29 THEN 'UNIX' 						
    WHEN 30 THEN 'UNIX' 
    WHEN 31 THEN 'UNIX' 
    WHEN 32 THEN 'UNIX' 
    WHEN 33 THEN 'Windows 7' 	
    WHEN 34 THEN 'Windows Server 2008 R2'
    ELSE 'Unknown' END as OS, 
    c.Servicepack, 
    c.Domainname, 
    CAST(IPAddress / 256 / 256 / 256 % 256 as varchar) + '.'  +
       CAST(IPAddress / 256 / 256 % 256 as varchar) + '.' +
          CAST(IPAddress / 256 % 256 as varchar) + '.' +
             CAST(IPAddress % 256 as varchar) as IP, 
    fgpant.PathAndName as GroupPath 
FROM computersanddeletedcomputers as c with (nolock) 
	full join computergroupmapping as cgm with (nolock) on cgm.computerid = c.id
	full join dbo.GroupPathAndNameTable() as fgpant on fgpant.groupid = cgm.groupid 	
where c.managed = 1 and c.deleted=0

     It might also be worth mentioning that in Excel you can connect directly to SQL server to obtain data.  E.g.

    "Excel 2007" - "Data" - "From Other sources" - "From SQL server"

    You could then execute such a command directly from within Excel.

    Good luck,

    Jak

    :3110
Children
No Data
Unfiltered HTML