SSH "get all endpoint" report's content

[ThomasArti]

Hello,

I'm currently working on automating a report of the endpoint's details, after trying with the API (wich was not the best solution), i switch to using a report that you can generate by connecting directly to ISE in SSH following the instructions that @thomas gave in response to another post (Solved: REST API Cisco ISE Endpoint Details - Cisco Community).

Everithing works great and the report (in .csv) is full of informations on the endpoints, but browsing in this report using excel i found lines with informations that i don't understand their meanings and that are randomly present in the report.

You can see above the lines in questions, the problems with this 3 lines, that appear multiple time in the report, is in a need to automate the reporting i will parce this document and this line add difficulties.

So if someone know at least the meanings of this lines that will be a good start for me.   

Thanks,

Thomas.A

[thomas]

Do those bits of text appear on their own lines in the same columns?

Or are they randomly sprinkled in different rows and fields?

Always the same columns? If so, which columns?

[ThomasArti]

Those bits of text always take 3 rows, only this 4 informations doesn't differ in each occurence (always in the first 2 colums) :

But there a other informations that are randomly placed in different columns (always in the same 3 rows shown above)   :

Dates  : 

Linux version : 

Mac address : 

Numbers : 

Equipment reference : 

I only saw the the last one 1 time in the entire document it is in the first occurence (check by using ctrl+F), so there are probably other type of information that are present (i didn't check all the document it has 170 000 rows).

[Marcelo Morais]

Hi @ThomasArti ,

 please take a look at MatchedPolicy column (filter on Excel), check if there is anything other than an Endpoint (I think this might be the fastest way to check the info in a 170,000 rows).

Hope this helps !!!

[ThomasArti]

Hi @Marcelo Morais and thanks for your advice,

I didn't think of checking with the filters (pretty stupid of me), and so by using the filters i saw there is 2681 occurences of the "unusual text" for a total of 191008 rows in total, like i said before it doesn't appear periodically (sometime every 10 rows and sometimes 100 rows) .

Like i said before everytime the "unusual text" takes 3 rows, and i did what you suggested and check the Matched Policy column and found numbers (like the fourth screenshots in my response to thomas) instead of the usual policies.

But like i said before the differents informations can appear in other columns so i can found numbers like this in other columns even sometime on the same row.

(Sorry for not responding directly on your comment but the site doesn't let me)

[Marcelo Morais]

Hi @ThomasArti ,

 when I generated a [16] Get All Endpoints on my LAB (almost 1,000 lines) I didn't find anything like that (a "Technical Support" on the MACAddress column), at the end of the day I will generate [16] Get All Endpoints on my Production to check the info.

Regards

[Marcelo Morais]

Hi @ThomasArti ,

 I checked the [16] Get All Endpoints of my Production (almost 260,000 lines), only MAC Addr on the MACAddress column.

Please let me know if there is anything I can do.

Regards.

[ThomasArti]

Hi @Marcelo Morais and thanks for checking on your production,

I had more time to invastigate on the matter and i found 2 things :

-I did found the "technical" text in another column : cdpCacheVersion and the particularity is that all the the text that is usually dispached in 3 rows is in one cell. see the screenshot below

-I also found that it only appear after an endpoint wich is cisco's equipement (cisco switch, AP-Aironet-1850, Cisco router, AP catalyst...) as you can see by checking the MatchedPolicy and OUI columns .

Is it a problem with the parsing of the CSV (because of the coma presents in the "thecnical text" ? 

[thomas · Accepted solution]

Great job hunting that down, @ThomasArti and @Marcelo Morais !

You may manually edit the file before importing to Excel or other spreadsheet and put "'s around the cdpCacheVersion text and it will parse correctly.  You could also try using sed to do this if the beginning and ending of the cdpCacheVersion fields are unique and easy to match:

sed -e 's/Cisco IOS Software,/"Cisco IOS Software,/' \
    -e 's/prod_rel_team/prod_rel_team"/' \
    export.csv

Hopefully that takes care of it for you!

[ThomasArti]

Hi @thomas ,

Thanks for the answer, just to get back to you about using the sed command, like you said it's easy to use if the beginning and the ending a easy to match but the ending can differ as "compiled by" can change. 

But the most important thing it's we spotted the issue ! 

Thanks again @Marcelo Morais for your help !

[Marcelo Morais]

Hi @ThomasArti ,

 yes, that's the problem ... the parsing of CSV, I found the same on my LAB on the cdpCacheVersion:

Cisco IOS Software, C2960 Software (C2960-LANBASEK9-M), Version 12.2(55)SE12, RELEASE SOFTWARE (fc2) Technical Support: http://www.cisco.com/techsupport

Regards