Splunk Search

How to edit my search to list jobs in a table per user, per day?

Svill321
Path Finder

Hello,

One of my co-workers is using a search to make a table listing the days the events of interest took place, as well as the corresponding User IDs on those days and the Jobs that are associated with it. So this:

index=index_name date_wday=* User=* Job=*  | bucket span=1d _time |stats count by date_wday User Job _time | search count >25 | stats values(_time) as time list(User) as UserID  values(Job) as JobName list(count) as count  by date_wday | eval Time=strftime(time, "%m-%d-%Y") | fields - time | table Time, date_wday, UserID,JobName,count | sort - Time | rename date_wday as Day_of_week

Creates something like this:

07-19-2017   |     wednesday    |         User1      |         Job1                   |              34
             |                  |          User2      |         Job2                   |              45
             |                  |          User3      |         Job3                   |              34
             |                  |          User3      |         Job4                   |              32

This works fine, but I'm wondering if it is possible to set this up so that if a user is associated with multiple jobs, the jobs appear in one line, as opposed to another entry of the user. For example:

07-19-2017   |     wednesday    |         User1      |         Job1                   |              34
             |                  |          User2      |         Job2                   |              45
             |                  |          User3      |         Job3, Job4             |              34

Is this even possible using the search queries, though?

0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

Give this a try (deriving date_wday from _time itself for more accuracy)

index=index_name User=* Job=*  | bucket span=1d _time 
|stats count by User Job _time | search count >25 
| stats sum(count) as count values(Job) as Job by _time User delim="," | nomv Job
| stats list(User) as UserID  list(Job) as JobName list(count) as count  by _time 
| eval Time=strftime(_time, "%m-%d-%Y") | eval Day_of_week=strftime(_time,"%A") 
| table Time, Day_of_week, UserID,JobName,count | sort - Time 

View solution in original post

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Give this a try (deriving date_wday from _time itself for more accuracy)

index=index_name User=* Job=*  | bucket span=1d _time 
|stats count by User Job _time | search count >25 
| stats sum(count) as count values(Job) as Job by _time User delim="," | nomv Job
| stats list(User) as UserID  list(Job) as JobName list(count) as count  by _time 
| eval Time=strftime(_time, "%m-%d-%Y") | eval Day_of_week=strftime(_time,"%A") 
| table Time, Day_of_week, UserID,JobName,count | sort - Time 
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...