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