How to create a daily report on the total count of different events by IP address with my sample data?

[markwymer]

We are currently evaluating Splunk (I love it!) so I'm a complete newbie at this! I'm not even sure of the correct terminology yet so, I hope, I can explain myself properly.

We are feeding in some logs from one of our applications that has, amongst the million of events per minute, 6 lines that are of interest to us, i.e.

Logon for user xxxxx on IP 111.222.333.444
Logon error for IP 111.222.333.444
Captcha correct for user xxxxx on IP 111.222.333.444
Captcha failed for user xxxxx on IP 111.222.333.444
Search requested for product YYY on IP 111.222.333.444
Invalid search request for product XXX on IP 1111.222.333.444

I've created Field extractions for the IP addresses (obviously they're not all from 111.222.333.444 🙂 ) plus extractions for the successful and failed Logons, Captchas and Searches, but my boss has now asked me to demonstrate a report that shows counts for the events by IP address. E.g

IP Address     |Total Logons|Successful Logons|Failed Logons|Total Captchas|Successful Captachas
111.222.333.444|     12     |        8        |      4      |       3      |          1    

I have no problem using the search and drilling in to my 'Interesting Fields' however he wants it as a daily report.

Individually, I can produce 6 reports that show counts by IP address for each of the 6 elements (e.g. Index=app.log Logon | stats count by IPAddress) but I can't figure out how to put the counts for all the 6 event types into one report.

My apologies for asking what is probably a very basic question, judging by some of the other q and a's on this forum, but I need to get this done asap to show the benefits of Splunk before our trial license expires.

Many thanks, Mark.

[somesoni2]

This is how I would do this

index=app.log "Logon for user" OR "Logon error for IP" OR "Captcha correct for user" OR "Captcha failed for user" OR "Search requested for product" OR "Invalid search request for product" 
| eval Type=case(match(_raw,"Logon for user"),"Successful Logon",match(_raw,"Logon error for IP"),"Failed Logon", match(_raw,"Captcha correct for user" ),"Successful Captcha",match(_raw,"Captcha failed for user" ),"Failed Captch",match(_raw,"Search requested for product" ),"Successful Product Search",match(_raw,"Invalid search request for product"),"Failed Product Search")| chart count over IPAddress by Type | eval "Total Logon"='Successful Logon" + "Failed Logon" | eval "Total Captcha"="Successful Captcha" + "Failed Captcha" | eval "Total Product Search"= "Successful Product Search" + "Failed Product Search" | table  IPAddress Logon", 'Successful Logon", "Failed Logon" , "Total Captcha" , "Successful Captcha", "Failed Captcha" , "Total Product Search" , "Successful Product Search" , "Failed Product Search"

[aweitzman · Accepted solution]

Based on the search sample you've given, it doesn't appear that you've assigned a field to contain the kind of information you've extracted. I'm basing this on the fact that your search simply looks for Logon which is just a piece of text. Is there a value assigned to a field to represent this?

That would make things far easier to work with, because then you could simply write the following (assuming your field name is event_content😞

index=app.log | stats count(eval(event_content="Logon")) as "Total Logons" count(eval(event_content="FailedLogon")) as "Failed Logons" [etc.] by IPAddress

Alternatively, there's the hard way:

index=app.log | stats count(eval(_raw LIKE "Logon for%")) as "Total Logons" count(eval(_raw LIKE "Logon error%")) as "Failed Logons" [etc.] by IPAddress

Either will work, but the first one is much easier to read.

EDIT: Turns out this will also work as a somewhat easier-to-read alternative for the second search:

index=app.log | stats count(eval(searchmatch("Logon for"))) as "Total Logons" count(eval(searchmatch("Logon error"))) as "Failed Logons" [etc.] by IPAddress

(In the long run, though you're still better off creating a field for this information.)

[markwymer]

Thanks - got the report done = "Happy Boss" !!!

[markwymer]

Sorry my illustration of the type of report didn't tab very well - but I hope you get the gist!