topic Re: Base search not working properly in Dashboards & Visualizations

Why is my base search not working properly?

aditsss — Mon, 03 Oct 2022 08:41:09 GMT

Hi Team,

I have below queries in my dashboard

Panel1:

index="abc" sourcetype="abc" $reg$ |lookup local=t Org_Alias.csv OrgFolderName OUTPUT OrgName| search OrgName=$OrgName$ | rename OrgName as "Salesforce Org Name" | chart latest(NumberOfActiveUsersNotLoggedInForMoreThan15Days) as "# Active Users NOT logged in > 15 days" latest(NumberOfActiveUsersNotLoggedInForMoreThan30Days) as "# Active Users NOT logged in > 30 days" latest(NumberOfActiveUsersNotLoggedInForMoreThan60Days) as "# Active Users NOT logged in > 60 days" latest(NumberOfActiveUsersNotLoggedInForMoreThan90Days) as "# Active Users NOT logged in > 90 days" by "Salesforce Org Name"

Panel2:

index="abc" sourcetype="abc" $reg$ |lookup local=t Org_Alias.csv OrgFolderName OUTPUT OrgName| search OrgName=$OrgName$ | chart latest(NumberOfActiveUsers) as "Number Of ActiveUsers" latest(SalesforceOrgId) as "Salesforce Org Id" latest(NumberOfActiveUsersNotLoggedInForMoreThan15Days) as "Number Of ActiveUsers Not Logged In For MoreThan 15Days" latest(NumberOfActiveUsersNotLoggedInForMoreThan30Days) as "Number Of ActiveUsers Not Logged In For MoreThan 30Days" latest(NumberOfActiveUsersNotLoggedInForMoreThan60Days) as "Number Of ActiveUsers Not Logged In For MoreThan 60Days" latest(NumberOfActiveUsersNotLoggedInForMoreThan90Days) as "Number Of ActiveUsers Not Logged In For MoreThan 90Days" by OrgName

panel3:

index="abc" sourcetype="abc" InactiveForMoreThan90Days !="No" $reg$ $type$ |lookup local=t Org_Alias.csv OrgFolderName OUTPUT OrgName| search OrgName=$OrgName$ | dedup _raw |stats count(InactiveForMoreThan90Days) as "Total Inactive Users" by OrgName

panel4

index="abc" sourcetype="abc" InactiveForMoreThan90Days !="No" $reg$ $type$ |lookup local=t Org_Alias.csv OrgFolderName OUTPUT OrgName|search OrgName=$selected_value4$ | dedup _raw | stats values(OrgName) as "Org" by Name Email UserId UserName LicenseName LastLoginDateTime

I have made my base search as this:

index="abc" sourcetype="abc" $reg$ |lookup local=t Org_Alias.csv OrgFolderName OUTPUT OrgName| search OrgName=$OrgName$|rename OrgName as "Salesforce Org Name"

But its not working can someone guide me here.

Re: Base search not working properly

richgalloway — Sun, 02 Oct 2022 21:24:22 GMT

"it's not working" isn't a problem description. Tell us what results you get and how that doesn't meet your expectations. Also, don't just share the queries since base searches and post-processing depend on more than just the <query> elements. Please share snippets of the dashboard code.

Re: Base search not working properly

bowesmana — Mon, 03 Oct 2022 01:47:55 GMT

See the documentation on base searches

https://docs.splunk.com/Documentation/Splunk/9.0.1/Viz/Savedsearches#Post-process_searches_2

You have not used a transforming base search, therefore the documentation explicitly states that you need to use the fields command to specify the fields you need for any post process searches.

Re: Base search not working properly

gcusello — Mon, 03 Oct 2022 06:59:21 GMT

Hi @aditsss,

