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
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
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
Ahhhh now I see what was missing. Thanks Somesoni2!
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" ")"
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 (
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.
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
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
Are you doing the field extraction for MAC
inline in the search or saved the field extraction (with sharing permissions global)?
lookup table just has one column . ->MAC
within the event the extraction is done in the search via rex, resolves to field ->mac
Is | rex field=_raw "\[Sercomm (?[\w-]+) (?[\w.]+) (?\w+)\]"
is rex for mac ??