How to create a search query that finds a field value (eg A) that have mismatched field value(B) and field value (C)?

[man03359]

Hi All,

Below is my search query -

index="idx-network-firewall" (sourcetype="fgt_traffic" OR sourcetype="fortigate_traffic")
| stats latest(_time) values(srcname) as src latest(app) as app by src_ip
| lookup Stores_Inventory src_ip OUTPUT Device
| table src_ip Device src app

 I have 3 fields src_ip, src and device. I am getting the field values for src from the first 2 lines of the query -

index="idx-network-firewall" (sourcetype="fgt_traffic" OR sourcetype="fortigate_traffic")
| stats latest(_time) values(srcname) as src latest(app) as app by src_ip

 I am trying to build a search query that finds src_ips that have mismatched src name and device name.

Thanks in advance.

[gcusello]

Hi @man03359,

what do you mean with "src_ips that have mismatched src name and device name."?

Maybe src_ips that have different src_name or different device_name?

if this is your requirement, please try this:

index="idx-network-firewall" (sourcetype="fgt_traffic" OR sourcetype="fortigate_traffic")
| lookup Stores_Inventory src_ip OUTPUT Device
| stats 
   latest(_time) AS latest
   values(srcname) as srcname 
   latest(app) as app 
   dc(srcname) AS srcname_count
   dc(Device) AS Device_count
   BY src_ip
| where srcname_count>1 OR Device_count>1
| table src_ip Device src app

In this way you'll list all the src_ips with more than one name or device.

Ciao.

Giuseppe

[man03359]

Hi @gcusello ,

What I am trying to do it create search query that finds src_ip when the field values of two fields, src and device matches with the below output.

src field values I am getting from --

index="idx-network-firewall" (sourcetype="fgt_traffic" OR sourcetype="fortigate_traffic")
| stats latest(_time) values(srcname) as src latest(app) as app by src_ip 

and device field value I am getting from lookup -

| lookup Stores_Inventory src_ip OUTPUT Device
| table src_ip Device src app

hope this is clear!

[gcusello]

Hi @man03359,

only for confirmation: you need to filter the main search using two fields from a lookup (src_ip and Device), is it correct?

if this is your requirement, you have to use a subsearch with inputlookup:

index="idx-network-firewall" (sourcetype="fgt_traffic" OR sourcetype="fortigate_traffic") [ | inputlookup Stores_Inventory | fields src_ip Device ]
| stats latest(_time) values(srcname) as src latest(app) as app by src_ip 

 Ciao.

Giuseppe

[man03359 · Accepted solution]

Hi @gcusello ,

I was able to figure out the query which worked for me-

index="idx-network-firewall" (sourcetype="fgt_traffic" OR sourcetype="fortigate_traffic") * [| inputlookup  Stores_Inventory
| search Device="stp*"
| return 1601 src_ip]
| stats latest(_time) values(srcname) as src latest(app) as app by src_ip
| lookup Stores_Inventory src_ip OUTPUT Device
| eval Device = lower(Device)  
| eval src=lower(src)
| where isnotnull(Device) AND isnotnull(src)
| rex field=src "(?i)^(?<src>.*?)(?:\..*)?$"
| where src!=Device 
| table src_ip Device src app 

Thanks for the Help, really appreciate it 🙂 🙂

Ciao.

[gcusello]

Hi @man03359 ,

good for you, see next time!

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated 😉