How to properly use inputlookup csv file to perform extended searches?

[awant68]

Hello, I am stuck on a query and need someone's help please.  The goal of the query is to perform a lookup on column A and B which is a list of hostnames and FQDN's that are the targeted scope to perform the extended lookup.  I need to find out what new local accounts have been created AND who created them.  OS scope would be Windows for now, however i will need to do this search on *NIX servers as well.

The query itself works, but i don't know if the input scope is being targeted for sure or what is the best practice method.  There are 2 columns im focused on in the csv, "name" and "fqdn".

I have done extensive research on this and one article mentions to put in [] brackets after the main query, but then another article states to put in the inputlookup query as first string and remaining questions next.  Let me know what is right/wrong or reasons why to do either way?

Here is my query:

sourcetype=wineventlog source="WinEventLog:Security" (EventCode=4720 OR EventCode=624)
| eval CreatedBy = mvindex(Account_Name,0)
| eval New_User = mvindex(Account_Name,1)
| search CreatedBy=*
| table _time ComputerName EventCode CreatedBy New_User name ip_address
| sort by ComputerName, _time
[|inputlookup Servers.csv |fields fqdn, name |lookup Servers fqdn AS ComputerName, name AS ComputerName ]

[awant68]

Thank you first of all for replying and helping on this issue.  What i am doing is using a csv file dumped out of a security tool that already has inventory of "name" or just netbios name and "fqdn" as FQDN of that same netbios name but just with the domain name added onto it of course.  My goal is to use this input csv file as the source scope of the lookup query to see what local accounts were created, by whom, and when, then matching that up against the csv scope list.  Hopefully that makes more sense.

So on the inputlookup csv called "Servers.csv" i need to use that list that contains 2 columns of data, one is "fqdn" and the other is "name".  I need to use these as primary lookups to match data in Splunk query Windows event code.

[awant68]

The query you sent is executing but the output results are not matching server names from my csv list.

Example in the csv i may have server1 thru server2000.  (Server1..Server2...Server3....Server2000) by name.  The output results are showing server3000,server3001,server3002, no rhyme or reason, just not matching up to servers in the csv list.  The csv list should be the primary lookup for hostname or fqdn.  So instead of me having to write a massive query of 10k server names or run hundreds of queries, im just wanting to use inputlookup file to set the stage for the lookup.  I hope this makes more sense and apologies if i didn't explain well the first time.  🙂

Can you help me with making the csv the authoritative lookup source by name or fqdn?  I dont need both, but at least fqdn minimum.  Name was just a bonus lookup.

[yuanliu]

Maybe you can start by explaining the logic of using that lookup.  Why do you need that lookup?  As written, the lookup is not affecting the results at all.  You can verify this by removing the entire [] block.  By the way, this block is a subsearch.  In your code example, the subsearch will return something equivalent to

( (ComputerName="computer1") OR (ComuterName="computer2") OR ... )

You can add any additional string in the end of sort command and have zero effect. (BTW, that word "by" following sort command also has zero effect because you have no field named "by".)

Second, you need to explain raw data.  What are in WinEventLog:Security?  What is the format of AccountName?  It looks like you really want to match ComputerName.  Does this field come with WinEventLog:Security?  What is the format of this this field?  Why you think it will match both "fqdn" and "name" in Servers.csv?  What is the content of that field "name" in the table command?  Does "name" come with WinEventLog:Security?

Third, you need to explain what the two fields "fqdn" and "name" are in that Servers.csv.  If I have to speculate, fqdn is a fully qualified domain name, and "name" is a Windows name.  I would assume that they carry different and incompatible formats, like

fqdn	name
server123.example.com	windows-a
server456.example.com	windows-b

If I speculate further, ComputerName carries the format of "name", not that of "fqdn", and that you want to output the FQDN corresponding to the value of that ComputerName.

If the speculation is correct, the search you are looking for could be

sourcetype=wineventlog source="WinEventLog:Security" (EventCode=4720 OR EventCode=624)
| eval CreatedBy = mvindex(Account_Name,0)
| eval New_User = mvindex(Account_Name,1)
| search CreatedBy=*
| table _time ComputerName EventCode CreatedBy New_User name ip_address
| sort by ComputerName, _time
| lookup Servers name AS ComputerName output fqdn

