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

[servus_kkozoriz]

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

PIR Table

Link Table

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

[servus_kkozoriz]

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"

[ITWhisperer]

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)

[servus_kkozoriz]

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"

[ITWhisperer]

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

| eventstats values(TargetId) as TargetId by SourceId

[servus_kkozoriz]

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

[ITWhisperer]

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) | ...

[servus_kkozoriz]

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

[ITWhisperer · Accepted solution]

Fieldnames are case-sensitive so try

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

| eventstats values(PIRStatus) as PIRStatus by TargetId

[servus_kkozoriz]

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

[servus_kkozoriz]

It is now working.  Had to change the order:

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

[ITWhisperer]

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"