Re: How to optimize my base search for syslog

vrmandadi · ‎10-06-2022

Hello Splunkers ,

I have the below source code and using the base search as index=syslog process!=switchd but its taking a while to load...is there a better way to write the base search to optimize the searches and make the dashboards load faster

<form theme="dark">
  <label>basesearch</label>
  <search id="base">
    <query>index=syslog process!=switchd |</query>

  <earliest>-30m@m</earliest>
        <latest>now</latest>
          </search>
  <fieldset submitButton="false">
    <input type="multiselect" token="multi_process" searchWhenChanged="true">
      <label>Process</label>
      <choice value="*">All</choice>
      <default>*</default>
      <initialValue>*</initialValue>
      <fieldForLabel>process</fieldForLabel>
      <fieldForValue>process</fieldForValue>
      <search base ="base">
        <query>search error OR ERROR OR fail OR failed OR errors OR faulted OR "*NVRM: Xid (PCI*" NOT NOTIFICATION process!=switchd host=$host_preos$
| search $multi_process$
| rex field=_raw "\d{4}\-\d{2}\-\d*\w\d*\:\d*\:\d*\.\d*(\+|\-)\d*\:\d*\s*(\S*)\s*(\S*\:|\S*)\s*(?&lt;Message&gt;(.*))"  | search Message!="*failed=0*" Message!="*level=info*" Message=*$search_text$*
| stats count by host process 
| dedup process</query>
        
      </search>
      <valuePrefix>process="</valuePrefix>
      <valueSuffix>"</valueSuffix>
      <delimiter> OR </delimiter>
    </input>
    <input type="text" token="search_text" searchWhenChanged="true">
      <label>Search Text</label>
      <default>*</default>
    </input>
    <input type="multiselect" token="host_preos">
      <label>Preos Hosts</label>
      <fieldForLabel>host</fieldForLabel>
      <fieldForValue>host</fieldForValue>
      <search base ="base">
        <query>search error OR ERROR OR fail OR FAIL OR failed OR Failed OR  errors process!=switchd process=*  host="preos*"
| rex field=_raw "\d{4}\-\d{2}\-\d*\w\d*\:\d*\:\d*\.\d*(\+|\-)\d*\:\d*\s*(\S*)\s*(\S*\:|\S*)\s*(?&lt;Message&gt;(.*))"  | search Message!="*failed=0*" Message!="*level=info*"
| stats count by  host  
| dedup host</query>
       
      </search>
      <choice value="preos*">All</choice>
      <default>preos*</default>
    </input>
  </fieldset>
  <row>
    <panel>
      <title>Error Message Counts - For Host ($host_preos$)</title>
      <chart>
        <search base ="base">
          <query>search "*NVRM: Xid (PCI*62*" OR error OR ERROR OR fail OR failed OR errors OR faulted OR "NVRM: Xid" NOT NOTIFICATION process!=switchd host IN($host_preos$)
| search $multi_process$ 
| rex field=_raw "\d{4}\-\d{2}\-\d*\w\d*\:\d*\:\d*\.\d*(\+|\-)\d*\:\d*\s*(\S*)\s*(\S*\:|\S*)\s*(?&lt;Message&gt;(.*))" 
| search Message!="*failed=0*" Message!="*level=info*" Message=*$search_text$* 
| timechart span=1h count(_time) by host limit=0</query>
          
        </search>
        <option name="charting.chart">line</option>
        <option name="charting.drilldown">none</option>
        <option name="charting.legend.labelStyle.overflowMode">ellipsisMiddle</option>
        <option name="charting.legend.placement">right</option>
        <option name="refresh.display">progressbar</option>
      </chart>
    </panel>
  </row>
  <row>
    <panel>
      <title>Overall Error Message Count - For Host ($host_preos$)</title>
      <table>
        <search base ="base">
          <query>search "*NVRM: Xid (PCI*62*" OR error OR ERROR OR fail OR failed OR errors OR faulted OR "*NVRM: Xid (PCI*" NOT NOTIFICATION process!=switchd host IN($host_preos$) 
