Splunk Search

Splunk Search Query

Awanish1212
Explorer

Suppose there are 10 events as "raw text" in Splunk in last 7 days as below :

Event 1 : 7/11/23 5:28:33.265 PM

"host":"111.123.23.34","level":1,"msg":"cricket score : 10","time":"2023-07-11T17:28:33.265Z"

Event 2 : 7/11/23 6:28:33.265 PM

"host":"111.123.23.34","level":2,"msg":"cricket score : 20","time":"2023-07-11T18:28:33.265Z"

Event 3 : 7/12/23 5:28:33.265 PM

"host":"111.123.23.34","level":3,"msg":"cricket score : 30","time":"2023-07-12T17:28:33.265Z"

Event 4 : 7/12/23 6:28:33.265 PM

"host":"111.123.23.34","level":4,"msg":"cricket score : 40","time":"2023-07-12T18:28:33.265Z"

Event 5 : 7/13/23 5:28:33.265 PM

"host":"111.123.23.34","level"5,"msg":"cricket score : 50","time":"2023-07-13T17:28:33.265Z"

Event 6 : 7/13/23 6:28:33.265 PM

"host":"111.123.23.34","level":1,"msg":"cricket score : 10","time":"2023-07-13T18:28:33.265Z"

Event 7 : 7/14/23 5:28:33.265 PM

"host":"111.123.23.34","level":2,"msg":"cricket score : 20","time":"2023-07-14T17:28:33.265Z"

Event 8 : 7/14/23 6:28:33.265 PM

"host":"111.123.23.34","level":3,"msg":"cricket score : 30","time":"2023-07-14T18:28:33.265Z"

Event 9 : 7/15/23 5:28:33.265 PM

"host":"111.123.23.34","level":4,"msg":"cricket score : 40","time":"2023-07-15T17:28:33.265Z"

Event 10 : 7/15/23 6:28:33.265 PM

"host":"111.123.23.34","level"5,"msg":"cricket score : 50","time":"2023-07-15T16:28:33.265Z"

So I need to create a Splunk query to get output as below in table format.

Date -  Sum of cricket Score on that particular date

Date - Total Cricket Score

2023-07-11 - 30

2023-07-12 - 70

2023-07-13 - 60

2023-07-14 - 50

2023-07-15 - 90

Request your help for the same.

Labels (5)
0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @Awanish1212 ,

if you didn't do, you have to extract fields using the following regex:

| rex "\"cricket score : (?<cricket_score>[^\"]+)\",\"time\":\"(?<time>[^\"]+)"

that you can test at https://regex101.com/r/L1lFzb/1

then you have to extract the day from the time field and you can do this using strptime or substr:

at the end, you should try a search like the following:

<your_search>
| rex "\"cricket score : (?<cricket_score>[^\"]+)\",\"time\":\"(?<time>[^\"]+)"
| eval day=substr(time,1,10)
| stats sum(cricket_score) AS total BY day

Ciao.

Giuseppe

 

0 Karma
Get Updates on the Splunk Community!

AppDynamics Summer Webinars

This summer, our mighty AppDynamics team is cooking up some delicious content on YouTube Live to satiate your ...

SOCin’ it to you at Splunk University

Splunk University is expanding its instructor-led learning portfolio with dedicated Security tracks at .conf25 ...

Credit Card Data Protection & PCI Compliance with Splunk Edge Processor

Organizations handling credit card transactions know that PCI DSS compliance is both critical and complex. The ...