Splunk Search

How to join the indexes to show the changed information and the status of the PIR?

servus_kkozoriz
Engager

I have 3 indexes that I need to join.  

One index is the changes that we have in created in our Service Management tool. The second index is the Post Implementation Reviews (PIR's).  The third index links the two tables together.

Change Table

servus_kkozoriz_0-1649166911395.png

PIR Table

servus_kkozoriz_2-1649167022467.png

Link Table

servus_kkozoriz_3-1649167108681.png

The SourceId is the change RecId, TargetID is the PIR RecId.  What I would like to do is join the indexes so that I can show the change information and the status of the PIR.  What I have so far displays only the change information.

index=index_prod_sql_ism_change * | rename Owner as SamAccountName | lookup AD_Lookup SamAccountName OUTPUT DisplayName, Department |dedup ChangeNumber
| table ChangeNumber, Status, TypeOfChange, Priority, DisplayName, OwnerTeam, Category, ScheduledStartDate, ScheduledEndDate
| sort ChangeNumber

Can someone please assist.

Thanks

Labels (2)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

Fieldnames are case-sensitive so try

 

| eval TargetId=if(index="index_prod_sql_ism_pir",RecId,TargetId)

| eventstats values(PIRStatus) as PIRStatus by TargetId

 

 

View solution in original post

0 Karma

servus_kkozoriz
Engager

So have  been able to get the data into a table.  The only issue that I am now having is that the PIR Status is displaying the status from the Change index.  What the table needs to display is the change status and the PIR status which are 2 different indexes.  In the indexes the values are named the same.

(index=index_prod_sql_ism_change_v2 OR index=index_prod_sql_ism_pir OR index=index_prod_sql_ism_fusionlink) * * *
| rename Owner as SamAccountName
| lookup AD_Lookup SamAccountName OUTPUT DisplayName, Department
| eval SourceId=if(index="index_prod_sql_ism_change_v2",RecId,SourceId)
| eval TargetId=if(index="index_prod_sql_ism_pir",RecId,TargetId)
| eval PIRStatus=if(index="index_prod_sql_ism_pir",null(),Status)
| eventstats values(TargetId) as TargetID by SourceId
| eventstats values(PIRStatus) as PIRStatus by TargetId
| where index="index_prod_sql_ism_change_v2" or index="index_prod_sql_ism_pir"
| dedup ChangeNumber
| table ChangeNumber, Status, Subject, TypeOfChange, Priority, DisplayName, OwnerTeam, Category, ScheduledStartDate, ScheduledEndDate, PIRStatus
| sort ChangeNumber
| rename ChangeNumber AS Change, Subject AS Summary, TypeOfChange AS "Type of Change", DisplayName AS Owner, OwnerTeam AS "Owner Team", ScheduledStartDate AS "Scheduled Start Date", ScheduledEndDate AS "Scheduled End Date", PIRStatus AS "PIR Status"

 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

How about doing it this way so that PIRStatus is set to Status if the index is *_pir, and afterwards, Status is set to null() is the index is *_pir (as it has already been copied to PIRStatus

| eval PIRStatus=if(index="index_prod_sql_ism_pir",Status,null())
| eval Status=if(index="index_prod_sql_ism_pir",null(),Status)
0 Karma

servus_kkozoriz
Engager

If I add the code below, The table does not populate under PIRStatus column.   The status is from the PIR index and is linked to the Change through the FusionLink index.  I have provided a screenshot of the table.

Not really sure what I am missing.

 

(index=index_prod_sql_ism_change_v2 OR index=index_prod_sql_ism_pir OR index=index_prod_sql_ism_fusionlink) * * *
| rename Owner as SamAccountName
| lookup AD_Lookup SamAccountName OUTPUT DisplayName, Department
| eval SourceId=if(index="index_prod_sql_ism_change_v2",RecId,SourceId)
| eval TargetId=if(index="index_prod_sql_ism_pir",RecId,TargetId)
| eval PIRStatus=if(index="index_prod_sql_ism_pir",Status,null())
| eval Status=if(index="index_prod_sql_ism_pir",null(),Status)
| eventstats values(TargetId) as TargetID by SourceId
| eventstats values(PIRStatus) as PIRStatus by TargetId
| where index="index_prod_sql_ism_change_v2"
| dedup ChangeNumber
| table ChangeNumber, Status, Subject, TypeOfChange, Priority, DisplayName, OwnerTeam, Category, ScheduledStartDate, ScheduledEndDate, PIRStatus
| sort ChangeNumber
| rename ChangeNumber AS Change, Subject AS Summary, TypeOfChange AS "Type of Change", DisplayName AS Owner, OwnerTeam AS "Owner Team", ScheduledStartDate AS "Scheduled Start Date", ScheduledEndDate AS "Scheduled End Date", Status1 AS "PIR Status"

 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

As I mentioned before, field names are case sensitive - try updating this line

| eventstats values(TargetId) as TargetId by SourceId
0 Karma

servus_kkozoriz
Engager

I have gotten the above to work.  How do I show the PIR status in the table?  

servus_kkozoriz_0-1649171784348.png

 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

It doesn't look like you are searching the PIR (or Link for that matter) index - try starting the search with this

(index=index_prod_sql_ism_change OR index=index_prod_sql_ism_pir OR index=index_prod_sql_ism_link) | ...
0 Karma

servus_kkozoriz
Engager

Sorry, just not getting what you are saying.  I have added the search to the top of the query but it is still not showing the PIR status.

(index=index_prod_sql_ism_change OR index=index_prod_sql_ism_pir OR index=index_prod_sql_ism_fusionlink)
| rename Owner as SamAccountName
| lookup AD_Lookup SamAccountName OUTPUT DisplayName, Department
| eval SourceId=if(index="index_prod_sql_ism_change",RecId,SourceId)
| eval TargetId=if(index="index_prod_sql_ism_pir",RecId,TargetID)
| eval PIRStatus=if(index="index_prod_sql_ism_pir",Status,null())
| eventstats values(TargetId) as TargetId by SourceId
| eventstats values(PIRStatus) as PIRStatus by TargetID
| where index="index_prod_sql_ism_change"
| dedup ChangeNumber
| table ChangeNumber, Status, TypeOfChange, Priority, DisplayName, OwnerTeam, Category, ScheduledStartDate, ScheduledEndDate, PIRStatus
| sort ChangeNumber

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Fieldnames are case-sensitive so try

 

| eval TargetId=if(index="index_prod_sql_ism_pir",RecId,TargetId)

| eventstats values(PIRStatus) as PIRStatus by TargetId

 

 

0 Karma

servus_kkozoriz
Engager

Still no data.  I am thinking that somehow the tables are not linking or joining correctly.

 

0 Karma

servus_kkozoriz
Engager

It is now working.  Had to change the order:

| eval PIRStatus=if(index="index_prod_sql_ism_pir",null(),Status)

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

One way to do this is to search all three indexes and copy PIR data and Change Recid based on which index the event belongs to. Then you can use eventstats to "join" the information

| makeresults
| eval Status="Implemented"
| eval TypeOfChange="Standard"
| eval Priority=5
| eval Category="Server"
| eval Recid="3CCB"
| eval index="Change"
| append
    [| makeresults
    | eval Status="Completed"
    | eval Recid="BAED"
    | eval index="PIR"]
| append
    [| makeresults
    | eval SourceId="3CCB"
    | eval TargetId="BAED"
    | eval Recid="EA8E"
    | eval index="Link"]
``` The lines above create dummy data based on your examples (to be replaced by your search of all three indexes ```
| eval SourceId=if(index="Change",Recid,SourceId)
| eval TargetId=if(index="PIR",Recid,TargetId)
| eval PIRStatus=if(index="PIR",Status,null())
| eventstats values(TargetId) as TargetId by SourceId
| eventstats values(PIRStatus) as PIRStatus by TargetId
| where index="Change"
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...