cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

SQL Query Database pull - ePO 5.9.1

Jump to solution

We are integrating with a new SIEM tool and our only way to integrate data into the tool is with a direct DB pull. We are having some extreme issues with creating / finding a query that will give us all the data we need inside of the tool. We have identified multiple fields we need, but we are having a huge issue attempting to find what the MD5 (HASH) of threats found from a host based VirusScan Enterprise event.

We are having the same amount of trouble pulling the IP address of the hosts as well. For reference, the MD5 can be found on the GUI of our ePO server. We can go to the "Threat Event Log Details" view that has the malware event, and scroll down to "Additional Event details from VirusScan Enterprise". In this table, we see "MD5: [hash]". We are not sure what this value is called, and we do not know what table to pull it from.

Similarly, we can see the ip address in this table as "Threat Source IPv4 Address". We are not sure what this value is called, and we do not know what table to pull it from.

1 Solution

Accepted Solutions
cdinet
McAfee Employee
McAfee Employee
Report Inappropriate Content
Message 2 of 4

Re: SQL Query Database pull - ePO 5.9.1

Jump to solution

When you have the query created in epo that gives the data you would like to see from the siem, make sure it is saved.  Select that query, then go to actions, view sql.  That will tell you what tables and columns data is being pulled from.

 

Was my reply helpful?
If this information was helpful in any way or answered your question, will you please select Accept as Solution in my reply and together we can help other members?

View solution in original post

3 Replies
cdinet
McAfee Employee
McAfee Employee
Report Inappropriate Content
Message 2 of 4

Re: SQL Query Database pull - ePO 5.9.1

Jump to solution

When you have the query created in epo that gives the data you would like to see from the siem, make sure it is saved.  Select that query, then go to actions, view sql.  That will tell you what tables and columns data is being pulled from.

 

Was my reply helpful?
If this information was helpful in any way or answered your question, will you please select Accept as Solution in my reply and together we can help other members?

View solution in original post

Re: SQL Query Database pull - ePO 5.9.1

Jump to solution

This seems to helped with the majority of things, but the SQL query is bringing back IP addresses up as a negative and non-decimal number.

Is there a conversion for these numbers?

cdinet
McAfee Employee
McAfee Employee
Report Inappropriate Content
Message 4 of 4

Re: SQL Query Database pull - ePO 5.9.1

Jump to solution

Yes - run this in sql:

SELECT dbo.RSDFN_ConvertIntToIPString(1979448947)
It should add a new column in the table to pull from.  If that doesn't do it, then use these steps:
 
SELECT *,

"AnalyzerIPV4_desc" = ( convert(varchar(3),

convert(tinyint,

substring(convert(varbinary(4),

convert(bigint,

("EPOEvents"."AnalyzerIPV4" + 2147483648))),

1,

1)))+'.'+convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,("EPOEvents"."AnalyzerIPV4" + 2147483648))),2,1)))+'.'+convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,("EPOEvents"."AnalyzerIPV4" + 2147483648))),3,1)))+'.'+convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,("EPOEvents"."AnalyzerIPV4" + 2147483648))),4,1))) ), "SourceIPV4_desc" = ( convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,("EPOEvents"."SourceIPV4" + 2147483648))),1,1)))+'.'+convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,("EPOEvents"."SourceIPV4" + 2147483648))),2,1)))+'.'+convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,("EPOEvents"."SourceIPV4" + 2147483648))),3,1)))+'.'+convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,("EPOEvents"."SourceIPV4" + 2147483648))),4,1))) ), "TargetIPV4_desc" = ( convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,("EPOEvents"."TargetIPV4" + 2147483648))),1,1)))+'.'+convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,("EPOEvents"."TargetIPV4" + 2147483648))),2,1)))+'.'+convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,("EPOEvents"."TargetIPV4" + 2147483648))),3,1)))+'.'+convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,("EPOEvents"."TargetIPV4" + 2147483648))),4,1))) )

FROM [EPOEvents]

WITH (NOLOCK)

Once we run the above query. It will convert sourceIPV4 from the INT  to actual IP format. It will add one more column to show the Actual IP address.
 

Was my reply helpful?
If this information was helpful in any way or answered your question, will you please select Accept as Solution in my reply and together we can help other members?

You Deserve an Award
Don't forget, when your helpful posts earn a kudos or get accepted as a solution you can unlock perks and badges. Those aren't the only badges, either. How many can you collect? Click here to learn more.

Community Help Hub

    New to the forums or need help finding your way around the forums? There's a whole hub of community resources to help you.

  • Find Forum FAQs
  • Learn How to Earn Badges
  • Ask for Help
Go to Community Help

Join the Community

    Thousands of customers use the McAfee Community for peer-to-peer and expert product support. Enjoy these benefits with a free membership:

  • Get helpful solutions from McAfee experts.
  • Stay connected to product conversations that matter to you.
  • Participate in product groups led by McAfee employees.
Join the Community
Join the Community