Splunk Search
Highlighted

Combine and dedup results to only show one row per user

Contributor

I have the following search

index=iis
| eval WebShellActive=if(match($Webshell$,"true"),"Yes",WebShellActive) 
| eval LauncherActive=if(match($cs_User_Agent_$,"NeoNative*"),"Yes",LauncherActive) 
| eval BrowserFlexActive=if($LauncherActive$="Yes","No",if($WebShellActive$="Yes","No","Yes"))
| bucket span=1d _time
| fillnull value="No" LauncherActive WebShellActive
| stats count by _time GUID LauncherActive WebShellActive BrowserFlexActive

For some users, that use multiple access methods in one day, the results will be as follows.

 _time  GUID    LauncherActive  WebShellActive  BrowserFlexActive   count
1   2016-06-02  TESTER1234  No  Yes No  156
2   2016-06-02  TESTER1234  Yes No  No  112
3   2016-06-02  TESTER1234  Yes Yes No  34

The desired result would be to drop the count and only have one entry for each user.

The entry would show, by day, by user, each access method used.

 _time  GUID    LauncherActive  WebShellActive  BrowserFlexActive   
1   2016-06-02  TESTER1234  Yes Yes No  

Hope you can help!

Thanks,

0 Karma
Highlighted

Re: Combine and dedup results to only show one row per user

Legend

Try something like this

index=iis
 | eval WebShellActive=if(match($Webshell$,"true"),"1",WebShellActive) 
 | eval LauncherActive=if(match($cs_User_Agent_$,"NeoNative*"),"1",LauncherActive) 
 | eval BrowserFlexActive=if($LauncherActive$="1", "0",if($WebShellActive$="1","0","1"))
 | bucket span=1d _time
 | fillnull value=0 LauncherActive WebShellActive
 | stats max(LauncherActive) max(WebShellActive) max(BrowserFlexActive) by _time GUID 

View solution in original post

Highlighted

Re: Combine and dedup results to only show one row per user

Contributor

This works by just changing

 | stats count by _time GUID LauncherActive WebShellActive BrowserFlexActive

to

| stats max(LauncherActive) AS LauncherActive  max(WebShellActive) AS WebShellActive max(BrowserFlexActive) AS BrowserFlexActive by _time GUID 

in the original search.

I understand using a numerical field may be a better idea but do you know the logic behind using stats max() with text values?

Thanks,

Dan

0 Karma