Dashboards & Visualizations

How to edit my search to show a row for every day in my results.

frostyflamez
Explorer

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 *

Labels (1)
0 Karma
1 Solution

bowesmana
Champion

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)

 

View solution in original post

0 Karma

bowesmana
Champion

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)

 

View solution in original post

0 Karma

ITWhisperer
Ultra Champion

Your large fields command doesn't include _time so the stats may not have anything to work with?

frostyflamez
Explorer

I added _time to the fields and received the same result

0 Karma

ITWhisperer
Ultra Champion

At what point do you get zero results? Try removing a line at a time until you find out when the results return.

0 Karma

frostyflamez
Explorer

When I remove _time and | bin span=1d, it returns results.

0 Karma

ITWhisperer
Ultra Champion

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?

0 Karma

Tune In & Win!

Don't miss out on your
chance to take home free
prizes by helping our players
save the Splunk Cloudom!

Dungeons & Data
Monsters: Splunk O11y
Day Editions Games
stream live:
5/4 at 6:30pm PST
5/5 at 7:00pm PST
on