Multi sourcetype search for extracted value

[whipstash]

I am having some issues getting this to work correctly. It does not return all the results. I have different records in different sourcetypes under the same index.

sourcetypeA

eventID = computerName.sessionID

infoIWant1 = someinfo1

infoIWant2 = someinfo2

SourcetypeB's events are broken into events that I need to correlate.

sourcetypeB

event1-------------------------------------------------------

sessionID= sessionNo1

direction=receive

-----------------------------------------------------------------

event2--------------------------------------------------------

sessionID=sessionNo1

direction=send

-----------------------------------------------------------------

I attempted the below search using the transaction command to correlate the records in sourcetypeB.

index=INDEX sourcetype=sourcetypeA
| rex field=eventID "\w{0,30}+.(?<sessionID>\d+)"
| do some filter on infoIWant fields here
| join type=inner sessionID
[ search index=INDEX sourcetype=sourcetypeB
| transaction sessionID
| where eventcount==2
| fields sessionID duration ]
|  chart count by duration

[whipstash]

Thanks for your help Giusepe. This is helpful for getting the duration. However, I would also like to table the results from filtering the events in sourcetypeA and having the duration. This solution does not seem to merge the two resulting searches.

ex.

table _time computerName sessionID filteredInfoIWant1 filteredInfoIwant2 duration

[gcusello]

Hi @whipstash ,

add to the stats command, using the values option9 all the fields you need from both the searches:

index=INDEX sourcetype=sourcetypeA
| rex field=eventID "\w{0,30}+.(?<sessionID>\d+)"
| do some filter on infoIWant fields here
| append [ search 
     index=INDEX sourcetype=sourcetypeB
     | stats 
          count AS eventcount
          earliest(_time) AS earliest 
          latest(_time) AS latest 
          BY sessionID
     | eval duration=latest-earliest
     | where eventcount=2
     | fields sessionID duration field3 field4 ]
| stats 
     values(eventID) AS eventID
     values(duration) AS duration
     values(field1) AS field1
     values(field2) AS field2
     values(field3) AS field3
     values(field4) AS field4
     values(count) AS count
     BY sessionID

Ciao.

Giuseppe

[gcusello]

Hi @whipstash ,

don't use join command that's a very slow command, use a different approach:

index=INDEX sourcetype=sourcetypeA
| rex field=eventID "\w{0,30}+.(?<sessionID>\d+)"
| do some filter on infoIWant fields here
| append [ search 
     index=INDEX sourcetype=sourcetypeB
     | stats 
          count AS eventcount
          earliest(_time) AS earliest 
          latest(_time) AS latest 
          BY sessionID
     | eval duration=latest-earliest
     | where eventcount=2
     | fields sessionID duration ]
| stats 
     values(eventID) AS eventID
     values(duration) AS duration
     values(count) AS count
     BY sessionID

Please adapt this approach to your real situation.

Ciao.

Giuseppe