Splunk Search

How to create a new column using the subsearch

ibob0304
Communicator

I have this query (thanks to somesoni2) which will scan the logs and say whether the sources has any log events or not.

Existing Query

    index=saq source=* 
  | rex field=source "(?:\\\\\\172.168.1.1\\\Logs\\\Production\\\*\\\(?<Application>.+?(?=\\\))|(?:\\\\\\\172.168.1.2\\\Logs\\\(?<Application1>.+?(?=\\\))))"
  | eval Apps = coalesce(Application, Application1) 
  | stats count by Apps
  | append [| gentimes start=-1 | eval Apps="App1#App2#App3#App4...all app names here separated by #"  | table Apps | makemv Apps delim="#" | mvexpand Apps | eval count=0 ]
  | stats sum(count) as count by Apps
  | eval hasevents= if (count>=1,"YES","NO") 

Output

App                  count                  hasevents
 ----------------------------------------------------------
 B2B                     101                       YES
 Silverlight             95                        YES
 B2C                     102                       YES
 Ldoc                    40                        NO

I want to run a sub search to get the errors count and wanted to add that errors count output to the main search output. So, I tried adding sub search to run similar query with error keywords for getting the errors count .

Query tried using join,

  index=saq source=* 
  | rex field=source "(?:\\\\\\172.168.1.1\\\Logs\\\Production\\\*\\\(?<Application>.+?(?=\\\))|(?:\\\\\\\172.168.1.2\\\Logs\\\(?<Application1>.+?(?=\\\))))"
  | eval Apps = coalesce(Application, Application1) 
  | stats count by Apps
  | append [| gentimes start=-1 | eval Apps="App1#App2#App3#App4...all app names here separated by #"  | table Apps | makemv Apps delim="#" | mvexpand Apps | eval count=0 ]
  | stats sum(count) as count by Apps
  | eval hasevents= if (count>=1,"YES","NO")

  | join [search     index=saq source=* 
  | search ("error" OR  "Timeout")
  | rex field=source "(?:\\\\\\172.168.1.1\\\Logs\\\Production\\\*\\\(?<Application>.+?(?=\\\))|(?:\\\\\\\172.168.1.2\\\Logs\\\(?<Application1>.+?(?=\\\))))"
  | eval Apps = coalesce(Application, Application1) 
  | stats count by Apps
  | append [| gentimes start=-1 | eval Apps="App1#App2#App3#App4...all app names here separated by #"  | table Apps | makemv Apps delim="#" | mvexpand Apps | eval count=0 ]
  | stats sum(count) as errorcount by Apps
  | eval Errors= if (errorcount>=1,"YES","NO") ]

First, it took long time to return results, second the Apps names are repeated multiple times. So, I think I didn't make it correct. Is there any way to get the errors count from subsearch and add that to the main search to get the below output ?

Desired Output

App                  count                  hasevents               Errors
 ----------------------------------------------------------------------------------------
 B2B                     101                       YES                       Yes 
 Silverlight             95                        YES                       NO
 B2C                     102                       YES                       NO
 Ldoc                    40                        NO                        NO

in short [hasevents column will scan for all logs, errors column only scan for specified errors.]

0 Karma

somesoni2
SplunkTrust
SplunkTrust

You probably don't need the subsearch to get the error count. Besides join is an expensive command and should be avoided if possible. For your requirement, try something like this (can merge both searches into one since the base search is same for errors)

  index=saq source=* 
   | rex field=source "(?:\\\\\\172.168.1.1\\\Logs\\\Production\\\*\\\(?<Application>.+?(?=\\\))|(?:\\\\\\\172.168.1.2\\\Logs\\\(?<Application1>.+?(?=\\\))))"
   | eval Apps = coalesce(Application, Application1) 
   | eval Error=if(searchmatch("error OR Timeout"),1,0)
   | stats count as EventCount sum(Error) as ErrorCount by Apps
   | append [| gentimes start=-1 | eval Apps="App1#App2#App3#App4...all app names here separated by #"  | table Apps | makemv Apps delim="#" | mvexpand Apps | eval EventCount=0 | eval ErrorCount=0]
   | stats sum(EventCount) as EventCount sum(ErrorCount) as ErrorCount by Apps
   | eval hasevents=if (EventCount>0,"YES","NO") 
   | eval haserrors=if(ErrorCount>0,"YES","NO")

ibob0304
Communicator

Your query is freaking fast, I previously tried with join command which worked but gave me timeouts and used to took longer time.
Two doubts :
1. Is there a way to apply NOT condition in searchmatch? I want to search "error" and "timeout" strings like in the query, but don't want my error query to capture "fatal timeout" or "low memory ERROR" count.

0 Karma

nryabykh
Path Finder

Hi!

It seems you forgot to specify the field to join on. It causes multiple repeat of Apps names. Try this: | join Apps [search index=saq ... ]

Nested subsearches and duplicated loading data from index are bad influence on query execution time. So, consider rewriting your query on this way:


| makeresults
| eval Apps="App1#App2#App3#App4...all app names here separated by #"

| table Apps
| makemv Apps delim="#"
| mvexpand Apps
| join type=left Apps
[search index=saq source=*
| eval is_error=if(like(_raw,"%error%") OR like(_raw, "%Timeout%"), 1, 0)
| rex field=source "...regexp to extract application..."
| eval Apps = coalesce(Application, Application1)
| stats count as hasevents, sum(is_error) as errors by Apps
]
| eval hasevents = if(hasevents > 0, "Yes", "No"), errors = if(errors > 0, "Yes", "No")

Also, it may be better to create Extracted field for Application name and Event type for errors/timeouts.

0 Karma

ibob0304
Communicator

Error in 'makeresults' command: This command must be the first command of a search., When I defined |makeresults at the beginning it didnt returned any results. And removing the makeresults caused multiple repeated apps.

0 Karma

nryabykh
Path Finder

Hmm, yes, in my query above makeresults is the first command of the search as Splunk requires. makeresults just returns a single row with a _time field. It's only needed for further manual creating Apps field with the list of all applications. If you want, you can replace makeresults with gentimes start=-1 like in your initial example. It is no matter.

0 Karma

ibob0304
Communicator

It worked with join type left. Thanks

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...