The following search works well, but I'm not able to get it to occur for daily results with each day getting a row in the results. How can I get it to provide daily results?
index=cdb_summary source=CDM_*_Daily_Summary sourcetype=vuln_summary OR (sourcetype=assetmanager_summary devicetype!=npvw
[| inputlookup IT_Systems where Status="Operational" OR Status="Modification"
| fields fismaid] ) fismaid=*
| fields asset_id,hostname,ipaddress,ec2_instance_id,devicetype,os,everSeenBy,firstSeen,lastScan,last_scan_attempt_scandate,last_scan_attempt_credentialed,Credentialed_Scan,lastSeen,fismaid,CriticalCount,HighCount,Criticals,Highs,TotalAssetScore ,AuthenticationRequired,CriticalAssetScore,HighAssetScore
| stats values(*) as * by asset_id
| eval elast=strptime(lastSeen,"%Y-%m-%d %H:%M:%S")
| where elast>=relative_time(now(),"@mon")
| eval AutoFail=if(AuthenticationRequired=="1" AND (Credentialed_Scan=="false" OR isnull(Credentialed_Scan)),1,0)
| eval CriticalPoints=CriticalCount*12, HighPoints=HighCount*4
| eval CriticalPoints=if(CriticalPoints>60,60,CriticalPoints), HighPoints=if(HighPoints>40,40,HighPoints)
| eval PointsLost=CriticalPoints + HighPoints
| eventstats sum(PointsLost) as TotalPointsLost,sum(TotalAssetScore) as TotalScore, dc(asset_id) as TotalAssets, sum(AutoFail) as AutoFailTotal, count(eval(isnull(Credentialed_Scan))) as MissingScans
| eval Percent_to_AutoFails=round(AutoFailTotal/TotalAssets*100)
| eval Percent_MissingScans=round(MissingScans/TotalAssets*100)
| eval Percent_PointsLost=round(TotalPointsLost/TotalAssets)
| eval VULN_Score= round(TotalScore/TotalAssets)
| lookup IT_Systems fismaid output Title , hva
| replace 1 with "True", 0 with "False" in hva
| rename hva as "High Value Assets"
| table VULN_Score, Percent_MissingScans,TotalAssets,Percent_PointsLost
| stats values(*) as *
I tried adding | bin and by bucket to get the below search, which returns zero results, but does match on events.
index=cdb_summary source=CDM_*_Daily_Summary sourcetype=vuln_summary OR (sourcetype=assetmanager_summary devicetype!=npvw
[| inputlookup IT_Systems where Status="Operational" OR Status="Modification"
| fields fismaid] ) fismaid=* | bin span=1d _time
| fields asset_id,hostname,ipaddress,ec2_instance_id,devicetype,os,everSeenBy,firstSeen,lastScan,last_scan_attempt_scandate,last_scan_attempt_credentialed,Credentialed_Scan,lastSeen,fismaid,CriticalCount,HighCount,Criticals,Highs,TotalAssetScore ,AuthenticationRequired,CriticalAssetScore,HighAssetScore
| stats values(*) as * by asset_id by _time
| eval elast=strptime(lastSeen,"%Y-%m-%d %H:%M:%S")
| where elast>=relative_time(now(),"@mon")
| eval AutoFail=if(AuthenticationRequired=="1" AND (Credentialed_Scan=="false" OR isnull(Credentialed_Scan)),1,0)
| eval CriticalPoints=CriticalCount*12, HighPoints=HighCount*4
| eval CriticalPoints=if(CriticalPoints>60,60,CriticalPoints), HighPoints=if(HighPoints>40,40,HighPoints)
| eval PointsLost=CriticalPoints + HighPoints
| eventstats sum(PointsLost) as TotalPointsLost,sum(TotalAssetScore) as TotalScore, dc(asset_id) as TotalAssets, sum(AutoFail) as AutoFailTotal, count(eval(isnull(Credentialed_Scan))) as MissingScans
| eval Percent_to_AutoFails=round(AutoFailTotal/TotalAssets*100)
| eval Percent_MissingScans=round(MissingScans/TotalAssets*100)
| eval Percent_PointsLost=round(TotalPointsLost/TotalAssets)
| eval VULN_Score= round(TotalScore/TotalAssets)
| lookup IT_Systems fismaid output Title , hva
| replace 1 with "True", 0 with "False" in hva
| rename hva as "High Value Assets"
| table VULN_Score, Percent_MissingScans,TotalAssets,Percent_PointsLost
| stats values(*) as *
This is wrong
| stats values(*) as * by asset_id by _time
should be
| stats values(*) as * by asset_id _time
i.e. it thinks you are splitting by 3 fields (asset_id, by, _time)
This is wrong
| stats values(*) as * by asset_id by _time
should be
| stats values(*) as * by asset_id _time
i.e. it thinks you are splitting by 3 fields (asset_id, by, _time)
Your large fields command doesn't include _time so the stats may not have anything to work with?
I added _time to the fields and received the same result
At what point do you get zero results? Try removing a line at a time until you find out when the results return.
When I remove _time and | bin span=1d, it returns results.
Are you saying that
index=cdb_summary source=CDM_*_Daily_Summary sourcetype=vuln_summary OR (sourcetype=assetmanager_summary devicetype!=npvw
[| inputlookup IT_Systems where Status="Operational" OR Status="Modification"
| fields fismaid] ) fismaid=*
gives you results but
index=cdb_summary source=CDM_*_Daily_Summary sourcetype=vuln_summary OR (sourcetype=assetmanager_summary devicetype!=npvw
[| inputlookup IT_Systems where Status="Operational" OR Status="Modification"
| fields fismaid] ) fismaid=* | bin span=1d _time
doesn't?
If so, what field has your time value in?