Complex searches based on events before or after

[daboos]

I am solution architect for an operator and I am evaluating splunk for the organization, currently i trying to do the following:

I have a log file that contains the following information:

Date,Time, Request Type, XML Message

For example:

2010-07-22   09:10       Sent                    <Employee_INFO_REQ><EMP_ID>54634</EMP_ID></Employee>

2010-07-22   09:10       Received                    <Employee_INFO_RES><EMP_ID>54634</EMP_ID><EMP_NAME><EMP_NAME><EMP_SALARY></EMP_SALARY><EMP_Position></EMP_Position><Employee_INFO_RES>

I am extracting the following fields:

date(2010-07-22), time(09:10), Request Type (Send), Message Type (Employee_INFO_REQ)

If I a am searching for transactions related to a specific employee it's simple as I could search using the EMP_ID value and this will return all relevant transactions.

But for situations as the below:

2010-07-19   07:15       Sent                    <Employee_INFO_REQ><EMP_ID>23</EMP_ID></Employee>

2010-07-19   07:15       Received                    <error>E245 Employee not found</error>

If i search using the emp_id only then only the request shall be displayed, but corresponding error response shall not be displayed. I am wondering if I could search using the emp_id and if the following event is an error and same time then also display it in the search results.

I have tried using joins and sub queries but they did work

Join using the time:

search source="src" emp_id="23" | join type=inner date_time [ search source="src" | where message_type="error" | fields + date_time ]

Kindly note that each of these command by itself returns correct data, but the join for some reason does not work.

[Lowell]

Have you tried using a transaction search to group events that occurred at the same time?

This would be more flexible than using the exact time as you seem be trying with your date_time field in your join because it doesn't have to be an exact match. You could use maxspan=5s witch would group events that occur within a 5 second window.

source=src | transaction maxspan=5s | search emp_id=23

The real problem here is that your log doesn't provide any kind of unique correlation id that lets your accurately group your events together so you have to come up with some alternate (and less accurate) way to group your events. Another approach would be to tell splunk where your events start and stop. This works if your events are always a "Sent" event followed bya "Received" event and there aren't ever intermixed. If this is the case, you could try a search like this:

source=src | transaction starswith="Sent" endswith="Received" | search emp_id=23

Hope that gives you some ideas to try.