How to use a lookup table to restrict a search?

[dbcase]

Hi,

I have a lookup table that is just a list of MAC addresses. I need to be able to search a data set that has mac addresses in the event (extracted via a rex function - this is already done) and pull out the firmware version.

I have a query that looks like this but sadly it doesn't match any events

index="blah" sourcetype="cvr-mesos-stdout" source="*latest*" cvr-livecache-service thumbnail  "POST /"  [ | inputlookup blahQAMACs.csv | fields MAC ]| rex field=_raw "\[Sercomm (?[\w-]+) (?[\w.]+) (?\w+)\]"  | eval  output="mac="+ mac + " model="+ cammodel + " firmware=" + firmware | fields output

[somesoni2 · Accepted solution]

Try like this

 earliest=-1h index="xhome" sourcetype="cvr-mesos-stdout" source="*latest*" cvr-livecache-service thumbnail "POST /" | rex field=_raw "\[Sercomm (?<cammodel>[\w-]+) (?<firmware>[\w.]+) (?<mac>\w+)\]"  | search [ | inputlookup CoxQAMACs.csv | fields MAC | rename MAC as mac]| eval  output="mac="+ mac + " model="+ cammodel + " firmware=" + firmware | fields output

[dbcase]

Ahhhh now I see what was missing. Thanks Somesoni2!

[JCISplunker]

Assuming the column name in your lookup matches field name in your base search you should be able to restrict it simply by:

your_base_search | search [ | inputlookup yourlookup.csv ]

To test it check what the inner search evaluates to first:

| inputlookup yourlookup.csv | format 

which should evaluate to:

((MAC="blah") OR (MAC="anotherblah") OR ...)
If it didn't work properly, you might try:

| inputlookup yourlookup.csv | format "(" "(" "" ")" "OR" ")"

[dbcase]

Hi JCISplunker,

Ran this

 | inputlookup yourlookup.csv | format 

and ended up with this.... So it looks like the inputlookup is working

  ( ( MAC="944A0C19A58A" ) OR ( MAC="D42122C9B556" ) OR ( MAC="B4A5EFE7218E" ) OR ( MAC="B4A5EFE71C04" ) OR ( MAC="944A0C19A98F" ) OR ( MAC="B4A5EFE72197" ) OR ( MAC="B4A5EFE71C36" ) OR ( MAC="B4A5EFE71C3D" ) OR ( MAC="944A0C19A982" ) OR ( MAC="B4A5EFE71C2E" ) OR ( MAC="944A0C19A26A" ) OR ( MAC="B4A5EFF45530" ) OR ( MAC="E060660AA2C2" ) OR ( MAC="944A0C19A585" ) OR ( MAC="944A0C19B6A3" ) OR ( MAC="D42122C9B486" ) OR ( MAC="B4A5EFE72031" ) OR ( MAC="B4A5EFE72072" ) OR ( MAC="B4A5EFE7218C" ) OR ( MAC="944A0C19A230" ) OR ( MAC="D42122C9B462" ) OR ( MAC="B4A5EFE71C26" ) OR ( MAC="944A0C19B3BD" ) OR ( MAC="D42122D9C3BC" ) OR ( MAC="944A0C19A222" ) OR ( MAC="B4A5EFE72076" ) OR ( MAC="B4A5EFF43C37" ) OR ( MAC="E060660AA2C2" ) OR ( MAC="944A0C1D7091" ) OR ( MAC="7894B4E8C2B2" ) OR ( MAC="B4A5EFE72196" ) OR ( MAC="D42122C9B503" ) OR ( MAC="944A0C179598" ) OR ( MAC="D42122C9B581" ) OR ( MAC="E06066144107" ) OR ( MAC="944A0C254208" ) OR ( MAC="7894B4FA4E5E" ) OR ( MAC="B4A5EFE72081" ) OR ( MAC="B4A5EFE72069" ) OR ( MAC="944A0C19B504" ) OR ( MAC="B4A5EFE72192" ) OR ( MAC="E06066144125" ) OR ( MAC="7894B4EEFE71" ) OR ( MAC="944A0C19A1F8" ) OR ( MAC="D42122C9B57E" ) OR ( MAC="7894B4FA4E64" ) OR ( MAC="B4A5EFF45520" ) OR ( MAC="944A0C10FECB" ) OR ( 

[JCISplunker]

Great, now that you established that, you have to make sure your base search returns events with MAC field defined. What I like to do in these type of filtering operations, is to add the qualifier to the base search, so in your case:

index="blah" sourcetype="cvr-mesos-stdout" source="*latest*" MAC="*" cvr-livecache-service thumbnail  "POST /"  [ | inputlookup blahQAMACs.csv ] ...

Note that case matters, so if your field is called "mac" or "Mac" the above will return zero events.

[skoelpin]

You're missing an append

index="blah" sourcetype="cvr-mesos-stdout" source="*latest*" cvr-livecache-service thumbnail  "POST /"  | append [ | inputlookup blahQAMACs.csv | fields MAC ]| rex field=_raw "\[Sercomm (?[\w-]+) (?[\w.]+) (?\w+)\]"  | eval  output="mac="+ mac + " model="+ cammodel + " firmware=" + firmware | fields output

[dbcase]

Hi,

I have this updated query which now returns results but they are inaccurate. The results include MAC addresses that are NOT in the lookup file

earliest=-1h index="xhome" sourcetype="cvr-mesos-stdout" source="*latest*" cvr-livecache-service thumbnail "POST /" | rex field=_raw "\[Sercomm (?<cammodel>[\w-]+) (?<firmware>[\w.]+) (?<mac>\w+)\]"  | append [ | inputlookup CoxQAMACs.csv | fields MAC ]| eval  output="mac="+ mac + " model="+ cammodel + " firmware=" + firmware | fields output

[somesoni2]

Are you doing the field extraction for MAC inline in the search or saved the field extraction (with sharing permissions global)?

[dbcase]

lookup table just has one column . ->MAC

within the event the extraction is done in the search via rex, resolves to field ->mac

[somesoni2]

Is | rex field=_raw "\[Sercomm (?[\w-]+) (?[\w.]+) (?\w+)\]" is rex for mac ??