How to change the queries to base search?

[aditsss]

Hi Everyone,

I have created multiple Dashboards  with the multiple searches.

Now this is impacting splunk performance. I want to use base search for my dashboards now.

Not sure how to use base search. 

Below are my queries  for one of my dashboard:

<query>index="abc" sourcetype="O2-Salesforce-SalesforceUserLicense" | lookup local=t Org_Alias.csv OrgFolderName OUTPUT OrgName as OrgName | stats count by OrgName</query>

<query>index="abc" sourcetype="O2-Salesforce-SalesforceUserLicense" |stats count by LicenseName</query>

<query>
index="abc" sourcetype="O2-Salesforce-SalesforceUserLicense" $type$ TotalLicenses!=0 | lookup local=t Org_Alias.csv OrgFolderName OUTPUT OrgName as OrgName | search $OrgName$ |dedup OrgFolderName, LicenseName, SalesforceOrgId |chart sum(TotalLicenses) as "Total Licenses" sum(UnusedLicenses) as "Unused Licenses" sum(UsedLicenses) as "Used Licenses" by LicenseName

<query>index="abc" sourcetype="O2-Salesforce-SalesforceUserLicense" $type$ | lookup local=t Org_Alias.csv OrgFolderName OUTPUT OrgName as OrgName | search $OrgName$ |dedup OrgFolderName, LicenseName, SalesforceOrgId |stats sum(TotalLicenses) as "Total-Licenses" sum(UsedLicenses) as "Used Licenses" sum(UnusedLicenses) as "Unused Licenses" by LicenseName OrgName SalesforceOrgId | sort -Total-Licenses</query>

<query>index="abc" sourcetype="O2-Salesforce-SalesforceUserLicense" $type$ | lookup local=t Org_Alias.csv OrgFolderName OUTPUT OrgName as OrgName | search $OrgName$ |dedup OrgFolderName, LicenseName, SalesforceOrgId |stats latest(TotalLicenses) as "Total-Licenses" latest(UsedLicenses) as "Used Licenses" latest(UnusedLicenses) as "Unused Licenses" by LicenseName OrgName SalesforceOrgId | sort -Total-Licenses |sort OrgName</query>

<query>index="abc" sourcetype="O2-Salesforce-SalesforceUserLicense" $type$ | lookup local=t Org_Alias.csv OrgFolderName OUTPUT OrgName as OrgName | search $OrgName$ |dedup OrgFolderName, LicenseName, SalesforceOrgId |stats latest(TotalLicenses) as "Total-Licenses" latest(UsedLicenses) as "Used Licenses" latest(UnusedLicenses) as "Unused Licenses" by LicenseName OrgName SalesforceOrgId | sort -Total-Licenses |sort OrgName</query>

I have read multiple base search documents but not working for my dashboards.

Can someone guide me on this.

How I can apply base search for my queries

[gcusello]

Hi @aditsss,

you have to group all the searches with similar main search and possibly using a streming command to filter in each panel.

Anyway, if you don't use a streaming command in the base-search (as in your cases), remember to put, at the end of your search the list of the fields to use with the fields command.

