Splunk Search

How to create new rows using conditions.

ibob0304
Communicator

I have 6 sources, each application has it own source location. I used regular expression to get the app names from the sourcetype by trimming the not required keywords and backslash, which gives me the app names. These apps log events sometimes based on schedule not all the time.

| rex field=source "(?:\\\\\\172.168.1.1\\\Logs\\\Production\\\*\\\(?<Application>.+?(?=\\\))|(?:\\\\\\\172.168.1.2\\\Logs\\\(?<Application1>.+?(?=\\\))))"
| eval Apps = coalesce(Application, Application1) 

It will output below if there are events, if no events then it wont output that app name.

App
-------------
B2B
Silverlight
B2C
Ldoc
Omega
Sigma

I counted the number of events in those apps

| stats count by Apps

output

App                  count
-----------------------------
B2B                     101
Silverlight             95
B2C                     102
Ldoc                    40 

I created another column saying has events in last 10minutes

| eval hasevents= if (count>=50,"YES","NO")

output

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

If you see, I am missing the rows Omega and Sigma. Because there are no events logged in those app. I want to show Omega and Sigma as well. If those apps(sources) really has events then It would return the real results, If it doesn't then it should show dummy row like below. I tried with if condition, but it is working for only 1 row and also making other Apps rows blank.

Desired Output

App                  count                  hasevents
----------------------------------------------------------
B2B                     101                       YES
Silverlight             95                        YES
B2C                     102                       YES
Ldoc                    40                        NO
Omega                   0                         NO
Sigma                   0                         NO
0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

Give this a try.

If your base search consists of only metadata fields (index sourcetype source host), you can try something like this

Your base search 
 | 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 [| tstats count WHERE Your Base Search Here by source | rex field=source "(?:\\\\\\172.168.1.1\\\Logs\\\Production\\\*\\\(?<Application>.+?(?=\\\))|(?:\\\\\\\172.168.1.2\\\Logs\\\(?<Application1>.+?(?=\\\))))"
 | eval Apps = coalesce(Application, Application1) | table Apps | eval count=0 ]
 | stats sum(count) as count by Apps
 | eval hasevents= if (count>=50,"YES","NO")

If your Apps are hardcoded fixed values, then you can replace the append query with a query that generates hardcoded list, like this

Your base search 
 | 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>=50,"YES","NO")

View solution in original post

somesoni2
SplunkTrust
SplunkTrust

Give this a try.

If your base search consists of only metadata fields (index sourcetype source host), you can try something like this

Your base search 
 | 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 [| tstats count WHERE Your Base Search Here by source | rex field=source "(?:\\\\\\172.168.1.1\\\Logs\\\Production\\\*\\\(?<Application>.+?(?=\\\))|(?:\\\\\\\172.168.1.2\\\Logs\\\(?<Application1>.+?(?=\\\))))"
 | eval Apps = coalesce(Application, Application1) | table Apps | eval count=0 ]
 | stats sum(count) as count by Apps
 | eval hasevents= if (count>=50,"YES","NO")

If your Apps are hardcoded fixed values, then you can replace the append query with a query that generates hardcoded list, like this

Your base search 
 | 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>=50,"YES","NO")

ibob0304
Communicator

you are awesome. I used second one since all my sources are constant and it worked.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Are those app names fixed? Can you also provide the query that you use to retrieve App from sourcetype?

0 Karma

ibob0304
Communicator

Yes, appnames are fixed. I updated the query.

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 ...