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!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...