Splunk Search

Event correlation

miguel1423
Explorer

Hello,

I would like to display some VPN informations in splunk like username, host information, session id. My problem is that I cannot display username and host information in the same table, the user field doesn't exist??

below the search that I did.

I search for user with specifique application use and I try to catch the username, session_id and client_info_host but I get nothing

 

 

 

 

 

index="index_X" partition="/Common/XXXXXXXXXXXX:Common" | stats count by user session_id client_info_host | table session_id user client_info_host

 

 

 

 

 

 

If I remove the field "client_info_host" I get the result below. 

 

 

 

 

 

index="index_X" partition="/Common/XXXXXXXXX:Common" | stats count by user session_id | table session_id user client_info_host

 

 

 

 

 

miguel1423_0-1601283625714.png

 

If I filter only with the field "client_info_host" I don't get the value username on the filed with this event.

miguel1423_2-1601284048156.png

 

But for all events the commune value is the session_id, How can I collerate all fields from session_id ?

 

Regards,

 

Labels (5)
0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @miguel1423,

after a stats command you have only the fields in the stats, in your first case: user, session_id, client_info_host.

In the second and third example you don't use some fields in the BY clause, for this reason you don't have in the following table.

If you want them, you have to put them in the stats command, e.g. using values option, something like this:

index="index_X" partition="/Common/XXXXXXXXX:Common" 
| stats values(client_info_host) AS client_info_host count by user session_id
| table session_id user client_info_host

Ciao.

Giuseppe

0 Karma

miguel1423
Explorer

Hello thank you for your reply,

I tried your suggestion but I get the username and session id but not the client_info host 😕 😭

miguel1423_0-1601288850564.png

 

 

P.S: The  client_info host data is not in the same event that the username, but the session id is in all events.

Regards,

Miguel

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @miguel1423,

sorry I didn't see that there's a space between "client_info" and "host": are you meaning two fields or one field with space between?

Anyway, the approach is the one I described:

use values(field) AS field for all the fields that you haven't in BY clause and you want in table.

Beware to correctly write the field names (they are case sensistive).

Ciao.

Giuseppe

0 Karma

ITWhisperer
Ultra Champion

It looks like client_info_host hasn't been extracted. Can you find an event with this field? Does it also have the user and session id field?

index="index_X" partition="/Common/XXXXXXXXXXXX:Common" | stats count values(client_info_host) as client_info_host values(user) as user by session_id | table session_id user client_info_host
0 Karma

miguel1423
Explorer

Hello Thank you to answer,

Yes I can find event with this field but the username isn't in the same event. The only same value that I can find in all event is the session id field

 

Regards,

0 Karma

ITWhisperer
Ultra Champion

So did you try the query I posted?

index="index_X" partition="/Common/XXXXXXXXXXXX:Common" 
| stats count values(client_info_host) as client_info_host values(user) as user by session_id 
| table session_id user client_info_host

This stats should "join" these events by session_id giving you the values of client_info_host and user for each session_id

0 Karma

miguel1423
Explorer

Hello,

Cool, yes it's work thank you 😁 if I want tu show the connexion time Do I need tu insert the field "_time" after the "by session_id" ? and in the table

 

Regards

0 Karma

ITWhisperer
Ultra Champion

No, but you could add earliest(_time) as session_start

index="index_X" partition="/Common/XXXXXXXXXXXX:Common" 
| stats count values(client_info_host) as client_info_host values(user) as user earliest(_time) as session_start by session_id 
| table session_id user client_info_host session_start

If you do something similar with latest(_time) as session_end you could calculate the duration of the session, if that is what you are after 

0 Karma

miguel1423
Explorer

Hi,

I have a last request, now I'm going to try to get a specific message_id and his value "message"

I added the value in the stats count value as below but I get all message and message ID I can't sort by message id with the field "where" Do you have any idea about that ?

 

index="index_XX" partition="/Common/XXXXXXXX:Common" 
| stats count values(client_info_host) as client_info_host values(user) as user values(message) as message values(message_id) as message_id  by session_id 
| dedup user
| where message_id="0505" 
| table session_id user client_info_host message_id message

 

miguel1423_0-1601299790594.png

 

 

Regards,

 

Miguel

 

0 Karma

ITWhisperer
Ultra Champion
index="index_X" partition="/Common/XXXXXXXXXXXX:Common" | stats count values(client_info_host) as client_info_host by user session_id | table session_id user client_info_host
0 Karma
.conf21 CFS Extended through 5/20!

Don't miss your chance
to share your Splunk
wisdom in-person or
virtually at .conf21!

Call for Speakers has
been extended through
Thursday, 5/20!