GFI
English Deutsch Français Italiano Nederlands Español
Forums  Register  Login  My Profile  Inbox  Address Book  My Subscription  My Forums 

Member List  Search  FAQ  Ticket List  Log Out

 

Generate Excel table (Computers x Checks) from NSM report

  		Logged in as: Guest
Users viewing this topic: none
  Printable Version
All Forums >> [Network Security] >> GFI Network Server Monitor >> Generate Excel table (Computers x Checks) from NSM report Page: [1]
Login
Message << Older Topic   Newer Topic >>
Generate Excel table (Computers x Checks) from NSM report - 17.Jun.2008 8:19:05 AM   
pjgfi

 

Posts: 19
Joined: 7.Jan.2008
Status: offline 		Hello,

I'd like to build an Excel table from NSM Report where I would have Machines as columns and Checks as line and crosses where Machine X has Check Y.

So far, I can get one column with all machines and the other column with all checks from a simple CSV export.

I know this is more Excel stuff than GFI but maybe someone here already has done such a table and as such can share his technique.

Thanks.

PJ
Post #: 1
RE: Generate Excel table (Computers x Checks) from NSM ... - 17.Jun.2008 9:06:21 AM   
DrewE

 

Posts: 476
Joined: 28.Apr.2008
From: Cary, NC
Status: offline 		PJ,

Is it simply that you want to reverse the columns and rows of the Excel sheet, or would you like to replace the results with a symbol indicating that a particular check has been applied to a particular machine?

_____________________________

Drew Easley - Technical Support Representative
GFI Software - www.gfi.com

(in reply to pjgfi)
Post #: 2
RE: Generate Excel table (Computers x Checks) from NSM ... - 17.Jun.2008 9:16:20 AM   
pjgfi

 

Posts: 19
Joined: 7.Jan.2008
Status: offline 		Thanks for being so quick to answser. Sorry for being unclear.

Here's what I've got from GFI report :

Machine1 - Ping
Machine1 - Folder existence
Machine1 - Disk Space
Machine2 - Folder existence
Machine2 - CPU

And what I'd like to have :
                           Machine1 Machine2   
Ping                       x           
Folder existence       x            x
Disk Space              x           
CPU                                     x

So yes, it's the Symbol thing !

I'm sure Excel knows how to handle such data !

(in reply to DrewE)
Post #: 3
RE: Generate Excel table (Computers x Checks) from NSM ... - 17.Jun.2008 10:02:11 AM   
DrewE

 

Posts: 476
Joined: 28.Apr.2008
From: Cary, NC
Status: offline 		PJ,

Although we will not be able to design this type of table for you, it will require a bit of work with Excel's formulas if this is even possible:

  1. You'll need to extract the machine name from the check into a new field with a formula like this:
    =LEFT(Reports!A3,FIND(" - ",Reports!A3))
    This will return the server name the check belongs to.
  2. You'll need to extract the name of the check into a new field:
    =RIGHT(Reports!A3,LEN(Reports!A3)-Sheet1!A3-2)
Hopefully, this should get you, or anybody else who wants to tackle this issue started.  These instructions are far from complete though.  From here, my best guess is to use Excel's PivotTables or VLookup function.

_____________________________

Drew Easley - Technical Support Representative
GFI Software - www.gfi.com

(in reply to pjgfi)
Post #: 4
RE: Generate Excel table (Computers x Checks) from NSM ... - 17.Jun.2008 10:10:28 AM   
pjgfi

 

Posts: 19
Joined: 7.Jan.2008
Status: offline 		Thanks, that's exactly the clue I was expecting to start with !

I'll have a look tomorrow morning and, of course, I'll post the technique here if I succeed.

But Pivot Table technique seems very promising !

(in reply to DrewE)
Post #: 5
RE: Generate Excel table (Computers x Checks) from NSM ... - 18.Jun.2008 6:12:13 AM   
pjgfi

 

Posts: 19
Joined: 7.Jan.2008
Status: offline 		Here are the steps if anyone wants to buil such a table :

- generate report for all checks, CSV format with "; " as separator
- as machine name and checks are in the same column, separated by " - ", first of all, you have to slightly edit CSV with a text editor and replace " - " that are not in machine/check names (like "DNS Server - Port") with "-"
- replace " - " with ";" before you open the file with Excel, so you get two distinct columns Machines + Checks.
- open the file in Excel. Add  a third column with "1"s to feed the Pivot Table with some data
- Use Data / Pivot Table, which is pretty self explanatory (or have a look to this introduction : http://www.cpearson.com/excel/pivots.htm)
- voilà !

Next step would be to get such a table dynamically from GFI NSM report, but that's another story...

(in reply to pjgfi)
Post #: 6
Page:   [1]
All Forums >> [Network Security] >> GFI Network Server Monitor >> Generate Excel table (Computers x Checks) from NSM report Page: [1]
Jump to:





New Messages No New Messages
Hot Topic w/ New Messages Hot Topic w/o New Messages
Locked w/ New Messages Locked w/o New Messages
 Post New Thread
 Reply to Message
 Post New Poll
 Submit Vote
 Delete My Own Post
 Delete My Own Thread
 Rate Posts


   © 2008. All rights reserved. GFI Software Home Products Download Trials Support Ordering Site Map About Us Contact us
GFI solutions: Exchange anti spam filter - exchange anti virus - isa server - network vulnerability scanner - event log management - USB security software - exchange archiving - fax server software