help correct eval command

[olawalePS]

Please help me correct the command below. It keeps returning all the devices as no even though the app is installed.

index="jamf" sourcetype="jssUapiComputer:computerGeneral"
| dedup computer_meta.serial
| rename computerGeneral.lastContactTime AS lastContactTime
| eval timestamp = strptime(lastContactTime, "%Y-%m-%dT%H:%M:%S.%3QZ")
| eval sixtyDaysAgo = relative_time(now(), "-60d") 
| where timestamp>sixtyDaysAgo
| eval installed=if(computer_meta.serial IN [ search index="jamf" computer_meta.managed="true" sourcetype="jssUapiComputer:app" app.name="VMware CBCloud.app"| fields computer_meta.serial], "Yes", "No")
| table computer_meta.name, installed

[olawalePS]

The results remained the same.

[gcusello]

Hi @olawalePS ,

are you sure about the time format?

could you share a sample of your logs?

Ciao.

Giuseppe

[olawalePS]

So I have attached to images

Computers that have checked-in in less than 60 days (274)

The subset of that that has CBC installed (270)

What I want now is a query to identify the 4 devices that do not have the app installed

[gcusello]

Hi @olawalePS ,

the issue is probably related to the time format: you have different formats in yout data: 1,2 or 3 digits in milliseconds, probably your eval command correctly extracts data only when it matchjes the correct format.

You sould try to normalize your data, sometimes like this:

| eval 
   timestamp1=strptime(lastContactTime,"%Y-%m-%dT%H:%M:%S.%NZ"),
   timestamp2=strptime(lastContactTime,"%Y-%m-%dT%H:%M:%S.%2NZ"),   
   timestamp2=strptime(lastContactTime,"%Y-%m-%dT%H:%M:%S.%3NZ")
| eval timestamp=coalesce(timestamp1,timestamp2,timestamp3)

Ciao.

Giuseppe

[gcusello]

Hi @olawalePS,

rename computer_meta.serial

index="jamf" sourcetype="jssUapiComputer:computerGeneral"
| dedup computer_meta.serial
| rename computerGeneral.lastContactTime AS lastContactTime computer_meta.serial AS computer_meta_serial
| eval timestamp = strptime(lastContactTime, "%Y-%m-%dT%H:%M:%S.%3QZ")
| eval sixtyDaysAgo = relative_time(now(), "-60d") 
| where timestamp>sixtyDaysAgo
| eval installed=if(computer_meta_serial IN [ search index="jamf" computer_meta.managed="true" sourcetype="jssUapiComputer:app" app.name="VMware CBCloud.app"| rename computer_meta.serial AS computer_meta_serial | fields computer_meta_serial], "Yes", "No")
| table computer_meta.name, installed

or use single quotes (').

I prefer to rename fields, even if is longer.

ciao.

Giuseppe

[olawalePS]

Renaming it did not change the results.

[ITWhisperer]

Try something like this

index="jamf" sourcetype="jssUapiComputer:computerGeneral"
| dedup computer_meta.serial
| rename computerGeneral.lastContactTime AS lastContactTime
| eval timestamp = strptime(lastContactTime, "%Y-%m-%dT%H:%M:%S.%3QZ")
| eval sixtyDaysAgo = relative_time(now(), "-60d") 
| where timestamp>sixtyDaysAgo
| eval installed=if(computer_meta.serial IN ([ search index="jamf" computer_meta.managed="true" sourcetype="jssUapiComputer:app" app.name="VMware CBCloud.app"| stats count by computer_meta.serial
    | eval search="\"".'computer_meta.serial'."\""
    | stats values(search) as search
    | eval search = mvjoin(search,",")]), "Yes", "No")
| table computer_meta.name, installed