How to create new rows using conditions.

[ibob0304]

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

[somesoni2 · Accepted solution]

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]

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

[somesoni2]

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

[ibob0304]

Yes, appnames are fixed. I updated the query.