I have a report to generate which should have multiple fields for the data like below:-
"10.10.10.10" 2015-09-15 15:54:55 POST /services/service1 200
"10.10.10.20" 2015-09-15 15:55:55 POST /services/service2 200
"10.10.10.30" 2015-09-15 15:56:55 POST /services/service3 200
"10.10.10.10" 2015-09-15 15:57:55 POST /services/service1 200
"10.10.10.20" 2015-09-15 16:00:55 POST /services/service3 200
The output should be like a table:-
1. Serial Number :- 1, 2, 3, 4, 5
2. Endpoint URI :- /services/service1, /services/service1, /services/service2, /services/service3, /services/service3
3. Consumer :- Consumer1, Consumer2, Consumer3
4. Total Count per Consumer per EndpointURI
5. Error Count per Consumer per EndpointURI
Report should look like:-
Sr# EndpointURI ConsumerIP HitCount ErrorCount
1 /services/service1 10.10.10.10 100 3
2 /services/service1 10.10.10.20 0 0
3 /services/service1 10.10.10.30 150 1
4 /services/service2 10.10.10.10 640 2
5 /services/service3 10.10.10.20 10 0
How can I create something like above using chart, table or fields or any other commands in splunk search?
[UPDATED ANSWER]
Based on the sample data provided please find the following run anywhere search it finds the total count of hits to and Endpoint URI from Specific IP and gives the Error Count as well.
PS: As stated by @MuS your rex
command seems incorrect. So, I have provided Regular Expression as well. Replace the commands till | rename data as _raw
with your current base search and try the rex
and stats
command provided afterwards. Also if you are saving the result as a dashboard, you can turn on Serial Number through the Chart Configuration provided in the previous answer.
| makeresults
| eval data="\"10.10.10.10\" 2015-09-15 15:54:55 POST /services/service1 200;\"10.10.10.20\" 2015-09-15 15:55:55 POST /services/service2 200;\"10.10.10.30\" 2015-09-15 15:56:55 POST /services/service3 200;\"10.10.10.10\" 2015-09-15 15:57:55 POST /services/service1 404;\"10.10.10.20\" 2015-09-15 16:00:55 POST /services/service3 200;\"10.10.10.10\" 2015-09-15 15:54:55 POST /services/service1 200;\"10.10.10.20\" 2015-09-15 15:55:55 POST /services/service2 200;\"10.10.10.30\" 2015-09-15 15:56:55 POST /services/service2 200;\"10.10.10.10\" 2015-09-15 15:57:55 POST /services/service1 200;\"10.10.10.20\" 2015-09-15 16:00:55 POST /services/service2 400"
| makemv data delim=";"
| mvexpand data
| rename data as _raw
| rex "\"(?<ConsumerIP>[^\"]+)\"\s+(?<_time>\d{4}-\d{2}-\d{2}\s\d{2}:\d{2}:\d{2})\s(?<method>[^\s]+)\s(?<EndpointURI>[^\s]+)\s(?<status>\d+)"
| stats count as TotalHits count(eval(status!=200)) as ErrorCount by EndpointURI ConsumerIP
PS: I was under impression that your current field extractions are working as expected and you already have the required fields and you just needed the stats command.
@AdsicSplunk, try the following search:
<YourBaseSearch>
| stats count as TotalHits count(eval(status!=200)) as ErrorCount by EndpointURI ConsumerIP
Once you save as a table you can use Format Visualization
option to turn on Serial Number. Following is corresponding Simple XML Configuration:
<option name="rowNumbers">true</option>
[UPDATED ANSWER]
Based on the sample data provided please find the following run anywhere search it finds the total count of hits to and Endpoint URI from Specific IP and gives the Error Count as well.
PS: As stated by @MuS your rex
command seems incorrect. So, I have provided Regular Expression as well. Replace the commands till | rename data as _raw
with your current base search and try the rex
and stats
command provided afterwards. Also if you are saving the result as a dashboard, you can turn on Serial Number through the Chart Configuration provided in the previous answer.
| makeresults
| eval data="\"10.10.10.10\" 2015-09-15 15:54:55 POST /services/service1 200;\"10.10.10.20\" 2015-09-15 15:55:55 POST /services/service2 200;\"10.10.10.30\" 2015-09-15 15:56:55 POST /services/service3 200;\"10.10.10.10\" 2015-09-15 15:57:55 POST /services/service1 404;\"10.10.10.20\" 2015-09-15 16:00:55 POST /services/service3 200;\"10.10.10.10\" 2015-09-15 15:54:55 POST /services/service1 200;\"10.10.10.20\" 2015-09-15 15:55:55 POST /services/service2 200;\"10.10.10.30\" 2015-09-15 15:56:55 POST /services/service2 200;\"10.10.10.10\" 2015-09-15 15:57:55 POST /services/service1 200;\"10.10.10.20\" 2015-09-15 16:00:55 POST /services/service2 400"
| makemv data delim=";"
| mvexpand data
| rename data as _raw
| rex "\"(?<ConsumerIP>[^\"]+)\"\s+(?<_time>\d{4}-\d{2}-\d{2}\s\d{2}:\d{2}:\d{2})\s(?<method>[^\s]+)\s(?<EndpointURI>[^\s]+)\s(?<status>\d+)"
| stats count as TotalHits count(eval(status!=200)) as ErrorCount by EndpointURI ConsumerIP
PS: I was under impression that your current field extractions are working as expected and you already have the required fields and you just needed the stats command.
@AdsicSplunk, try the following search:
<YourBaseSearch>
| stats count as TotalHits count(eval(status!=200)) as ErrorCount by EndpointURI ConsumerIP
Once you save as a table you can use Format Visualization
option to turn on Serial Number. Following is corresponding Simple XML Configuration:
<option name="rowNumbers">true</option>
Thank you @NiketNilay and @MuS!!
The problem was with the regular expression that was created by Splunk Field Extractor. Its working now.
Thank you for your reply Niket.
However, I am not receiving any result for this search. How is the value of status defined. What is status? Is it a variable?
I am using below query with a regex for the log data with which i can at least receive the hit count per Endpoint URI. But my requirement is like mentioned in the question. please advise.
index="abcd" source="def" | rex _raw="^(?P[^\t]+)\t(?P[^\t]+)\t(?P[^\t]+)\t(?P\w+)\t(?P[^\t]+)\t(?P\d+)" | chart usenull=f useother=f limit=0 count by EndpointURI | streamstats count as "SNo"
Can a regex be used for this report as well. If yes, please advise.
@niketnilay
Could you please briefly explain what is to be done here?
@AdsicSplunk, sorry for the delay in my response. I have updated my answer. Please try out and confirm!
Hi there, is this just copy/paste gone wrong or do you have no names for your capturing groups?
Hi Mus,
Its copy paste gone wrong. I pasted the query with groups but I think it got omitted at the time of posting. Anyway, I have got a regex with which I can extract a part of the report like endpoint URI and total hit counts.
However, I need help in creating full report. please advise.