Splunk Search

Combine and dedup results to only show one row per user

DanielFordWA
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
1 Solution

sundareshr
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

sundareshr
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 

DanielFordWA
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
Get Updates on the Splunk Community!

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Get More Out of Your Security Practice With a SIEM

Get More Out of Your Security Practice With a SIEMWednesday, July 31, 2024  |  11AM PT / 2PM ETREGISTER ...