Cannot figure out subsearch

[Sean_oldR3dF0x]

I am struggling with subsearches and getting and correlating data in a single output.

I need to figure out which users are using external devices. I have two indexes:

1. AD authentication logs (computer name and user-id)
2. Logs for device activity (computer name only)

The device activity logs only reports the computer names and I want to have a single table that lists the computer name and the user names along with additional fields from the activity logs.

I have the following search:

eventtype=device_activity_index sourcetype=syslog_device_control  ExternalDeviceType=USB*
[search index="windows_dc" Source_Workstation!="server-*"
| fields Source_Workstation,user]
| table _time, Tenant, EventName, DeviceName, Source_Workstation, user,ExternalDeviceType, ExternalDeviceName, ExternalDeviceVendorID, ExternalDeviceProductID, ExternalDeviceSN, ZoneNames

Each search on their own works just fine and returns results. I have specified a specific computer name (Source_Workstation for AD and DeviceName for the activity log) for both searches and confirmed that when they are individually run both indexes contains logs for the same system.

 I have tried using

| append [search …] as well as | where DeviceName=[ search …] and I get 0 results.

As I mentioned before, I have been struggling with getting subsearches to work and despite reading the Splunk documentation, Googling, and YouTube videos something is just not clicking. I am just not sure what is not clicking.

Any help on what I could try to get the above search to work would be greatly appreciated.

[ITWhisperer]

[search index="windows_dc" Source_Workstation!="server-*"
| fields Source_Workstation,user]

effective becomes

( ( Source_Workstation="WorkstationA" AND user="UserX" ) OR ( Source_Workstation="WorkstationA" AND user="UserY") )

That is for each row returned by the subsearch you get an AND condition of the values in the row and an OR condition between the rows.

Given that log device activity doesn't include users, you will always get no results.

Try pressing <ctrl><shift>E in the search to expand the query to see what you get.

You possibly want to join the two searches - rename the device field in one of the searches so it matches the name in the other search - or, append stats values by device field - again rename the device field so they are the same in both searches.

[Sean_oldR3dF0x]

I renamed the Source_Workstation field in the subsearch to DeviceName to match the outer search and left user. Zero results were returned.  I removed the user field from the subsearch and results were returned. 

The part I am not understanding is why this is happening. I thought subsearches were a way to correlate logs even if one set of logs does not contain some of the fields that another log has. 

I get what the subsearch returns and why. I just do not understand how to get results to show information from both logs when I am using a field that I thought would help link the two logs together with the common field of the computer name. My understanding was that you needed at least at least one field with the same information in order to correlate and link the two logs together to get a single result with matching data. 

[ITWhisperer]

The way you are using the subsearch is as a qualifier for the main search. What I mean by that is your subsearch is expanded before the main search and the results of the subsearch are then applied to the main search.

eventtype=device_activity_index sourcetype=syslog_device_control  ExternalDeviceType=USB* Device_name=device1 AND username=user1

Since username isn't a field in syslog_device_control, you will always get zero results by doing it that way.

You could try:

eventtype=device_activity_index sourcetype=syslog_device_control  ExternalDeviceType=USB*
| join type=left DeviceName [search index="windows_dc" Source_Workstation!="server-*"
| rename Source_Workstation as DeviceName
| fields DeviceName,user]
| table _time, Tenant, EventName, DeviceName, user,ExternalDeviceType, ExternalDeviceName, ExternalDeviceVendorID, ExternalDeviceProductID, ExternalDeviceSN, ZoneNames