Splunk Search

How to write a search that sorts by field with a date format in its values?

lbogle
Contributor

Hello Splunkers,
I have a search that's coming up nicely but I need to refine the search further by sorting by a field name of Last Audit that happens to have dates in it in the format of 1/1/2011. I'd like to have my existing query but include something along the lines of "Last Audit"=>(1/1/2011). Basically, give me the results from anything since 1/1/2011. What do I need to add to create that functionality?
Thanks!

Tags (3)
0 Karma
1 Solution

somesoni2
Revered Legend

Try something like this (remove some filters as they were redundant)

index=test_assets  "System Type"="Laptop" OR "System Type"="Workstation" OR "Sub Type"=Workstation OR "Sub Type"=Laptop | rename "Asset Tag Number" as "Asset Tag" | rename "Sub Type" as "System Type" | eventstats values("Last Audit") as "Last Audit" by "Asset Tag" | where strptime("Last Audit","%m/%d/%Y") >= strptime("1/1/2011","%m/%d/%Y")| dedup "Asset Tag"| stats dc(Asset Tag) by source

View solution in original post

lbogle
Contributor

I switched the search up a little. Check it out and let me know if you think this should work:
index=test_assets source="C:\Splunk Test Assets\Altiris_hostnames.csv" | rename "HostName" as hostname | rename "System Name" as hostname | where strptime("Last Audit","%m/%d/%Y") >= strptime("1/1/2014","%m/%d/%Y") | replace "C:\Splunk Test Assets\Altiris_hostnames.csv" with Altiris in source | eval hostname=lower(hostname) | dedup hostname | stats dc(hostname)

0 Karma

somesoni2
Revered Legend

Try something like this (remove some filters as they were redundant)

index=test_assets  "System Type"="Laptop" OR "System Type"="Workstation" OR "Sub Type"=Workstation OR "Sub Type"=Laptop | rename "Asset Tag Number" as "Asset Tag" | rename "Sub Type" as "System Type" | eventstats values("Last Audit") as "Last Audit" by "Asset Tag" | where strptime("Last Audit","%m/%d/%Y") >= strptime("1/1/2011","%m/%d/%Y")| dedup "Asset Tag"| stats dc(Asset Tag) by source

lbogle
Contributor

index=test_assets NOT "System Type"="Server" NOT "System Type"="Virtual" NOT "System Type"="Thin Client" NOT "System Type"="Blanks" "System Type"="Laptop" OR "System Type"="Workstation" OR "Sub Type"=Workstation OR "Sub Type"=Laptop | rename "Asset Tag Number" as "Asset Tag" | rename "Sub Type" as "System Type" | dedup "Asset Tag" | stats dc(Asset Tag) by source

I'm just not sure how to identify that "Last Audit" field (which contains a series of dates) in the 1/1/2001 format and search by anything newer than a certain date.

0 Karma

lbogle
Contributor

Summary: There are two asset databases represented by two .csv files that are fed into a single index. The search excludes several asset types, focuses on Laptops and Workstations and searches by a common "Asset Tag" field between them. There is a single date field in one of the .csv files I am trying to filter by so that only assets counts are done after that time range are represented.

0 Karma

somesoni2
Revered Legend

You can apply filter for field "Last Audit" to be greater than or equal to your date. Something like this
Assuming "Last Audit" is string.

....| where strptime("Last Audit","%m/%d/%Y") >= strptime("1/1/2011","%m/%d/%Y")

If post your current query, we can suggest a better solution(if any 🙂 ).

Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...