Exporting Computer Details From EM Console

Hello Stephen,

you could click into the Computer Details pane (hide the Dashboard first) , then use Ctrl+A, Ctrl+C (or Edit  from the menu bar -> Select All -> Copy). You should get a pop up Copying ...  Wait until it disappears  and (as the list is copied in tab delimited form to the clipboard) simply paste to Excel .

HTH

Christian

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.

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

Good luck,

Jak

D'oh!  Why didn't I try this first?  Thank you for this.

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