You can find some useful examples of using base-searches in the Splunk Dashboard Examples app (https://splunkbase.splunk.com/app/1603/).

In your case you can group the first two searches in:

Base search
index="abc" sourcetype="O2-Salesforce-SalesforceUserLicense" 
| lookup local=t Org_Alias.csv OrgFolderName OUTPUT OrgName as OrgName 
| fields OrgName LicenseName

Panel 1
| stats count by OrgName
Panel 2
|stats count by LicenseName

For the others, you could try:

Base search
index="abc" sourcetype="O2-Salesforce-SalesforceUserLicense" $type$ 
| lookup local=t Org_Alias.csv OrgFolderName OUTPUT OrgName as OrgName 
| search $OrgName$ 
| dedup OrgFolderName, LicenseName, SalesforceOrgId 
| fields TotalLicenses UnusedLicenses UsedLicenses LicenseName OrgName SalesforceOrgId 

Panel 1
| search TotalLicenses!=0 
| chart sum(TotalLicenses) as "Total Licenses" sum(UnusedLicenses) as "Unused Licenses" sum(UsedLicenses) as "Used Licenses" by LicenseName

Panel 2
| stats sum(TotalLicenses) as "Total-Licenses" sum(UsedLicenses) as "Used Licenses" sum(UnusedLicenses) as "Unused Licenses" by LicenseName OrgName SalesforceOrgId 
| sort -Total-Licenses

Panel 3
| stats latest(TotalLicenses) as "Total-Licenses" latest(UsedLicenses) as "Used Licenses" latest(UnusedLicenses) as "Unused Licenses" by LicenseName OrgName SalesforceOrgId 
| sort -Total-Licenses 
| sort OrgName

Panel 4
| stats latest(TotalLicenses) as "Total-Licenses" latest(UsedLicenses) as "Used Licenses" latest(UnusedLicenses) as "Unused Licenses" by LicenseName OrgName SalesforceOrgId 
| sort -Total-Licenses 
| sort OrgName

One additional info: if you use two sort command, the first one isn't useful!

Ciao .

Giuseppe

[aditsss]

@gcusello 

Thanks for the wonderful suggestion

Below are my dashboards queries with sequence

Query1:

<query>index="abc" sourcetype="O2-Salesforce-SalesforceUserLicense" $type$ TotalLicenses!=0 | lookup local=t Org_Alias.csv OrgFolderName OUTPUT OrgName as OrgName | search $OrgName$ |dedup OrgFolderName, LicenseName, SalesforceOrgId |chart sum(TotalLicenses) as "Total Licenses" sum(UnusedLicenses) as "Unused Licenses" sum(UsedLicenses) as "Used Licenses" by LicenseName</query>

Query2:

<query>index="abc" sourcetype="O2-Salesforce-SalesforceUserLicense" $type$ | lookup local=t Org_Alias.csv OrgFolderName OUTPUT OrgName as OrgName | search $OrgName$ |dedup OrgFolderName, LicenseName, SalesforceOrgId |stats sum(TotalLicenses) as "Total-Licenses" sum(UsedLicenses) as "Used Licenses" sum(UnusedLicenses) as "Unused Licenses" by LicenseName OrgName SalesforceOrgId | sort -Total-Licenses</query>

Query3:

<query>index="abc" sourcetype="O2-Salesforce-SalesforceUserLicense" $type$ | lookup local=t Org_Alias.csv OrgFolderName OUTPUT OrgName as OrgName | search $OrgName$ |dedup OrgFolderName, LicenseName, SalesforceOrgId |stats latest(TotalLicenses) as "Total-Licenses" latest(UsedLicenses) as "Used Licenses" latest(UnusedLicenses) as "Unused Licenses" by LicenseName OrgName SalesforceOrgId | sort -Total-Licenses |sort OrgName</query>

@gcusello can you guide me how to make search for these 3 and how to use base search in actual queries

[gcusello · Accepted solution]

Hi @aditsss,

as i said, you have to find the common parties of your searches and extrapolate them, leaving the not common parties in each panel's search.

In my previous answer, you can find the answer to your question, anyway:

Base search
index="abc" sourcetype="O2-Salesforce-SalesforceUserLicense" $type$ 
| lookup local=t Org_Alias.csv OrgFolderName OUTPUT OrgName as OrgName 
| search $OrgName$ 
| dedup OrgFolderName, LicenseName, SalesforceOrgId 
| fields TotalLicenses UnusedLicenses UsedLicenses LicenseName OrgName SalesforceOrgId 

Panel 1
| search TotalLicenses!=0 
| chart sum(TotalLicenses) as "Total Licenses" sum(UnusedLicenses) as "Unused Licenses" sum(UsedLicenses) as "Used Licenses" by LicenseName

Panel 2
| stats sum(TotalLicenses) as "Total-Licenses" sum(UsedLicenses) as "Used Licenses" sum(UnusedLicenses) as "Unused Licenses" by LicenseName OrgName SalesforceOrgId 
| sort -Total-Licenses

Panel 3
| stats latest(TotalLicenses) as "Total-Licenses" latest(UsedLicenses) as "Used Licenses" latest(UnusedLicenses) as "Unused Licenses" by LicenseName OrgName SalesforceOrgId 
| sort -Total-Licenses 
| sort OrgName

Panel 4
| stats latest(TotalLicenses) as "Total-Licenses" latest(UsedLicenses) as "Used Licenses" latest(UnusedLicenses) as "Unused Licenses" by LicenseName OrgName SalesforceOrgId 
| sort -Total-Licenses 
| sort OrgName

Ciao.

Giuseppe