Generate Stats from 3 indexes.

[new2splunk1]

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?

[gcusello]

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

[aasabatini]

great job! @gcusello