Splunk Search
Highlighted

How do you group by day without grouping your other columns?

Explorer

I am trying to produce a report that spans a week and groups the results by each day. I want the results to be per user per category. I have been able to produce a table with the information I want with the exception of the _time column. It gives me an entry for each line. What I'd like to have is all the identical cells in the _time column consolidated into one cell but keep the other rows for the same _time value separated from each other.

How do I do this please?

Here is the query I have so far (I'll worry about formatting and renaming values once I nail down the report).

index=paloalto eventtype="pan_url" vendor_action="block-url" ( http_category="malware" OR http_category="questionable" OR http_category="shareware-and-freeware" ) | bin _time span=1d | stats count values(dest_name) by _time user http_category

What I want:

*** _time *** | *** user *** | *** http_category ***  | *** count *** | *** values(dest_name) *** |
--------------|--------------|------------------------|---------------|---------------------------| 
  04/27/2018  |   Fred       | shareware-and-freeware |       5       |       github.com          |
--------------|--------------|------------------------|---------------|---------------------------|  
              |   Wilma      |      questionable      |       6       |         nsfw.com          |
              |              |                        |               |    also-nsfw.com          |
--------------|--------------|------------------------|---------------|---------------------------| 
              |   Wilma      |         malware        |       2       |      malware.com          | 
--------------|--------------|------------------------|---------------|---------------------------|
  04/28/2018  |   Barney     |      questionable      |      22       |         nsfw.com          |
--------------|--------------|------------------------|---------------|---------------------------| 
              |   Betty      | shareware-and-freeware |       5       |       github.com          |
--------------|--------------|------------------------|---------------|---------------------------|
              |   Bam Bam    | shareware-and-freeware |       9       |     download.com          |
--------------|--------------|------------------------|---------------|---------------------------|

What I get:

*** _time *** | *** user *** | *** http_category ***  | *** count *** | *** values(dest_name) *** | 
--------------|--------------|------------------------|---------------|---------------------------|
  04/27/2018  |   Fred       | shareware-and-freeware |       5       |       github.com          |
--------------|--------------|------------------------|---------------|---------------------------| 
  04/27/2018  |   Wilma      |      questionable      |       6       |         nsfw.com          | 
              |              |                        |               |    also-nsfw.com          |
--------------|--------------|------------------------|---------------|---------------------------| 
  04/27/2018  |   Wilma      |         malware        |       2       |      malware.com          |
--------------|--------------|------------------------|---------------|---------------------------| 
  04/28/2018  |   Barney     |      questionable      |      22       |         nsfw.com          |
--------------|--------------|------------------------|---------------|---------------------------| 
  04/28/2018  |   Betty      | shareware-and-freeware |       5       |       github.com          |
--------------|--------------|------------------------|---------------|---------------------------|
  04/28/2018  |   Bam Bam    | shareware-and-freeware |       9       |     download.com          |
--------------|--------------|------------------------|---------------|---------------------------|


What I don't want:

*** _time *** | *** user *** | *** http_category ***  | *** count *** | *** values(dest_name) *** |
--------------|--------------|------------------------|---------------|---------------------------|
  04/27/2018  |   Fred       | shareware-and-freeware |      13       |       github.com          | 
              |   Wilma      |      questionable      |               |         nsfw.com          | 
              |              |         malware        |               |    also-nsfw.com          | 
              |              |                        |               |      malware.com          |
--------------|--------------|------------------------|---------------|---------------------------|  
  04/28/2018  |   Barney     |      questionable      |      36       |         nsfw.com          | 
              |   Betty      | shareware-and-freeware |               |       github.com          |
              |   Bam Bam    | shareware-and-freeware |               |     download.com          |
--------------|--------------|------------------------|---------------|---------------------------|
Tags (4)
0 Karma
Highlighted

Re: How do you group by day without grouping your other columns?

SplunkTrust
SplunkTrust

Not sure if your exact expected output can be generated, due to values(destname) already being multivalued field (merging rows will require other columns to be multivalued, values(destname) is already that so would be tough to differentiate). See something like this works for you:

index=paloalto eventtype="pan_url" vendor_action="block-url" ( http_category="malware" OR http_category="questionable" OR http_category="shareware-and-freeware" ) | bin _time span=1d | stats count values(dest_name) as destinations by _time user http_category delim=", " | nomv destinations | stats list(*) as * by _time  

OR (may not be the best looking table)

index=paloalto eventtype="pan_url" vendor_action="block-url" ( http_category="malware" OR http_category="questionable" OR http_category="shareware-and-freeware" ) | bin _time span=1d | stats count values(dest_name) as destinations by _time user http_category delim=", " | nomv destinations | streamstats count as sno by _time | foreach user http_category destinations [| eval <<FIELD>>=sno.") ".<<FIELD>>]| stats list(*) as * by _time  

View solution in original post

0 Karma
Highlighted

Re: How do you group by day without grouping your other columns?

Explorer

Somesoni2,

Thanks for the reply. I tried both of your queries and it seems nothing happens at all in the search bar. Weird.

Some of the commands are new to me so I'll research those to try and get a grasp of what you've done here and maybe I can figure out what is preventing it to work. I'll report back.

Thanks,

Jeff

0 Karma
Highlighted

Re: How do you group by day without grouping your other columns?

Explorer

Okay, it looks like my browser session had timed out and that's the only reason the commands didn't work.

Both of these ran, and they're much closer to what I'm looking for. #2 is most helpful because it is at least numbering each result, but your'e right, it isn't the best looking table. Is there no way to get them all to appear on the same row? At this point I think it is just a formatting task.

0 Karma
Highlighted

Re: How do you group by day without grouping your other columns?

SplunkTrust
SplunkTrust

Is it only gonna be used for reporting and not drilldown or something else. If yes, give this a try

 index=paloalto eventtype="pan_url" vendor_action="block-url" ( http_category="malware" OR http_category="questionable" OR http_category="shareware-and-freeware" ) | eval timestamp=strftime(_time,"%m-%d-%Y") | stats count values(dest_name) by timestamp user http_category | streamstats current=f window=1 values(timestamp) as prev_timestamp 
| eval timestamp=if(prev_timestamp=timestamp,"",timestamp) | fields - prev_timestamp
Highlighted

Re: How do you group by day without grouping your other columns?

Explorer

OMG! That's fantastic! I didn't even think of a replacement with a blank if already used (I wouldn't have been able to come up with that code anyway at my current level of knowledge).

Thank you! Perfect. 😄