as @bowesmana and @richgalloway said, see the documentation and the Splunk Dashboard Examples App (https://splunkbase.splunk.com/app/1603) where there are some useful examples also about Post process Search.

anyway, your problem probably is related to the fact that, when you don't use a streaming command (as stats or timechart, etc...) you have to define in the basesearch the field that you want to use in the panels, in other words, add at the end of the base search a row with:

| fields ename OrgName NumberOfActiveUsersNotLoggedInForMoreThan15Days NumberOfActiveUsersNotLoggedInForMoreThan30Days NumberOfActiveUsersNotLoggedInForMoreThan60Days NumberOfActiveUsersNotLoggedInForMoreThan90Days NumberOfActiveUsers SalesforceOrgId InactiveForMoreThan90Days Name Email UserId UserName LicenseName LastLoginDateTime

then is some of your panels, in which you are using _raw for dedupping, maybe it could be betetr to use two basesearches. one for the panels with dedup_raw (panels 3 and 4) and one for the without ones (Panels 1 and 2).

Ciao.

Giuseppe

Re: Base search not working properly

aditsss — Mon, 03 Oct 2022 17:43:04 GMT

@gcusello

For my 1st panel the query is below:

index="abc" sourcetype="xyz" $reg$ |lookup local=t Org_Alias.csv OrgFolderName OUTPUT OrgName| search OrgName=$OrgName$ | rename OrgName as "Salesforce Org Name" | chart latest(NumberOfActiveUsersNotLoggedInForMoreThan15Days) as "# Active Users NOT logged in > 15 days" latest(NumberOfActiveUsersNotLoggedInForMoreThan30Days) as "# Active Users NOT logged in > 30 days" latest(NumberOfActiveUsersNotLoggedInForMoreThan60Days) as "# Active Users NOT logged in > 60 days" latest(NumberOfActiveUsersNotLoggedInForMoreThan90Days) as "# Active Users NOT logged in > 90 days" by "Salesforce Org Name"

And I made my base search as below:

index="abc" sourcetype="xyy" $reg$ |lookup local=t Org_Alias.csv OrgFolderName OUTPUT OrgName| search OrgName=$OrgName$| fields ename OrgName NumberOfActiveUsersNotLoggedInForMoreThan15Days NumberOfActiveUsersNotLoggedInForMoreThan30Days NumberOfActiveUsersNotLoggedInForMoreThan60Days NumberOfActiveUsersNotLoggedInForMoreThan90Days NumberOfActiveUsers SalesforceOrgId InactiveForMoreThan90Days Name Email UserId UserName LicenseName LastLoginDateTime

And I am using my base search like this in my 1st panel but not working:

<query> | chart latest(NumberOfActiveUsersNotLoggedInForMoreThan15Days) as "# Active Users NOT logged in > 15 days" latest(NumberOfActiveUsersNotLoggedInForMoreThan30Days) as "# Active Users NOT logged in > 30 days" latest(NumberOfActiveUsersNotLoggedInForMoreThan60Days) as "# Active Users NOT logged in > 60 days" latest(NumberOfActiveUsersNotLoggedInForMoreThan90Days) as "# Active Users NOT logged in > 90 days" by "Salesforce Org Name"</query>

Can you guide me why its not working

Re: Base search not working properly

bowesmana — Mon, 03 Oct 2022 23:24:27 GMT

As @richgalloway says, if you say "it is not working", it is very hard to provide a solution without knowing your data, what the results look like and what 'not working' means to you. For example, "not working" could mean

splunk reports an error
there are no results shown
the result you see are not what you expect
splunk reports that it is waiting for input
something else

Please describe or screenshot your sanitized output, along with what you see as 'wrong' and if it is related to data, then please describe your data as well.

Re: Base search not working properly

aditsss — Tue, 04 Oct 2022 04:53:10 GMT

@gcusello @bowesmana

Its showing No result found.

Attached is the screenshot.

Re: Base search not working properly

bowesmana — Tue, 04 Oct 2022 05:55:34 GMT

Click the 'Open in search' magnifying glass icon to open the search in a new tab and then you can diagnose why - the way to diagnose the search is to build up your components of the search piece by piece and you will then discover why it breaks, e.g. if your search is

piece_of_search_1 piece_of_search_2 piece_of_search_3

and you get no results, then in that new window you have, remove your equivalent to piece_of_search_3 and see if that gives you correct data.

At some point you will be able to understand which SPL command is not giving you what you expect - when you have worked that out, you can post here your results and we can advise.

Re: Base search not working properly

isoutamo — Tue, 04 Oct 2022 09:02:41 GMT

Easy way to exclude part of you search is just comment it out with three ` like ```spl1 | spl2```

Then just move start comment e.g. line by line towards end to find where it fails.

And as you are using this as a base search then you also need to check how many results it get in base search part (as there are upper limit 500k lines. Also check how long it takes as there is also 60s limit.

You should also remember that this search is not working 1:1 with dashboard search when you are running it in separate session. But as @bowesmana said, this is the best way to find where the issue is.

Re: Base search not working properly

gcusello — Tue, 04 Oct 2022 16:14:11 GMT

Hi @aditsss,

the "Salesforce Org Name" field isn't in the fields list of the basesearch

ciao.

Giuseppe

Re: Base search not working properly

aditsss — Tue, 04 Oct 2022 17:50:06 GMT

@gcusello

This is my base search:

index="abc" sourcetype="xyz" $reg$ |lookup local=t Org_Alias.csv OrgFolderName OUTPUT OrgName| search OrgName=$OrgName$| rename OrgName as "Salesforce Org Name"|fields Salesforce Org Name NumberOfActiveUsersNotLoggedInForMoreThan15Days NumberOfActiveUsersNotLoggedInForMoreThan30Days NumberOfActiveUsersNotLoggedInForMoreThan60Days NumberOfActiveUsersNotLoggedInForMoreThan90Days NumberOfActiveUsers SalesforceOrgId InactiveForMoreThan90Days Name Email UserId UserName LicenseName LastLoginDateTime

This is my query after base search for 1st panel:

<search base = "basesearch">
<query> | chart latest(NumberOfActiveUsersNotLoggedInForMoreThan15Days) as "# Active Users NOT logged in > 15 days" latest(NumberOfActiveUsersNotLoggedInForMoreThan30Days) as "# Active Users NOT logged in > 30 days" latest(NumberOfActiveUsersNotLoggedInForMoreThan60Days) as "# Active Users NOT logged in > 60 days" latest(NumberOfActiveUsersNotLoggedInForMoreThan90Days) as "# Active Users NOT logged in > 90 days" by "Salesforce Org Name"</query>
</search>

This is the original query for 1st panel:

Can you guide me where I am wrong as still its showing "NO RESULT FOUND".

I try to debug but its not showing anything

@gcusello please guide

Re: Base search not working properly

gcusello — Wed, 05 Oct 2022 07:00:53 GMT

as I said, the "Salesforce Org Name" field isn't in the fields list, so please try this as basesearch:

index="abc" sourcetype="xyz" $reg$ | lookup local=t Org_Alias.csv OrgFolderName OUTPUT OrgName | search OrgName=$OrgName$ | rename OrgName as "Salesforce Org Name" | fields Salesforce Org Name NumberOfActiveUsersNotLoggedInForMoreThan15Days NumberOfActiveUsersNotLoggedInForMoreThan30Days NumberOfActiveUsersNotLoggedInForMoreThan60Days NumberOfActiveUsersNotLoggedInForMoreThan90Days NumberOfActiveUsers SalesforceOrgId InactiveForMoreThan90Days Email UserId UserName LicenseName LastLoginDateTime "Salesforce Org Name"

Then name field is duplicated, but it isn't relevant.

Then try to avoid field names with spaces, eventually you can rename them as last row, but avoid them during the search building.

Ciao.

Giuseppe

Re: Base search not working properly

bowesmana — Wed, 05 Oct 2022 07:03:19 GMT

@gcusello I think what he did was

| rename... | fields Salesforce Org Name ...

without the quotes as the first field name listed

Re: Base search not working properly

gcusello — Wed, 05 Oct 2022 08:19:53 GMT

Hi @aditsss,

yes as correctly @bowesmana said, you should move the rename (| rename OrgName as "Salesforce Org Name" in the panel after the stats command and use the Orgname field in the stats command and in the basesearch.

So the basesearch should be:

index="abc" sourcetype="xyz" $reg$ | lookup local=t Org_Alias.csv OrgFolderName OUTPUT OrgName | search OrgName=$OrgName$ | fields Salesforce Org Name NumberOfActiveUsersNotLoggedInForMoreThan15Days NumberOfActiveUsersNotLoggedInForMoreThan30Days NumberOfActiveUsersNotLoggedInForMoreThan60Days NumberOfActiveUsersNotLoggedInForMoreThan90Days NumberOfActiveUsers SalesforceOrgId InactiveForMoreThan90Days Email UserId UserName LicenseName LastLoginDateTime OrgName

and the panel's search should be:

| chart latest(NumberOfActiveUsersNotLoggedInForMoreThan15Days) as "# Active Users NOT logged in > 15 days" latest(NumberOfActiveUsersNotLoggedInForMoreThan30Days) as "# Active Users NOT logged in > 30 days" latest(NumberOfActiveUsersNotLoggedInForMoreThan60Days) as "# Active Users NOT logged in > 60 days" latest(NumberOfActiveUsersNotLoggedInForMoreThan90Days) as "# Active Users NOT logged in > 90 days" by Orgname | rename OrgName AS "Salesforce Org Name"

Ciao.

Giuseppe

Re: Base search not working properly

aditsss — Mon, 10 Oct 2022 20:58:10 GMT

@gcusello Thank you for this fantastic solution.

But the panels which have dedup is not working

My base serach:

index="abc" sourcetype="xyz" $reg$ $type$ |lookup local=t Org_Alias.csv OrgFolderName OUTPUT OrgName| search OrgName=$OrgName$ | fields Salesforce Org Name
NumberOfActiveUsersNotLoggedInForMoreThan15Days
NumberOfActiveUsersNotLoggedInForMoreThan30Days
NumberOfActiveUsersNotLoggedInForMoreThan60Days
NumberOfActiveUsersNotLoggedInForMoreThan90Days
NumberOfActiveUsers SalesforceOrgId InactiveForMoreThan90Days
SalesforceOrgId
Email
UserId
UserName
LicenseName
LastLoginDateTime
OrgName

My panel search:

| dedup _raw |stats count(InactiveForMoreThan90Days) as "Total Inactive Users" by OrgName

Actual panel query without base search:

index="abc" sourcetype="xyz" InactiveForMoreThan90Days !="No" $reg$ $type$ |lookup local=t Org_Alias.csv OrgFolderName OUTPUT OrgName| search OrgName=$OrgName$ | dedup _raw |stats count(InactiveForMoreThan90Days) as "Total Inactive Users" by OrgName

Re: Base search not working properly

gcusello — Tue, 11 Oct 2022 07:16:06 GMT

Hi @aditsss,

as I said, you have to put in the fields list at the end of the base search all the fields to use in the panel's search.

In your case, you dedup for _raw that isn't a field in the base search, so you don't have any result.

You could try to add _raw to the fields list or (BETTER) put the "| dedup _raw" in the base search.

Ciao.

Giuseppe

Re: Base search not working properly

aditsss — Tue, 11 Oct 2022 20:33:32 GMT

@gcusello

I used like this but isn't working

Base search:

index="abc" sourcetype="xyz" $reg$ $type$ |lookup local=t Org_Alias.csv OrgFolderName OUTPUT OrgName| search OrgName=$OrgName$ |fields Salesforce Org Name
NumberOfActiveUsersNotLoggedInForMoreThan15Days
NumberOfActiveUsersNotLoggedInForMoreThan30Days
NumberOfActiveUsersNotLoggedInForMoreThan60Days
NumberOfActiveUsersNotLoggedInForMoreThan90Days
NumberOfActiveUsers SalesforceOrgId InactiveForMoreThan90Days
SalesforceOrgId
Email
UserId
UserName
LicenseName
LastLoginDateTime
OrgName| dedup _raw

panel search:

|stats count(InactiveForMoreThan90Days) as "Total Inactive Users" by OrgName

Re: Base search not working properly

gcusello — Wed, 12 Oct 2022 12:09:06 GMT

Hi @aditsss ì,

use fields as last command

index="abc" sourcetype="xyz" $reg$ $type$ | lookup local=t Org_Alias.csv OrgFolderName OUTPUT OrgName | search OrgName=$OrgName$ | dedup _raw | fields Salesforce Org Name NumberOfActiveUsersNotLoggedInForMoreThan15Days NumberOfActiveUsersNotLoggedInForMoreThan30Days NumberOfActiveUsersNotLoggedInForMoreThan60Days NumberOfActiveUsersNotLoggedInForMoreThan90Days NumberOfActiveUsers SalesforceOrgId InactiveForMoreThan90Days SalesforceOrgId Email UserId UserName LicenseName LastLoginDateTime OrgName

Then check if, running the base search, you still have the fields: InactiveForMoreThan90Days and OrgName.

Ciao.

Giuseppe

Re: Base search not working properly

bowesmana — Wed, 12 Oct 2022 21:27:42 GMT

One thing that has been pointed out before - the statement in the base search

| fields Salesforce Org Name

should I believe be

| fields "Salesforce Org Name"

as you later refer to this as a field with quotes - if you do not use quotes in the first statement, then you are asking for 3 fields Salesforce, Org and Name.