| search $multi_process$ 
| rex field=_raw "\d{4}\-\d{2}\-\d*\w\d*\:\d*\:\d*\.\d*(\+|\-)\d*\:\d*\s*(\S*)\s*(\S*\:|\S*)\s*(?&lt;Message&gt;(.*))" 
| search Message!="*failed=0*" Message!="*level=info*" Message=*$search_text$* 
| stats count by host 
| addcoltotals labelfield=host 
| sort -count</query>
          
        </search>
        <option name="drilldown">none</option>
        <option name="refresh.display">progressbar</option>
        <format type="color" field="host">
          <colorPalette type="sharedList"></colorPalette>
          <scale type="sharedCategory"></scale>
        </format>
      </table>
    </panel>
    <panel>
      <title>Error Message Count per Process - For Host ($host_preos$)</title>
      <table>
       <search base ="base">
          <query>search "*NVRM: Xid (PCI*62*" OR  error OR ERROR OR fail OR failed OR errors OR faulted OR "*NVRM: Xid (PCI*" NOT NOTIFICATION process!=switchd host IN($host_preos$) 
| search $multi_process$ 
| rex field=_raw "\d{4}\-\d{2}\-\d*\w\d*\:\d*\:\d*\.\d*(\+|\-)\d*\:\d*\s*(\S*)\s*(\S*\:|\S*)\s*(?&lt;Message&gt;(.*))" 
| search Message!="*failed=0*" Message!="*level=info*" Message=*$search_text$* 
| stats count by host process 
| addcoltotals labelfield=host 
| sort -count</query>
          
        </search>
        <option name="drilldown">none</option>
        <option name="refresh.display">progressbar</option>
        <format type="color" field="host">
          <colorPalette type="sharedList"></colorPalette>
          <scale type="sharedCategory"></scale>
        </format>
      </table>
    </panel>
  </row>
  <row>
    <panel>
      <title>NVRM Xid Error Summary</title>
      <table>
        <title>NVRM Xid Error</title>
        <search base ="base">
          <query>searcg  "*NVRM: Xid (PCI*"  process!=switchd host IN($host_preos$)
| rex field=_raw "NVRM\:\sXid\s\(PCI\:(?&lt;PCI_Address&gt;[^ \)]+)\)\:\s(?&lt;Error_Code&gt;[^ ]+)\,\s*pid\=(?&lt;pid&gt;[^ ]+)\,\s*name\=(?&lt;name&gt;[^ ]+)\,\s(?&lt;Error_Message&gt;(.*))" 
| stats count by host Error_Code Error_Message PCI_Address pid name
| addcoltotals count labelfield=host
| sort -count 
| fields host Error_Code Error_Message count PCI_Address pid name</query>
          
        </search>
        <option name="count">5</option>
        <option name="drilldown">none</option>
        <option name="refresh.display">progressbar</option>
        <format type="color" field="host">
          <colorPalette type="sharedList"></colorPalette>
          <scale type="sharedCategory"></scale>
        </format>
      </table>
    </panel>
  </row>
  <row>
    <panel>
      <title>RmInit Error on Boot Summary</title>
      <table>
        <search>
          <query>index=syslog **RmInit error ==* process!=switchd host IN($host_preos$) 
| search process="nhc-boot.sh"
| rex field=_raw "\[\d*\]\:\s*\[(?&lt;Log_Level&gt;[^\] ]+)" 
| rex field=_raw "(prolog\:|kernel\:|\[\d*\]\:)(?&lt;Message&gt; *(.+))" 
| rex field=_raw "NHC\:\s*(?&lt;Error_Message&gt;[^.*]+)\=\="
| search Message!="*failed=0*" Message!="*level=info*" 
| stats count by host 
| addcoltotals labelfield=host
| sort -count</query>
          <earliest>$search_time.earliest$</earliest>
          <latest>$search_time.latest$</latest>
        </search>
        <option name="count">10</option>
        <option name="drilldown">none</option>
        <option name="refresh.display">progressbar</option>
        <format type="color" field="host">
          <colorPalette type="sharedList"></colorPalette>
          <scale type="sharedCategory"></scale>
        </format>
      </table>
    </panel>
    <panel>
      <title>RmInitAdapter Summary</title>
      <table>
        <search>
          <query>index=syslog *RmInitAdapter* host IN($host_preos$)
