Splunk Search

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

ankurtaunk
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

badarsebard
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

Vijeta
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

ankurtaunk
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

Vijeta
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

ankurtaunk
Explorer

This works too. Thanks

0 Karma

niketn
Legend

@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

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

ankurtaunk
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

badarsebard
Communicator

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

0 Karma

ankurtaunk
Explorer

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

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...