Hello members,
I am new to Splunk and able to produce simple stats using STATS count by command but looking for direction on how to combine indexes and generate stats.
We need to generate monthly stats from 3 systems residing in different indexes. The events across systems/indexes can be joined using TRAN_ID. Transactions are passed from System 1 to System 2 and system 2 to System 3.
The purpose is to do recon every month and identify the number of transactions initiated from System 1 that didn’t make it to System 2 or System 3.
Listed below are the fields that are present in different systems/indexes. The transactions codes and status are different across systems and it needs to be shown on the report.
System 1 (index= ind1)
Timestamp
Month_&_Year – to be extract from timestamp field
TRAN_ID
Tran_Name
Sys1_Transaction_Status_Code
System 2 (index= ind2)
Timestamp
TRAN_ID
Sys2_Transaction_Code
Sys2_Transaction_Status
System 3 (index= ind3)
Timestamp
TRAN_ID
Sys3_Transaction_Code
Sys3_Transaction_Status
Stats Layout:
Transaction counts – 10 and 8 indicate transactions initiated from system 1 made it to system 2 but didn’t make it to system 3. Therefore the cells are highlighted in colours just to indicate they contain no values.
Transaction counts – 5 and 3 indicate transactions initiated from system 1 failed so didn’t make it system 2. Therefore, the cells are highlighted in colours just to indicate they contain no values.
In SQL world, we achieve the desired results by using LEFT JOIN and GROUP BY clause and I know Splunk is not a relational database but yet powerful. In the community, I also read that joins are not recommended. Please advise how do I generate a similar stats in Splunk?
HI @new2splunk1,
I cannot test your situation because I haven't your data, but i'm describing the approach to solve your need, try something like this:
index=ind1 OR index=ind2 OR index=ind3
| bin span=1mon _time
| stats
earlier(_time) AS Timestamp
values(Tran_Name) AS Tran_Name
values(Sys1_Transaction_Status_Code) AS Sys1_Transaction_Status_Code
values(Sys2_Transaction_Code) AS Sys2_Transaction_Code
values(Sys2_Transaction_Status) As Sys2_Transaction_Status
values(Sys3_Transaction_Code) AS Sys3_Transaction_Code
values(Sys3_Transaction_Status) AS Sys3_Transaction_Status
count AS Transaction_Count
BY TRAN_ID
| eval Timestamp=strftime(Timestamp,"%m %Y")
Ciao.
Giuseppe
great job! @gcusello