| search process="kernel"
| rex field=_raw "\[\d*\]\:\s*\[(?&lt;Log_Level&gt;[^\] ]+)" 
| rex field=_raw "(prolog\:|kernel\:|\[\d*\]\:)(?&lt;Message&gt; *(.+))" 
| rex field=_raw "NVRM\:\sGPU\s*(?&lt;GPU&gt;[^ ]+)\:\s*(?&lt;Error_Message&gt;[^.*]+)"
| search Message!="*failed=0*" Message!="*level=info*" _raw="*RmInitAdapter failed*" 
| stats count by host GPU Error_Message 
| addcoltotals labelfield=host
| sort - count</query>
          <earliest>$search_time.earliest$</earliest>
          <latest>$search_time.latest$</latest>
        </search>
        <option name="count">6</option>
        <option name="drilldown">none</option>
        <option name="refresh.display">progressbar</option>
        <format type="color" field="host">
          <colorPalette type="sharedList"></colorPalette>
          <scale type="sharedCategory"></scale>
        </format>
      </table>
    </panel>
  </row>
  <row>
    <panel>
      <title>Error Message - For Host ($host_preos$)</title>
      <table>
        <search base ="base">
          <query>search "*NVRM: Xid (PCI*62*" OR error OR ERROR OR fail OR failed OR errors OR faulted OR "*NVRM: Xid (PCI*" NOT NOTIFICATION process!=switchd host IN($host_preos$)
| search $multi_process$
| rex field=_raw "\d{4}\-\d{2}\-\d*\w\d*\:\d*\:\d*\.\d*(\+|\-)\d*\:\d*\s*(\S*)(\s*\S*\s\[\s*\S*\s|\s*\S*\s\[\S*\s|(\S*)\s*(\S*\:|\S*)\s*)(?&lt;Message&gt;(.*))" 
|  search Message!="*failed=0*" Message!="*level=info*" Message=*$search_text$*
| stats count by host process _time Message
| addcoltotals labelfield=host
| sort -count</query>
          
        </search>
        <option name="count">15</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">none</option>
        <option name="percentagesRow">false</option>
        <option name="refresh.display">progressbar</option>
        <option name="rowNumbers">false</option>
        <option name="totalsRow">false</option>
        <option name="wrap">true</option>
        <format type="color" field="host">
          <colorPalette type="sharedList"></colorPalette>
          <scale type="sharedCategory"></scale>
        </format>
      </table>
    </panel>
  </row>
</form>

gcusello · ‎10-06-2022

Hi @vrmandadi,

your base search is too general and using it you have too many results,

then you make some following searches on every results of the base search, this is a wrong approach, because best practices say that you have to put search terms as left as possible.

So try to move all common search terms in the base search.

Then if possible use a streming command on these results.

Then you use more levels of searches: thesis is a slower approach: group all search terms in the main search, son't use search ... | search ... | search ...

Then, you have some regex extractions in the search, if you configure these extarctions as new fiels, you can use the following searches in the main search, having better performaces.

then in your inputs you many times used:

| stats count by  host  
| dedup host

it isn't efficient: directly use

<your_search>
| dedup host
| sort host
| table host

Now I try to optimize your base search but only you can check them and eventually find other optimizations:

