Splunk Search

How to compare a field name with different field values?

chandanaberi
Explorer

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.

0 Karma
1 Solution

MonkeyK
Builder

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]

View solution in original post

MuS
Legend

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

0 Karma

chandanaberi
Explorer

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.

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

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

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

Hi @chandanaberi,
Have you tried?

0 Karma

chandanaberi
Explorer

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.

0 Karma

MonkeyK
Builder

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]

chandanaberi
Explorer

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

0 Karma

MonkeyK
Builder

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

0 Karma

chandanaberi
Explorer

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.

0 Karma

MonkeyK
Builder

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

chandanaberi
Explorer

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

0 Karma

MonkeyK
Builder

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.

0 Karma

chandanaberi
Explorer

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.

0 Karma
Get Updates on the Splunk Community!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...