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.
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.
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
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
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:
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"
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
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
@gcusello I think what he did was
| rename...
| fields Salesforce Org Name ...
without the quotes as the first field name listed
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
@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
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
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
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.
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
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.
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.
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
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.
"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.