Dashboards & Visualizations

Why is my base search not working properly?

aditsss
Motivator

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.

 

Labels (3)
0 Karma

bowesmana
SplunkTrust
SplunkTrust

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.

 

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

aditsss
Motivator

@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 &gt; 15 days" latest(NumberOfActiveUsersNotLoggedInForMoreThan30Days) as "# Active Users NOT logged in &gt; 30 days" latest(NumberOfActiveUsersNotLoggedInForMoreThan60Days) as "# Active Users NOT logged in &gt; 60 days" latest(NumberOfActiveUsersNotLoggedInForMoreThan90Days) as "# Active Users NOT logged in &gt; 90 days" by "Salesforce Org Name"</query>

Can you guide me why its not working

Tags (1)
0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @aditsss,

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

ciao.

Giuseppe

aditsss
Motivator

@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 &gt; 15 days" latest(NumberOfActiveUsersNotLoggedInForMoreThan30Days) as "# Active Users NOT logged in &gt; 30 days" latest(NumberOfActiveUsersNotLoggedInForMoreThan60Days) as "# Active Users NOT logged in &gt; 60 days" latest(NumberOfActiveUsersNotLoggedInForMoreThan90Days) as "# Active Users NOT logged in &gt; 90 days" by "Salesforce Org Name"</query>
</search>

This is the original query for 1st panel:

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 &gt; 15 days" latest(NumberOfActiveUsersNotLoggedInForMoreThan30Days) as "# Active Users NOT logged in &gt; 30 days" latest(NumberOfActiveUsersNotLoggedInForMoreThan60Days) as "# Active Users NOT logged in &gt; 60 days" latest(NumberOfActiveUsersNotLoggedInForMoreThan90Days) as "# Active Users NOT logged in &gt; 90 days" by "Salesforce Org Name"

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

 

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi

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

0 Karma

bowesmana
SplunkTrust
SplunkTrust

@gcusello I think what he did was 

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

without the quotes as the first field name listed

gcusello
SplunkTrust
SplunkTrust

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 &gt; 15 days" latest(NumberOfActiveUsersNotLoggedInForMoreThan30Days) as "# Active Users NOT logged in &gt; 30 days" latest(NumberOfActiveUsersNotLoggedInForMoreThan60Days) as "# Active Users NOT logged in &gt; 60 days" latest(NumberOfActiveUsersNotLoggedInForMoreThan90Days) as "# Active Users NOT logged in &gt; 90 days" by Orgname
| rename OrgName AS "Salesforce Org Name"

 Ciao.

Giuseppe

aditsss
Motivator

@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

 

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

aditsss
Motivator

@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

0 Karma

bowesmana
SplunkTrust
SplunkTrust

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.

 

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

aditsss
Motivator

@gcusello @bowesmana 

Its showing No result found.

Attached is the screenshot.

 

0 Karma

bowesmana
SplunkTrust
SplunkTrust

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.

0 Karma

isoutamo
SplunkTrust
SplunkTrust

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. 

0 Karma

bowesmana
SplunkTrust
SplunkTrust

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.

 

0 Karma

richgalloway
SplunkTrust
SplunkTrust

"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.

---
If this reply helps you, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

Streamline Data Ingestion With Deployment Server Essentials

REGISTER NOW!Every day the list of sources Admins are responsible for gets bigger and bigger, often making the ...

Remediate Threats Faster and Simplify Investigations With Splunk Enterprise Security ...

REGISTER NOW!Join us for a Tech Talk around our latest release of Splunk Enterprise Security 7.2! We’ll walk ...

Introduction to Splunk AI

WATCH NOWHow are you using AI in Splunk? Whether you see AI as a threat or opportunity, AI is here to stay. ...