<form theme="dark">
  <label>basesearch</label>
  <search id="base">
    <query>
		index=syslog process!=switchd (error OR ERROR OR fail OR failed OR Failed OR errors OR faulted OR NVRM) process!=switchd host IN($host_preos$)		
	</query>
	<earliest>-30m@m</earliest>
    <latest>now</latest>
  </search>
  <fieldset submitButton="false">
    <input type="multiselect" token="multi_process" searchWhenChanged="true">
      <label>Process</label>
      <choice value="*">All</choice>
      <default>*</default>
      <initialValue>*</initialValue>
      <fieldForLabel>process</fieldForLabel>
      <fieldForValue>process</fieldForValue>
      <search base ="base">
        <query>
			search "*NVRM: Xid (PCI*" NOT NOTIFICATION process!=switchd $multi_process$
			| rex field=_raw "\d{4}\-\d{2}\-\d*\w\d*\:\d*\:\d*\.\d*(\+|\-)\d*\:\d*\s*(\S*)\s*(\S*\:|\S*)\s*(?&lt;Message&gt;(.*))"  
			| search Message!="*failed=0*" Message!="*level=info*" Message=*$search_text$*
			| dedup process
		</query>
      </search>
      <valuePrefix>process="</valuePrefix>
      <valueSuffix>"</valueSuffix>
      <delimiter> OR </delimiter>
    </input>
    <input type="text" token="search_text" searchWhenChanged="true">
      <label>Search Text</label>
      <default>*</default>
    </input>
    <input type="multiselect" token="host_preos">
      <label>Preos Hosts</label>
      <fieldForLabel>host</fieldForLabel>
      <fieldForValue>host</fieldForValue>
      <search base ="base">
        <query>
			search process!=switchd process=*  host="preos*"
			| rex field=_raw "\d{4}\-\d{2}\-\d*\w\d*\:\d*\:\d*\.\d*(\+|\-)\d*\:\d*\s*(\S*)\s*(\S*\:|\S*)\s*(?&lt;Message&gt;(.*))"  
			| search Message!="*failed=0*" Message!="*level=info*"
			| dedup host
		</query>
      </search>
      <choice value="preos*">All</choice>
      <default>preos*</default>
    </input>
  </fieldset>
  <row>
    <panel>
      <title>Error Message Counts - For Host ($host_preos$)</title>
      <chart>
        <search base ="base">
          <query>
			search "*NVRM: Xid (PCI*62*" OR "NVRM: Xid" NOT NOTIFICATION $multi_process$ 
			| rex field=_raw "\d{4}\-\d{2}\-\d*\w\d*\:\d*\:\d*\.\d*(\+|\-)\d*\:\d*\s*(\S*)\s*(\S*\:|\S*)\s*(?&lt;Message&gt;(.*))" 
			| search Message!="*failed=0*" Message!="*level=info*" Message=*$search_text$* 
			| timechart span=1h count(_time) by host limit=0
		</query>
        </search>
        <option name="charting.chart">line</option>
        <option name="charting.drilldown">none</option>
        <option name="charting.legend.labelStyle.overflowMode">ellipsisMiddle</option>
        <option name="charting.legend.placement">right</option>
        <option name="refresh.display">progressbar</option>
      </chart>
    </panel>
  </row>
  <row>
    <panel>
      <title>Overall Error Message Count - For Host ($host_preos$)</title>
      <table>
        <search base ="base">
          <query>
			search "*NVRM: Xid (PCI*62*" OR "*NVRM: Xid (PCI*" NOT NOTIFICATION $multi_process$ 
			| rex field=_raw "\d{4}\-\d{2}\-\d*\w\d*\:\d*\:\d*\.\d*(\+|\-)\d*\:\d*\s*(\S*)\s*(\S*\:|\S*)\s*(?&lt;Message&gt;(.*))" 
			| search Message!="*failed=0*" Message!="*level=info*" Message=*$search_text$* 
			| stats count by host 
			| addcoltotals labelfield=host 
			| sort -count
		  </query>
        </search>
        <option name="drilldown">none</option>
        <option name="refresh.display">progressbar</option>
        <format type="color" field="host">
          <colorPalette type="sharedList"></colorPalette>
          <scale type="sharedCategory"></scale>
        </format>
      </table>
    </panel>
    <panel>
      <title>Error Message Count per Process - For Host ($host_preos$)</title>
      <table>
       <search base ="base">
          <query>
			search "*NVRM: Xid (PCI*62*" OR "*NVRM: Xid (PCI*" NOT NOTIFICATION $multi_process$ 
			| rex field=_raw "\d{4}\-\d{2}\-\d*\w\d*\:\d*\:\d*\.\d*(\+|\-)\d*\:\d*\s*(\S*)\s*(\S*\:|\S*)\s*(?&lt;Message&gt;(.*))" 
			| search Message!="*failed=0*" Message!="*level=info*" Message=*$search_text$* 
			| stats count by host process 
			| addcoltotals labelfield=host 
			| sort -count
		  </query>         
        </search>
        <option name="drilldown">none</option>
        <option name="refresh.display">progressbar</option>
        <format type="color" field="host">
          <colorPalette type="sharedList"></colorPalette>
          <scale type="sharedCategory"></scale>
        </format>
      </table>
    </panel>
  </row>
  <row>
    <panel>
      <title>NVRM Xid Error Summary</title>
      <table>
        <title>NVRM Xid Error</title>
        <search base ="base">
          <query>
			searcg  "*NVRM: Xid (PCI*"
			| rex field=_raw "NVRM\:\sXid\s\(PCI\:(?&lt;PCI_Address&gt;[^ \)]+)\)\:\s(?&lt;Error_Code&gt;[^ ]+)\,\s*pid\=(?&lt;pid&gt;[^ ]+)\,\s*name\=(?&lt;name&gt;[^ ]+)\,\s(?&lt;Error_Message&gt;(.*))" 
			| stats count by host Error_Code Error_Message PCI_Address pid name
			| addcoltotals count labelfield=host
			| sort -count 
		  </query>          
        </search>
        <option name="count">5</option>
        <option name="drilldown">none</option>
        <option name="refresh.display">progressbar</option>
        <format type="color" field="host">
          <colorPalette type="sharedList"></colorPalette>
          <scale type="sharedCategory"></scale>
        </format>
      </table>
    </panel>
  </row>
  <row>
    <panel>
      <title>RmInit Error on Boot Summary</title>
      <table>
        <search>
          <query>
			index=syslog **RmInit error ==* process!=switchd host IN($host_preos$) process="nhc-boot.sh"
			| rex field=_raw "\[\d*\]\:\s*\[(?&lt;Log_Level&gt;[^\] ]+)" 
			| rex field=_raw "(prolog\:|kernel\:|\[\d*\]\:)(?&lt;Message&gt; *(.+))" 
			| rex field=_raw "NHC\:\s*(?&lt;Error_Message&gt;[^.*]+)\=\="
			| search Message!="*failed=0*" Message!="*level=info*" 
			| stats count by host 
			| addcoltotals labelfield=host
			| sort -count
		  </query>
          <earliest>$search_time.earliest$</earliest>
          <latest>$search_time.latest$</latest>
        </search>
        <option name="count">10</option>
        <option name="drilldown">none</option>
        <option name="refresh.display">progressbar</option>
        <format type="color" field="host">
          <colorPalette type="sharedList"></colorPalette>
          <scale type="sharedCategory"></scale>
        </format>
      </table>
    </panel>
    <panel>
      <title>RmInitAdapter Summary</title>
      <table>
        <search>
          <query>
			index=syslog *RmInitAdapter* host IN($host_preos$) process="kernel"
			| rex field=_raw "\[\d*\]\:\s*\[(?&lt;Log_Level&gt;[^\] ]+)" 
			| rex field=_raw "(prolog\:|kernel\:|\[\d*\]\:)(?&lt;Message&gt; *(.+))" 
			| rex field=_raw "NVRM\:\sGPU\s*(?&lt;GPU&gt;[^ ]+)\:\s*(?&lt;Error_Message&gt;[^.*]+)"
			| search Message!="*failed=0*" Message!="*level=info*" _raw="*RmInitAdapter failed*" 
			| stats count by host GPU Error_Message 
			| addcoltotals labelfield=host
			| sort - count
		  </query>
          <earliest>$search_time.earliest$</earliest>
          <latest>$search_time.latest$</latest>
        </search>
        <option name="count">6</option>
        <option name="drilldown">none</option>
        <option name="refresh.display">progressbar</option>
        <format type="color" field="host">
          <colorPalette type="sharedList"></colorPalette>
          <scale type="sharedCategory"></scale>
        </format>
      </table>
    </panel>
  </row>
  <row>
    <panel>
      <title>Error Message - For Host ($host_preos$)</title>
      <table>
        <search base ="base">
          <query>
			search "*NVRM: Xid (PCI*62*" OR "*NVRM: Xid (PCI*" NOT NOTIFICATION $multi_process$
			| rex field=_raw "\d{4}\-\d{2}\-\d*\w\d*\:\d*\:\d*\.\d*(\+|\-)\d*\:\d*\s*(\S*)(\s*\S*\s\[\s*\S*\s|\s*\S*\s\[\S*\s|(\S*)\s*(\S*\:|\S*)\s*)(?&lt;Message&gt;(.*))" 
			|  search Message!="*failed=0*" Message!="*level=info*" Message=*$search_text$*
			| stats count by host process _time Message
			| addcoltotals labelfield=host
			| sort -count
		  </query>         
        </search>
        <option name="count">15</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">none</option>
        <option name="percentagesRow">false</option>
        <option name="refresh.display">progressbar</option>
        <option name="rowNumbers">false</option>
        <option name="totalsRow">false</option>
        <option name="wrap">true</option>
        <format type="color" field="host">
          <colorPalette type="sharedList"></colorPalette>
          <scale type="sharedCategory"></scale>
        </format>
      </table>
    </panel>
  </row>
