Als je vanuit een of andere logfile gegevens in Excel gaat bewerken dan is het mogelijk dat de octets van het gelogde IP adres in de verkeerde volgorde staan, achterstevoren dus. Om de volgorde van het IP adres om te keren kun je geen gebruik maken van de standaard functie in Excel om een tekst in een veld om te keren. De volgorde van de cijfers in elk octet is namelijk wel juist, alleen de volgorde van de octets niet. Zo wordt het ip adres 192.168.0.1 bijvoorbeeld als 1.0.168.192 gelogd.

Ik heb zelf bijvoorbeeld een Windows Performance Monitor Trace Log file via LogParser geconverteerd naar een .csv bestand dat ik vervolgens als externe gegevensbestand in een Excel werkblad opgenomen heb. Daarop heb ik ander tabblad een draaitabel gemaakt zodanig dat alle IP adressen daarin vermeld worden. In dat .csv bestand bevat de zogenaamde userdata details van het event gelogde event. Deze details zijn door een pipe-teken gescheiden. Bij het inlezen van het externe gegevensbestand in het Excel werkblad heb ik er voor gezorgd dat zowel de comma- als het pipe-teken als veldscheidingsteken gebruikt zijn. Hierdoor komen de IP adressen netjes in een aparte kolom. De waardes in die kolom zien er uit als “daddr=1.0.168.192″.

Omdat ik eigenlijk direct de echte IP adressen in mijn excelsheet wil zien en niet iets als “daddr=1.0.168.192″ moest ik een formule ontwikkelen die het IP adres uit het veld bepaalde en daarvan de octets in de juiste volgorde zet. In onderstaande formule is uitgegaan van het feit dat de brontekst (bijv. “daddr=1.0.168.192″) zich in het veld A1 bevindt. Wil je de formule op een ander veld toepassen dan moet je A1 overal in de formule vervangen door het juiste veld.

Excel Formule voor het bepalen en omkeren van het IP adres:

=TEKST.SAMENVOEGEN(WAARDE(RECHTS(A1; LENGTE(A1)-VIND.ALLES(“.”; A1; VIND.ALLES(“.”;A1; VIND.ALLES(“.”; A1)+1)+1)));”.”;DEEL(A1; VIND.ALLES(“.”; A1; VIND.ALLES(“.”; A1)+1)+1; VIND.ALLES(“.”; A1; VIND.ALLES(“.”; A1; VIND.ALLES(“.”; A1)+1)+1)-VIND.ALLES(“.”; A1; VIND.ALLES(“.”; A1)+1)-1);”.”;DEEL(A1;VIND.ALLES(“.”;A1)+1;VIND.ALLES(“.”;A1;VIND.ALLES(“.”;A1)+1)-VIND.ALLES(“.”;A1)-1);”.”;WAARDE(LINKS(RECHTS(A1;LENGTE(A1)-VIND.ALLES(“=”;A1)); VIND.ALLES(“.”;RECHTS(A1;LENGTE(A1)-VIND.ALLES(“=”;A1)))-1)))

Heb je een engelstalige Excel dan moet je het volgende vervangen:

VIND.ALLES wordt FIND
DEEL wordt MID
LENGTE wordt LEN
WAARDE wordt VALUE
LogParser help: http://support.microsoft.com/kb/910447 

Comments are closed.