topic Re: Delete rows in a CSV lookup file in Splunk Search

Delete rows in a CSV lookup file

Sasquatchatmars — Tue, 29 Sep 2020 11:51:22 GMT

Hi all,

I have succesfully made a search to populate a CSV file thanks to @gcusello , this file lets me add Usernames and timestamps to monitor their last succesfull logon. Now after a certain time i would like to delete some rows without overwriting the file. If possible i want to check in the AD if the user list is the same as in the CSV file. If a certain user is not in the AD anymore, that usernames row needs to be deleted in the CSV file. Can anyone help me create a search to delete those rows if this is possible? Otherwise i will have to do it manually or via an other script.

This is an example of the CSV file that is generated.

Time | User --------------------------------------------- 1601341200 | User_Alpha 1601348400 | User_Beta 1601355600 | User_Charlie

Thank you very much,

Sasquatchatmars

Re: Delete rows in a CSV lookup file

gcusello — Tue, 29 Sep 2020 12:39:18 GMT

Hi @Sasquatchatmars,

you have to run a search that takes all the rows fron the lookup and filter them with the AD.

In other words you have to extract the user list from AD putthing this list e.g. in a lookup and then filter the main lookup, something like this:

Cao.

Giuseppe

Re: Delete rows in a CSV lookup file

Sasquatchatmars — Tue, 29 Sep 2020 13:01:57 GMT

Hi @gcusello ,

If I understand correctly, I will have to do this in the following order?

Search the AD to retrieve all usernames
Put the result in a lookup csv file
Apply your search to filter out the unwanted users.

If I'm doing it with your search it isn't supposed to delete the other entries, right?

Thank you,

Sasquatchatmars

Re: Delete rows in a CSV lookup file

gcusello — Tue, 29 Sep 2020 14:03:30 GMT

Hi @Sasquatchatmars,

You have two way to proceed:

if you need a quick answer, the extraction od AD users in a lookup is the best approach,
if you have to update your main lookup once a day you don't need to pass through the lookup.

if you have a search to query AD (e.g. using the SA-LdapSearch), you don't need to pass for a lookup you can also use it in the search.

This depends on the response time you're waiting for, in other words, if you have to run this search once a day to update your main lookup, you can also directly use the Ldap extraction without passing through the lookup, if instead you want a search that quickly answers you have to pass for the lookup because the AD extraction is really slow.

Ciao.

Giuseppe

Re: Delete rows in a CSV lookup file

Sasquatchatmars — Wed, 30 Sep 2020 06:47:15 GMT

Hi @gcusello,

I don't need a really fast query, it will run every week or every month at midnight when nothing else runs. I already noticed that it took quit some time for a search to have results. Is it possible for you to give me the search needed to update the CSV file then? It has only been a couple of weeks for me, so I'm still learning.
So the CSV file can not loose any fields if the are no changes and if there is a user delete in AD, it needs to be deleted in my CSV file that contains the users and logon timestamps.

Thank you!

Sasquatchatmars

Re: Delete rows in a CSV lookup file

gcusello — Wed, 30 Sep 2020 07:24:17 GMT

Hi @Sasquatchatmars,

as I said, you have to install the SA-LdapSearch (https://splunkbase.splunk.com/app/1151/) or the Add-on for LDAP (https://splunkbase.splunk.com/app/1852/#/details) that gives you a search to extract data from Active Directory.

then you have to run a search like this:

Ciao.

Giuseppe

Re: Delete rows in a CSV lookup file

Sasquatchatmars — Wed, 30 Sep 2020 09:49:33 GMT

Hi @gcusello,

I have already installed SA-LdapSearch. I used exactly your search but it returned "no results found".

Sasquatchatmars

Re: Delete rows in a CSV lookup file

gcusello — Wed, 30 Sep 2020 10:12:32 GMT

Hi @Sasquatchatmars,

at first see if the LDAP query gives you results,

then compare them with the lookup ones to see if the format is the same.

Ciao.

Giuseppe

Re: Delete rows in a CSV lookup file

Sasquatchatmars — Wed, 30 Sep 2020 11:50:37 GMT

Hi @gcusello,

I verified everything, I separated the searches to see if they work individually and I have no problem.
Could it be that there are no results because he doesn't see any change between both results?

Thank you,

Sasquatchatmars

Re: Delete rows in a CSV lookup file

gcusello — Wed, 30 Sep 2020 11:57:09 GMT

Hi @Sasquatchatmars,

To check this, manually add a row in the lookup and run again the search.

Ciao.

Giuseppe

Re: Delete rows in a CSV lookup file

Sasquatchatmars — Wed, 30 Sep 2020 12:46:53 GMT

Hi @gcusello ,

I tried it but it but the search actually deleted every input in the file.

Sasquatchatmars