Splunk Search

How to edit my current search to filter out results from my chart?

eandresen
Path Finder

I have been looking around for an solution to my question for a day now and I cannot seem to find a similar Answers post that works for me. If I missed one, feel free to direct me to it.

I am running the following search command to get back a list of server clusters and if they are forwarding data into our Splunk 4 or Splunk 6 environments. I am basing this off of the IP addresses used in the IDX field (IP address and port number) of the Splunkd internal logs.

index=_internal sourcetype=splunkd NOT host=chsxspl* component=TcpOutputProc log_level=INFO splunk_server_group=ewe earliest=-1h       
| rex field=idx "(?<ip_address>\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})"       
| eval splunk_environment=case(cidrmatch("xx.xx.xx.0/24",ip_address), "Splunk 4", cidrmatch("xx.xx.xx.0/24",ip_address), "Splunk 6")
| stats dc(host) as server_count by host, splunk_environment 
| rex field=host "^(?<server_cluster>\S+[^0-9])\d+" 
| eval server_cluster = upper(server_cluster) 
| chart sum(server_count) by server_cluster, splunk_environment
| rename server_cluster as "Server Clusters"
| addcoltotals

The results of the search command provides a list of server clusters as rows and a Splunk 4 and Splunk 6 columns. For each row, there is a possibility of a value in both columns or just in the Splunk 6 column. A sample of the search results are provided below where HOST 1 and 2 are running in both environments and HOST 3 and 4 are only running in the Splunk 6 environment.

Server Clusters Splunk 4        Splunk 6
HOST 1           100             100
HOST 2           16           16
HOST 3                          16
HOST 4                          2

I am trying to update the search string to filter out the rows that do not have a value under the Splunk 4 column, meaning I want to return only the server clusters that are running only in the Splunk 6 environment and not both. The results above would like like the following results with the updated search string.

Server Clusters Splunk 6
HOST 3          16
HOST 4          2

I have worked with several different where causes adding them throughout the search string without any luck. I have even attempted to break up the case statement into two different ones that use different fields and then used where causes to drop out the unneeded data.

Please let me know what I am doing wrong and thanks in advanced for any help you can provide me.

Thanks,
Erik

0 Karma
1 Solution

eandresen
Path Finder

Thanks to ngatchasandra's help, I was able to come up with the following search string that works for me needs. It dropps the server farms that are reporting into both the Splunk 4 and Splunk 6 environments and only returns a list of the server farms that are report into the Splunk 6 environment.

index=_internal sourcetype=splunkd NOT host=chsxspl* component=TcpOutputProc log_level=INFO splunk_server_group=ewe earliest=-15m       
| rex field=idx "(?<ip_address>\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})"       
| eval splunk_environment=case(cidrmatch("xx.xx.xx.0/24",ip_address), "Splunk 4", cidrmatch("xx.xx.xx.0/24",ip_address), "Splunk 6")
| stats dc(host) as server_count by host, splunk_environment 
| rex field=host "^(?<server_cluster>\S+[^0-9])\d+" 
| eval server_cluster = upper(server_cluster) 
| stats sum(server_count) as server_count by server_cluster, splunk_environment
| stats values(splunk_environment) as splunk_environment_list by server_cluster,server_count
| where NOT splunk_environment_list = "Splunk 4"
| chart sum(server_count) as "Splunk 6" by server_cluster
| addcoltotals

Thanks,
Erik

View solution in original post

0 Karma

eandresen
Path Finder

Thanks to ngatchasandra's help, I was able to come up with the following search string that works for me needs. It dropps the server farms that are reporting into both the Splunk 4 and Splunk 6 environments and only returns a list of the server farms that are report into the Splunk 6 environment.

index=_internal sourcetype=splunkd NOT host=chsxspl* component=TcpOutputProc log_level=INFO splunk_server_group=ewe earliest=-15m       
| rex field=idx "(?<ip_address>\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})"       
| eval splunk_environment=case(cidrmatch("xx.xx.xx.0/24",ip_address), "Splunk 4", cidrmatch("xx.xx.xx.0/24",ip_address), "Splunk 6")
| stats dc(host) as server_count by host, splunk_environment 
| rex field=host "^(?<server_cluster>\S+[^0-9])\d+" 
| eval server_cluster = upper(server_cluster) 
| stats sum(server_count) as server_count by server_cluster, splunk_environment
| stats values(splunk_environment) as splunk_environment_list by server_cluster,server_count
| where NOT splunk_environment_list = "Splunk 4"
| chart sum(server_count) as "Splunk 6" by server_cluster
| addcoltotals

Thanks,
Erik

0 Karma

ngatchasandra
Builder

Hi,

Try with this:

index=_internal sourcetype=splunkd NOT host=chsxspl* component=TcpOutputProc log_level=INFO splunk_server_group=ewe earliest=-1h

| rex field=idx "(?\d{1,3}.\d{1,3}.\d{1,3}.\d{1,3})"

