topic Why am I getting where clause? in Splunk Search

Why am I getting where clause?

hantun — Tue, 25 Apr 2023 01:39:17 GMT

I have a search where I am comparing two indexes for a matching cell and I am trying to filter where Business = 1X... here's the SPL:

index=csmp OR index=aws-business-map
| eval BindleNew = case(sourcetype="sim_csmp", AWSAccountName, sourcetype="csv", BindleName)
| stats values(IssueUrl), values(AWSAccountName) as AWSAccountName, values(BindleName), values(Business) by BindleNew
| search AWSAccountName!=""

I am unsure where to put Business-="1X" clause.

Also, if we have more indexes like csmp that I am trying to compare to aws-business-map, how do we go about matching 4 indexes to aws-business-map?

Re: Getting where clause

richgalloway — Mon, 24 Apr 2023 21:48:38 GMT

This should answer the question

index=csmp OR index=aws-business-map | eval BindleNew = case(sourcetype="sim_csmp", AWSAccountName, sourcetype="csv", BindleName) | stats values(IssueUrl), values(AWSAccountName) as AWSAccountName, values(BindleName), values(Business) as Business by BindleNew | search AWSAccountName!="" Business-="1X"

To add more indexes

index=csmp OR index=aws-business-map OR index=index3 OR index=index4 | eval BindleNew = case(sourcetype="sim_csmp", AWSAccountName, sourcetype="csv", BindleName, sourcetype="st3", foo, sourcetype="st4", bar) | stats values(IssueUrl), values(AWSAccountName) as AWSAccountName, values(BindleName), values(Business) as Business by BindleNew | search AWSAccountName!="" Business-="1X"

I believe it will not solve the problem, however. That's because the values function returns a multi-value field that doesn't work well with many other commands. Multi-value fields need to be processed with mv* functions (mvindex, mvfind, etc).

Re: Getting where clause

hantun — Tue, 25 Apr 2023 18:23:47 GMT

index=csmp OR index=aws-business-map OR index=sim OR index=guardduty
| eval BindleNew = case(sourcetype="sim_csmp", AWSAccountName, sourcetype="csv", BindleName, sourcetype="sim_prod", WAWT2-BindleName, sourcetype="sim_prod", CloudTrail-AWSAccountName, sourcetype="sim_gd", AWSAccountId)
| stats values(IssueUrl), values(AWSAccountName) as AWSAccountName, values(BindleName), values(WAWT2-BindleName), values(CloudTrail-AWSAccountName), values(AWSAccountId), values(Business) as Business by BindleNew
| search AWSAccountName!="" Business="XP"

This is what I have for combination so far. Something to note is that CloudTrail-AWSAccountName, WAWT2-BindleName, BindleName, AWSAccountName are all the same data. Can we normalize them using Match?

Re: Getting where clause

richgalloway — Tue, 25 Apr 2023 20:07:55 GMT

If those fields are all the same then there's no need for a case function. Use the coalesce function to choose the first field that is present in the current event.

index=csmp OR index=aws-business-map OR index=sim OR index=guardduty | eval BindleNew = lower(coalesce(AWSAccountName, BindleName, WAWT2-BindleName, CloudTrail-AWSAccountName, AWSAccountId)) | stats values(IssueUrl), values(AWSAccountName) as AWSAccountName, values(BindleName), values(WAWT2-BindleName), values(CloudTrail-AWSAccountName), values(AWSAccountId), values(Business) as Business by BindleNew | search AWSAccountName!="" Business="XP"

Re: Why am I getting where clause?

hantun — Tue, 25 Apr 2023 23:48:20 GMT

Can we put that into a static function of a dropdown? If so, how should we go about it?

I am building dashboard where we are counting the business

Re: Why am I getting where clause?

richgalloway — Thu, 27 Apr 2023 12:43:28 GMT

Static values are pre-defined and cannot use search results. It's the dynamic dropdowns that use searches.

Re: Getting where clause

hantun — Thu, 27 Apr 2023 19:19:06 GMT

How can we put that SPL query into a dynamic dropdown?

My project is looking for if the query spits out 1X, splunk gets all the events with the Business as 1X. If query is looking for 2X, gets all the events with Business as 2X. Then, I have dashboard panels that are doing counts etc depending on the dropdown.

Re: Getting where clause

richgalloway — Thu, 27 Apr 2023 21:12:51 GMT

Edit the dashboard and click on the edit icon for the dropdown. Put the query into the search box in the Dynamic Options section.

richgalloway_0-1682629922266.png