Splunk Search Advanced
Search & Reporting Advanced Techniques
mvindex
One of the issues I solved was the "Account_Name" value having multiple entries.
Solution: ​| eval Account_Name=mvindex(Account_Name,-1)
index="windows" source="WinEventLog:security" | where Logon_Type IN (2,10)
| eval Account_Name=mvindex(Account_Name,-1)
| eval Date=strftime(_time, "%m-%d-%Y")
| eval Time=strftime(_time, "%H:%M:%S")
| Fields Logon_Type, Account_Name, ComputerName, Keywords, Date, Time, EventCode
| Table Logon_Type, Account_Name, ComputerName, Keywords, Date, Time, EventCode
| rename Account_Name as "Account"
| rename ComputerName As "Source Server"
| rename Keywords As "Logon Result"
| rename EventCode As "Event ID"
IN
Utilize the IN function tp only get logon types of 2,10 (Interactive & Remote Desktop)
index="windows" source="WinEventLog:security" | where Logon_Type IN (2,10)
| eval Account_Name=mvindex(Account_Name,-1)
| eval Date=strftime(_time, "%m-%d-%Y")
| eval Time=strftime(_time, "%H:%M:%S")
| Fields Logon_Type, Account_Name, ComputerName, Keywords, Date, Time, EventCode
| Table Logon_Type, Account_Name, ComputerName, Keywords, Date, Time, EventCode
| rename Account_Name as "Account"
| rename ComputerName As "Source Server"
| rename Keywords As "Logon Result"
| rename EventCode As "Event ID"
Windows Event Logon_Type
Logon Type 2: Interactive. A user logged on to this computer.
Logon type 3: Network. A user or computer logged on to this computer from the network.
Logon type 4: Batch. Where processes may be executing on behalf of a user without their direct intervention.
Logon type 5: Service. A service was started by the Service Control Manager.
Logon type 7: Unlock. This workstation was unlocked.
Logon type 8: NetworkCleartext. A user logged on to this computer from the network.
Logon type 9: NewCredentials. A caller cloned its current token and specified new credentials for outbound connections.
Logon type 10: RemoteInteractive. A user logged on to this computer remotely using Terminal Services or Remote Desktop.
Logon type 11: CachedInteractive. A user logged on to this computer with network credentials that were stored locally on the computer.
Splunk sort
Multiple sort columns in order and specify (+) ascending or (-) descending order
index = "details"
| Fields PSComputerName, FQDN, Domain, Operating_System, IP_Address, Manufacturer, C_Drive_Total, C_Drive_Free, RAM_Total, RAM_Free, Last_Reboot, _time
| Table PSComputerName, FQDN, Domain, Operating_System, IP_Address, Manufacturer, C_Drive_Total, C_Drive_Free, RAM_Total, RAM_Free, Last_Reboot, _time
| rename PSComputerName as Hostname
| rename IP_Address as "IP Address"
| rename C_Drive_Total as "C:\ Total"
| rename C_Drive_Free as "C:\ Free Space"
| rename Operating_System as "Operating System"
| sort Hostname -_time
Sorting it all out !
In this example the query to the right (top) has a sort where I get the results I wanted. I originally had the setup right (bottom) where my results are skewed and not in the correct sort order desired. Make sure you place the sort in the correct sequence. I wanted all the Enabled Systems together and they were not.
​
Incorrect Results:
| sort _time Enabled Name
Correct Results:
index="computerobjects" earliest = @d
| sort _time
| dedup CN
| sort -Enabled Name
| fields Name, DNSHostName, IPv4Address, DistinguishedName, Enabled, Description, Created, PasswordLastSet
| table Name, DNSHostName, IPv4Address, DistinguishedName, Enabled, Description, Created, PasswordLastSet
Create a Lookup Table
I wanted a way to check if all my systems were showing up in my Index so I supplied a static Lookup CSV file. This can be uploaded into Splunk and replaced as needed. Create a Lookup Table by selecting the settings menu and selecting Lookups \ Lookup Table Files.
​
In the top right select "New Lookup Table File"
View Lookup
Simply place the name of the file into the query widow to see your Lookup file data.
​
| inputlookup "ServerList.csv"
Lookup in Action
Now I can use this query I found on a forum and changed it to the fit my needs.
I have a CN value in my CSV header as well as a CN field in my index.
​
index="computerobjects" | stats count by CN | table CN | eval CN=upper(CN) | eval Observed=1
| append [|inputlookup "ServerList.csv" | table CN | eval Observed=0 ]
| stats max(Observed) as Observed by CN | where Observed=0