Required assistance how to add specifics source file with same string (date) in file name in splunk query

Question

I am having one requirement where we are getting files every day with the respective date mentioned in the files:

for example the file names are:

test_dev_08_07_2021.json
test_dev_09_07_2021.json
test_prod_08_07_2021.json
test_prod_09_07_2021.json

Now the requirement we have here is to add the files content which have same dates. The splunk query we are using is below :

eventtype="metric:sample:example" source="test_dev_.json" OR source="test_prod_.json" | stats sum(number_of_car) as "# Total_Car  ", 
sum(Parked_cars) as "# Stopped_Cars", sum(Buses) as "# Total_Bus", sum(Parked_buses) as "# Stopped_Buses " by source | addcoltotals

but there it's getting combined result of all the four file:

source  # Total Car # Stopped Cars  # Total Bus # Stopped  Buses
test_dev_08_07_2021.json    23  21  295 124
test_dev_09_07_2021.json    22  22  297 123
test_prod_08_07_2021.json   2   3   429 66
test_prod_09_07_2021.json   2   3   427 66
                                                                             
                           49   49  1448    379

What we are trying to achieve only content of file with same date should get added. For example if the date is mentioned 08_07_2021 in test_dev and test_prod then only these two file content should get added and it should show the result and same for the files with date 09_07_2021 as well. We should be getting separate result after the addition of the addition.

Please Note: Also we will be getting these files every day. hence the date and month range will varies in each file and no way we can't change the file name now

Is there any way can we achieve this task or if someone can help us with the respective splunk query will much help.

Please assist.

score 0 · Answer 1 · answered Jul 09 '21 at 13:57

The trick is to extract the date and use that as a grouping field.

eventtype="metric:sample:example" source="test_dev_.json" OR source="test_prod_.json" 
| rex field=source "_(?<groupBy>\d\d_\d\d_\d\d\d\d)"
| stats list(source) as sources sum(number_of_car) as "# Total_Car", 
  sum(Parked_cars) as "# Stopped_Cars", sum(Buses) as "# Total_Bus", 
  sum(Parked_buses) as "# Stopped_Buses" by groupBy
| fields - groupBy
| table sources "# Total_Car" "# Stopped_Cars" "# Total_Bus" "# Stopped_Buses"
| addcoltotals

Required assistance how to add specifics source file with same string (date) in file name in splunk query

1 Answers1