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

[renopaul]

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?

[vinodmadaan · Accepted solution]

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.

[markthompson]

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]

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.

[renopaul]

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)

[renopaul]

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

[markthompson]

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?

[renopaul]

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

[markthompson]

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]

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.

[renopaul]

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]

Thats good 🙂

Try:

sort -count

OR

sort +count

[markthompson]

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

[renopaul]

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.

[markthompson]

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 🙂