topic Re: how to assemble 2 distinct splunk searches into one? in Splunk Search

how to assemble 2 distinct splunk searches into one?

amir_thales — Tue, 03 Apr 2018 15:58:33 GMT

hello everyone,

I just want to merge the 2 splunk searches.

In the first query, i have all information about mounting the usb key.
In the second query, i have the information about the unmounting the usb key.

The events that are used to mount and unmount the USB drive are not similar, so I want to add the unmount events to the mount event. You should know that in the unmount request I use the transaction command to group the log audit between them.

The first query (mount query😞

sourcetype="mtab_executer" OR source="unix:useraccounts"
| rex max_match=0 "(^|\n)(?<usb_key>\/\S+\s\/\S+)"
| eval user_id=coalesce(user_id, uid)
| eventstats latest(user) AS user BY user_id
| convert timeformat="%d/%m/%Y %H:%M:%S" ctime(_time) AS c_time
| stats latest(c_time) AS Mount, latest(user) AS user, latest(user_id) AS user_id, count BY usb_key

The second query (unmount query😞

sourcetype="linux_audit" | transaction startswith="type=SYSCALL" endswith="type=PATH"
| convert timeformat="%d/%m/%Y %H:%M:%S" ctime(_time) AS c_time
| stats latest(c_time) AS Umount, count BY name

I want to add the result of this search sourcetype="linux_audit" | transaction startswith="type=SYSCALL" endswith="type=PATH" to the result of this search sourcetype="mtab_executer" OR source="unix:useraccounts"

To summarize, all this I want to merge the 2 requests and display the table of the request 1 but I add the date of unmount of the key and more.

Thank you
Amir

Re: how to assemble 2 distinct splunk searches into one?

adonio — Tue, 03 Apr 2018 17:02:54 GMT

using |append or |join will do the trick however, looks like there is a better way to merge the two queries.
http://docs.splunk.com/Documentation/Splunk/7.0.3/SearchReference/Append
http://docs.splunk.com/Documentation/Splunk/7.0.3/SearchReference/Join
hope it helps for the meantine

Re: how to assemble 2 distinct splunk searches into one?

kmaron — Tue, 03 Apr 2018 17:05:16 GMT

I believe you can do that with a subsearch. Maybe something like this?

sourcetype="mtab_executer" OR source="unix:useraccounts" 
| rex max_match=0 "(^|\n)(?<usb_key>\/\S+\s\/\S+)" 
| eval user_id=coalesce(user_id, uid) 
| eventstats latest(user) AS user BY user_id 
| convert timeformat="%d/%m/%Y %H:%M:%S" ctime(_time) AS c_time 
| stats latest(c_time) AS Mount, latest(user) AS user, latest(user_id) AS user_id, count BY usb_key 
    [ search sourcetype="linux_audit" 
    | transaction startswith="type=SYSCALL" endswith="type=PATH" 
    | convert timeformat="%d/%m/%Y %H:%M:%S" ctime(_time) AS c_time 
    | stats latest(c_time) AS Umount, count BY name 
    | fields Umount ] 
| table Mount Umount user user_id usb_key

Re: how to assemble 2 distinct splunk searches into one?

amir_thales — Wed, 04 Apr 2018 08:40:18 GMT

hello kmaron and adonio,

kmaron:

I had already tried with a sub-search but no event is displayed at the level of the sub-search I do not know why and yet I had followed the doc splunk.

I tried the request that you proposed to me but I have the same problem no result is displayed, I will try to find the problem.

adonio:

I thought I read in the doc concerning append and join that for the requests on many events, her 2 commands were slow and she consumed a lot of resources. That's why I prefer to go through a sub-search before using his commands.

Thank you
Amir

Re: how to assemble 2 distinct splunk searches into one?

amir_thales — Wed, 04 Apr 2018 09:08:34 GMT

sorry for the double post.

Adonio:

to use join you need a common field that binds the 2 queries unless I'm wrong.

Re: how to assemble 2 distinct splunk searches into one?

FrankVl — Wed, 04 Apr 2018 09:27:17 GMT

And you don't have such a common field? How do you then want to correlate the mounts and unmounts? Because that is what you want to achieve in the end, right?

Can you perhaps share some sample data (output of those 2 queries) and also show what you want the results to look like in the end?

Re: how to assemble 2 distinct splunk searches into one?

amir_thales — Tue, 29 Sep 2020 18:46:40 GMT

hello @FrankVI,

I'm not trying to correlate the information between her I just want to add the data from the second query with the first query and then I'll do a stats to group the data that interests me.

As I said above the data used for unmount and mount are different.

Data unmount:

For unmount data, I use audit logs that allows me to know when the key is removed and so in the table of the first query I just have to add the unmount date because I have almost all the information in the first request.

Data mount:

For data mount, I use the splunk plugin add-on for unix where I activated the script allowing me to generate events of / etc / passwd, I also generated events of a file system file ( mtab) via a script and I correlated the events of / etc / passwd with those of the script for the first request. And i use transaction command to group same events.

script event:

add-on event:

Wed Apr 4 12:10:15 CEST 2018 user=local_splunk password=x user_id=500 user_group_id=500 home=/home/local_splunk shell=/bin/bash

host =PFSplunk-Srv source =Unix:UserAccounts sourcetype = Unix:UserAccounts

thank you
amir

Re: how to assemble 2 distinct splunk searches into one?

amir_thales — Wed, 04 Apr 2018 15:07:27 GMT

sorry for the error.

I use transaction command in the data unmount and not in the data mount.

Re: how to assemble 2 distinct splunk searches into one?

elliotproebstel — Wed, 04 Apr 2018 15:07:43 GMT

Hey @amir_thales - Based on what I'm reading here and what I remember/understand from your other post, I'm going to suggest this:

sourcetype="linux_audit" 
| transaction startswith="type=SYSCALL" endswith="type=PATH" 
| convert timeformat="%d/%m/%Y %H:%M:%S" ctime(_time) AS c_time 
| stats latest(c_time) AS Umount, count BY name 
| rename name AS usb_key 
| append 
    [ sourcetype="mtab_executer" OR source="unix:useraccounts" 
    | rex max_match=0 "(^|\n)(?<usb_key>\/\S+\s\/\S+)" 
    | eval user_id=coalesce(user_id, uid) 
    | eventstats latest(user) AS user BY user_id 
    | convert timeformat="%d/%m/%Y %H:%M:%S" ctime(_time) AS c_time 
    | stats latest(c_time) AS Mount, latest(user) AS user, latest(user_id) AS user_id, count BY usb_key] 
| stats latest(Mount) AS Mount, latest(user) AS user, latest(user_id) AS user_id, latest(Umount) AS Umount, sum(count) AS count BY usb_key

If that doesn't return what you're looking for, I'd recommend stripping off that final stats call and see if the appended results look like what you expect. I'm pretty sure name in the first search represents the link to usb_key in the second search, and I've deliberately reversed the order of searches, because transaction is an expensive command that's more likely to time out (making it a bad candidate for a subsearch).

Re: how to assemble 2 distinct splunk searches into one?

amir_thales — Wed, 04 Apr 2018 15:55:11 GMT

hello @elliotproebstel,

firstly thanks for the help you gave me on the previous post.

I tested the request that you proposed to me and nothing appears. I am currently seeing what is wrong but at the level of the names you are right the name does well reference to the usb key.

thank you
Amir

Re: how to assemble 2 distinct splunk searches into one?

amir_thales — Wed, 04 Apr 2018 16:31:40 GMT

@elliotproebstel,

To use the append command, must there be a link between the two search query or not because I tested the 2 search queries 1 by 1 it works well but if I put the append command as in the above query nothing does not appear as it does not recognize my command, which is surprising to me that there is no error message to tell me what's wrong.

Here's what I have when I launch the command:

I stay on the beginning page.

Thank you
Amir

Re: how to assemble 2 distinct splunk searches into one?

elliotproebstel — Thu, 05 Apr 2018 01:43:27 GMT

It doesn't look like the search was even launched. You did click to start the search, right? What happens if you try removing the final stats command, as I suggested in my post? Can you show what that returns?

Re: how to assemble 2 distinct splunk searches into one?

amir_thales — Mon, 16 Apr 2018 09:55:34 GMT

hello @elliotproebstel,

Sorry to answer you so late. I had some complications in his last days.

yes, i clicked to start search in this picture.

When i delete the stats command nothing is returned, i have the same result as above.

Thank you
Amir

Re: how to assemble 2 distinct splunk searches into one?

amir_thales — Tue, 17 Apr 2018 14:01:22 GMT

hello @elliotproebstel,

I'm coming back to what I told you about the post above, NAME is not the same as USB_KEY because in the USB_KEY we have the name of the mount point and more than the NAME refers to the name of the usb key .

Thank you
Amir

Re: how to assemble 2 distinct splunk searches into one?

amir_thales — Tue, 17 Apr 2018 14:02:11 GMT

hello ,

I finally managed to find what I wanted.

Thank you
amir

Re: how to assemble 2 distinct splunk searches into one?

FrankVl — Tue, 17 Apr 2018 14:31:34 GMT

Care to share your solution? Then others can also benefit from it if they stumble upon your question 🙂

Re: how to assemble 2 distinct splunk searches into one?

amir_thales — Tue, 17 Apr 2018 14:39:31 GMT

hello Hello @FrankVI,

yes sorry

Thank you
Amir

Re: how to assemble 2 distinct splunk searches into one?

amir_thales — Tue, 17 Apr 2018 14:44:16 GMT

Here is the solution that takes what was proposed above @elliotproebstel and I added some command because I had a problem, the result did not display and it came from the fact that usb_key and name was not similar .

If it can be improved do not hesitate to propose your improvements.

solution:

Thank you
Amir

Re: how to assemble 2 distinct splunk searches into one?

elliotproebstel — Tue, 29 Sep 2020 19:03:37 GMT

Hi Amir, I think the reason your first two searches were not returning anything at all while that last search returns something has to do with the time window, not just the changed query. I see that the values for name and usb_key are not the same, so good change with not renaming name to usb_key - but the fact that you were getting no results at all before either means the search wasn't running (which is honestly what the picture seems to show) or maybe the search was timing out because you were running a transaction command over a 7 day window, which is really hard for Splunk to handle.

Re: how to assemble 2 distinct splunk searches into one?

amir_thales — Wed, 18 Apr 2018 08:47:16 GMT

hi elliot,

I'm on now the mistake comes from the fact that I thought at first that the fields name and usb_key were similar but I was wrong when I rectified that I get what I want.

Thank you for your assistance elliot and that of all those who participated.

Thank you
Amir