topic Re: Dedup field values using if(match in Splunk Search

Dedup field values using if"match"?

BB34 — Thu, 06 Aug 2020 21:40:46 GMT

Hello all,

I am attempting to put together a search where I'm taking website status (200=allowed, etc) and breaking it into allowed and denied:

| stats count by user, status, http_method | eval action=if(match(status,"^(2)([0-9]*)$"),"Allowed","Denied") | stats list(action) as Action, list(count) as "Count", sum(count) as total by user http_method | eval "Creds Entered"=if(http_method="POST","Yes","No") | sort - total | fields - total | table user Action Count "Creds Entered"

The issue I'm running into is the field for Action (allowed/denied) is not just populating allowed or denied, but multiples of each:

So far I have reviewed the following with no success:

https://docs.splunk.com/Documentation/Splunk/8.0.5/SearchReference/MultivalueEvalFunctions

https://docs.splunk.com/Documentation/Splunk/8.0.5/SearchReference/Multivaluefunctions

https://docs.splunk.com/Documentation/Splunk/8.0.5/SearchReference/Mvcombine

https://docs.splunk.com/Documentation/Splunk/8.0.5/SearchReference/Makemv#Description

https://docs.splunk.com/Documentation/Splunk/8.0.5/SearchReference/Nomv

https://docs.splunk.com/Documentation/Splunk/8.0.5/SearchReference/Mvexpand

https://community.splunk.com/t5/Splunk-Search/Dedup-within-a-MV-field/td-p/34957

https://community.splunk.com/t5/Splunk-Search/dedup-gives-different-result-if-a-table-command-is-used-before/td-p/253021

https://community.splunk.com/t5/Splunk-Search/dedup-results-in-a-table-and-count-them/td-p/40339

Any help would be greatly appreciated.

Re: Dedup field values using if(match

mfasciano_splun — Thu, 06 Aug 2020 19:52:27 GMT

If I understand you correctly, try using values(action) rather than list(action)

Re: Dedup field values using if(match

BB34 — Thu, 06 Aug 2020 20:00:30 GMT

Awesome! but now crops up a new issue: my count of allowed/denied is busted.

Going to keep messing with it, thank you for the prompt response!

Re: Dedup field values using if(match

mfasciano_splun — Thu, 06 Aug 2020 20:18:16 GMT

Maybe you need a sum(count) rather than a list(count)? I'm not sure what the overall goal is so sorry for the piece meal answers.

Re: Dedup field values using if(match

BB34 — Thu, 06 Aug 2020 20:48:37 GMT

No! thank for the responses! I'm making a search panel that we can input a website, from there the search tables the User, Action(allowed/denied), Count(of the action), and credentials entered.

My current revised script is:

<search> | stats count by user, status, http_method | eval action=if(match(status,"^(2)([0-9]*)$"),"Allowed","Denied") | stats values(action) as Action, sum(count) as "Count" by user http_method | eval "Creds Entered"=if(http_method="POST","Yes","No") | table user Action Count "Creds Entered"

Which outputs:

What I'm attempting to created is a table where all HTTP Status codes that = 2XX are "Allowed" while all others are "Denied", in the next column I want to have a count of "Allowed" and "Denied"

Excuse my crude example:

Action

Count

Allowed

Denied

Allowed

Denied

120

Allowed

Denied

Re: Dedup field values using if(match

to4kawa — Sat, 08 Aug 2020 15:51:53 GMT

<search> | eval Action=if(match(status,"^(2)([0-9]*)$"),"Allowed","Denied") | stats count by user, Action, http_method | eval tmp=if(http_method="POST","Yes","No") | stats list(Action) as Action list(count) as Count list(tmp) as "Creds Entered" by user

This is better than before.

Re: Dedup field values using if(match

BB34 — Mon, 10 Aug 2020 15:33:07 GMT

Thanks for the input! We got Count to match up with Action but now we've split the Action

It looks as though the split it due to the http_method: