I am trying obtain a list of userid's (field) that come up under spamreport (event).
With that list of userid's I would like to do another search to find the number of delivered events for each userid.
The final step is to calculate the percentage of spamreport over the total number of delivered for each userid and sort the table by highest to lowest spam percentage.
Right now I am searching the followng:
sourcetype=json event=delivered [search sourcetype=json event=spamreport (ip=220.127.116.11 OR ip=18.104.22.168) | stats count by userid | eval SPMRPTS=count/2 | table userid] | stats count by userid | eval DLVRD=count | eval PercentSpam=SPMRPTS/DLVRD | table userid, DLVRD, SPMRPTS, PercentSpam
Essentially there is a subsearch to find the userid's with spamreports and to calculate the value of spamreports into the variable SPMRPTS. Then an outer search searches for the total delivered for each userid. However, There is a problem accessing the SPMRPTS variable from the inner subsearch from the context of the outer search. Right now the SPMRPTS and PercentSpam fields are both empty in the table while the DLVRD field displays correctly.
Is there a way to make this subsearch variable accessible to the outside search? Is there another way to structure this so that the two searches happen simultaneously in one splunk query?
Thanks for the reply Lp,
The search you recommended is not exactly what I'm looking for. These are the relavent fields in the log events I'm trying to search:
I want to search first for all event="spamreport" and ip="some_ip_here" log events. From that search I wish to send a list (or splunk table as I was previously playing with) of unique userid fields into a second search.
The second search will search the log events for each unique user id (from the first search), and return the count of logs where event="delivered".
From this 2nd search I wish to organize a table that sorts each userid from highest PercentSpam (calculated by (count of event="spamreport" logs) / (count of event="delivered" logs) ) to lowest.
The problem that I have faced in my approach with piping subsearch results into an outer search is that the eval variable I use to store the number of event="spamreport" logs is not accessible from the outer search where I actually do the calculation of PercentSpam as shown above.
Is there a way to structure this search so that I can reference this variable?
I do not know the content of your log events, Can you try this query?
sourcetype=json event=delivered |stats count as DLVRD by userid | appendcols [search sourcetype=json event=spamreport AND (ip="22.214.171.124" OR ip="126.96.36.199") | stats count as SPMRPTS by userid | eval SPMRPTS=count/2]| eval PercentSpam=SPMRPTS/DLVRD |table userid, DLVRD, SPMRPTS, PercentSpam