Compare daily search results against expected table

[gabarrygowin]

Hello,

So I may be the victim of my own good deeds. Built an input form for the Infrastructure team to enter their daily system checks into Splunk with, and then it computes the daily results to the manager's dashboard as xx% complete.

Now manager would like a report that compares the list of daily "CheckPerformed" against another lookup we have called checkperformed.csv.

Here's the current working search:

Blockquote

| inputlookup GenAtomicsCheck.csv | where _time>=relative_time(now(),"@d") | search CheckStatus="Complete*" AND CheckType="Daily" | chart dc(CheckPerformed) AS Completed | eval percentage=Completed/13*100 | chart count by percentage

Blockquote

I need help computing the differenct between the total checks available in checkpeformed.csv and those that got accomplished in the search above.

Many, many thanks in advance!

Barry

[gabarrygowin · Accepted solution]

Thanks for the quick response Somesoni2!

I really have to be careful on this one and am dropping the knowledge a bit. When you say different field names, are you saying after the inputlookup checkperformed.csv | search CheckPerformed should be a different name?

Reason asking is that the first search is writing to GenAtomicsCheck.csv and the checkperformed.csv is a reference file for the form app (that you helped build). I trying to pull all the possible checkperformed from the cp.csv and compare those with the checks that the admins input in the first search.

Thanks for your patience.

[somesoni2]

After running the first search on GenAtomicsChecks.csv, you'd get one row with two columns- percentage and count. The appendcols would add the columns from second search to this, so the column names coming out of second search should be made different by using rename command, if not different already. If you could share what you'll calculate with cp.csv, I may come up with little more specific search.

[gabarrygowin]

No, I have that part working. I need to display which "CheckPerformed" did not get completed.

[somesoni2]

Seems you've completed the hard part. If I understand it correctly, this will give you list of CheckPerformed not yet completed (no available in GenAtomicsCheck.csv) today

| inputlookup checkperformed.csv | stats count by CheckPerformed
| table CheckPerformed | where NOT [| inputlookup GenAtomicsCheck.csv | where _time>=relative_time(now(),"@d") | stats count by CheckPerformed | table CheckPerformed ]

[gabarrygowin]

Hmm interesting. I'll give this a try tomorrow when I get back on the system.

Much appreciated!

[gabarrygowin]

Ok, I got curious about this. SO CLOSE now.

The new problem (there's always one), is that in the checkperformed.csv there are (Daily), (Weekly) and (Monthly) checks in the one column.

How can explicity only show the (Daily) in the results. I don't care about (Weekly) and (Monthly) at this time.

[gabarrygowin]

Got it!

| inputlookup checkperformed.csv | search CheckPerformed=Daily | stats count by CheckPerformed
| table CheckPerformed | where NOT [| inputlookup GenAtomicsCheck.csv | where _time>=relative_time(now(),"@d") | stats distinct_count by CheckPerformed | table CheckPerformed ]

Thanks so much Somesoni2!

[gabarrygowin]

Thanks. So the checkperformed.csv is hoped to remain a static file, as it is the primary reference file for the form to populate a pulldown with.

Here's the form code (for reference purposes):

Blockquote

S&I System Checks

<input type="dropdown" token="Administrator">
  <label>Step 1:  Administrator Performing Check:</label>
  <search>
    <query>| inputlookup splunkcheckadmins.csv | fields "Administrator" | dedup "Administrator"</query>
  </search>
  <fieldForLabel>Administrator</fieldForLabel>
  <fieldForValue>Administrator</fieldForValue>
  <prefix>Administrator="</prefix>
  <suffix>"</suffix>
</input>
<input type="dropdown" token="CheckPerformed">
  <label>Step 2:  System or Item Checked:</label>
  <search>
    <query>| inputlookup checkperformed.csv | fields "CheckPerformed" | dedup "CheckPerformed"</query>
  </search>
  <fieldForLabel>CheckPerformed</fieldForLabel>
  <fieldForValue>CheckPerformed</fieldForValue>
  <prefix>CheckPerformed="</prefix>
  <suffix>"</suffix>
</input>
<input type="radio" token="CheckType">
  <label>Step 3:  Checktype:</label>
  <default>Daily</default>
  <choice value="Daily">Daily</choice>
  <choice value="Weekly">Weekly</choice>
  <choice value="Monthly">Monthly</choice>
  <prefix>CheckType="</prefix>
  <suffix>"</suffix>
</input>
<input type="radio" token="CheckStatus">
  <label>Step 4:  Check Status:</label>
  <default>Complete, No Issues noted</default>
  <choice value="Complete, No Issues noted">Complete, No Issues noted</choice>
  <choice value="Complete, Issue Identified and being worked">Complete, Issue Identified</choice>
  <choice value="Not Completed">Not Completed</choice>
  <prefix>CheckStatus="</prefix>
  <suffix>"</suffix>
</input>
<input type="text" token="Findings">
  <label>Step 5:  Findings:</label>
  <default>Add Findings Here</default>
  <prefix>Findings="</prefix>
  <suffix>"</suffix>
</input>


<panel>
  <table>
    <search>
      <query>| makeresults |eval _time=now() |eval $Administrator$ | eval $CheckPerformed$  | eval $CheckType$ | eval $CheckStatus$ | eval $Findings$ | table _time, Administrator, CheckPerformed, CheckType, CheckStatus, Findings | outputlookup append=true GenAtomicsCheck.csv</query>
    </search>
    <option name="count">10</option>
    <option name="refresh.display">progressbar</option>
  </table>
</panel>

Blockquote

Thanks!

[somesoni2]

What all fields you get (and what values they contain) when you run | inputlookup checkperformed.csv ?

I just re-read your question text. Does the checkperformed.csv has say 100 checks, and say GenAtomicsCheck.csv has 90 checks, do you want to calculate how many checks have been completed (which'll be 90% in this example)?

[somesoni2]

You can use appendcols command to add your calculated field from checkpeformed.csv (separate subsearch) to your current result, like this

| inputlookup GenAtomicsCheck.csv | where _time>=relative_time(now(),"@d") | search CheckStatus="Complete*" AND CheckType="Daily" | chart dc(CheckPerformed) AS Completed | eval percentage=Completed/13*100 | chart count by percentage
| appendcols [| inputlookup checkpeformed.csv |..your logic to calculate check performed based on data for this lookup.. make sure to give different field names else it'll overwrite existing fields..]