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.]
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")
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.
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")
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.
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.
It worked with join type left. Thanks