topic Re: How to compare a field name with different field values? in Splunk Search

How to compare a field name with different field values?

chandanaberi — Tue, 29 Sep 2020 16:52:30 GMT

I am new to splunk,

I have two field names: status and ip_address,
status has different field values, status=200, 300, 400, 405. I can select any status value to list out ip_addresses by using below search
index=tutorial_data status = 200 | stats dc by ip_address
some ip_addresses are common in different status. I want to pull out all common ip_addresses list from status=200 and status=400.
How can i do that in a single search?

Appreciate your answers.

Re: How to compare a field name with different field values?

MonkeyK — Wed, 22 Nov 2017 23:50:02 GMT

a direct approach might be

index=tutorial_data status=200  
| stats count by status ip_address 
| sort 10 count 
| append [search index=tutorial_data status=400  | stats count by status ip_address | sort 10 count]

Re: How to compare a field name with different field values?

kamlesh_vaghela — Thu, 23 Nov 2017 08:09:04 GMT

Hi @chandanaberi

Can you please try this search?

YOUR_SEARCH | makemv status delim="," | where status="200"

Sample Search:

| makeresults 
| eval status="200,300,400,405",ip_address="10.0.0.1" 
| append 
    [| makeresults 
    | eval status="300,400,405",ip_address="10.0.0.2" ] 
| makemv status delim="," | search status="200" status="400"

You can use search OR where for filter.

| search status="200" status="400"
| where status="200" AND status="400"

This will give ip_address which contain 200 status.

If you want ip_address only with 200 status then use below search,

YOUR_SEARCH | makemv status delim="," | stats count by status, ip_address | search status="200" status="400"

Sample Search:

| makeresults 
| eval status="200,300,400,405",ip_address="10.0.0.1" 
| append 
    [| makeresults 
    | eval status="300,400,405",ip_address="10.0.0.2" ] 
| makemv status delim="," | stats count by status, ip_address | search status="200" status="400"

I hope this will help you.

Happy Splunking

Re: How to compare a field name with different field values?

chandanaberi — Tue, 29 Sep 2020 17:01:02 GMT

This command is works fine, but it's listing only 50,000 statistics of append search. I want to list all common ip_addresses and also want to list uncommon ip_addresses w.r.t status=200 and status=400

Re: How to compare a field name with different field values?

kamlesh_vaghela — Mon, 27 Nov 2017 18:22:27 GMT

Hi @chandanaberi,
Have you tried?

Re: How to compare a field name with different field values?

chandanaberi — Tue, 29 Sep 2020 17:01:08 GMT

Yeah, I tried the above searches, Those are not what i am looking exactly. I want to list all common/uncommon ip_addresses in a single search Because my data has 100+ ip_addresses.

Re: How to compare a field name with different field values?

MuS — Mon, 27 Nov 2017 19:04:45 GMT

Hi chandanaberi,

as you said correctly, there is no need for any subsearch here. A single stats search with additional eval will do it:

index=_internal clientip=* status=* 
| eval dc_200=case(status="200", 1, 1=1, 0), dc_404=case(status="404", 1, 1=1, 0) 
| stats dc(eval(dc_200 + dc_404)) AS sum_status count by clientip 
| eval frequent=case(sum_status="2", "common", sum_status="1", "uncommon", 1=1, "unknown")

This is a run everywhere search and you need to adapt it to your needs 😉

Hope this helps to get you started ...

cheers, MuS

Re: How to compare a field name with different field values?

MonkeyK — Wed, 29 Nov 2017 02:26:54 GMT

Sorry, I had misunderstood how you were using common and uncommon. If common mean has both and uncommon means has only 1, you can do it like this:

index=tutorial_data (status=200 OR status=400) | stats dc(status) as statusCount by ip_address| eval common=case(statusCount=2,"Y", statusCount=1,"N", 1=1, "error") | table ip_address common

we could add count to the stats command and sort on that if you were interested in the most/least frequent IP addresses

Re: How to compare a field name with different field values?

chandanaberi — Fri, 01 Dec 2017 19:13:29 GMT

Thank you so much for your answer.

This one is listing common and uncommon ip_addresses. Though, It's not full solution, i wanted to list uncommon values with status values.

Re: How to compare a field name with different field values?

chandanaberi — Tue, 29 Sep 2020 17:00:03 GMT

Thank you for your answer.

I did some changes to get minimum solution. Two changes I did.
1. Distinct count w.r.t clientip
2. Which is done in the last eval search, replaced with count instead of sum_status then it works fine.

index=_internal clientip=* status=*
| stats dc(clientip) by clientip status
| eval dc_200=case(status="200", 1, 1=1, 0), dc_404=case(status="404", 1, 1=1, 0)
| stats dc(eval(dc_200 + dc_404)) AS sum_status count by clientip
| eval frequent=case(count="2", "common", count="1", "uncommon", 1=1, "unknown")

But i need to represent uncommon ip_addresses with status value.

Re: How to compare a field name with different field values?

MonkeyK — Fri, 01 Dec 2017 19:59:26 GMT

OK, simple correction:

index=tutorial_data (status=200 OR status=400) 
| stats dc(status) as statusCount max(status) as status by ip_address
| eval common=case(statusCount=2,"Y", statusCount=1,"N", 1=1, "error"), status=case(statusCount=1,status,1=1,"") 
| table ip_address common status

Re: How to compare a field name with different field values?

chandanaberi — Fri, 01 Dec 2017 20:08:04 GMT

Wow, this is what i expected.
But I have one more doubt, How can i know, which command I have to use for a particular situation? I am confused with the search commands. If you know any references to learn more about it, please suggest me.

Thank you so much

Re: How to compare a field name with different field values?

MonkeyK — Fri, 01 Dec 2017 20:27:35 GMT

Glad I could help. In learning commands, we all start to accumulate patterns of solutions.

The main patterns of searches that I use:
When I want to transform data, or create new fields, I lean on eval. Within that I learn the eval functions.
When I want to summarize data, I lean on stats. Within that I learn the stats functions
When I want to search based on previous results, I use a subsearch or a lookup table.

When the categories that I know how to use fail me, I come to answers.splunk.com and somebody usually tells me about a new command or explains new ways to think about the ones that I already know.

Re: How to compare a field name with different field values?

chandanaberi — Fri, 01 Dec 2017 20:33:32 GMT

I also summarize as you but some times I had to scratch my head to use particular commands. You just used values command to solve this problem. Anyways, thank you so much.