cancel
Showing results for 
Search instead for 
Did you mean: 

Formatting IP Addresses from ePO DB with analytics/BI software

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!

4 Replies

Re: Formatting IP Addresses from ePO DB with analytics/BI software

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.

Highlighted
Reliable Contributor exbrit
Reliable Contributor
Report Inappropriate Content
Message 3 of 5

Re: Formatting IP Addresses from ePO DB with analytics/BI software

Moved to ePO

Peter

Moderator

Re: Re: Formatting IP Addresses from ePO DB with analytics/BI software

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

Re: Re: Re: Formatting IP Addresses from ePO DB with analytics/BI software

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.

MPower Badge Now Available
Customers attending MPower can earn a community badge. Check into the MPower forum and say hi to have the badge awarded to your community profile.