Thread Info
  • State Verified Answer
  • Locked Locked
  • Replies 4 replies
  • Answers 1 answer
  • Subscribers 2 subscribers
  • Views 8851 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

    Although copying and pasting will get you the information, there is no way to filter it.  There is no delimiter to parse the data in any valuable manner.  Taking the data from SQL per Jak's response is really the only way to select only the data you want.

     

    Here is a powershell "script" that calls a SQL query text file.  #I am sure there is a nicer way to pass it in but this is what I whipped up.

    Powershell part:

     

    if ( (Get-PSSnapin -Name SqlServerCmdletSnapin100 -ErrorAction SilentlyContinue) -eq $null )
    {
    Add-PsSnapin SqlServerCmdletSnapin100
    }

    if ( (Get-PSSnapin -Name SqlServerProviderSnapin100 -ErrorAction SilentlyContinue) -eq $null )
    {
    Add-PsSnapin SqlServerProviderSnapin100
    }

    $table = Invoke-Sqlcmd -ServerInstance .\sophos -Database SOPHOS540 -InputFile "C:\Scripts\OutputComputerList\ComputerList.sql" -ErrorAction 'Stop' -Verbose -QueryTimeout 1800
    $table | Export-Csv -NoTypeInformation C:\Scripts\OutputComputerList\SophosComputerlist.csv

     

    SQL queary (ComputerList.sql)

     

    select c.Name,
    c.lastmessagetime,
    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'
    WHEN 35 THEN 'Windows 8'
    WHEN 36 THEN 'Windows Server 2012 Standard'
    WHEN 38 THEN 'Windows 8.1 Pro'
    WHEN 39 THEN 'Windows Server 2012 R2 Standard'
    WHEN 40 THEN 'Windows 10'
    ELSE 'Unknown' END as OS,
    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

Reply
  • 0

    Although copying and pasting will get you the information, there is no way to filter it.  There is no delimiter to parse the data in any valuable manner.  Taking the data from SQL per Jak's response is really the only way to select only the data you want.

     

    Here is a powershell "script" that calls a SQL query text file.  #I am sure there is a nicer way to pass it in but this is what I whipped up.

    Powershell part:

     

    if ( (Get-PSSnapin -Name SqlServerCmdletSnapin100 -ErrorAction SilentlyContinue) -eq $null )
    {
    Add-PsSnapin SqlServerCmdletSnapin100
    }

    if ( (Get-PSSnapin -Name SqlServerProviderSnapin100 -ErrorAction SilentlyContinue) -eq $null )
    {
    Add-PsSnapin SqlServerProviderSnapin100
    }

    $table = Invoke-Sqlcmd -ServerInstance .\sophos -Database SOPHOS540 -InputFile "C:\Scripts\OutputComputerList\ComputerList.sql" -ErrorAction 'Stop' -Verbose -QueryTimeout 1800
    $table | Export-Csv -NoTypeInformation C:\Scripts\OutputComputerList\SophosComputerlist.csv

     

    SQL queary (ComputerList.sql)

     

    select c.Name,
    c.lastmessagetime,
    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'
    WHEN 35 THEN 'Windows 8'
    WHEN 36 THEN 'Windows Server 2012 Standard'
    WHEN 38 THEN 'Windows 8.1 Pro'
    WHEN 39 THEN 'Windows Server 2012 R2 Standard'
    WHEN 40 THEN 'Windows 10'
    ELSE 'Unknown' END as OS,
    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

Children
No Data
Unfiltered HTML