How to filter on multiple values from multiple fields?

[newill]

Hi,

I have a log file that generates about 14 fields I am interested in, and of those fields, I need to look at a couple of fields and correlate on them, but still return the results of all.

The fields of interest are username, Action, and file. I have limited Action to 2 values, allowed and denied. What I need to show is any username where they had both an allowed action and a denied action for the same file. For example:

John allowed temp.txt
John Allowed 3.jpg
tom allowed temp.txt
tom denied temp.txt
fred allowed horse.jpg
fred allowed pudding.png
fred denied horse.jpg

should look something like this

username action file
tom allowed temp.txt
tom denied temp.txt
fred allowed horse.jpg
fred denied horse.jpg

I am currently using a stats(*) as * username which kind of gets me there, but it leaves me with one line with multiple events and only showing the unique field names for the other 11 fields> However, I need it to show each event specific field values and only if they allowed and denied the same file.

[somesoni2 · Accepted solution]

How about this

your base search action="allowed" OR action="denied"
| eventstats dc(action) as actions by username file
| where actions=2 | fields - actions

[newill]

This got me where I needed to be. I did have to add some extra sorting and what nots. Thanks!

[gokadroid]

Can you give this a try please:

your query to return username, action, file when searched for "allowed" OR "denied"
| sort username,file
| autoregress username as oldUserName p=1
| autoregress file as oldFileName p=1
| autoregress action as oldAction p=1
| eval tabulate=if( (username=oldUserName) AND (file=oldFileName) AND (action!=oldAction), 1, 0)
| eval zipAction=mvzip(oldAction,action)
| table username, zipAction, file, tabulate
| search tabulate=1
| mvexpand zipAction
| fields - tabulate

[newill]

This is getting close. However, it only shows me the details from the log entry for one of the events, I need both events details shown, each log entry has its own timestamp and could have slightly different field values for the other 11 fields.