How do I create a search which takes a list of items as input and returns a table

[dtilly]

I am using Splunk to log all data from a firewall. I get records that contain MAC addresses and timestamps among many other fields. An example of these fields would be:

time="2015-03-28 12:03:17" srcMac=f0:a2:25:82:b0:c1

I am trying to write a search or dashboard or report that will allow me to import a list of srcMac values and have it return a table that lists the input values and the last time that each srcMac has appeared in the logs. The following search will give me what I want for one value:

f0:a2:25:82:b0:c1 | head 1 | table time, srcMac

The question is, how do I make this loop through a list of srcMac values and create one table listing each?

[fdi01]

I think if you want the srcMac list for each time value then tries like :

...|stats values(srcMac) as "liste of srcMac " by  time|...

or

...|stats list(srcMac) as "liste of srcMac " by  time|...

[stephanefotso]

.......... srcMac=* time=*|table time, srcMac

[dtilly]

Thanks for the quick response, but your query returns every single srcMac and the time of it's entry. This will return millions of records per day. I need only the last entry for each srcMac. If I add | head 1 to the query, I only get the last one record, not the last one record per srcMac.

[stephanefotso]

Ha ok ! i think i now understand what you want. Test this:

 .......... srcMac=* time=*|stats  last(time) by  srcMac

Hope it would help

[dtilly]

OK I think that's it, except for some reason, your query returns the first occurances of each srcMac. So I tried:

srcMac=* time=*|stats first(time) by srcMac

That did the trick. I guess the query searches for the first database occurrence searching backwards from most recent. Is this true?

[sideview]

Yep. that's correct. to understand last() and first() you have to remember that the commands always start processing the data from the latest event to the earliest event. first() is the first one they see, therefore the, er, "last". It's very confusing. There's also earliest() and latest() but it's better to get the hang of first()/last() cause a) earliest/latest needs _time to be present on the rows, and b) obviously it has to do a little more processing with them whereas first() last() requires almost no work at all.

[stephanefotso]

Thanks to sideview for that clarification. In fact, what you say is true dtilly. first(X) is what to use, since it returns the first seen value of the field X. In general, the first seen value of the field is the most recent instance of this field, relative to the input order of events into the stats command, which is the the last entry for each srcMac you wanted.

Concerning your question, I thing sideview gave you the correct answer.

[dtilly]

That makes perfect sense. Now your query will serve my needs, but let me ask for a little clarification. fdi01 above listed a way to include a list in the query... "stats values(srcMac) as "liste of srcMac " by time". Your query will give me the last for every single MAC address, but let's say I am only interested in 10 MAC addresses. I could use your query and dump everything to a spreadsheet and then search for the ones I am interested in, but how would I limit the searching to a set of MACs vs every single one.

Would something like this work:
srcMac=* as "list" time=*|stats first(time) by srcMac

Sorry I am a newbie at this.

[stephanefotso]

It depend on you. Here are some examples you can use!

srcMac=* time=*|stats first(time) by srcMac|head X

or

srcMac=* time=*|stats first(time) by srcMac  |where like(srcMac, "f0:a2:%:82:b0:c%")

or

srcMac="*25:82*" time=*|stats first(time) by srcMac
........................................