</form>

Then there's a final doubt in your search: for my knowledge this search shouldn't give any result because if in the base search there isn't any streaming command (as in your case) you need to add, at the end of the base search, the fields command with all the fields that you are using in the panels, otherwise they aren't available.

Ciao.

Giuseppe

vrmandadi · ‎10-07-2022

@gcusello thank you for your reply...So I have changed the field extraction to new fields and in base search I added fields in table command as below but all the other searches are taking the |table Message host process which makes the other dashboards not populate

index=syslog process!=switchd (error OR ERROR OR fail OR failed OR Failed OR errors OR faulted OR NVRM) process!=switchd host IN($host_preos$) |table Message host process

gcusello · ‎10-07-2022

Hi @vrmandadi,

could you share your new baase search and panels' searches?

Ciao.

Giuseppe

vrmandadi · ‎10-07-2022

hello @gcusello

Base search
index=syslog process!=switchd (error OR ERROR OR fail OR failed OR Failed OR errors OR faulted OR NVRM) process!=switchd host IN($host_preos$) |table Message host process

One of the panel search

search "*NVRM: Xid (PCI*62*" OR "NVRM: Xid" NOT NOTIFICATION $multi_process$
| rex field=_raw "\d{4}\-\d{2}\-\d*\w\d*\:\d*\:\d*\.\d*(\+|\-)\d*\:\d*\s*(\S*)\s*(\S*\:|\S*)\s*(?<Message>(.*))"
| search Message!="*failed=0*" Message!="*level=info*" Message=*$search_text$*
| timechart span=1h count(_time) by host limit=0

gcusello · ‎10-07-2022

Hi @vrmandadi,

your searches in panels use a full text search on the _raw field, that you must add to the fields definition in the base search.

In the same way, you use -time in timechart and you have to add it to the list.

In other words, you have to list in the last row of the base search ALL the fields you'll use in the panels,

something like this.

index=syslog process!=switchd (error OR ERROR OR fail OR failed OR Failed OR errors OR faulted OR NVRM) process!=switchd host IN($host_preos$) 
| fields _raw _time Message host process

In the same way, I don't know what field is used in the $multi_process$ token, eventually add it to the fields list.

Ciao.

Giuseppe

How to optimize my base search for syslog?

other

Introducing the 2024 SplunkTrust!

Introducing the 2024 Splunk MVPs!

Splunk Custom Visualizations App End of Life