Splunk Search

Dedup field values using if"match"?

BB34
Explorer

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:

BB34_0-1596737597297.png

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-use...

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

Any help would be greatly appreciated.

Labels (4)
Tags (1)
0 Karma

mfasciano_splun
Splunk Employee
Splunk Employee

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

BB34
Explorer

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

 

BB34_0-1596743860434.png

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

0 Karma

mfasciano_splun
Splunk Employee
Splunk Employee

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. 

BB34
Explorer

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:

BB34_0-1596746311094.png

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:

ActionCount

Allowed

Denied

3

0

Allowed

Denied

120

1

Allowed

Denied

5

23

0 Karma

to4kawa
SplunkTrust
SplunkTrust
<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.

BB34
Explorer

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

BB34_0-1597070879314.png

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

BB34_1-1597073449615.png

 

 

0 Karma
Take the 2021 Splunk Career Survey

Help us learn about how Splunk has
impacted your career by taking the 2021 Splunk Career Survey.

Earn $50 in Amazon cash!