How to use a timestamp value from a lookup.csv as the earliest time marker in a report search?

[auzark]

I am trying to use a timestamp field from a lookup.csv as the earliest time marker, but it will not set the value of earliest when the report runs.   Can you help, please.  The earliest and latest always use the defaul preset value.

index=a4_designeng_generic_app_audit_prd sourcetype=cba:designeng:nonstop:syslog

[| inputlookup  Production_Health_Status.csv
   | tail 1
    | eval earliest=Status_Check_Timestamp    <--- this value is being set each time the report runs.
    | fields earliest
]

! Invalid value "09/28/2022 13:06:00" for time term 'earliest'

[gcusello]

Hi @auzark,

timestamp is in epochtime, your value from lookup doesn't, you have to convert it:

index=a4_designeng_generic_app_audit_prd sourcetype=cba:designeng:nonstop:syslog
[| inputlookup  Production_Health_Status.csv
   | tail 1
    | eval earliest=strptime(Status_Check_Timestamp,"%m/%d/%Y %H:%M:%S")
    | fields earliest
]

Ciao.

Giuseppe

[auzark]

Hi  Giuseppe,

Thanks for  your suggestion. I no longer get the invalid value error, however the "earliest" time modifier does not use the value of Status_Check_Timestamp from my lookup.csv????   It uses the value from preset (ie... last 15 min)

Sample value of Status_Check_Timestamp - 09/28/2022 18:56:00

[gcusello]

Hi @auzark,

what's the result of the subsearch?

it should be only one field: earliest.

Ciao.

Giuseppe

[auzark]

Hi Giuseppe,

My apologies for my delayed response.

The results of the sub search is the value of "earliest", however, I am wanting to use this value as the search time window for a report.

earliest=Status_Check_Timestamp  latest=(now)

[gcusello]

Hi @auzark,

if you rename your subsearch fields as earliest and latest, and you use the fields command (that says use only these fields for the correlation) they are used in the main search as time period:

index=a4_designeng_generic_app_audit_prd sourcetype=cba:designeng:nonstop:syslog
[| inputlookup  Production_Health_Status.csv
   | tail 1
    | eval earliest=strptime(Status_Check_Timestamp,"%m/%d/%Y %H:%M:%S"), latest=now()
    | fields earliest latest
]

 Ciao.

Giuseppe

[auzark]

Hi Giuseppe,

I've used that exact syntax and got the below error.

index=a4_designeng_generic_app_audit_prd sourcetype=cba:designeng:nonstop:syslog
[| inputlookup NonStop_Production_Health_Status.csv
| tail 1
| eval earliest=strptime(Status_Check_Timestamp,"%m/%d/%Y %H:%M:%S"), latest=now()
| fields earliest latest
]

Error in 'search' command: Unable to parse the search: 'AND' operator is missing a clause on the left hand side.

The search job has failed due to an error. You may be able view the job in the 

[gcusello]

Hi @auzark,

at first check if the subsearch returns the correct values (two timestamps in epochtime).

then check if there's a space before the square parenthesis of the subsearch.

At least check that the field name Status_Check_Timestamp is correct and check its format.

Ciao.

Giuseppe

[auzark]

Hi Giuseppe,

I have fixed my issue by using the | return command as per below.

index=a4_designeng_generic_app_audit_prd sourcetype=cba:designeng:nonstop:syslog

[| inputlookup NonStop_Production_Health_Status.csv

| tail 1

| eval earliest=strptime(Status_Check_Timestamp,"%m/%d/%Y %H:%M:%S"), latest=now()

| fields earliest latest

| return earliest=$earliest

]

[gcusello]

Hi @auzark,

it should work also without return command.

Anyway, I'm happy that you solved your issue.

If one answer solves your need, please accept one answer for the other people of Community or tell us how we can help you.

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated by all the Contributors;-)

[bowesmana]

That's odd - can you look at the search.log in the job inspector and look to see if there are any errors or if the search parser is showing something that doesn't look right.