Splunk Search

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

renopaul
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
1 Solution

vinodmadaan
Path Finder

Hi renopaul,

If you are going to present the your analysis to Management I would rather suggest you to go for a the PIVOT that gives you bar graphs or various visualization methods which would be very easy to explain to management.

Here is a link to create the pivot report:
https://www.youtube.com/watch?v=MdjDrDTXYWQ&list=PL59B00A6F603366EA&index=8

I hope this will help you.

Vinod.

View solution in original post

0 Karma

vinodmadaan
Path Finder

Hi renopaul,

If you are going to present the your analysis to Management I would rather suggest you to go for a the PIVOT that gives you bar graphs or various visualization methods which would be very easy to explain to management.

Here is a link to create the pivot report:
https://www.youtube.com/watch?v=MdjDrDTXYWQ&list=PL59B00A6F603366EA&index=8

I hope this will help you.

Vinod.

View solution in original post

0 Karma

markthompson
Builder

Hi Vinod,
Pivot is a good idea, I mean, I like it and I considered suggesting it, but given the circumstances and @renopaul wanting to learn more about how searching works etc, I suggested he start with table and by outputting his results.

From here he can start using pivot, I mean, I don't use it that much, I prefer to table things and chart things on a dashboard as we are in the process of going paperless.

markthompson
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

renopaul
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 LOGIN_FAILED 108171456 0 : User ****** - Client_ip ... - 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 LOGIN_FAILED 108162410 0 : User ****** - Client_ip ... - 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 LOGIN_FAILED 108136749 0 : User ****** - Client_ip ... - 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 LOGIN_FAILED 108132475 0 : User ****** - Client_ip ... - 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 LOGIN_FAILED 108130850 0 : User ****** - Client_ip ... - Failure_reason "External authentication server denied access" - Browser Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; Trident/5.0)

0 Karma

renopaul
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

markthompson
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

renopaul
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

markthompson
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

renopaul
Explorer

I'm getting close to what I'm looking for

index="index" "External authentication server denied access" | convert timeformat="%b %d %H:%M" ctime(_time) as Time | table Time, User | stats count by User, Time | sort count

However I've tried several different combinations of the sort command but can not seem to get it in descending order.

0 Karma

renopaul
Explorer

index="index" "External authentication server denied access" | convert timeformat="%b %d %H:%M" ctime(_time) as Time

Created a Pivot table and Bob's your uncle. Thank you guys I'm learning.

markthompson
Builder

Thats good 🙂

Try:

sort -count

OR

sort +count
0 Karma

markthompson
Builder

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

0 Karma

renopaul
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

markthompson
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
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.