I have a database that has a trend table. In the trend table there are two entries for each ticket: One for open date and one for closed date. I want to find the open tickets that do not have an entry for closed. If I use a join or append, then I am essentially doing the search twice: one to find open tickets and one to find closed tickets, then compare and show only tickets that do not have closed date. I have almost 2 million tickets on the table. How would I find open events that do not have a corresponding closed event with only one search? Would it make more sense to do this during the SQL query? | dbquery "TicketDB" "SELECT "TicketID" FROM Trend WHERE ChangeType NOT like "closed" AND ChangeType like "open" " this didn't work ... View more

I am doing a search from two databases and comparing data from both. I am using the appenccols command to get the data from both databases in one row so that I can do math on the data like this. [| dbquery "db1" "SELECT" | join type left [dbquery "db2" "SELECT"]| fields db1_count notindb2 | appenccols [| dbquery "db2" "SELECT" | join type left [dbquery "db1" "SELECT"]| fields db2_count notindb1 | | eval db1_total=db1_count + notindb1 | eval db2_total=db2_count + notindb2 | eval confidence=tostring(round(db1_total/db2_total*100,2))."%" this gives me a table db1_count notindb1 db2count notinbd2 db1_total db2_total confidence 126 126 236 15 252 251 99.60% I would like to split up the one row to look like this: database_name count notindb total db1 126 126 252 db2 236 15 251 confidence 99.60% Any suggestions would be appreciated ... View more

I have this search that is a very sparse search over 7 days and takes a long time to complete (more than ten hours) . eventtype=wineventlog_windows host_list=1 | eval Host=if(isnull(Host), upper(host), upper(Host)) | stats count by Host | eval _key = Host | outputlookup windows_event_hosts.csv This search is using a lookup file to tag systems as being on the host_list I checked and there are 147 systems tagged and more than three times as many that are not on the list. how can I reduce the time to run this search? Some ideas I had were to limit to known indexes, Change from eventtype to sourcetype, run a subsearch to get the host names and do a search on just the host names I don't quite know what will happen if I check "Accelerate this search" box or if the data will be returned correctly in the dashboards. I don't know how accelerating the scheduled search will impact resources like CPU, Memory, Disk Space? ... View more

Any ideas what this warning is telling me? message from "python /opt/splunk/etc/apps/Splunk_TA_vmware/bin/ta_vmware_collection_scheduler.py" WARNING:splunk.rest.format:There was an error parsing the feed document. Error: Input is not proper UTF-8, indicate encoding these messages are coming from my indexer not my search head. PS installed the APP and copied all the VMWare TA's to the indexers. I guess that is right but why would the Collection Scheduler be running on the indexer and should I turn it off? ... View more

My Firewall guys run this report every day to get data out of the firewall index="firewall" source_zone_name="*" destination_zone_name="*" policy_name="*" | stats count by "source_zone_name", "destination_zone_name", "policy_name" | sort "source_zone_name", "destination_zone_name", "policy_name" I ran this report for 1 minute of time in the recent past in "Fast Mode" and it took more than 1 minute to get the return. The 24 hour report takes almost 7 hours to run and returned 25M records from 195M records and shows 1330 resultant rows I don't know what would be the best way to set this up to have the lowest impact on the system so that they could login and get the report whenever they like without taking too much time. Also they manually combine the daily reports into one large report for monthly activity. ... View more

I am using dbquery to join two tables and display the results. Unfortunately, both tables use the column "NAME" I want to use the AS function in SQL to rename the column from one DB so that it shows correctly in splunk like this | dbquery "MAIN" "SELECT m.NAME AS HOST_NAME, s.ID AS GROUP_ID, s.NAME AS GROUP_NAME FROM MAIN.MACHINE m JOIN SYS.MUID s on m.uid=s.uid" the "AS" clause in the SQL is not renaming the field, so splunk picks the first NAME field it sees and does not show the other NAME field in the row. This should be a simple SQL command, but it doesn't seem to be working with Splunk DB Connect? ... View more

I have a form dashboard that return results in a table format with potently more than 10,000 rows. I have been asked if i can show the total number of results at the top of the table I have this results view <panel> <title>Hardware Lookup</title> <table> <title>Matching events</title> <option name="count">10</option> </table> </panel> I found this answer by @sideview In a dashboard panel, how would I get the result count to show (with paging)? I just could not find where to put this code in the HTML to make it work What option can I use or what module to show the total count of rows returned for the form search? Oh an were in the HTML would I put the module. I know where to put the option. ... View more

I have a query that looks through 55 databases using a UNION command that looks like this: | dbquery "MyDatabase" "(SELECT * FROM ORG2.MACHINE) UNION (SELECT * FROM ORG3.MACHINE) UNION (SELECT * FROM ORG4.MACHINE) UNION (SELECT * FROM ORG5.MACHINE) UNION (SELECT * FROM ORG6.MACHINE) UNION (SELECT * FROM ORG7.MACHINE) UNION (SELECT * FROM ORG8.MACHINE) UNION (SELECT * FROM ORG9.MACHINE) UNION (SELECT * FROM ORG10.MACHINE) UNION (SELECT * FROM ORG11.MACHINE) UNION (SELECT * FROM ORG12.MACHINE) UNION (SELECT * FROM ORG13.MACHINE) UNION (SELECT * FROM ORG14.MACHINE) UNION (SELECT * FROM ORG15.MACHINE) UNION (SELECT * FROM ORG16.MACHINE) UNION (SELECT * FROM ORG17.MACHINE) UNION (SELECT * FROM ORG18.MACHINE) UNION (SELECT * FROM ORG19.MACHINE) UNION (SELECT * FROM ORG20.MACHINE) UNION (SELECT * FROM ORG21.MACHINE) UNION (SELECT * FROM ORG22.MACHINE) UNION (SELECT * FROM ORG23.MACHINE) UNION (SELECT * FROM ORG24.MACHINE) UNION (SELECT * FROM ORG25.MACHINE) UNION (SELECT * FROM ORG26.MACHINE) UNION (SELECT * FROM ORG27.MACHINE) " I can add the search to find one particular machine like this | search IP=xxx.xxx.xxx.xxx I would like to know from which of the 55 databases the system came from so I can look up more information from one of the other tables in that database related to that system. How can i enrich the output to show the database name that the systems come from? ... View more