How to write a search to match and translate a field between two source types?

[cpalicensing]

I have a source type full of data with cryptic username fields. These usernames translate to human readable usernames via a SQL table that I pull into Splunk under a different source type (different log source). I know the correct way may be to use lookup tables, but is there a way in search where I can match and translate a field on the fly between two source types?

Thanks!

[somesoni2]

You can do like this (assuming sourcetype1 is cryptic username field called username, sourcetype2 has fields username (cryptic) and h_username)

sourcetype=sourcetype1 OR sourcetype=sourcetype2 | stats values(sourcetype) as sourcetypes values(h_username) as h_username by username | where mvcount(sourcetypes)=2 

Or (slow performance)

sourcetype=sourcetype1 | join type=left username [search sourcetype2 | stats count by username h_username | fields - count]