Hi,
trying to run DB query (db connect1) on dashboard using the following command:
<panel>
<title>My title</title>
<single>
<title>Nmy title</title>
<searchString> | dbquery "DBNAME"" SELECT * FROM XXX Where CreatedDate >=dateadd(d,datediff(d,0, getdate()),-7) and CreatedDate<dateadd(d,datediff(d,0, getdate()),0)" | stats count </searchString>
</single>
</panel>
getting the error
Encountered the following error while trying to update: In handler 'views': Error parsing XML on line 95: Couldn't find end of Start Tag dateadd line 95
how can i count event from the last week not using sql syntax?
Change you query to this
| dbquery "DBNAME" "SELECT * FROM XXX Where CreatedDate >=dateadd(d,datediff(d,0, getdate()),-7) and CreatedDate<dateadd(d,datediff(d,0, getdate()),0)" | stats count
If you are trying to add this in the "edit source" section you might need to change the >'s to > and so on. The easier way is to just go back to the actual dashboard and hit "edit panels" then "edit search string" for the panel you need the query in. When you save it from their Splunk will automatically change any characters it needs to for it to work with XML.
Here is what your query looks like when I did it like I stated above.
<dashboard>
<label>test</label>
<row>
<panel>
<single>
<search>
<query>| dbquery "DBNAME" "SELECT * FROM XXX Where CreatedDate >=dateadd(d,datediff(d,0, getdate()),-7) and CreatedDate<dateadd(d,datediff(d,0, getdate()),0)" | stats count</query>
<earliest>-15m</earliest>
<latest>now</latest>
</search>
<option name="drilldown">none</option>
<option name="colorBy">value</option>
<option name="colorMode">none</option>
<option name="numberPrecision">0</option>
<option name="showSparkline">1</option>
<option name="showTrendIndicator">1</option>
<option name="trendColorInterpretation">standard</option>
<option name="trendDisplayMode">absolute</option>
<option name="useColors">0</option>
<option name="useThousandSeparators">1</option>
<option name="linkView">search</option>
</single>
</panel>
</row>
</dashboard>