Delete rows in a CSV lookup file

[Sasquatchatmars]

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

[gcusello]

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:

| inputlookup your_lookup.csv
| search [ | inputlookup AD_lookup.csv | fields user ]
| table user Time
| outputlookup your_lookup.csv

Cao.

Giuseppe

[Sasquatchatmars]

Hi @gcusello ,

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

1. Search the AD to retrieve all usernames
2. Put the result in a lookup csv file
3. 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

[gcusello]

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

[Sasquatchatmars]

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

[gcusello]

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:

| inputlookup your_lookup.csv
| search [ | ldapsearch domain=your_domain search="(objectClass=user)" | fields user ]
| table user Time
| outputlookup your_lookup.csv

Ciao.

Giuseppe

[Sasquatchatmars]

Hi @gcusello,

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

Sasquatchatmars

[gcusello]

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

[Sasquatchatmars]

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

[gcusello]

Hi @Sasquatchatmars,

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

Ciao.

Giuseppe

[Sasquatchatmars]

Hi @gcusello ,

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

Sasquatchatmars