Splunk Search
Highlighted

How to edit my search to create a more detailed report including date and time of events for management?

Explorer
index="index" "some form of data" | top limit=100 User showperc=f

I have the above search string which works great. However, I’m being asked to break out just a bit more data, mainly the date/time that the event occurred. The information is in the detailed data, however makes for a very ugly report. Being a novice at best I’ve reached the limit of my knowledge. What can I add to the search string to provide this information for management in a usable report?

Tags (3)
0 Karma
Highlighted

Re: How to edit my search to create a more detailed report including date and time of events for management?

Builder

Hi renopaul,
No worries, everyone starts somewhere!

You could use the table command, which is one of the output commands and is used in the format: | table

So you could use:
table _time, User

But if you can provide an example of your data, I'm sure there are plenty of other things that we can help you to achieve.

0 Karma
Highlighted

Re: How to edit my search to create a more detailed report including date and time of events for management?

Explorer

I believe your help is going to be valuable. I'm working on getting a sample of data, however I need to mask sensitive data.

0 Karma
Highlighted

Re: How to edit my search to create a more detailed report including date and time of events for management?

Builder

Hi Paul,
No problem,
If you can give us sort of a template that your data follows, and anything sensitive just put *******, atleast then we can get the jest of your data.

Just a quick comment, instead of replying in the form of an answer, it'll make the thread look untidy, if you just comment on one of our answers 🙂

0 Karma
Highlighted

Re: How to edit my search to create a more detailed report including date and time of events for management?

Explorer

here is a sample of the data, sensitive data has been masked.

Feb 23 08:35:17 10.220.12.34 23/02/2015:08:35:17 hostname** 0-PPE-0 : AAA LOGINFAILED 108171456 0 : User ****** - Clientip ... - Failure_reason "External authentication server denied access" - Browser Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; Trident/5.0)

Feb 23 08:33:05 10.220.12.34 23/02/2015:08:33:05 hostname** 0-PPE-0 : AAA LOGINFAILED 108162410 0 : User ****** - Clientip ... - Failure_reason "External authentication server denied access" - Browser Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; Trident/5.0)

Feb 23 08:27:52 10.220.12.34 23/02/2015:08:27:53 hostname** 0-PPE-0 : AAA LOGINFAILED 108136749 0 : User ****** - Clientip ... - Failure_reason "External authentication server denied access" - Browser Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; Trident/5.0)

Feb 23 08:26:39 10.220.12.34 23/02/2015:08:26:40 hostname** 0-PPE-0 : AAA LOGINFAILED 108132475 0 : User ****** - Clientip ... - Failure_reason "External authentication server denied access" - Browser Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; Trident/5.0)

Feb 23 08:26:18 10.220.12.34 23/02/2015:08:26:18 hostname** 0-PPE-0 : AAA LOGINFAILED 108130850 0 : User ****** - Clientip ... - Failure_reason "External authentication server denied access" - Browser Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; Trident/5.0)

0 Karma
Highlighted

Re: How to edit my search to create a more detailed report including date and time of events for management?

Builder

Which parts of the data do you need to produce to your management?

0 Karma
Highlighted

Re: How to edit my search to create a more detailed report including date and time of events for management?

Explorer

for the above data

User Count
**** 5
Feb 23 08:35
Feb 23 08:33
Feb 23 08:27
Feb 23 08:26
Feb 23 08:26

0 Karma
Highlighted

Re: How to edit my search to create a more detailed report including date and time of events for management?

Builder

OK, so lets start with the formatting of the date, I believe, as you're still learning, that you should make the most of the commands, and not take the easy way out, so instead of using a regex to extract your date, we can use the convert functionality.

So;
convert timeformat="%b %d %H:%M" ctime(_time) as Time

This will transform your _time stamp into the format that you require, into a new field called Time

What do you mean by *5?

0 Karma
Highlighted

Re: How to edit my search to create a more detailed report including date and time of events for management?

Explorer

*5 didn't translate correctly, for the above data in the summary we need count of the same event for the same user, so this example would be user * would have 5 events, then broke down by when the event occurred.

0 Karma
Highlighted

Re: How to edit my search to create a more detailed report including date and time of events for management?

Builder

Right, so for that you would need the Count function of the stats command,
please read this documentation:
http://docs.splunk.com/Documentation/Splunk/6.2.1/SearchReference/Stats

It will provide you with an overview of one of the other output commands, Stats which will be useful for ou in the future