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
The results remained the same.
Hi @olawalePS ,
are you sure about the time format?
could you share a sample of your logs?
Ciao.
Giuseppe
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
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
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
Renaming it did not change the results.
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