Lookup is the most efficient if the objective is as I speculated.  No need for subsearch.  But without data illustration and explanation of your logic, speculation can only go so far.  Hope this help clarify the way forward.

[awant68]

Answers..

Second, you need to explain raw data.  What are in WinEventLog:Security?  

--User account creation events.  Tells whom created a local account, when they created it and on what server they created it, and name of the local account.  Hence the 4720/624 event id's .

What is the format of AccountName?  

johndoe, johndoe, svcaccount1, svcaccount2, etc...

It looks like you really want to match ComputerName.  Does this field come with WinEventLog:Security?  

--Yes it is tied to the data in splunk as example like "server1.xyz.com"  So trying to match ComputerName with either "name" or "fqdn" from csv file to match them as a lookup match.

What is the format of this this field?  

--shown above.

Why you think it will match both "fqdn" and "name" in Servers.csv?  

--Because the database im querying is all logs from all of our Windows servers in Splunk and is mandatory for compliancy reasons.  The list of servers in the csv file MUST be in compliance and reporting Windows Security Event logs into Splunk.

What is the content of that field "name" in the table command?  

--disregard this, i made a mistake as i am renaming it to ComputerName.  What i mean is that i can remove that out of the table command because ComputerName will be the output i need. 

Does "name" come with WinEventLog:Security?

--No its only in the csv.  Sorry for the confusion.

Third, you need to explain what the two fields "fqdn" and "name" are in that Servers.csv.  If I have to speculate, fqdn is a fully qualified domain name, and "name" is a Windows name.  I would assume that they carry different and incompatible formats, like

fqdn	name
server123.example.com	windows-a
server456.example.com	windows-b

 --Yes correct this is the format and example.

input file looks like this

Fqdn	Name	Ip
Server1.abc.com	Server1	1.2.3.4
Server2.abc.com	Server2	1.2.3.5
Etc	Etc	etc

Input + existing splunk event codes 4720 or 624 =

= output like below matching fqdn or name as ComputerName.

_time	ComputerName	EventCode	CreatedBy	New_User	Ip_address
10:00am etc etc	Server1.abc.com	4720	Johnd	Svcacct34	1.2.3.4
xxxx	Server2.abc.com	4720	Josephb	Svcacct1234	1.2.3.5
xxx	Etc	Etc	Etc	Etc	etc

[yuanliu]

Thanks for the explanation.  I get the impression that

1. you don't want to see/process any events from servers not matching Fqdn or Name in that CSV file; and
2. you cannot predetermine if ComputerName in WinEventLog will match Fqdn or Name in that CSV file.  In other words, you want to try both.

Because I am unfamiliar with WinEventLog, I am still a little unclear how you expect AccountName to be an array of two strings.  But I can assume that the events are structured in JSON, XML, or CSV that Splunk automatically extracts.  In all cases, based on 1, I will focus on restricting the index search itself, and leave the rest of coding to you.  The strategy is to make a subsearch that produces a list of ComputerName's based on the content of the CSV. (You can lookup the linked document to see who subsearch affects results.)

sourcetype=wineventlog source="WinEventLog:Security" (EventCode=4720 OR EventCode=624) 
    [| inputlookup Servers.csv
    | eval ComputerName = mvappend(Fqdn, Name) ``` so we can use both values ```
    | fields ComputerName ``` so we only output field name that exists in index ```]

After this, you can perform all the magic that you originally intended.  Hope this helps.

[awant68 · Accepted solution]

Thanks again for your help and support.  The last search wasn't what i was looking for, but i did get some internal support from a cross team member.  Here is what i was looking for but i just need to modify the table outputs etc to clean it up.  Wanted to reply back so everyone can see and understand the concept.

I am needing to do a subsearch on the inputlookup file searching on a column called "Asset type...." for only Windows server in this example.  Then we rename and match up the key/column name in lookup csv file to internal Splunk value of "host" so all records will search as host so splunk doesnt get confused.  Host is the default name in our splunk server for Windows event logs hostname so need to match that up.  Rest is below.

index=wineventlog* EventCode=4720
[| inputlookup Inventory.csv
| search "Asset type / Class" = "Windows Server"
| rename "Asset Name" as host
| fields + host]
| where Account_Domain=host     ----to keep local account lookups not including domain accounts------
| eval Subject_Security_ID = mvindex(Security_ID,0)   
| table user, host, Subject_Security_ID   -----will need to add more here to get exactly what i need----