I'd like to create a base search in a report that will allow me to do a stats count against the different type of values searched for ie... Disk_Error_Count, Power_issue_Count, Severity_Major_Count etc.....
| search ("*$CHKR*" "*disk*") OR "*Power_Issue*" OR "*Severity: Major*" OR "*Processor Down*" OR "*TEST Msg" OR "Report Delivery Failure"
outputting the values to a lookup.csv
I'm trying to prevent the report having to hit the index for the individual counts.
I have a dashboard that will then output the counts for visualization.
I have used a basesearch before in a dashboard. What I'm trying to achieve is a scheduled report outputting multiple count values to a lookup.csv. I would then use the values of the lookup.csv to utilise in a traffic light type dashboard as well as a secondary dashboard drilled down from the traffic light dashboard.
1. Report runs every 5 to 10 minutes loading a lookup.csv
2. Traffic Light Dashboard utilizes the lookup.csv to decide Green(ok) Red (Bad)
3. Secondary Dashboard drilled down from Traffic Light Dashboard will show multiple panels using the lookup.csv to highlight the area of concern in one of the panels.
4. Drilldown from secondary Dashboard Panel of concern to look at events of concern.
the lookup.csv will have Error/Issue counts columns (ie..Ingestion count, system error count, backup error count etc...
So I basically want to load this lookup.csv from the scheduled report but only having to hit the index once.
You should give a good unique name for that lookup.csv like my_own_lookup_for_dashbords.csv (probably some better name) where you populate data on scheduled report. If you want that also other users can use it then you must define it on settings and share it inside that app where you have those dashboards and saved search to populate it.
On dashboards use it just like any other lookups in your query, like
| inputlookup my_own_lookup_for_dashbords.csv | ....
This should work that way if I understood correctly your needs?
I have no issue creating my lookup.csv and I have no issue outputting to my lookup.csv and I do not have any issue retrieving into my dashboard from the lookup.csv.
What I can't work out is how to have my search within my scheduled report access the index 1 time (ie... a basesearch) and retrieve multiple count values which I then populate my lookup.csv with.
Scheduled report sample... System_Error_Cnt1 and System_Error_Cnt2 do not show a count but System_error_Cnt3 does.
| search "IGNORE THIS" OR ("*$CHKR*" "*disk*")
| stats count as System_Error_Cnt1
| search "*Power_Issue*" OR ("*$CHKR*" "*NOT RUNNING*")
| stats count as System_Error_Cnt2
| search "*Severity: Major*" OR "*Processor Down*" OR "*TEST DRDOWN" OR "Incident Report Delivery Failure"
| stats count as System_Error_Cnt3
| table System_Error_Cnt1 System_Error_Cnt2 System_Error_Cnt3
System_Error_Cnt1 System_Error_Cnt2 System_Error_Cnt3
Probably now I have gotten what's your issue ,-) This is little bit complicated than I think.
Basically you cannot do like you have in you SPL example. Stats can use only those columns which it can find and it produce only those as an output to the next phase. Like after first stats you have only column System_Error_Cnt1 in use, nothing else. So you cannot search "xyz" after that as you haven't _raw anymore.
Have you any fields which has those values (like "Power_Issue" or "Severity: Major") or are all those free text?
If last one then you could try to use rex to mach those to named fields.
After that you use one stats like
... | stats sum(eval(isnotnull(field1))) as System_Error_Cnt1 sum(eval(isnotnull(field2))) as System_Error_Cnt2 ....
If those texts are in fields already you can use those in previous example. Or probably you need to combine both of those options.
It's hard to give you an exact SPL as I haven't your sample events to check and test it.
I appreciate your feedback.
The data is free text, so I will try to work out the regex command to isolate the values I'm searching for into field names and then try your suggested stats command.
Can you show me how you would create rex commands to create fields from the below search, seems tricky for an old Cobol programmer like myself. So I need 10 fields, one for each search item I am looking for.
OR ("*$CHKR*" "*disk*")
OR ("*$CHKR*" "*NOT RUNNING*")
OR "*Severity: Major*"
OR "*Processor Down*"
OR "*TEST DRDOWN"
OR "Incident Report Delivery Failure"
OR "*link pulse is down*"
OR "*CLIM Link Pulse Down*"
Long time when I've using Cobol last time (9x?) 😉
You can try this
.... | rex max_match=0 "(?<f_ignore>IGNORE THIS)|(?<f_chkr>\$CHKR)|(?<f_disk>disk)|(?<f_power>Power_Issue)|(?<f_not_run>NOT RUNNING)|(?<f_severiry>Severity: Major)|(?<f_proc>Processor Down)|(?<f_test>TEST DRDOWN)|(?<f_inc>Incident Report Delivery Failure)|(?<f_link>link pulse is down)|(?<f_clim>CLIM Link Pulse Down)"
Then you have those f_xyz fields defined by event if those texts have found and if not then those fields are NULL on that event.
You can see this on regex101.com by https://regex101.com/r/JC6Wy3/1
I'll have a play with this, but think it does not quite meet my needs.
I need to return 10 fields with a count of how many matches have occurred, which I will have my report output to my alert_status_lookup.csv
f_ignore f_chkr f_disk f_power f_not_run f_severiry f_proc f_test f_inc f_link f_clim
0 0 2 3 0 1 0 0 0 0 0
I then will have my dashboard reference the alert_status_lookup.csv.