topic Re: How to correlate fields from two different sourcetypes spawning across multiple hosts? in Splunk Search

How to correlate fields from two different sourcetypes spawning across multiple hosts?

AbhinandGokul — Tue, 27 Jan 2015 09:17:13 GMT

Hello Guys,

I have a problem in correlating fields spawning across multiple hosts and different sourcetypes.

Here is how our setup looks like:

Apache access Log

CLIENTIP - - [27/Jan/2015:01:49:35 -0500] "POST /context1 HTTP/1.1" 200 295 "-" "unknown" "JSESSIONID" "-"
CLIENTIP - - [27/Jan/2015:01:49:35 -0500] "POST /context2 HTTP/1.1" 200 1896 "-" "unknown" "JSESSIONID" "-"

App Server Logs

01:49:35,580 INFO  [CLASS_NAME] [IP_ADDRESS] [1422341129579] [Company_ID,COMPANY_NAME,OTHER_DETAIL1,OTHER_DETAIL2,OTHER_DETAIL3,OTHER_DETAIL4,OTHER_DETAIL5] Time to generate SQL: 0.503659ms
01:45:35,580 INFO  [CLASS_NAME] [IP_ADDRESS] [1422341129579] [Company_ID,COMPANY_NAME,OTHER_DETAIL1,OTHER_DETAIL2,OTHER_DETAIL3,OTHER_DETAIL4,OTHER_DETAIL5]

Requirement:

I want to be able to search for all CLIENT IPs in my Apache's access logs which have the context 'context1', take those CLIENT IPs and search them in my app server logs (the field name will be different here) and then get out the company name from my app server logs.

Basically I want to be able to create a report of how many requests per company has come in for a given context.

Could somebody please help me with this?
Regards,
Abhi

Re: How to correlate fields from two different sourcetypes spawning across multiple hosts?

aljohnson_splun — Tue, 27 Jan 2015 18:26:09 GMT

You could do something like this maybe ?

sourcetype=access_combined uri_path="/context*"
| fields CLIENTIP uri_path
| append [search sourcetype=app_server IP_ADDRESS=*
| fields IP_ADDRESS company ]
| eval ip=coalesce(CLIENTIP, IP_ADDRESS)
| stats count  by context, ip, company

Break down:
Select relevant fields

sourcetype=access_combined uri_path="/context*"
| fields CLIENTIP uri_path

Append a search of the second sourcetype:

| append [search sourcetype=app_server IP_ADDRESS=*
| fields IP_ADDRESS company ]

Coalesce the IP fields:

| eval ip=coalesce(CLIENTIP, IP_ADDRESS)

Lastly, count the contexts

| stats count by context, ip, company

Re: How to correlate fields from two different sourcetypes spawning across multiple hosts?

AbhinandGokul — Thu, 29 Jan 2015 05:37:57 GMT

Unfortunately, the way SPLUNK has been set up IP_ADDRESS in my app server logs is not a field but it is just a log entry. This would not work 😞

Regards,
Abhi

Re: How to correlate fields from two different sourcetypes spawning across multiple hosts?

cpride_splunk — Fri, 30 Jan 2015 22:02:01 GMT

I think this is closer to working than you might think. Even if you don't want to add a field extraction for IP_ADDRESS you could easily pull it out with a "rex" command.

The only other comment is I might try to do this with just an OR clause in the base search to get events from both logs.

Re: How to correlate fields from two different sourcetypes spawning across multiple hosts?

AbhinandGokul — Mon, 02 Feb 2015 08:01:50 GMT

Thanks! After extracting it out as a field it started working! 🙂

Re: How to correlate fields from two different sourcetypes spawning across multiple hosts?

aljohnson_splun — Mon, 02 Feb 2015 17:06:50 GMT

Sorry I just saw your comment now or else I would've responded.

Glad to hear it is working !