Security

This will show you how to dump the Bradford device database to a CSV, massage the data and prepare it for import to CPPM.

Excel is the only commercial product required.

**Note: This will only work if all endpoints contain the same attributes (example: MAC, username, role).

1. In CPPM, navigate to Administration > Dictionaries > Attributes and then click Add at the top right.
   
   
2. Entity should be Endpoint. Create an attribute called “Bradford-Role” with Data Type “Text”. Change “Allow Multiples” to No.
   
   
3. If you don’t already have a “Username” attribute, follow steps 1 and 2 to create one. (It may be called [Username])
   
   
4. Export your data from Bradford to a CSV. You’ll want to navigate to the “Host View” and click the Export to CSV button at the bottom (location varies by version)
   
   At the bare minimum, you’ll want to export “Adapter – Physical Address” and “User – User ID”. I also recommend exporting “Host – Role”.
   
   
   
   
5. Open up the exported CSV in Excel. Add two columns to the end. Call the first one "mac-no-colons" and the second one "macAddress".
   
   
6. In the “mac-no-colons” column for row 2, add the following formula
   
   

   =SUBSTITUTE(<cell where exported mac address lives>, ":", "")

    

7. In the “macAddress” column for row 2, add the following formula:
   
   

   =LOWER(<replace with mac-no-colons column row 2 i.e. D2>)

    

8. Highlight both of the new columns in row 2 and click the bottom right corner until the cursor turns to a plus sign. Now drag down to the bottom of the sheet. This will replicate the formula.
   
   
9. Add another new column and call it “status”. In row 2, type “Known”. Copy that cell’s value and then select rows 3 to the end and paste.
   
   
10. The next step is to hide (don’t delete, just hide) the original “Adapter – Physical Address” and “mac-no-colons” columns. (Click the column header, right click and choose Hide)
    
    
11. Now delete Row 1 (the header row).
    
    
12. Now Select All (Control + A, Command + A) and then Click the “Find & Select” button which is indicated by a binocular icon. Select Go To Special, click Visible cells only, then click OK.
    
    
    
    
    
    
13. Copy the text to the clipboard (Control + V, Command + V)
    
    
14. Create a new worksheet by clicking the + at the bottom of the screen.
    
    
    
    
15. Go to the new worksheet, and click the arrow under the Paste icon and select Paste Values.
    
    
    
    
16. Save this file as an Excel sheet.
    
    
17. Make sure you are on the second worksheet, and now save the file as a CSV (Comma delimited). Click OK at all of the prompts.
    
    
18. Now head over to http://www.freeformatter.com/csv-to-xml-converter.html
    
    
19. Open your CSV file in a plain-text editor. Select All, copy and then paste into “Option 1”. Skip Option 2
    
    
20. In the “XML Template” box, enter copy and paste the formula below
    
    

    <Endpoint macAddress="##3##" status="##4##">
    <EndpointTags tagName="Username" tagValue="##1##"/>
    <EndpointTags tagName="Bradford-Role" tagValue="##2##"/>
    </Endpoint>

     

21. Click Convert CSV to XML. Click Copy to Clipboard and then paste the output to a text editor. (Notepad++ recommended as it keeps the formatting)
    
    
22. Remove everything before the first <Endpoint xxx > entry and remove the </document> at the end.
    
    
    
    
    
    
    
23. Add the following to the beginning:
    
    

    <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <TipsContents xmlns="http://www.avendasys.com/tipsapiDefs/1.0">
      <TipsHeader exportTime="Mon Jun 16 12:14:26 EDT 2014" version="6.3"/>
      <Endpoints>

     
    
    
    

24. Add the following to the end:
    
    

    </Endpoints>
    </TipsContents>

     
    
    
    

25. Save this file as an XML file and Import it into the endpoint database in CPPM (Configuration > Identity > Endpoints > Import)

The endpoints are now imported and you can use the "Bradford-Role" attribute in policy decisions.