| eval splunk_environment=case(cidrmatch("xx.xx.xx.0/24",ip_address), "Splunk 4", cidrmatch("xx.xx.xx.0/24",ip_address), "Splunk 6")
| stats dc(host) as server_count by host, splunk_environment
| rex field=host "^(?\S+[^0-9])\d+"
| eval server_cluster = upper(server_cluster)
| chart sum(server_count) by server_cluster, splunk_environment
| where sum(server_count)!=" " AND splunk_environment = " splunk6" AND splunk_environment!=splunk4
| rename server_cluster as "Server Clusters"
| addcoltotals

0 Karma

eandresen
Path Finder

Still no results were returned with the new where clause.

0 Karma

ngatchasandra
Builder

Have you written well clause where like follow:

| where sum(server_count) !=" "  AND   splunk_environment = " splunk 6"  AND splunk_environment !="splunk 4"
0 Karma

eandresen
Path Finder

Yep, here is the search string you provided and I used, which returned an error message of "Error in 'where' command: The 'sum' function is unsupported or undefined."

index=_internal sourcetype=splunkd NOT host=chsxspl* component=TcpOutputProc log_level=INFO splunk_server_group=ewe earliest=-15m       
| rex field=idx "(?<ip_address>\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})"       
| eval splunk_environment=case(cidrmatch("xx.xx.xx.0/24",ip_address), "Splunk 4", cidrmatch("xx.xx.xx.0/24",ip_address), "Splunk 6")
| stats dc(host) as server_count by host, splunk_environment 
| rex field=host "^(?<server_cluster>\S+[^0-9])\d+" 
| eval server_cluster = upper(server_cluster) 
| chart sum(server_count) by server_cluster, splunk_environment
| where sum(server_count) !=" "  AND   splunk_environment = " splunk 6"  AND splunk_environment !="splunk 4"
| rename server_cluster as "Server Clusters"
| addcoltotals
0 Karma

ngatchasandra
Builder

Ok!
i am working on it

0 Karma

eandresen
Path Finder

Thanks for the help.

0 Karma

ngatchasandra
Builder

Try now with this, i used untable command

index=_internal sourcetype=splunkd NOT host=chsxspl* component=TcpOutputProc log_level=INFO splunk_server_group=ewe earliest=-15m

| rex field=idx "(?\d{1,3}.\d{1,3}.\d{1,3}.\d{1,3})"

| eval splunk_environment=case(cidrmatch("xx.xx.xx.0/24",ip_address), "Splunk 4", cidrmatch("xx.xx.xx.0/24",ip_address), "Splunk 6")
| stats dc(host) as server_count by host, splunk_environment
| rex field=host "^(?\S+[^0-9])\d+"
| eval server_cluster = upper(server_cluster)
| chart sum(server_count) as servercount by server_cluster, splunk_environment
| untable server_cluster splunk_environment servercount
| where (servercount! =" ") AND splunk_environment = (" splunk 6") AND (server_cluster=" HOST3" ) AND (server_cluster="HOST4")
| chart servercount by server_cluster, splunk_environment

| rename server_cluster as "Server Clusters"
| addcoltotals

I test it with search that follow:

index=_internal | chart count by sourcetype, user |head 10 |untable  sourcetype user count | where (count!=0) AND (user="admin") |chart count by sourcetype, user
0 Karma

eandresen
Path Finder

That still did not return any results, but it pointed me down a new path that helped me to get a search string that worked. I will post it for others to look and use.

Thanks!

0 Karma

ngatchasandra
Builder

Please try to run firstly :

index=_internal | chart count by sourcetype, user |head 10 |untable sourcetype user count | where (count!=0) AND (user="admin") |chart count by sourcetype, user

This will help you !

0 Karma

eandresen
Path Finder

So, I added an alias onto the sum(server_count) field in the chart command and used that alias in the where clause. The updated search string is below and this one ran without error but still returned no results.

index=_internal sourcetype=splunkd NOT host=chsxspl* component=TcpOutputProc log_level=INFO splunk_server_group=ewe earliest=-15m       
| rex field=idx "(?<ip_address>\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})"       
 | eval splunk_environment=case(cidrmatch("xx.xx.xx.0/24",ip_address), "Splunk 4", cidrmatch("xx.xx.xx.0/24",ip_address), "Splunk 6")
| stats dc(host) as server_count by host, splunk_environment 
| rex field=host "^(?<server_cluster>\S+[^0-9])\d+" 
| eval server_cluster = upper(server_cluster) 
| chart sum(server_count) as servercount by server_cluster, splunk_environment
| where servercount !=" "  AND   splunk_environment = " splunk 6"  AND splunk_environment !="splunk 4"
| rename server_cluster as "Server Clusters"
| addcoltotals
0 Karma
Get Updates on the Splunk Community!

Stay Connected: Your Guide to November Tech Talks, Office Hours, and Webinars!

&#x1f342; Fall into November with a fresh lineup of Community Office Hours, Tech Talks, and Webinars we’ve ...

Transform your security operations with Splunk Enterprise Security

Hi Splunk Community, Splunk Platform has set a great foundation for your security operations. With the ...

Splunk Admins and App Developers | Earn a $35 gift card!

Splunk, in collaboration with ESG (Enterprise Strategy Group) by TechTarget, is excited to announce a ...