Formatting IP Addresses from ePO DB with analytics/BI software

[cscoiata]

This is an unsupported configuration so far as I know.  Wasn't able to locate any content all that related, so figured it was worth a go to ask

Have already tried using the function listed here [] with no success in translating the following columns  to a human readable format:

EPOEvents. TargetIPV6  [BINARY]

EPOEvents. TargetIPV4  [INT]

Should the data types for these columns be NVARCHAR so that an external analytics tool can process them properly, returning results in a human-readable format?

Any insight on how to solve this matter would be most appreciated!

[cscoiata]

i posed the same question on the LI ePO forum and got this back.  Good stuff!

Dwayne Noaker

Solutions Consultant at Advanced Technology Consultants

Ian,

I had this problem too when trying to integrate any third-party product with the ePO database. This is the query I used to translate the IP to usable format.

SELECT [AutoID]
,[AnalyzerName]
,[AnalyzerHostName]
,dbo.RSDFN_ConvertIntToIPString (AnalyzerIPV4) AnalyzerIPV4
,[SourceHostName]
,dbo.RSDFN_ConvertIntToIPString (SourceIPV4) SourceIPV4
,[TargetHostName]
,dbo.RSDFN_ConvertIntToIPString (TargetIPV4) TargetIPV4
FROM [dbo].[EPOEvents]

--

This got me further than the previous code linked in my original post.  Though we're still having troubles with the IP addresses being processed.  The colleague I'm working with asked the BI software support about recommendations, and they advised to set the data type for the IP columns to STRING.  At this point, I'm lost on how to proceed to get what we're after.

[exbrit]

Moved to ePO

Peter

Moderator

[fitchsoccer342]

If you looking to just convert the IP address columns to string, just CAST to varchar. I'm running the below in SSMS and it is working fine..

And if you want to convert IPv6 you need to use the function dbo.RSDFN_ConvertBigIntToIPString

[fitchsoccer342]

Actually, as I started looking at this more today, the original FN already converts the IP address as varchar, so there is no need to do my above cast.. If you're having trouble with the IP's being processed its definitely not because they are not in a string format, because they are as a result of RSDFN_ConvertIntToIPString.. see below. I don't think it would make a difference but you could try to CAST to NVARCHAR instead of VARCHAR just to try it.. nvarchar can store any unicode data as varchar is restricted to an 8 bit-codepage.. just a thought.