How to append field value to events based on its category

[sangs8788]

I have all events logged under one index. The events arent categorzied. Below is the query

index=main host="prod*" AND host= "*web*" AND _raw!="*sql*" AND exception!="*db2*" error earliest=1504915200 latest=1510358400 | eval layer="Application"| append [search index=main host="prod*" MQ _raw="ERROR" earliest=1504915200 latest=1510358400 | eval layer="Queue"]| append [search index=main host="prod*" dataservice _raw="ERROR" earliest=1504915200 latest=1510358400 | eval layer="Dataservice"]|stats count by layer

Is it possible to combine both to single query somehting like below so that same index need not be queried twice

index=app host="prod*" _raw!="INFO" error earliest=1504915200 latest=1510358400 |eval layer=case(host=="web" OR host=="wap" AND _raw!="" AND _raw!="sql" AND _raw!="MQ" AND exception!="db2" AND exception !="solr", "Application", raw=="MQ", "Queue") |stats count by layer

[gcusello · Accepted solution]

Hi
it could be possible but in your search there's something I don't understand: _raw is the full event, how you can have in it "" or only "sql" or only "MQ"?
do you want to filter events for this words?
if this is your situation, you could perform something like this (eventually, modify filters in the main search)

index=app host="prod*" _raw!="INFO" error earliest=1504915200 latest=1510358400 
| rex "(?<layer>MQ) 
| eval layer=if(layer="MQ","MQ","Application")
| stats count by layer

Bye.
Giuseppe

[sangs8788]

Edited the query. Somehow splunk didnt display the wildcard character. The above solution would only work if there is only two categories. What if i have more categorize based on different words present in the event ?
How can i categorize and display

[gcusello]

Hi sangs8788,
if you can, use other regexes to extract other fields and use case in eval condition.
can you share other examples?
Bye.
Giuseppe

[sangs8788]

Here is an example with dataservice included.

index=main host="prod*" AND host= "web" AND _raw!="sql" AND exception!="db2" error earliest=1504915200 latest=1510358400 | eval layer="Application"| append [search index=main host="prod*" MQ _raw="ERROR" earliest=1504915200 latest=1510358400 | eval layer="Queue"]| append [search index=main host="prod*" _raw="sql" _raw="ERROR" exception="db2" earliest=1504915200 latest=1510358400 | eval layer="Dataservice"]|stats count by layer

[gcusello]

Try something like this:

index=main host="prod*" earliest=1504915200 latest=1510358400 
| eval layer=case(host= "web" AND _raw!="sql" AND exception!="db2","Application",MQ _raw="ERROR","Queue",_raw="sql" AND _raw="ERROR" AND exception="db2","Dataservice"]
|stats count by layer

Anyway, I continue to don't understand how you can have _raw equal to only one word (sql or ERROR)!
or maybe when you say "_raw!=sql" you mean the in _raw there isn't "sql"?
If this is your situation use like or match in each evaluation.
Bye.
Giuseppe

[sangs8788]

@cusello. It is not just sql or db2 in the query. It has * wildcard before and after. I guess splunk has removed the * and displayed
and above query doesnt return any results for me. Not sure what is wrong in there

[gcusello]

if you want to search a word in _raw, you don't need to insert_raw=sql you can insert sql in your search (if you run a search) or "like" or "match" functions if you're using an eval.
Something like this:

index=main host="prod*" earliest=1504915200 latest=1510358400 
 | eval layer=case(host="web" AND like(_raw,"%sql%") AND exception!="db2","Application",like(_raw,"%ERROR%"),"Queue",like(_raw,"%sql%") AND like(_raw,"%ERROR%") AND exception="db2","Dataservice"]
 |stats count by layer

Bye.
Giuseppe