Splunk Search

How to table events in columns with time/date related counts

Explorer

I want to write a search where the events are in one column and the related counts are in each column corresponding to the date, something like this :

         01/01/18        01/02/18        01/03/18         .......             01/29/18         01/30/18
userid1  3               5               30                                   8                41
userid2  5               88              10                                   7                8
userid3  45              78              7                                    8                2  
0 Karma
1 Solution

Communicator

Update

So with a high cardinality field where timechart won't work you can use a straight stats then xyseries.

index=data | stats count by user, date | xyseries user date count


Previous Answer

Take a look at timechart. It creates this exact set of data but transposed; the fields are in the columns and the times in the rows.

View solution in original post

Influencer

@ankurtaunk
You can use chart command , try below-

<your search>| bin span=1d _time | eval date=strftime(_time,"%Y-%m-%d")| chart count over users by date

Explorer

Not all the dates are coming . After 10 days, I see colum with "Others". IS this Splunk's limitation that after certain colum it shows others ?

I do not think, there is any issue qith query though. Can anyone please suggest ?

0 Karma

Influencer

@ankurtaunk Yes by default limit for timechart and chart is 10 results. You can use limit=0 option with chart and try

<your search>| bin span=1d _time | eval date=strftime(_time,"%Y-%m-%d")| chart count over users by date limit=0

Explorer

This works too. Thanks

0 Karma

SplunkTrust
SplunkTrust

@ankurtaunk try the following run anywhere example to see if it fits your needs

index=_internal sourcetype=splunkd log_level!=INFO earliest=-7d@d latest=now
| eval Time=strftime(_time,"%Y/%m/%d")
| chart count as Error by component Time
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

Communicator

Update

So with a high cardinality field where timechart won't work you can use a straight stats then xyseries.

index=data | stats count by user, date | xyseries user date count


Previous Answer

Take a look at timechart. It creates this exact set of data but transposed; the fields are in the columns and the times in the rows.

View solution in original post

Explorer

The problem is - there are millions of userID and I do not want them in column. I am good if I have millions of rows than column.

0 Karma

Communicator

In that case you'll need xyseries. I'll update answer with example.

0 Karma

Explorer

Your query is working fine and giving all the column. Thanks a lot.

0 Karma