Need help with Where for _time from lookup

[gabarrygowin]

Hi all,

So inherited a lookup table from former contractor and want to pull and display information based on what was written the last week.

On my own built app (with Somesoni2s help) I have this function working great and boss loves it and wants more. Struggling to get this working on the inherited app.

Working search (for reference):
| inputlookup GenAtomicsCheck.csv | where _time>=relative_time(now(),"-mon@mon") | search CheckStatus="Complete" AND CheckType="Monthly" | chart dc(CheckPerformed) AS Completed | eval percentage=Completed/1*100 | chart count by percentage*

Inherited app (with this search I see data, so I know I can pull from the lookup):
*| inputlookup ga_kvstore_lookup | eval KeyID = _key | table KeyID, AppName, AuditorName, AuditDateTime, ComplianceCheck, AuditUnixTime | sort -AuditUnixTime | fields KeyID, AppName, AuditorName, AuditDateTime, ComplianceCheck *

I try to add the simply where command and get no results, no error.

Thoughts?

Thanks!

[woodcock]

You have to have some kind of time/date field. In this case, I assume that it is either _time (which you would have been droppeing) or AuditUnixTime, so this:

| inputlookup ga_kvstore_lookup
| where AuditUnixTime>=relative_time(now(),"-mon@mon")
| fields _key, AppName, AuditorName, AuditDateTime, ComplianceCheck
| rename _key AS KeyID
| sort 0 -AuditUnixTime

You always filter as soon as possible, so move the where all the way up.
Never use table in the middle (use fields instead); table pulls all events back to the Search Head, killing Indexer benefit.
Always use sort #, ( sort 0 usually) because the default is sort 100, which will bite you.
Never use eval when rename will do; saves CPU and RAM.
Perhaps the lookup has a _time field so try where _time instead of where AuditUnixTime first.
Also, AuditUnixTime is probably a stirng instead of a time_t (AKA epoch), so you will surely need to do a | eval AuditUnixTime = strptime(AuditUnixTime, "Some Format Here") before the | where.

[gabarrygowin]

Hey Woodcock,

Thanks for the reply.

To clarify, the contractor has these going to a keystore vice a normal lookup (as I'm used to).

When running any of the where to _time (which doesn't appear to be in his table) I get no results.

I don't want to write to the keystore with this new search, just present the information for the last week of audits.

Tried the following with '0 results'. If I run the originally reported search, I get ALL/EVERY entry every put into the kvstore.

| inputlookup ga_kvstore_lookup
| where AuditUnixTime>=relative_time(now(),"@Week")
| fields _key, AppName, AuditorName, AuditDateTime, ComplianceCheck
| rename _key AS KeyID
| sort 0 -AuditUnixTime

[woodcock]

I told you that you almost certainly will have to convert AuditUnixTime and yet you didn't do that and you didn't show us the format of that field. We need more information.

[skoelpin]

Where did you try adding where?/