How to join 2 splunk searches to filter data

[sekhar463]

Hi Team,

I have 2 splunk searches in which i want to exclude of hostname in first search matches with Node field in the 2nd search.

how can i modify for joining this 2 searches to exclude hostname.

common field is hostname field in first one and it will be as Node field in the 2nd search 

index=_internal sourcetype=splunkd source="/opt/splunk/var/log/splunk/metrics.log" group=tcpin_connections os=Windows
| dedup hostname
| eval age=(now()-_time)
| eval LastActiveTime=strftime(_time,"%y/%m/%d %H:%M:%S")
| eval Status=if(age< 3600,"Running","DOWN")
| rename age AS Age
| eval Age=tostring(Age,"duration")
| lookup 0010_Solarwinds_Nodes_Export
Caption as hostname
OUTPUT
Application_Primary_Support_Group AS CMDB2_Application_Primary_Support_Group,
Application_Primary AS CMDB2_Application_Primary,
Support_Group AS CMDB2_Support_Group
NodeID AS SW2_NodeID
Enriched_SW AS Enriched_SW2
Environment AS CMDB2_Environment
| eval Assign_To_Support_Group=if(Assign_To_Support_Group_Tag="CMDB_Support_Group", CMDB2_Support_Group, CMDB2_Application_Primary_Support_Group)
| table _time, hostname,sourceIp, Status, LastActiveTime, Age, SW2_NodeID,Assign_To_Support_Group, CMDB2_Support_Group,CMDB2_Environment
|where Status="DOWN" AND NOT isnull(SW2_NodeID) AND CMDB2_Environment="Production"
| sort 0 hostname

index=ivz_em_solarwinds source="solwarwinds_query://Test_unmanaged_Nodes_Data"
| table Node Account Status From Until
| dedup Node

[sekhar463]

This is not working, the second search has one field StatusDescription, i want to add this using common field Name and host in 1st search

1st search:

```Table on Dashboard = M3_PROD_splunk__agent__universal_forwarder_status_is_down```
index=_internal sourcetype=splunkd source="/opt/splunk/var/log/splunk/metrics.log" group=tcpin_connections os=Windows
| dedup hostname
| eval age=(now()-_time)
| eval LastActiveTime=strftime(_time,"%y/%m/%d %H:%M:%S")
| eval Status=if(age< 3600,"Running","DOWN")
| rename age AS Age
| eval Age=tostring(Age,"duration")
| lookup 0010_Solarwinds_Nodes_Export
Caption as hostname
OUTPUT
Application_Primary_Support_Group AS CMDB2_Application_Primary_Support_Group,
Application_Primary AS CMDB2_Application_Primary,
Support_Group AS CMDB2_Support_Group
NodeID AS SW2_NodeID
Enriched_SW AS Enriched_SW2
Environment AS CMDB2_Environment
| eval Assign_To_Support_Group=if(Assign_To_Support_Group_Tag="CMDB_Support_Group", CMDB2_Support_Group, CMDB2_Application_Primary_Support_Group)
| where Status="DOWN" AND NOT isnull(SW2_NodeID) AND (CMDB2_Environment="Production" OR CMDB2_Environment="PRODUCTION")
```| table _time, hostname,sourceIp, Status, LastActiveTime, Age, SW2_NodeID,Assign_To_Support_Group, CMDB2_Support_Group,CMDB2_Environment```
| table _time, hostname,sourceIp, Status, LastActiveTime, Age, Assign_To_Support_Group, CMDB2_Environment

2nd search :

index=index_name sourcetype="nodes"
| lookup lookupfile1 Name OUTPUTNEW
| dedup Caption
| table Caption StatusDescription UnManaged UnManageFrom UnManageUntil
| search UnManaged=true
| eval UnManageUntil = strftime(strptime(UnManageUntil, "%Y-%m-%dT%H:%M:%S.%QZ"), "%Y-%m-%d %H:%M:%S")
| eval UnManageFrom = strftime(strptime(UnManageFrom, "%Y-%m-%dT%H:%M:%S.%QZ"), "%Y-%m-%d %H:%M:%S")
| eval UnManageUntil = coalesce(UnManageUntil, "NOT SET") ```replaces any null values in the "UnManageUntil" field with NOT SET```
| sort -UnManageFrom ```sorts the events in descending order based on the "UnManageFrom" field```

[sekhar463]

not getting status field from 2nd search 
Not showing any results.

actually i want to add status column based on the 2nd search results in the first.

if any hostname is matches with node name in the 2nd comun then it show the respective status or not there it show as null for status 

[gcusello]

Hi @sekhar463,

let me understand: you have to perform a join to extract the status field from the second search using as keys Node=hostname, is it correct?

If this is your requirement, appendcols isn't the solution, you could use "join" but I don't like it because there's the limit of 50,000 results in the second search and because it's very slow.

You should correlate events using stats, something like this:

