Splunk Search

How to use a lookup table to restrict a search?

dbcase
Motivator

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
0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

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

View solution in original post

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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
0 Karma

dbcase
Motivator

Ahhhh now I see what was missing. Thanks Somesoni2!

0 Karma

JCISplunker
Explorer

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" ")"
0 Karma

dbcase
Motivator

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 ( 
0 Karma

JCISplunker
Explorer

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.

0 Karma

skoelpin
SplunkTrust
SplunkTrust

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
0 Karma

dbcase
Motivator

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
0 Karma

somesoni2
SplunkTrust
SplunkTrust

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

0 Karma

dbcase
Motivator

lookup table just has one column . ->MAC

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

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...