topic Re: How to use multiple where conditions in a search to match and correlate start and end time fields? in Splunk Search

How to use multiple where conditions in a search to match and correlate start and end time fields?

jsven7 — Mon, 19 Oct 2015 17:15:03 GMT

Working with the following:

EventStarts.txt
UserID, Start Date, Start Time

SpecialEventStarts.txt
UserID, Start Date, Start Time

EventEnds.txt
UserID, Start Date, End Time

SpecialEventEnds.txt
UserID, Start Date, End Time

I have to match up the starts with the appropriate ends. So far I know how to extract the required data, but I don't know how to do it for the start and end so as to match them up. I believe I have to use a where condition. This is my thinking...

x = "EventStarts.txt" OR "SpecialEventStarts.txt" OR "EventEnds.txt" OR "SpecialEventEnds.txt"
| where x = EventStarts.txt
| do what I want you to do
| where x = SpecialEventStarts.txt
| do what I want you to do
| where x = EventEnds.txt
| #do what I want you to do
| where x = SpecialEventEnds.txt
| do what I want you to do

How do I know when the where condition stops???

Re: How to use multiple where conditions in a search to match and correlate start and end time fields?

GeorgeStarkey — Mon, 19 Oct 2015 18:29:18 GMT

This is likely a use case for transaction command.

http://docs.splunk.com/Documentation/Splunk/6.3.0/SearchReference/Transaction

something along the lines of

base search | transaction startswith=EventStarts.txt endswith=EventEnds.txt

Re: How to use multiple where conditions in a search to match and correlate start and end time fields?

woodcock — Mon, 19 Oct 2015 18:31:16 GMT

Are you getting these events by forwarding them in (monitoring the files) or by using inputlookup (or inputcsv)? If the former, which date are you using for your timestamp ( _time )?

Re: How to use multiple where conditions in a search to match and correlate start and end time fields?

somesoni2 — Mon, 19 Oct 2015 18:53:14 GMT

It depends on "do what I want you to do" whether it can be achieved by simple where clause or using transaction OR other commands. Could you provide more details on what you want to do here, how the Start and End will be correlated etc?

Re: How to use multiple where conditions in a search to match and correlate start and end time fields?

woodcock — Mon, 19 Oct 2015 19:16:43 GMT

I am assuming that for EventEnds.txt and SpecialEventEnds.txt you actually have End Date and not Start Date, right?

Re: How to use multiple where conditions in a search to match and correlate start and end time fields?

woodcock — Mon, 19 Oct 2015 19:17:49 GMT

You have not specified what you are really trying to do so we have to guess quite a bit but, assuming that you have forwarded in these events from files, you can do something like this and maybe this gets you far enough along to finish it for yourself:

index=* source="*EventStarts.txt" OR source="*SpecialEventStarts.txt" OR source="*EventEnds.txt" OR source="*SpecialEventEnds.txt" | eval special=if(like(source, "%Special%"), "Special", "Normal") | stats values(*) AS * by user special

Re: How to use multiple where conditions in a search to match and correlate start and end time fields?

jsven7 — Wed, 21 Oct 2015 12:54:44 GMT

I uploaded CSVs to test it out but the idea is to get these events from monitoring files.

Re: How to use multiple where conditions in a search to match and correlate start and end time fields?

jsven7 — Wed, 21 Oct 2015 12:56:26 GMT

That's right. My plan to match them up is to use the Start and End Dates. So to do this in the code I was thinking I'd need to use the Where function to execute lines of code only for a specific sourcetype and then move on to the next.

Re: How to use multiple where conditions in a search to match and correlate start and end time fields?

jsven7 — Wed, 21 Oct 2015 12:58:10 GMT

In the "do what I want you to do" I plan on identifying the date/time of the records and match them up chronologically.

Re: How to use multiple where conditions in a search to match and correlate start and end time fields?

jsven7 — Wed, 21 Oct 2015 13:02:21 GMT

eval special=if(like(source, "%Special%"), "Special", "Normal")

OK. Woodcock I'm thinking instead of a where condition I can use the if condition to determine the sourcetype. Sort of a similar problem though. I understand that the "Special" portion of the above line represents the executable if the if equals true and the "Normal" is the else. How do I perform multiple lines of executables when the if equals to true?

Re: How to use multiple where conditions in a search to match and correlate start and end time fields?

woodcock — Wed, 21 Oct 2015 16:31:11 GMT

Unfortunately, you have to stack more | eval x=if() clauses into the pipeline. There may be more tricky options but I would need to know exactly what you are trying to do.

Re: How to use multiple where conditions in a search to match and correlate start and end time fields?

jsven7 — Thu, 22 Oct 2015 11:56:22 GMT

Ok. Thanks I appreciate your help.