(index=_internal sourcetype=splunkd source="/opt/splunk/var/log/splunk/metrics.log" group=tcpin_connections os=Windows) OR (index=ivz_em_solarwinds source="solwarwinds_query://Test_unmanaged_Nodes_Data")
| rename hostname=coalesce(hostname,Node)
| stats latest(_time) AS _time values(status) AS status BY hostname
| eval age=(now()-_time)
| eval LastActiveTime=strftime(_time,"%y/%m/%d %H:%M:%S")
| eval Status=if(age< 3600,"Running","DOWN")
| rename age AS Age
| eval Age=tostring(Age,"duration")
| lookup 0010_Solarwinds_Nodes_Export Caption as hostname OUTPUT Application_Primary_Support_Group AS CMDB2_Application_Primary_Support_Group,
Application_Primary AS CMDB2_Application_Primary, Support_Group AS CMDB2_Support_Group NodeID AS SW2_NodeID Enriched_SW AS Enriched_SW2
Environment AS CMDB2_Environment 
| eval Assign_To_Support_Group=if(Assign_To_Support_Group_Tag="CMDB_Support_Group", CMDB2_Support_Group, CMDB2_Application_Primary_Support_Group)
| table _time, hostname,sourceIp, Status, LastActiveTime, Age, SW2_NodeID,Assign_To_Support_Group, CMDB2_Support_Group,CMDB2_Environment
| where Status="DOWN" AND NOT isnull(SW2_NodeID) AND CMDB2_Environment="Production"
| sort 0 hostname

please see the approach, if some field is missing, add it to the stats command.

ciao.

Giuseppe

[sekhar463]

Not showing any results.

actually i want to add status column based on the 2nd search results in the first.

if any hostname is matches with node name in the 2nd comun then it show the respective status or not there it show as null for status 

[gcusello]

Hi @sekhar463,

I suppose that "Node" from the second search is the hostname of the first and that you want to use the Node from the second as kay to filter the first search.

If this is true, you can use the second search as a subsearch of the first, renaming the field, something like this:

index=_internal sourcetype=splunkd source="/opt/splunk/var/log/splunk/metrics.log" group=tcpin_connections os=Windows [ search index=ivz_em_solarwinds source="solwarwinds_query://Test_unmanaged_Nodes_Data" | table Node Account Status From Until | dedup Node | rename Node AS hostnae | fields hostname ]
| dedup hostname
| eval age=(now()-_time)
| eval LastActiveTime=strftime(_time,"%y/%m/%d %H:%M:%S")
| eval Status=if(age< 3600,"Running","DOWN")
| rename age AS Age
| eval Age=tostring(Age,"duration")
| lookup 0010_Solarwinds_Nodes_Export Caption as hostname OUTPUT Application_Primary_Support_Group AS CMDB2_Application_Primary_Support_Group,
Application_Primary AS CMDB2_Application_Primary, Support_Group AS CMDB2_Support_Group NodeID AS SW2_NodeID Enriched_SW AS Enriched_SW2
Environment AS CMDB2_Environment 
| eval Assign_To_Support_Group=if(Assign_To_Support_Group_Tag="CMDB_Support_Group", CMDB2_Support_Group, CMDB2_Application_Primary_Support_Group)
| table _time, hostname,sourceIp, Status, LastActiveTime, Age, SW2_NodeID,Assign_To_Support_Group, CMDB2_Support_Group,CMDB2_Environment
| where Status="DOWN" AND NOT isnull(SW2_NodeID) AND CMDB2_Environment="Production"
| sort 0 hostname

This solution has only one limitation: the subsearch can have max 50,000 results.

Ciao.

Giuseppe

[sekhar463]

i have tried with appendcols command but not getting column from sub search 


index=_internal sourcetype=splunkd source="/opt/splunk/var/log/splunk/metrics.log" group=tcpin_connections os=Windows
| dedup hostname
| eval age=(now()-_time)
| eval LastActiveTime=strftime(_time,"%y/%m/%d %H:%M:%S")
| eval Status=if(age< 3600,"Running","DOWN")
| rename age AS Age
| eval Age=tostring(Age,"duration")
| lookup 0010_Solarwinds_Nodes_Export
Caption as hostname
OUTPUT
Application_Primary_Support_Group AS CMDB2_Application_Primary_Support_Group,
Application_Primary AS CMDB2_Application_Primary,
Support_Group AS CMDB2_Support_Group
NodeID AS SW2_NodeID
Enriched_SW AS Enriched_SW2
Environment AS CMDB2_Environment
| eval Assign_To_Support_Group=if(Assign_To_Support_Group_Tag="CMDB_Support_Group", CMDB2_Support_Group, CMDB2_Application_Primary_Support_Group)
| table _time, hostname,Status, sourceIp, LastActiveTime, Age, SW2_NodeID, Assign_To_Support_Group, CMDB2_Support_Group, CMDB2_Environment
| appendcols
[search index=ivz_em_solarwinds source=solwarwinds_query://Solarwinds_PROD_unmanaged_Nodes_Data
| table Node Account Status From Until
| dedup Node]