topic Re: create a search query that finds a field value (eg A) that have mismatched field value(B) and field value (C) in Splunk Search

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

man03359 — Mon, 18 Sep 2023 22:19:32 GMT

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.

Re: create a search query that finds a field value (eg A) that have mismatched field value(B) and field value (C)

gcusello — Mon, 18 Sep 2023 10:08:14 GMT

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

Re: create a search query that finds a field value (eg A) that have mismatched field value(B) and field value (C)

man03359 — Tue, 19 Sep 2023 12:23:27 GMT

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!

Re: create a search query that finds a field value (eg A) that have mismatched field value(B) and field value (C)

gcusello — Tue, 19 Sep 2023 12:27:07 GMT

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

Re: create a search query that finds a field value (eg A) that have mismatched field value(B) and field value (C)

man03359 — Mon, 25 Sep 2023 11:59:01 GMT

Hi @gcusello ,

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

Thanks for the Help, really appreciate it 🙂 🙂

Ciao.

Re: create a search query that finds a field value (eg A) that have mismatched field value(B) and field value (C)

gcusello — Mon, 25 Sep 2023 12:27:02 GMT

Hi @man03359 ,

good for you, see next time!

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated 😉